How to Calculate Pro Rata Share in Excel (with 2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


What Is Pro Rata Share?

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.


How to Calculate Pro Rata Share in Excel: 2 Practical Examples

1. Calculation of Pro Rata Share for Employee of a Company

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.

How to Calculate Pro Rata Share in Excel

  • 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.

How to Calculate Pro Rata Share in Excel

  • Use Fill Handle to AutoFill the rest cells.

  • Next, in the cell F5 in the Amount column, insert the following formula:
=E5*$C$13

Where,
E5 = the fraction amount of the working days
C13 = The annual salary

How to Calculate Pro Rata Share in Excel

  • Now, press ENTER to have the pro rata share for that employee.

  • Finally, AutoFill the rest to finish the pro rata share calculation.

How to Calculate Pro Rata Share in Excel


2. Calculation of Pro Rata Share for House Rent

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) + 1

Here, 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.

How to Calculate Pro Rata Share in Excel

  • Next, press ENTER to have the number of days.

  • AutoFill the rest cells using Fill Handle.

How to Calculate Pro Rata Share in Excel

  • In cell F5, input the following formula to find the amount to be paid as rent.
=E5/SUM($E$5:$E$10)*$C$13

Here, 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.

How to Calculate Pro Rata Share in Excel

  • AutoFill the rest to find the other renters’ fees.


Practice Section

You can practice here for more expertise.

How to Calculate Pro Rata Share in Excel

Download Practice Workbook


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.


<< Go Back to Dividend | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo