The Rent Tracker Spreadsheet That Actually Works (When You Own 2–10 Units)
A rent tracker works when you log payments as dated rows and calculate the month grid from them — and fails when you type amounts into a month grid by hand. That single design choice is what makes partial payments, catch-up payments and mid-year rent increases behave correctly.
For 2–10 units, a spreadsheet is the right tool: property management software is priced for portfolios ten times larger. What you need is one payment log, one lease-driven rent roll, and about six summary numbers.
The short version
- One row per payment, with the month it applies to — never one cell per month.
- Drive what's owed from the lease terms, so a rent increase changes the math in one place.
- Color-code the roll: green paid, amber partial, red missed, gray vacant.
- Lock formula cells so a stray keystroke can't silently break a year of totals.
- Six numbers is a dashboard; twenty is a spreadsheet you stop opening.
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 goes wrong, and why it's the design rather than the tool: 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 rate, days vacant year-to-date, 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.
Skip the build-it-yourself weekend
Landlord Ledger Toolkit includes all of this, pre-built and checked — the Rent Tracker, the Schedule E Expense Log, and the rest of the bundle: 4 Excel workbooks, 3 editable Word templates and a printable PDF, for landlords with 2–10 units. Excel + Google Sheets, one-time purchase.
Either way: log payments, don't type grids. It's the one habit that keeps a rent tracker honest all year.
Questions landlords ask about this
Is a spreadsheet good enough for tracking rent on a few units?
For 2 to 10 units, yes. Property management software is designed and priced for much larger portfolios, and the problems it solves — owner statements, portals, large-scale accounting — are mostly not yours yet. What matters is the design: a dated payment log with a calculated rent roll, rather than a hand-typed month grid.
How do I record a partial rent payment in a rent tracker?
As its own dated row for the exact amount received, tagged to the month it applies to. The month then shows the total collected against the total due automatically, and the balance stays outstanding until it is paid.
How do I handle a payment that arrives late, for the previous month?
Tag the payment row with the month it applies to rather than the month it arrived. April then stops showing as missed and May does not show a surplus — which is exactly what a month grid cannot express.
What should a small landlord's rent dashboard show?
Collected year to date against scheduled, collection rate, outstanding balances by unit, occupancy rate, days vacant year to date, and late payments to date. That last figure is the early warning: a tenant drifting from the 1st to the 9th is signaling months before they miss.
Should each unit get its own tab?
No. Separate tabs remove the portfolio view that makes the tracker useful, and multiply the places a formula can break. Keep one payment log and one rent roll for everything, and let filtering do the separating.
This article is educational information, not financial or legal advice.