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

Example 1 – Calculation of Pro Rata Share for Employee of a Company

The sample dataset below will be used to calculate the annual salary of the employees of a company based on their working days throughout the year.

Steps:

Enter the information to Employee Name, From and To columns.
Two additional columns named Year Fraction and Amount are added.

In cell E5 insert the following formula:

=YEARFRAC(C5,D5,1)

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.

Press ENTER to get the fraction.

Use Fill Handle to AutoFill the rest cells.

In 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

Press ENTER to get the pro rata share for that employee.

AutoFill the rest to finish the pro rata share calculation.

Example 2 – Calculation of Pro Rata Share for House Rent

Steps:

Enter the information to Renter Name, From, and To columns.
Two additional columns named Days and Amount to be Paid are added.

Input the following formula in cell E5 to count the number of days.

=DAYS(D5,C5) + 1

The DAYS function counts the number of days from the dates mentioned in cells D5 and C5. It adds the value with 1 as the starting day of renting is counted as a day.

Press ENTER to get 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$13

The number of days mentioned in cell E5 is divided by the total number of days stayed in that house by all the renters. That fraction is multiplied by the annual rent to calculate the individual rent.