Build a Rent Roll That Flags Late Rent Automatically (Plus a Deposit Ledger)

Short on time? The Rental Property Tracker ($19) is the finished, tested version of what this tutorial builds.

A rent roll answers one question every month: who hasn't paid? Here's a version that answers it by itself, using nothing but standard formulas that work in Excel and Google Sheets.

The layout

A Rent Roll tab with one row per lease:

Put three settings somewhere at the top right:

• H1: As-of date. Use =TODAY(), or type a date to check a past month.
• H2: Due day (e.g., 1).
• H3: Grace days (e.g., 5).

Rent payments live in your Transactions tab: date in column A, unit in column B, category in C, amount in D.

Received this month

D2: =SUMIFS(Transactions!$D:$D, Transactions!$B:$B, A2, Transactions!$C:$C, "Rent",
            Transactions!$A:$A, ">="&DATE(YEAR($H$1),MONTH($H$1),1),
            Transactions!$A:$A, "<="&EOMONTH($H$1,0))

It sums every Rent payment for this unit dated inside the as-of month.

The status formula

E2: =IF(D2>=C2, "Paid",
     IF($H$1>DATE(YEAR($H$1),MONTH($H$1),$H$2+$H$3),
        IF(D2>0, "Partial - LATE", "LATE"),
        IF(D2>0, "Partial", "Due")))

Read it top to bottom:

• Fully paid → Paid.
• Otherwise, if we're past the due day plus grace days → LATE (or Partial - LATE if some money came in).
• Otherwise, still inside the grace period → Due or Partial.

Add conditional formatting: red when the cell contains "LATE", green for "Paid".

Use an as-of cell instead of TODAY() inside the formula. You can then type any date into H1 to see how the roll looked on the 10th of last month, which is handy for disputes.

A deposit ledger that always balances

A Deposits tab with one row per tenant:

N() treats empty cells as zero, so the formula works before anything is refunded. Add a total with =SUM(E2:E100) and compare it with the balance of the account where you actually keep deposits. Some states require a separate account and charge penalties for missing deadlines, so write the return deadline next to each move-out.

Common mistakes

• Leases that change mid-month. Give each lease its own row with start and end dates, and only count months inside the lease.
• Late fees counted as rent. Use a separate Late fee category. Otherwise a tenant who paid a fee but not the rent shows as "Paid".

Done-for-you version

The Rental Property Tracker has this rent roll built for 50 leases:

• lease start and end dates
• 12 monthly columns
• year-to-date expected vs. received
• the deposit ledger, linked to a dashboard and Schedule E summary

General information only. Landlord-tenant rules vary by state and city.

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.