Rent Late Fee Calculator Spreadsheet: Grace Period, Flat + Daily Fees and a Cap (Excel & Google Sheets)

Updated 2026-10-09 · Sheet & Flow

Short on time? The Landlord Tenant Toolkit ($39) is the finished, tested version of what this tutorial builds.

Late fees are where small landlords most often get the math wrong. One tenant pays on the 6th and gets charged when the lease says the grace period runs through the 6th. Another is three weeks late and the daily fee has quietly passed the maximum your lease (or your state) allows. Both mistakes are easy to make by hand and easy to avoid with four formulas.

This tutorial builds a rent ledger that calculates days late, the late fee, the balance and a status for every charge. It works the same in Excel, Google Sheets and LibreOffice. We tested every formula below by building a workbook with these exact formulas, recalculating it in LibreOffice, and comparing every cell to the worked example and to an independent Python calculation (plus 200 randomized rows).

1. A Setup tab with your late fee rule

Make a tab called Setup:

	A	B
1	As-of date	2026-10-20
2	Grace period (days)	5
3	Flat late fee	50
4	% of rent	0%
5	Daily fee after grace	10
6	Maximum fee (0 = no cap)	150

B1 is the date the ledger is measured from. Type a date for a monthly review, or use =TODAY(). The fee rule reads: once rent is more than B2 days late, charge B3 + B4 × rent + B5 for each day past the grace period, but never more than B6. Use the parts your lease uses and leave the others at 0. A lease with "$75 if rent is late" is B3 = 75 with everything else 0. A lease with "5% of the monthly rent" is B4 = 5%.

2. The Ledger tab

One row per charge (usually one per unit per month), starting in row 2. The first five columns are typed:

	A	B	C	D	E
1	Due date	Unit	Rent	Paid so far	Date paid in full
2	2026-10-01	101 Maple	1450	1450	2026-10-01

Paid so far is everything the tenant has paid toward this charge, including any late fee they paid. Date paid in full is the day the rent was fully paid. Leave it blank while any rent is still owed, because the clock keeps running until then.

3. The calculated columns

F2:  =IF(A2="","",MAX(0,IF(E2<>"",E2,Setup!$B$1)-A2))
G2:  =IF(F2="","",IF(F2<=Setup!$B$2,0,ROUND(IF(Setup!$B$6>0,MIN(Setup!$B$6,Setup!$B$3+Setup!$B$4*C2+Setup!$B$5*(F2-Setup!$B$2)),Setup!$B$3+Setup!$B$4*C2+Setup!$B$5*(F2-Setup!$B$2)),2)))
H2:  =IF(A2="","",C2+G2-N(D2))
I2:  =IF(A2="","",IF(H2<=0,"Paid",IF(N(D2)>=C2,"Fee owed",IF(A2>Setup!$B$1,"Upcoming",IF(F2>Setup!$B$2,"LATE",IF(N(D2)>0,"Partial","Due"))))))

Fill all four down as far as you have charges (blank rows stay blank). What each one does:

F, Days late: the date the rent was paid in full minus the due date. While rent is still owed, it counts to the as-of date instead. MAX(0, …) keeps early payments and future charges at 0.
G, Late fee: 0 while the payment is inside the grace period. After that, the flat fee plus the percentage plus the daily fee for each day past the grace period, capped at the maximum, rounded to cents. One fee per charge row.
H, Balance: rent plus late fee minus everything paid. N() treats an empty "paid" cell as 0.
I, Status: Paid when nothing is owed; Fee owed when the rent is paid but the late fee isn't; Upcoming before the due date; LATE once past the grace period; otherwise Partial or Due.

To color the rows, select the Ledger data and add a conditional-formatting rule with the custom formula =$I2="LATE" (red) and =$I2="Fee owed" (amber).

How the grace period lines up with the calendar

With rent due on the 1st and B2 = 5, rent paid on the 6th is 5 days late and has no fee. Paid on the 7th, it's 6 days late and the fee starts. That's why the formula uses "more than the grace period" (F2>Setup!$B$2). Lease wording like "rent is late after the 5th" is different: a payment on the 6th is already late, so that lease needs B2 = 4. Read yours and set B2 to the number of days late that are still free.

4. A per-row cap (when the maximum depends on the rent)

Some rules cap the fee as a share of the rent, so the maximum differs by unit. Put the cap in column J and use this version of G2 instead:

G2 (per-row cap in J):  =IF(F2="","",IF(F2<=Setup!$B$2,0,ROUND(MIN(J2,Setup!$B$3+Setup!$B$4*C2+Setup!$B$5*(F2-Setup!$B$2)),2)))
J2 (lesser of $50 or 5% of rent):  =MIN(50,5%*C2)
J2 (12% of rent):  =12%*C2

Here are three state rules, as examples of how to translate statute wording into B2 and the cap. This is how we read the text. Check the current law, your city's rules and your lease before charging anything.

