How-to
Rent Payment Tracker Spreadsheet for Landlords
Build a rent payment tracker in Google Sheets or Excel: the columns, a running-balance formula, a partial-payment example, and when to outgrow a spreadsheet.
The Resida Team · Published
A rent payment tracker is a ledger with one row for every charge and every payment, plus a running balance that shows what a tenant still owes. In Google Sheets or Excel you need seven columns and one formula. The sections below give you both, a worked example with a partial payment and a late fee, and the habits that keep it accurate.
The columns
Make one tab per lease and give each tab these columns:
| Column | What goes in it |
|---|---|
| A. Date | The day the charge was due or the payment arrived |
| B. Description | "November rent", "Payment", "Late fee", "Credit: repair reimbursement" |
| C. Charged | Amount the tenant owes for this line (blank for payments) |
| D. Paid | Amount received (blank for charges) |
| E. Balance | What the tenant owes after this line |
| F. Method | Zelle, check, cash, bank transfer, other |
| G. Note | Confirmation number, check number, "paid half now, rest Friday" |
Name the tab for the tenant and unit.
The running-balance formula
In the first data row (row 2), enter this in E2:
=C2-D2
In every row after that, E3 and down, enter:
=E2+C3-D3
Each balance is the previous balance, plus anything charged on this line, minus anything paid. Copy the formula down the column. A balance of 0 means the tenant is paid up, a positive number is what they owe, and a negative number is a credit in their favor.
To see the current balance in one cell, put =SUM(C:C)-SUM(D:D) in a cell outside columns C and D (for example I1), or on a summary tab point a cell at each tab's total.
A worked example
Here is a made-up lease at $1,200 a month, with a $50 flat late fee that the lease provides for after a grace period:
| Date | Description | Charged | Paid | Balance | Method |
|---|---|---|---|---|---|
| Nov 1 | November rent | 1,200 | 1,200 | ||
| Nov 3 | Payment | 700 | 500 | Zelle | |
| Nov 6 | Late fee | 50 | 550 | ||
| Nov 8 | Payment | 550 | 0 | Check #1042 | |
| Dec 1 | December rent | 1,200 | 1,200 |
Three things to notice. The partial payment on November 3 is its own row, so nothing is overwritten. The late fee is its own line, so the tenant and you can both see exactly what it was. And the ledger tells the whole story without anyone remembering it.
Habits that keep it accurate
- Add the rent charge on the due date. Don't wait for payment. A charge with no payment against it is what shows a tenant is behind.
- Log every payment the day it arrives. Partial ones too. The longer you wait, the more likely you are to forget which deposit was whose.
- Record the method and a confirmation. A Zelle note or a check number helps settle "I paid that" disagreements.
- Keep rent separate from other money. Put security deposits, repair reimbursements and fees on their own lines with their own descriptions, so the rent history stays readable.
- Reconcile with your bank once a month. Compare the month's payments in the ledger with the deposits in your account. Anything on one side and not the other is a mistake or a missing entry.
- Ask tenants to pay one way. One electronic method is much easier to track than a mix of cash, checks and transfers.
What the ledger is for at tax time
Rent you receive is income, and a clean ledger is the record behind it. Keep it alongside your expense records; the guide to tracking rental expenses covers the other side of Schedule E. On how long to keep records, the IRS says to keep them until the period of limitations for the return expires, which is generally 3 years from when you filed. It is 6 years if you left out income of more than 25% of the gross income on the return. Records for the building and improvements are property records, and the IRS says to keep those until the period of limitations expires for the year you dispose of the property.
When a spreadsheet stops being enough
A spreadsheet is a good tracker for a handful of units that pay one way. It starts to cost you time when:
- Rent arrives several ways. You open your bank app, find a Zelle from a name you don't recognize, work out which tenant it is, then type it in.
- Late fees are manual. You count days, apply the lease's fee, and add the line yourself every month.
- You chase. You check the sheet, then text each tenant who is behind.
None of that is hard. It is repetitive, and it grows with every unit.
With Resida: each lease has its own ledger of every charge, payment and credit, with the balance shown as "Amount owed by tenant" or "Credit in tenant favor". Link the bank account your rent lands in and Resida watches it for Zelle, cash and check deposits and proposes which tenant each one belongs to. You confirm or dismiss each match. After a payer's name has been matched a couple of times, later deposits from that name are applied automatically, and you can review them under Recently Auto-Applied. Late fees are set per lease as a flat fee, a percentage of rent, or a daily fee with a cap. On leases paid through ACH they are assessed automatically; on leases paid outside Resida they appear for you to approve or dismiss. See how rent tracking works, and how a lease's ledger sits with its terms and dates in lease management.
This is general information, not tax advice. Confirm the details for your own situation with a tax professional.
Sources: IRS, "How long should I keep records?"
Frequently asked questions
- What columns should a rent payment tracker have?
- Date, description, amount charged, amount paid, running balance, payment method and a note, on a tab named for the tenant and unit. One row per charge or payment, so a partial payment or a late fee gets its own line instead of overwriting the month.
- How do I track partial rent payments in a spreadsheet?
- Log each payment as its own row on the day it arrives and let the running balance show what is still owed. Don't edit the original rent charge; the history of what was charged and what was paid is the point of the ledger.
- Should I keep one sheet for all tenants or one per tenant?
- One tab per lease keeps each balance clean and is easy to print or send if a tenant asks for their payment history. Add a summary tab that pulls each tab's balance so you can see who owes money at a glance.
- When is a spreadsheet no longer enough?
- When rent arrives by several routes (Zelle, checks, cash, transfers) and you spend your time matching bank deposits to tenants, or when you are calculating late fees and sending reminders by hand. The spreadsheet still works; the upkeep is what grows.
- How long should I keep rent records?
- For taxes, the IRS says to keep records that support income and deductions until the period of limitations for the return expires, which is generally 3 years from when you filed, and longer in some cases. Property records are kept until the period of limitations expires for the year you dispose of the property.