Prorated rent formula in Excel for a mid-month move-in
Prorated rent is one division and one multiplication, but the day-count rule changes the answer. Here are both rules with a worked $1,800 example, and move-out too.
Free: Rent Due & Late Fee Calculator, one Excel tab. Confirm your email and the file arrives right away, plus an occasional note (at most one a week) on running a small rental. Unsubscribe in one click. Privacy.
General information only, not tax, legal or accounting advice. Late-fee, deposit and notice rules vary by state, city and lease; check your own. Tax references describe US federal pages on irs.gov as of the date above.
Pick the day-count rule first
Prorated rent is the monthly rent divided by a number of days, times the days the tenant occupies. Two rules are common and they give different answers: the actual number of days in that month, or a flat 30-day month. Your lease should say which one it uses and whether the move-in day counts as a day of occupancy. The examples below count the move-in day.
The formulas
Put the monthly rent in B1 and the move-in (or move-out) date in B2.
Actual days, move-in
Days in month =DAY(EOMONTH(B2, 0))
Days occupied =EOMONTH(B2, 0) - B2 + 1
Prorated rent =ROUND(B1 / DAY(EOMONTH(B2, 0)) * (EOMONTH(B2, 0) - B2 + 1), 2)
30-day month, move-in
Prorated rent =ROUND(B1 / 30 * (30 - MIN(DAY(B2), 30) + 1), 2)
Actual days, move-out (move-out day counts as occupied)
Prorated rent =ROUND(B1 / DAY(EOMONTH(B2, 0)) * DAY(B2), 2)EOMONTH(B2, 0) returns the last day of the date's month, so subtracting the move-in date and adding 1 counts both ends. It exists in Excel and Google Sheets and returns February 29 in leap years on its own.
Worked example: actual days
$1,800 rent, move-in March 15. March has 31 days and the tenant occupies the 15th through the 31st, which is 17 days. The daily rate is 1,800 / 31 = $58.0645..., and 58.0645... x 17 = 987.0967..., so the prorated rent is $987.10. Round only the final figure. Rounding the daily rate to $58.06 first gives $987.02, a few cents off.
The same move-in on a 30-day month
The daily rate is 1,800 / 30 = $60.00, and the 15th through the 30th is 30 - 15 + 1 = 16 days, so the prorated rent is $960.00. The MIN(DAY(B2), 30) part is there because without it a move-in on the 31st would count 30 - 31 + 1 = 0 days.
| Rule | Daily rate | Days | Prorated rent |
|---|---|---|---|
| Actual days (31) | $58.0645 | 17 | $987.10 |
| 30-day month | $60.00 | 16 | $960.00 |
The gap is $27.10 on this move-in, and the 30-day rule can look odd at the ends of short months: a move-in on February 28 in a 28-day February counts 30 - 28 + 1 = 3 days, which is $180.00, against 1/28 of the month, $64.29, on actual days. Neither rule is wrong as arithmetic. What matters is that the lease or move-in paperwork names the rule and whether the first day counts, so the tenant can recompute your number and get the same figure.
Move-out proration
The same arithmetic works from the other end of the month. If the tenant moves out on October 12, October has 31 days, and the move-out day counts as occupied, the days occupied is just the day number: 1,800 / 31 x 12 = $696.77. If the lease does not count the move-out day, use DAY(B2) - 1 and get 11 days, $638.71. Again, the lease decides.
Check your formula
Three move-in dates on actual days, with $1,800 rent, are worth typing in before you trust the sheet. The first and last day of the month should give the full rent and one day's rent, and a leap-year February should use 29 days.
| Move-in | Days in month | Days occupied | Prorated rent |
|---|---|---|---|
| March 1, 2026 | 31 | 31 | $1,800.00 |
| March 31, 2026 | 31 | 1 | $58.06 |
| February 10, 2028 | 29 | 20 | $1,241.38 |
Putting it in the ledger
For the move-in month you charge the prorated amount, and from the next month the full rent: $987.10 for March, then $1,800.00 for April, so $2,787.10 across the first two months in this example. Some leases skip proration altogether by making rent due on the move-in day of every month; if yours does, you need none of this.
What this does not cover
This is arithmetic only. It does not tell you whether proration is required or allowed where you rent, what happens to a security deposit, how to treat utilities, or what the tenant owes if they leave before the notice period ends. It also covers one rent amount; a mid-month rent change needs two prorated pieces. Check the lease and your state's landlord-tenant rules for those.
More landlord guides
- Security deposit ledger template for Excel: columns and a reconciliation check
- Schedule E expense categories spreadsheet: sorting receipts into lines 5-19
- Rental property vacancy rate spreadsheet: physical vs economic vacancy
- Rental property income and expense spreadsheet: a 12-month duplex log
- Rent roll template for Excel: what a small landlord's rent roll needs
- Rent payment tracker spreadsheet: a late-status formula
- Lease expiration tracker spreadsheet with a rent increase preview
- Late fee calculator in Excel: a formula with a grace period
- Duplex profit and loss per unit: splitting shared costs and finding NOI
Already built
The Landlord Rent & Expense Tracker's Rent Ledger has a prorated first-month column (monthly rent divided by the days in that month, times the days occupied) on a row per unit-month, while rent and lease start dates are listed on the Units & Leases tab. It uses the actual days in the month, so a 30-day-month lease or a move-out proration is not built in. One Excel file, $29 one-time, no bank link and no account.