“Pro rata,” meaning “in proportion,” is a common calculation used across various fields. Whether splitting household bills, calculating commissions, or adjusting budgets, understanding prorate calculations is crucial. Fortunately, we can easily calculate Pro Rata in Excel.
Pro Rata Formula
The basic prorate formula utilizes three main variables:
Number of units used: The specific units for which the prorated amount is required.
Total number of units: The total number of units the amount covers.
Total amount: The entire amount to be distributed.
The formula is:
Pro Rata Amount = Total Amount * (Number of Units Used / Total Number of Units)
Example:
Let’s say your friend rented an apartment with a monthly rent of $1,200. Your friend moves out on the 15th of the month. To calculate their prorated rent for the half-month they occupied the apartment, you can use the following formula:
Pro Rata Rent = $1,200 * (15 days / 30 days) = $600
Calculating Pro Rata in Excel: A Detailed Example
1. Setting Up Your Excel Sheet
Step 1: Open a new Excel workbook.
Step 2: In cell A1, type Number of Units Used. This cell will hold the specific units for which the prorated amount is required (e.g., the number of days used).
Step 3: In cell B1, type Total Number of Units. This cell will contain the total units the amount covers (e.g., total days in a month).
Step 4: In cell C1, type Total Amount. This will be the cell where you enter the entire amount to be distributed.
Step 5: In cell D1, type Pro Rata Amount. This cell will display the calculated pro rata amount.
2. Entering Data and Formulas
Step 6: Enter the number of units used in cell A2. For instance, 15 days if your friend moved out mid-month.
Step 7: In cell B2, input the total number of units. If considering days in a month, this might be 30.
Step 8: Enter the total amount in cell C2. For example, 1200 for the total rent.
Step 9: In cell D2, enter the pro rata formula: =C2*(A2/B2). This formula calculates the pro rata amount based on the values in cells A2, B2, and C2.
3. Calculating the Pro Rata Amount
Step 10: After entering the formula in cell D2, press Enter. Excel will automatically calculate the pro rata amount. In our example, it will display 600, which is the prorated rent for 15 days.
4. Formatting for Clarity
Step 11: You can format the cells for currency to make it more readable. Right-click on cells C2 and D2, choose ‘Format Cells’, and then select ‘Currency’.
5. Adjusting and Reusing the Formula
Step 12: This setup allows you to adjust the numbers in cells A2, B2, and C2 for different scenarios, and the formula in D2 will automatically update the pro rata amount.
Related Calculator:
