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

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

• Hit ENTER to get the rent to be paid by Vlahovic.

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

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF