What is the formula for calculating pro rata?

The 7 basic Excel formulas are SUM, AVERAGE, COUNT, MIN, MAX, IF, and VLOOKUP (or COUNTA / TRIM depending on the specific introductory list).

1. SUM

• Formula: =SUM(A1:A10)
• Purpose: Adds all numbers in a range of cells together.

2. AVERAGE

• Formula: =AVERAGE(A1:A10)
• Purpose: Calculates the mathematical mean (average) of a group of numbers.

3. COUNT

• Formula: =COUNT(A1:A10)
• Purpose: Counts how many cells in a range contain numbers.

4. MIN

• Formula: =MIN(A1:A10)
• Purpose: Finds the smallest number in a cell range.

5. MAX

• Formula: =MAX(A1:A10)
• Purpose: Finds the largest number in a cell range.

6. IF

• Formula: =IF(A1>50, "Pass", "Fail")
• Purpose: Performs a logical test, returning one value if true and another value if false.

7. VLOOKUP

• Formula: =VLOOKUP(LookupValue, Range, ColumnIndex, FALSE)
• Purpose: Searches for a value in the first column of a table and returns a value from another column in the same row.

You can learn more by checking out the Basic Excel Formulas Guide provided by the Corporate Finance Institute.

You can build a pro rata calculation sheet using Excel or Google Sheets by applying the core formula =(Days Active / Total Days) * Total Amount.

Essential Columns for Your Sheet

Set up your spreadsheet with these standard tracking columns:

• Item / Person: Name of the tenant, employee, or policy.
• Start Date: The beginning date of the partial period (e.g., move-in date).
• End Date: The final date of the partial period.
• Active Days (DAYS): Formula to count active days including the start date: =DAYS(End_Date, Start_Date) + 1.
• Total Period Days: The total number of days in the full cycle (e.g., 30 for a month, 365 for a year).
• Total Cost: The full price or salary amount (use absolute reference like _$D_$1).
• Pro Rata Share: Formula to calculate the final proportional amount: =(Active_Days / Total_Period_Days) * Total_Cost.

Quick Example: Prorated Rent

• Monthly Rent: $2,000
• Days Occupied: June 8 to June 30 = 23 days
• Days in Month: 30
• Calculation: (23 ÷ 30) × $2,000 = $1,533.33

To find a pro rata ratio or share, divide your specific portion or "true" item count by the maximum total quantity.

The Basic Formula

• Pro Rata Share = (Number of "True" Items) ÷ (Maximum Possible Quantity)
• Pro Rata Distribution = Pro Rata Share × Total Related Amount (such as total rent, dividends, or bonus)

Step-by-Step Calculation

1. Identify the true items: Find the specific amount you are measuring, such as days worked, days of rent used, or shares owned.
2. Identify the maximum total: Find the total possible amount for the full period or pool, such as 365 days in a year, 30/31 days in a month, or total shares outstanding.
3. Divide to find the ratio: Divide the true items by the maximum total to get a fraction or percentage.
4. Multiply by the total value: Multiply that ratio by the total pool of money or resource to find the final proportional amount.

Common Examples

• Rent/Lease: If rent is $1,000 for a 30-day month and you stay 10 days, your ratio is $1,000 ÷ 30 = $33.33 per day × 10 = $333.33.
• Shares/Dividends: If you own 100 shares out of 500 total shares (a 0.20 or 20% ratio) and the total dividend payout is $10,000, your share is 0.20 × $10,000 = $2,000.
• IPO Allotment: Pro-rata allotment ratios for oversubscribed shares equal total shares available divided by total shares applied for.

A salary of $75,000 pro rata means $75,000 is the full-time equivalent annual pay, but you will only receive a proportional amount based on the actual hours, days, or fraction of the year you work.

"Pro rata" is a Latin phrase that translates to "in proportion".

How Pro Rata Works for Salaries

If a job listing or contract states $75,000 pro rata, your actual pay depends on your specific working arrangement:

• Part-Time Hours: If full-time is 40 hours per week and you work 30 hours per week (75% of the time), your annual salary will be $56,250 ($75,000 × 0.75).
• Partial Year: If you only work part of the year—such as starting a 12-month position six months late—you receive a proportional slice of that annual rate for the time worked.

Common Scenarios

• Part-time schedules (e.g., working 3 days a week instead of 5).
• Fixed-term or contract roles lasting less than a full year.
• Mid-year starts or departures where employment covers only a fraction of the standard 12-month cycle.

To calculate a prorated amount in Excel based on partial days or a fraction of a period, use the Microsoft Excel Support guide or apply the standard daily proration formula: =(Total_Amount / Days_in_Period) * Active_Days.

Prorated Rent or Monthly Bill

To calculate a partial month's cost based on the exact number of days in that specific month:
=(Monthly_Cost / DAY(EOMONTH(Start_Date, 0))) * (DAY(EOMONTH(Start_Date, 0)) - DAY(Start_Date) + 1)

• Monthly_Cost: Cell containing the full monthly rate (e.g., rent).
• Start_Date: Cell containing the move-in or start date.
• EOMONTH finds the last day of the month to get total days automatically.

Annual or Long-Term Pro Rata (Using YEARFRAC)

To prorate an annual salary, subscription, or insurance premium over exact date ranges:
=YEARFRAC(Start_Date, End_Date, 1) * Annual_Amount

• Start_Date and End_Date: The active period.
• 1 tells Excel to use the actual/actual day count basis for the year.
• Annual_Amount: The full-year cost or salary.

Custom Day Counts Between Two Dates

If you have a strict start and end date and want to multiply by an exact daily rate:
=(End_Date - Start_Date + 1) * (Total_Amount / Total_Days_in_Year_or_Month)