State	Rule (summary)	Grace `B2`	Cap
Colorado	No fee unless rent is late by at least 7 calendar days; fee can't exceed the greater of $50 or 5% of the past-due rent (C.R.S. 38-12-105)	6	greater of $50 or 5% of the past-due rent
New York	No fee unless rent wasn't paid within 5 days of the due date; fee can't exceed $50 or 5% of the monthly rent, whichever is less (NY Attorney General, Real Property Law §238-a)	5	`=MIN(50,5%*C2)`
Texas	No fee unless rent has remained unpaid two full days after the due date; a fee of up to 12% of the rent (4 or fewer units) or 10% (more than 4 units) is considered reasonable, and an initial fee plus daily fees count as one late fee (Tex. Prop. Code §92.019)	2	`=12%C2` or `=10%C2`

Colorado's "late by at least 7 days" means 7 days late is the first day a fee is allowed, so the number of free days is 6. Texas's "two full days after the due date" means a fee can start on the third day late, so the grace setting is 2.

5. A small dashboard

On a Dashboard tab:

B1:  =SUMIFS(Ledger!$H$2:$H$200,Ledger!$A$2:$A$200,"<="&Setup!$B$1)
B2:  =SUM(Ledger!$G$2:$G$200)
B3:  =COUNTIF(Ledger!$I$2:$I$200,"LATE")
B4:  =COUNTIF(Ledger!$I$2:$I$200,"Fee owed")

Label them in column A: Owed now, Late fees assessed, Charges LATE, Rent paid, fee still owed. B1 only counts charges that are already due, so next month's rent doesn't inflate what you're owed today.

Worked example

As-of date October 20, 2026, grace period 5 days, $50 flat fee, 0%, $10 per day after the grace period, maximum $150:

Unit	Due	Rent	Paid so far	Paid in full	Days late	Late fee	Balance	Status
101 Maple	2026-10-01	1,450	1,450	2026-10-01	0	0.00	0.00	Paid
102 Maple	2026-10-01	1,395	1,395	2026-10-06	5	0.00	0.00	Paid
103 Maple	2026-10-01	1,395	1,395	2026-10-07	6	60.00	60.00	Fee owed
12 Pine	2026-10-01	1,500	1,590	2026-10-10	9	90.00	0.00	Paid
7 Birch A	2026-10-01	1,300	0		19	150.00	1,450.00	LATE
7 Birch B	2026-10-01	1,250	800		19	150.00	600.00	LATE
9 Cedar	2026-10-15	1,100	500		5	0.00	600.00	Partial
9 Cedar	2026-11-01	1,100	0		0	0.00	1,100.00	Upcoming

Dashboard: $2,710.00 owed now (the November charge isn't due yet, so it's left out), $450.00 in late fees assessed, 2 charges LATE and 1 with the rent paid but the fee still owed.

What the example shows:

The grace boundary is one day wide. 102 Maple paid on the 6th (5 days late, no fee). 103 Maple paid one day later and owes $50 + $10 × 1 = $60.
The cap does real work. 7 Birch A is 19 days late, so the uncapped fee would be $50 + $10 × 14 = $190. The $150 maximum applies.
A partial payment doesn't stop the clock. 7 Birch B paid $800 but still owes rent, so it's 19 days late too and carries the same capped fee: $1,250 + $150 − $800 = $600.
Paying the fee closes the row. 12 Pine paid on the 10th (9 days late, $50 + $10 × 4 = $90) and paid $1,590 in total, so the balance is 0 and the status is Paid.

Common mistakes

Counting from the wrong day. "Days late" here is the payment date minus the due date. If your lease counts the grace period differently, change B2, not the formula.
Letting daily fees run past the maximum. Always fill in B6 (or column J) if your lease or law sets a cap. A blank cap means no cap.
Charging a fee on top of a fee. This sheet calculates one late fee per charge. Some states (Colorado, for example) restrict charging more than one late fee per late payment, so don't add a second late-fee row for the same month.
Dates typed as text. If column F shows #VALUE!, a date was pasted as text. Re-enter it as a real date (in Google Sheets, Format → Number → Date).
A late fee your lease doesn't mention. Most rules require the fee to be in the written lease. The spreadsheet can't check that for you.

Want it done for you?

The Landlord Tenant Toolkit is a finished workbook with a rent ledger that calculates late fees from the same kind of rule (grace period, flat, percent, daily and a cap), plus a move-out inspection that feeds a security deposit itemization with the return deadline, a tenant screening scorecard, a turnover-vs-renewal calculator, a maintenance log and a dashboard. It comes with a filled-in 4-unit sample and works in Excel and Google Sheets with no macros.

Not legal advice. Late fee limits, grace periods and notice rules depend on your state, city and lease. Check them before charging a late fee.

Free tool: Free Rental Property Calculator: Cash Flow, Cap Rate, Cash-on-Cash, DSCR

Written with AI assistance. Every formula, command and number on this page was tested before publishing (see each section). Product links go to our store.