The Rent Tracker Spreadsheet That Actually Works (When You Own 2–10 Units)

Landlord Ledger · Guides for landlords with 2–10 units

If you self-manage a few rental units, you've probably built — or downloaded — a rent tracker spreadsheet that looked fine in January and became a lie by June.

A cell that says $1,200 in the "May" column. Paid in full? Paid late? Two partial payments and a promise? The spreadsheet doesn't know, so eventually neither do you. And when a tenant disputes their balance in October, "I think she caught up in July" is not an answer you can stand on.

Here's what a decade of small-landlord spreadsheet wreckage teaches: the problem isn't spreadsheets. Spreadsheets are the right tool at 2–10 units — property management software runs $20–50 a month to solve problems you mostly don't have yet. The problem is how most rent trackers are designed.

The one design decision that fixes everything

Bad rent trackers make you type amounts into a month grid. Good rent trackers make you log payments like a bank statement — one row per payment, with a date, a unit, an amount, and which month it applies to — and then calculate the grid from that log.

That one change is the difference between a spreadsheet you trust and one you argue with:

• Partial payments stop being a problem. Tenant pays $600 on the 3rd and $550 on the 28th? Two rows. The month's cell shows $1,150 collected against $1,150 due, automatically.
• Catch-up payments land in the right month. A payment received in May for April's rent applies to April — so April stops showing as missed and May doesn't show a mysterious surplus.
• Late payments flag themselves. If the payment date is after your grace day, the row gets marked late. You're not remembering; the log is.
• You get an audit trail. Every dollar has a date, a method, and a note. Disputes end quickly when the record is boring.

What the rent roll should show (without you touching it)

Once payments live in a log, the classic rent roll — units down the side, months across the top — becomes something the spreadsheet draws for you, color-coded: green paid in full, amber partial, red month started with nothing received, gray vacant.

Two seconds of looking replaces twenty minutes of reconstructing. And "what's owed" should come from the lease terms (start date, end date, rent amount), not from you re-typing rent into twelve cells — so a mid-year rent increase or a tenant turnover changes the math exactly once.

The numbers worth watching

A small landlord's dashboard needs about six numbers: collected year-to-date against scheduled, collection rate, outstanding balances (by unit, so you know who), occupancy, who's occupied today, and late payments to date — the early-warning light. A tenant who drifts from paying on the 1st to the 9th is telling you something months before they miss.

If your spreadsheet shows less than that, you're flying blind. If it shows much more, you'll stop opening it.

Three mistakes that kill rent trackers by summer

1. One tab per unit. Five units, five tabs, zero overview. Everything above assumes one payment log and one roll — the whole portfolio on one screen.

2. Formulas you can type over. One accidental keystroke in a total cell in March and every number after it is quietly wrong. Formula cells should be locked; entry cells shouldn't be.

3. Excel-only or Sheets-only. You'll eventually want the other one — on your phone, with your partner, with a bookkeeper. Whatever you use should work in both.

Build it or buy it?

You can absolutely build this yourself — payment log, lease-driven rent roll, conditional formatting, locked cells, a dashboard. Budget a weekend, plus a January afternoon when the year rolls over, and test it with deliberately messy fake data before you trust it: partial payments, a mid-year increase, a tenant who skips March.

Either way: log payments, don't type grids. It's the one habit that keeps a rent tracker honest all year.

This article is educational information, not financial or legal advice.