How to Do a Prorate Calculation in Excel
By ExcelReady TeamUpdated 7 min read
The short answer
To prorate in Excel, multiply the full amount by the share of the period used: amount × days used ÷ days in the period. For rent from a move-in date in C2, use =ROUND(B2*(EOMONTH(C2,0)-C2+1)/DAY(EOMONTH(C2,0)),2). EOMONTH finds the month end, DAY counts the days in that month and ROUND gives a clean money amount.
Key takeaways
- Prorated amount = full amount × days used ÷ days in the period.
- Count days inclusively: end date minus start date plus 1.
- DAY(EOMONTH(date,0)) gives the number of days in any month, leap years included.
- Round the final amount to 2 decimals with ROUND so it matches what you actually charge or pay.
On this page+
A prorate calculation in Excel shares out a full amount by how much of the period was used. If someone has a room for 13 days of a 30-day month, they pay 13/30 of the rent. The formula is always the same idea: full amount × days used ÷ days in the period. This guide shows it for rent, salary and refunds, with the exact Excel formulas and results.
What does prorate mean?#
To prorate means to divide an amount in proportion to the time used. A monthly bill, a yearly salary or an annual plan is priced for a full period. When someone starts or stops part way through, you charge or pay only their share.
Prorated amount = Full amount × Days used ÷ Days in the periodExcel needs three numbers: the full amount, the days used and the days in the period. Dates make the last two easy, because Excel stores every date as a whole number. Subtracting one date from another gives the days between them.
How to prorate rent for a move-in date#
To prorate rent for a mid-month move-in, count the days from the move-in date to the end of the month, and divide by the days in that month.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Tenant | Monthly rent | Move-in date | Prorated rent |
| 2 | Flat 4B | 1200 | 18/11/2026 | ? |
In D2, type:
=ROUND(B2*(EOMONTH(C2,0)-C2+1)/DAY(EOMONTH(C2,0)),2)Here is what each part does:
| Part | What it does | Result |
|---|---|---|
EOMONTH(C2,0) | The last day of the move-in month | 30/11/2026 |
EOMONTH(C2,0)-C2+1 | Days from the 18th to the 30th, counting both | 13 |
DAY(EOMONTH(C2,0)) | The day number of the month end, so the days in the month | 30 |
B2*13/30 | The share of the rent | 520 |
ROUND(...,2) | Rounds to cents | 520.00 |
The result is 520. The +1 matters: the tenant lives in the flat on the 18th as well as the 30th. Without it, you'd count 12 days and charge 480. For more on counting days, see how to calculate days between dates.
How to prorate for a move-out date#
For a move-out, count from the 1st of the month to the move-out date. That count is just the day number of the date:
=ROUND(B2*DAY(D2)/DAY(EOMONTH(D2,0)),2)With 1,200 rent and a move-out on 10 February 2026, DAY(D2) is 10 and February 2026 has 28 days. The result is 1200 × 10 ÷ 28 = 428.57.
The same formula handles leap years. In February 2028, which has 29 days, the divisor becomes 29 automatically. The guide to finding the number of days in a month explains the DAY(EOMONTH()) trick in more detail.
How to prorate an annual salary#
To prorate a yearly amount, divide by the days in the year. Say an employee on 52,000 a year starts on 1 April 2026 and you need their pay to 31 December 2026, with the start date in C2 and the end date in D2:
=ROUND(B2*(D2-C2+1)/365,2)April to December is 275 days, so the result is 52000 × 275 ÷ 365 = 39,178.08.
You may also see YEARFRAC used here. =ROUND(B2*YEARFRAC(C2,D2,1),2) returns 39,035.62, which is lower. YEARFRAC measures the gap between two dates, so it counts 274 days and leaves out the end date. Neither is wrong; they follow different rules. Use the one that matches your policy, and see the YEARFRAC guide for its basis options.
Many payroll policies prorate by working days or by calendar months instead. Always check the rule before you build the formula.
Prorating by working days#
For a monthly salary prorated by working days, count Monday-to-Friday days with NETWORKDAYS. Say the monthly pay of 3,000 is in B2 and the start date of 18 November 2026 is in C2:
=ROUND(B2*NETWORKDAYS(C2,EOMONTH(C2,0))/NETWORKDAYS(EOMONTH(C2,-1)+1,EOMONTH(C2,0)),2)The top part counts the working days from the start date to the month end: 9. The bottom part counts all the working days in November 2026, from the 1st (EOMONTH(C2,-1)+1) to the 30th: 21. The result is 3000 × 9 ÷ 21 = 1,285.71. Add a list of public holidays as a third argument to NETWORKDAYS if your policy excludes them.
How to calculate a prorated refund#
A refund is the unused share of something already paid. Work out the days used, then refund the rest:
=ROUND(B2*(365-(D2-C2+1))/365,2)A one-year plan costing 240 starts on 1 January 2026 (C2) and is cancelled on 15 March 2026 (D2). Days used are 74 (31 + 28 + 15), so 291 days are unused. The refund is 240 × 291 ÷ 365 = 191.34.
How to show the prorated share as a percentage#
Sometimes you need the share itself, not the money. Leave out the amount:
=(EOMONTH(C2,0)-C2+1)/DAY(EOMONTH(C2,0))For the 18 November move-in, this returns 0.4333. Press Ctrl+Shift+% (Mac: ⌃⇧%) to show it as 43%, or use Increase Decimal for 43.3%. A percentage of a whole works just like the examples in the Excel percentage formula guide.
Common prorate mistakes and how to avoid them#
These cause most wrong prorated amounts:
- Missing the +1. End minus start counts the gaps between days, not the days themselves. Add 1 when both the first and last day count.
- Using 30 for every month. Unless your rule says 30, use
DAY(EOMONTH(date,0))for the real number of days. - Dates stored as text. If a date sits on the left of the cell, Excel may see it as text, and the formula returns #VALUE!. Retype it or convert it with DATEVALUE.
- Rounding too early. Round only the final amount. Rounding the share first, such as 0.43 instead of 0.4333, makes the money wrong.
- Splits that don't add up. When you prorate one bill across several people, each rounded share may leave the total a cent off. Give the last person the remainder: the full amount minus the sum of the others.
The guide to rounding numbers with ROUND, ROUNDUP and ROUNDDOWN explains when to round up instead, and the ROUND function guide has the syntax.
Practise it#
A prorate formula is just day counting plus a percentage plus rounding. Practise days between dates in a live spreadsheet: you type each formula and it checks it instantly. Then practise percentages and % change and practise ROUND, ROUNDUP and ROUNDDOWN so the money comes out exactly right.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
A tenant moves in on the date in C2 (18 November 2026) and the monthly rent is in B2. In D2, work out the prorated rent for November: =ROUND(B2*(EOMONTH(C2,0)-C2+1)/DAY(EOMONTH(C2,0)),2).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Prorated rent | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 |
Tip: EOMONTH(C2,0)-C2+1 counts the days from the 18th to the 30th. DAY(EOMONTH(C2,0)) is 30, the days in November.
Practise this in a lesson
Frequently asked questions
What is the formula for prorating in Excel?
Multiply the full amount by the days used and divide by the days in the period: =ROUND(Amount*DaysUsed/DaysInPeriod,2). For a month, DAY(EOMONTH(date,0)) gives the days in the period.
How do I calculate prorated rent in Excel?
With the monthly rent in B2 and the move-in date in C2, use =ROUND(B2*(EOMONTH(C2,0)-C2+1)/DAY(EOMONTH(C2,0)),2). For 1,200 rent from 18 November, that is 13 of 30 days, or 520.
How do I prorate an annual salary for part of a year?
Multiply the salary by the days worked divided by 365: =ROUND(B2*(D2-C2+1)/365,2), with the start date in C2 and the end date in D2. Some employers use working days or months instead, so check the policy you are applying.
Should I use 30 days or the actual days in the month?
It depends on your contract or policy. Actual days is the most common, and Excel calculates it with DAY(EOMONTH(date,0)). If the rule says 30 days, simply divide by 30.