ZorantaGuides
Letvy

Letvy › Guides

Rent Tracker Spreadsheet: Columns, Formulas and Layout

A rent tracker spreadsheet needs one row per rent payment due, with the amount due, the amount received, the date it arrived and a status worked out by formula. Add a balance column and a 12-month summary tab, and you can answer "who still owes this month?" in a few seconds.

Below are the columns, the formulas for Excel or Google Sheets, and the point where a spreadsheet starts costing more time than it saves.

A rent ledger with paid, part-paid and unpaid rows

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:

ColumnWhat goes in itExample
A PropertyShort name or first line of the address12 Elm Road
B UnitFlat, room or "whole house"Flat 2
C TenantName on the tenancyJ. Patel
D Due dateThe day rent is due that month01/03
E Rent dueAgreed rent for the period950.00
F ReceivedWhat actually arrived (0 if nothing)600.00
G Date receivedDate it reached your account03/03
H MethodStanding order, transfer, cash, chequeStanding order
I StatusFormula: Paid, Partial or UnpaidPartial
J BalanceFormula: rent due minus received350.00
K NotesAnything you agreed or chasedRest promised 15/03

Keep amounts as plain numbers and format the column as currency. Typing "£950" as text breaks the formulas below.

Letvy

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 forThe full history of each tenancyA quick view of the year
Partial paymentsEasy: received and balance sit side by sideAwkward: one cell per month
FilteringFilter by tenant, property or statusHard to filter
Printing for a tenantFilter to one tenant and printNot 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.

  1. Status (column I): =IF(F2=0,"Unpaid",IF(F2<E2,"Partial","Paid"))
  2. Balance (column J): =E2-F2. A negative number means the tenant paid more than was due that month.
  3. 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

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

  1. At the start of the month, copy last month's rows and clear columns F, G and K. Due dates and rents carry over.
  2. A few days after the due date, open your bank statement and fill in Received and Date received for each payment you can match.
  3. Leave anything that has not arrived at 0. The formulas mark it Unpaid.
  4. 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:

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.

Letvy

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 Play

iPhone version coming soon

More guides

All guides

Published by Zoranta, the developer of Letvy.