Landlord rent tracker spreadsheet: see who has paid and who is late

If you rent out a few units, the same questions come back every month. Who has paid, who is a few days late, how much is still open, and which deposit sits with whom. This page shows how to build a landlord rent tracker in a spreadsheet: a unit list, a payment log, an expense log, a status per unit and a short worked example with invented numbers.

Why a bank statement is a weak rent record

A bank statement lists money that arrived. It does not tell you what should have arrived. Rent tracking needs both sides: the amount due on each date and the payments received against it. Once a tenant pays part of a month, or two months at once, the plain statement stops answering the simple question of what is still owed.

A rent tracker puts the schedule and the payments side by side. The gap between them is the number you want.

Start with a unit list

One row per unit keeps everything easy to filter. Record these fields:

• Unit name. A short label that is unique, such as 1A or Garden Flat.
• Tenant. Leave it empty for a vacant unit.
• Monthly rent. The agreed amount per month.
• First due date. The date of the first rent in the current lease. Rent then falls due on the same day each month.
• Lease end. Optional, but useful to mark ended leases.
• Deposit required, received and returned. Three columns show the full deposit story for each unit.

Log every payment on its own row

Use a separate sheet for payments. Each row holds the unit, the date received and the amount. Do not edit old rows. If a tenant pays 500 of a 950 rent, enter 500. If a payment is refunded, enter a negative amount.

The key rule: apply payments to the oldest rent first. That way a part payment reduces the oldest open month, and the summary shows exactly what is owed instead of a vague balance.

Turn dates into a status

Add a report date cell. Type a fixed date to look at a past or future day, or enter =TODAY() for the current day. Each unit then gets a status:

1. Paid. All rent due up to the report date has been received.
2. Due soon. The next rent falls inside a window you choose, for example 14 days.
3. Overdue. Rent due before the report date is still open.
4. Other cases. A blank first due date, a lease that has not started, a lease that has ended and a vacant unit each deserve their own label so they never hide inside Paid.

Alongside the status, three figures help: rent still owed, days overdue (report date minus the oldest unpaid due date) and next due date.

Track expenses with editable categories

A second log holds expenses: the date, the amount, the category and an optional unit. Typical categories are repairs, insurance, fees and utilities you pay yourself. Keep the category list editable so it matches your own books. An expense with a category that no longer exists should land in an "Other or uncategorised" line instead of vanishing.

Watch the security deposit

For each unit, compare the three deposit columns:

• Not received. A deposit is required and nothing has arrived.
• Part received. Some money arrived, less than required.
• Full amount received. The deposit sits with you.
• Part returned or Returned. Money has gone back to the tenant.
• Check. More was returned than received, which is worth a second look.

Deposit rules differ by place, so use this only as a record of what you received and returned.

A dashboard for the whole portfolio

Collect the main figures on one sheet: rent roll (monthly rent of all occupied units), overdue rent, due-soon rent, rent received, expenses, deposits held and the next five rents to fall due. A chart by month shows received rent against expenses. After you enter the payments for the day, this page replaces opening five tabs.

The formulas behind it

• Rent due to date = monthly rent times the number of due dates up to the report date.
• Rent still owed = rent due to date minus payments received up to the report date.
• Days overdue = report date minus the oldest unpaid due date.
• Deposit held = deposit received minus deposit returned.

Worked example (invented numbers)

These figures are made up for illustration. Unit 1A has a monthly rent of 1,200 due on the 1st. Unit 1B has a monthly rent of 950 due on the 5th. The report date is 15 October.

1. Unit 1A paid September but nothing yet for October. Rent still owed = 1,200. The oldest unpaid due date is 1 October, so days overdue = 14. Status: Overdue.
2. Unit 1B received 500 towards the 950 due on 5 August and nothing after that. Rent due since August = 950 times 3 (August, September, October) = 2,850. Rent still owed = 2,850 minus 500 = 2,350. The payment goes to August first, so the oldest unpaid due date is 5 August and days overdue = 71. Status: Overdue.
3. Rent roll for the two units = 1,200 + 950 = 2,150 per month.

Seeing the exact open amount per unit makes a polite reminder to a tenant much easier to write.

Limits to keep in mind

A tracker like this records and calculates. It covers no lease documents, screening or tax forms. Some situations need a workaround: for a rent change during a lease, end the old lease row and add a new row. Late fees, utilities billed to tenants, several tenants per unit, multiple currencies, depreciation, mortgages and taxes of any kind sit outside a simple rent and expense sheet.

A ready-made version

If you would rather not build the formulas yourself, the Landlord Rent and Expense Tracker follows this structure. It is one Excel workbook with 6 tabs: Instructions, Settings, Units, Payments, Expenses and Dashboard. It holds up to 30 units, 500 payments and 300 expenses, and it includes made-up example data you delete before real use plus a one-page Quick Start PDF.

Works in Excel and Google Sheets (checked by importing the file). No macros. It also opens in LibreOffice.

This page describes a record-keeping aid. It gives no financial, tax, accounting or legal advice.

See the Landlord Rent and Expense Tracker

Frequently asked questions

How do you track rent payments in a spreadsheet?

Give every unit one row with the tenant, the monthly rent and the first due date. Log each payment on a separate sheet with the unit, the date and the amount. A formula applies payments to the oldest rent first and shows the amount still owed, the days overdue, the next due date and a status.

What should a landlord spreadsheet include?

At minimum a unit list with tenant, rent and lease dates, a payment log, an expense log with categories and a summary page. Many landlords also track the security deposit per unit: required, received and returned.

Does a rent tracker spreadsheet work in Excel?

Yes. The tracker described here is built for Excel, uses no macros and also opens in LibreOffice. It also works in Google Sheets: the file was imported into Google Sheets and the unit and expense category dropdowns, the formulas and the chart carried over.

Is this financial, tax or legal advice?

No. This page describes a record-keeping method and a planning tool. Deposit and notice rules differ by place, so check your own lease and local rules, and ask a qualified adviser about your own situation.