Rent payment tracker spreadsheet: a late-status formula
A rent tracker should tell you who is late without you scanning every row. One nested IF gives each row a status, and a running balance shows what each unit owes.
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.
The status question
A rent log is only useful if it answers one question without you reading every row: who has paid, who is late, and who is still inside the grace period. Five statuses cover it. Paid is paid in full on time, Paid late is paid in full after the grace days, Partial is something paid but less than the rent, Late is nothing paid and past grace, and Due is nothing paid yet and not past grace.
The columns
One row per unit per month, data from row 5. A to F are typed; G to J are formulas. Two settings sit to the side: the as-of date in L1 (enter =TODAY(), or type a fixed date to see the sheet as it stood then) and grace days in L2.
- A Unit, B Month, C Due date, D Rent due
- E Amount paid, F Date paid
- G Status, H Days late, I Balance, J Running balance for the unit
The formulas
G5 Status =IF(D5="", "",
IF(E5>=D5, IF(F5-C5>$L$2, "Paid late", "Paid"),
IF(E5>0, "Partial",
IF($L$1-C5>$L$2, "Late", "Due"))))
H5 Days late =IF(D5="", "", MAX(0, IF(E5>=D5, F5, $L$1) - C5 - $L$2))
I5 Balance =IF(D5="", "", D5-E5)
J5 Running bal. =IF(D5="", "", SUMIFS($I$5:I5, $A$5:A5, A5, $C$5:C5, "<="&$L$1))Read the status formula from the outside in. No rent due, show nothing. Paid in full: compare the payment date with the due date plus grace. Otherwise, if anything was paid it is Partial. Otherwise nothing was paid, so it is Late once the as-of date is past due date plus grace, and Due before that. The order matters: Partial is checked before Late, so a half-paid month stays Partial however old it gets, while the days-late column still shows how old it is. If you set grace days to 0, Due turns to Late the day after the due date. If you record a payment but leave the date paid empty, a paid-in-full row shows Paid with 0 days late, so fill the date in.
Days late measures to the payment date for rows paid in full and to the as-of date for everything else. The balance is rent due minus paid, so an overpayment shows as a negative number. The running balance adds up that unit's balances from the top of the sheet down to the current row, counting only rows whose due date is on or before the as-of date, so a row for next month does not inflate what the tenant owes today.
Test cases
Grace days 5, as-of date March 10, rent $1,800. Type these rows in and check the formulas return what the table says. The first two rows test the edge of the grace period: March 6 is 5 days after the due date, which is inside grace, and March 7 is outside it.
| Case | Due date | Rent due | Paid | Status | Days late |
|---|---|---|---|---|---|
| Paid in full on March 6 | March 1 | $1,800 | $1,800 | Paid | 0 |
| Paid in full on March 7 | March 1 | $1,800 | $1,800 | Paid late | 1 |
| Half paid on March 2 | March 1 | $1,800 | $900 | Partial | 4 |
| Nothing paid | March 1 | $1,800 | $0 | Late | 4 |
| Nothing paid | March 8 | $1,800 | $0 | Due | 0 |
And one unit across three months, same as-of date, each month $1,800 due on the 1st:
| Month | Paid | Status | Days late | Balance | Running balance |
|---|---|---|---|---|---|
| January | $1,800 | Paid | 0 | $0 | $0 |
| February | $900 | Partial | 32 | $900 | $900 |
| March | $0 | Late | 4 | $1,800 | $2,700 |
February's rent was due 37 days before March 10; take off the 5 grace days and the half-paid month is 32 days late. To make the status column visible at a glance, add conditional formatting to column G: text equal to Late in red, Partial in amber, Paid in green.
What this does not cover
It assumes you enter each payment on the month's row it belongs to; it does not split one payment across months. It does not add late fees, send notices or decide what you may do about a late or partial payment, which depends on your lease and local rules. The five words are labels in your sheet, not legal categories. TODAY() changes every day, so type a fixed as-of date when you want a month-end snapshot you can keep.
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
- Prorated rent formula in Excel for a mid-month move-in
- 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 row per unit-month with a status of Paid, Paid late, Partial, Late or Due, days late, a late fee and a running balance. Each unit's rent and due day are listed on the Units & Leases tab. One Excel file, $29 one-time, no bank link and no account.