Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to prorate an amount in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=A2/B2*C2✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Full Amount | Total Days | Days Active | Prorated |
| 1200 | 30 | 15 | |
| 5000 | 31 | 20 | |
| 3600 | 28 | 7 | |
| 2400 | 30 | 30 |
=A2/B2*C2→600
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Column A = Full Amount, Column B = Total Days in Period, Column C = Days Active.
- 2In D2 enter =A2/B2*C2 to get the prorated amount.
- 3For a monthly prorate, B2 can reference DAY(EOMONTH(date,0)) to get the actual days in the month.
- 4Round if needed: =ROUND(A2/B2*C2,2).
Tips
- For mid-month starts, use EOMONTH and the start date to dynamically calculate the remaining days: =A2*(DAY(EOMONTH(StartDate,0))-DAY(StartDate)+1)/DAY(EOMONTH(StartDate,0)).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I prorate an annual amount to a partial year?
Divide by 365 (or 366 in a leap year) and multiply by days active: =AnnualAmount/365*DaysActive.
Should I use actual days or calendar days?
Most billing uses actual calendar days. For business-day prorating, use NETWORKDAYS to count working days instead.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.