What a rent tracker spreadsheet needs
Start with a single tab called Payments. Each row is one rent payment that falls due: one tenant, one month. These are the columns that matter:
| Column | What goes in it | Example |
|---|---|---|
| A Property | Short name or first line of the address | 12 Elm Road |
| B Unit | Flat, room or "whole house" | Flat 2 |
| C Tenant | Name on the tenancy | J. Patel |
| D Due date | The day rent is due that month | 01/03 |
| E Rent due | Agreed rent for the period | 950.00 |
| F Received | What actually arrived (0 if nothing) | 600.00 |
| G Date received | Date it reached your account | 03/03 |
| H Method | Standing order, transfer, cash, cheque | Standing order |
| I Status | Formula: Paid, Partial or Unpaid | Partial |
| J Balance | Formula: rent due minus received | 350.00 |
| K Notes | Anything you agreed or chased | Rest promised 15/03 |
Keep amounts as plain numbers and format the column as currency. Typing "£950" as text breaks the formulas below.
Letvy keeps the same paid, partial and unpaid colours for every unit, and marking a payment takes two taps on your phone. Get it on Google Play
Ledger layout or monthly grid?
There are two common ways to lay out rent records, and most landlords end up using both on separate tabs.
| Ledger (one row per payment) | Grid (tenants down, months across) | |
|---|---|---|
| Best for | The full history of each tenancy | A quick view of the year |
| Partial payments | Easy: received and balance sit side by side | Awkward: one cell per month |
| Filtering | Filter by tenant, property or status | Hard to filter |
| Printing for a tenant | Filter to one tenant and print | Not suited |
A rent ledger template is the first layout: a running list of what was due and what came in for one tenancy. The Payments tab above already is one; filter column C to a single tenant and you have that tenant's ledger. The grid is covered in the rent schedule section further down.
Three formulas that do the checking
Put these in row 2 and copy them down. They work the same way in Excel and Google Sheets.
- Status (column I): =IF(F2=0,"Unpaid",IF(F2<E2,"Partial","Paid"))
- Balance (column J): =E2-F2. A negative number means the tenant paid more than was due that month.
- Total owed by one tenant: on a summary tab, =SUMIFS(Payments!J:J,Payments!C:C,"J. Patel"). Swap the name for a cell reference so you can pick tenants from a list.
Then add conditional formatting to column I: green for Paid, yellow for Partial, red for Unpaid. Once the colours are on, you can scroll down a month and see the problem rows straight away.
Optional extras
- Days late: =IF(G2="",TODAY()-D2,G2-D2) for each row, so you can sort by the oldest unpaid rent.
- Month (column L): =TEXT(D2,"yyyy-mm") makes filtering and pivot tables by month much easier.
- Data validation on column H so the method is always spelled the same way.
The rent schedule tab: twelve months at a glance
A rent schedule template lists each unit down the side and the months across the top. Each cell pulls the amount received from the Payments tab:
=SUMIFS(Payments!F:F,Payments!B:B,$A2,Payments!L:L,B$1) where column L on the Payments tab holds the month and row 1 of the schedule holds the same month labels.
Add a row underneath for the rent due in each month and a row for the difference. The bottom-right cell then shows what is outstanding across the whole portfolio for the year so far. This tab is also the one to print or export when your accountant asks for rent received per property.
Keeping the spreadsheet accurate each month
- At the start of the month, copy last month's rows and clear columns F, G and K. Due dates and rents carry over.
- A few days after the due date, open your bank statement and fill in Received and Date received for each payment you can match.
- Leave anything that has not arrived at 0. The formulas mark it Unpaid.
- Write in Notes when you chase a payment and what the tenant said.
Update the rent figure in a new row when the rent changes, rather than editing old rows. Old months should still show what was due at the time.
Where a spreadsheet starts to slip
A spreadsheet only knows what you type into it. It does not see your bank account, so a payment you forgot to enter looks the same as one that never arrived. Other weak points show up as the portfolio grows:
- Copying the month forward by hand is easy to get wrong, especially after a rent change or a new tenant.
- Formulas get overwritten when someone types a number into the Status column.
- On a phone, a wide sheet is hard to read and harder to edit.
- Receipts for expenses end up in a different place from the rent records.
None of this is a reason to give up on a spreadsheet if it works for you. It is a reason to check it against the bank every month, and to keep a backup copy somewhere other than one laptop.
Prefer to skip the formulas?
Letvy holds your properties, units and tenants, and shows at a glance who still owes this month. You can export to CSV when you want the data back in a spreadsheet.
Get it on Google PlayiPhone version coming soon
More guides
- How to Track Rent Payments: A Monthly Routine for LandlordsA five-step monthly routine to track rent payments: set what is due, check the bank on a fixed day, match each payment, record part payments properly and log every chase.
- Landlord Expenses List: Categories, Examples and What to KeepA landlord expenses list to use as recording categories, how HMRC separates allowable expenses from capital costs, repair or improvement examples and how to log a cost.
- Rental Yield Calculator: Gross and Net Yield Worked OutWork out gross and net rental yield with a free calculator. Formulas, a worked example in pounds, what to count as costs, voids, and yield on the cash you put in.
- Rental Income and Expenses Spreadsheet: A Year-Round LayoutLay out a rental income and expenses spreadsheet with four tabs, work out monthly property cash flow, line it up with the UK tax year and keep the records HMRC expects.
Published by Zoranta, the developer of Letvy.