How to track what tenants owe in a spreadsheet
Every month, a landlord needs one answer: who owes what, and since when? A single "paid" cell per month can't answer it once partial payments, credits and late fees come in. This page shows how to track what each tenant owes in a sheet you build yourself, for 1 to 10 units: one balance per lease, how old that balance is, and a short list to work from each month. Near the end, this page describes Rentlark, the spreadsheets sold on this site, so you can judge whether it fits.
For the full record-keeping routine, see the guide: How to track rent and expenses in a spreadsheet.
What "owed" means
For each lease, what's owed is everything charged so far minus everything paid so far. Three things keep that number honest:
- Count only charges that are due. Next month's rent isn't owed until its due date.
- Keep rent and late fees apart. One combined number hides whether the tenant is behind on rent or only on a fee.
- Leave security deposits out. A deposit isn't a rent payment, so it shouldn't lower the balance.
Start from a ledger
Record every charge and every payment as its own row, oldest at the top, with these columns: A Date, B Lease ID, C Type (Rent charge, Late fee, Payment, Credit), D Charge, E Payment and F Note. Use a lease ID (for example OAK-1-2026) rather than the tenant's name, so a new lease or a shared name never mixes two balances. The page on setting up a rent ledger walks through a similar ledger step by step, but with separate Unit and Tenant columns, so its column letters differ. The formulas on this page use the lease-ID layout above; if you built that one, adjust the letters.
If a lease starts or ends partway through a month, you can work out that month's rent charge with the prorated rent calculator.
To total unpaid rent across several months, the back rent calculator builds a month-by-month table. For a fee on a late payment, use the late rent fee calculator.
One balance per lease
On a summary tab, one row per lease:
| A Lease | B Charged | C Paid | D Owed |
|---|---|---|---|
| OAK-1-2026 | Formula | Formula | Charged minus paid |
With the ledger on a tab called Ledger (B is the lease ID, D the charge and E the payment), the formulas in row 2 are:
- Charged (B2):
=SUMIFS(Ledger!D:D, Ledger!B:B, A2) - Paid (C2):
=SUMIFS(Ledger!E:E, Ledger!B:B, A2) - Owed (D2):
=B2-C2
SUMIFS is available in Google Sheets and in Microsoft Excel 2016 or later. Above zero means the tenant owes; below zero means a credit (they've paid ahead or overpaid). To split rent from late fees, add a second condition on the Type column (Ledger column C) to the Charged formula.
How old is each balance?
A balance alone doesn't say whether it's a few days or several months old. A simple way to find out is to apply each lease's payments to the oldest unpaid month first. Then whatever is still open always sits in the most recent months.
On a small grid for one lease, one row per month:
| A Month | B Rent due | C Due to date | D Open |
|---|---|---|---|
| 2026-08 | (from the lease) | Running total | Formula below |
Put the lease's total rent paid so far in G1. Then, in row 2, copied down:
- Due to date (C2):
=SUM($B$2:B2) - Open (D2):
=MIN(B2, MAX(0, C2-$G$1))
A month shows zero once payments cover it. The first month with an open amount is how far back the balance goes, so you can see at a glance whether a tenant is behind on one month or on three. If your lease, or your state or city, says how payments must be applied, follow that instead; the rules vary.
Partial payments, credits and bounced payments
- Partial payment: enter the amount received as its own payment row. The balance stays above zero by the difference. The page on partial rent payments covers this in more detail.
- Overpayment: record the full amount. The balance goes below zero, which is a credit toward the next charge.
- Bounced check or refund to the tenant: add a negative payment row, dated the day the money went back, with a note. Don't delete the original row.
Late fees
Add a late fee as its own charge row, and only if your lease provides for it. Rules on late fees, and on accepting partial rent, vary by state and city, so check yours.
The monthly "owed" list
Once a month:
- Post the month's rent charges and every payment received.
- Match the payment rows to your bank statement.
- Filter the summary tab to Owed above zero, and look at each lease's oldest open month.
- In the Note column, write down anything the tenant said, for example "remainder promised by the 20th".
Before you send any notice about unpaid rent, check your state's and city's rules. This page doesn't cover them. The free one-page checklist covers the full monthly routine: Download the Small landlord monthly record-keeping checklist (PDF).
If you'd rather not build it yourself: Rentlark
Rentlark is a set of rent and expense tracking spreadsheets for owners of 1 to 10 units and house hackers, made by AI agents. It doesn't use the ledger layout above: rent is tracked in a Rent Status tab, a lease-by-month grid showing due, paid and balance. You log each payment by lease, and Rentlark applies each payment to the oldest unpaid month first; its "For month" column is for your reference only. What it includes:
- Tracks rent per unit and per lease. Designed for Google Sheets and Microsoft Excel 2016 or later.
- A Rent Status tab shows each lease month by month with statuses such as Paid, Paid late, Partial, Unpaid, Late, Credit and Vacant.
- Rent Status carries partial payments and credit forward to the next month for each lease.
- An Expenses tab where each cost is logged once.
- A Repairs tab with opened date, unit, issue, priority, vendor, status, closed date and cost.
- An Annual Summary that totals rent and expenses by property, a records summary for your tax preparer.
- Expense categories follow the IRS Schedule E expense lines, to help you organize records for your tax preparer. Not tax advice. Based on the IRS draft 2026 Schedule E; categories may be updated if the final form changes.
What it isn't: it tracks the rent you record; payments don't run through it, and it has no bank connections. It has no leases, notices or screening forms.
Price: the price on our Gumroad page. One-time purchase, no subscription. Free updates within version 1.x. 30-day refund through Gumroad. You must be 18 or older to buy. Gumroad may add sales tax or VAT at checkout.
FAQ
Should a security deposit count against what a tenant owes? Not in your running balance. Track deposits separately. Rules on when and how a deposit can be applied vary by state and city, so check yours.
What if a tenant says a payment is for a particular month? Write what they said in the Note column. In a sheet you build yourself, you choose how payments are applied. (In Rentlark, payments are applied to the oldest unpaid month first, and the "For month" column is for your reference only.)
Does a negative balance mean I owe the tenant money? It means they've paid more than has been charged so far. You can carry it as a credit toward the next charge, unless your lease or local rules say otherwise. If you return it instead, record the refund as a negative payment row.
Does Rentlark take rent payments from tenants? No. Rentlark tracks the rent you record. Payments don't run through it.
Which spreadsheet apps is Rentlark designed for? For Google Sheets and Microsoft Excel 2016 or later.
Who makes Rentlark? Rentlark is made by AI agents for seller Tomer Avni. Questions: rentlark@mail.grokbot.com.
Made by AI agents. Seller: Tomer Avni. Support: rentlark@mail.grokbot.com. Not tax or legal advice.
How this was made: this page was researched and written by AI agents for seller Tomer Avni.
Rentlark home · Rent and expense tracking guide · Monthly checklist (PDF) · Terms · Privacy · Refund policy