Naimul Hasan Arif
Equal distribution has been a major issue probably from the start of time. The problem has not been solved yet properly. We may have found many ways to equal any particular things for purpose of equal distribution. Pro rata Share in Excel is just another way to equally distribute anything. In this article, I am going to explain how to calculate Pro rata share in Excel with two practical examples.
Download Practice Workbook
Pro rata often refers to a distribution in which each party or individual gets their just share in proportion to the whole. For example, we can consider dividend payments, which are cash payments made by firms to shareholders, are one area where pro rata calculations can be applied.
We can calculate the pro rata share for the employee of a company using Excel. In this section, we are gonna calculate the annual salary of the employees of a company based on their working days throughout the year. The whole process is described step by step in the following section.
Steps:
- First of all, I have gathered the information on the Employee’s name, starting day, till the day of counting salary and the company’s annual salary. Then, I decorated the information into Employee Name, From, and To columns.
- I have added two additional columns named Year Fraction and Amount.
- In the cell E5, I have applied the following formula:
=YEARFRAC(C5,D5,1)Here, the YEARFRAC function calculates the fraction of days between cell C5 and D5. 1 represents that the fraction is calculated considering the actual number of days of that year.
- Now, press ENTER to have the fraction.
- Use Fill Handle to AutoFill the rest cells.
- Next, in the cell F5 in the Amount column, insert the following formula:
=E5*$C$13Where,
E5 = the fraction amount of the working days
C13 = The annual salary
- Now, press ENTER to have the pro rata share for that employee.
- Finally, AutoFill the rest to finish the pro rata share calculation.
Read More: How to Calculate Intrinsic Value of a Share in Excel
In the case of pro rata share calculation for house rent, we do not follow the same procedure as the annual salary pro rata calculation process. At the time of house rent calculation, each renter has to give money based on his resident days and the total of all the renters combines the total rent.
Steps:
- Firstly, I have gathered the information on Renter’s name, starting day of living in the house, till the day of counting rent and the annual rent. Then, I decorated the information into Renter Name, From, and To columns.
- I have added two additional columns named Days and Amount to be Paid.
- Then, input the following formula in cell E5 to count the number of days.
=DAYS(D5,C5) + 1Here, the DAYS function counts the number of days from the dates mentioned in cells D5 and C5. It then added the value with 1 as the starting day of renting is also counted as a day.
- Next, press ENTER to have the number of days.
- AutoFill the rest cells using Fill Handle.
- In cell F5, input the following formula to find the amount to be paid as rent.
=E5/SUM($E$5:$E$10)*$C$13Here, the number of days mentioned in cell E5 is divided by the total number of days stayed in that house by all the renters. Then, that fraction is multiplied by the annual rent to calculate the individual rent.
- Hit ENTER to have the rent to be paid by Vlahovic.
- AutoFill the rest to find the other renters’ fees.
Read More: How to Calculate Market Share in Excel (4 Related Examples)
Practice Section
You can practice here for more expertise.
Conclusion
That’s all for the article. In this article, I have tried to explain the whole procedure of how to calculate Pro rata share in Excel with two practical examples. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our Exceldemy site for more details on Excel.
