Guide, 2026-11-04

How to track rent and arrears in a spreadsheet (for landlords)

If you own a few rental properties, the bank statement tells you what arrived, but not what should have arrived. That gap is your arrears, and it is easy to miss when rent days, lease dates and part payments are spread across several tenants. A spreadsheet can track it reliably if you calculate rent due from the lease rather than typing a monthly schedule.

The idea: rent due is a count of due dates

For monthly rent, the rent a tenant should have paid in a period is simply:

number of due dates that have passed × monthly rent

The period starts at the later of the lease start and the start of your financial year, and ends at the earlier of the lease end and today (or whatever as-of date you choose). Arrears are rent due minus rent received in the same period. That is the whole method. Everything else is making sure the count of due dates is right.

Step 1: one row per tenancy

Record property, tenant, monthly rent, due day (for example the 1st or the 15th), lease start and lease end. If the rent changes, end the old row on the change date and start a new row with the new rent. That keeps the history correct without editing past figures.

Step 2: one row per payment

Keep payments in their own list: date, tenancy, amount. Do not net them off against rent in the tenancy row. Total received per tenancy for the year is a SUMIFS on that list by tenancy and date range.

Step 3: count the due dates

For each month in the period, check whether the due date falls on or after the period start and on or before the period end. A helper grid with twelve month columns and a 1 or 0 in each is the easiest version to check by eye. Watch month ends: a rent day of the 31st has to fall on the 30th in September and the 28th or 29th in February.

Step 4: lease status

Days to lease end is lease end minus today. Label each tenancy: ended if the date has passed, ending soon if it is within, say, 60 days, otherwise active. A property with no current tenancy is vacant. Ending soon is your cue to talk about renewal well before the notice period, whatever that is where you are. Notice rules vary a lot, so check local rules.

A worked example

A landlord's financial year starts on 1 April. The as-of date is 15 September 2026.

Flat 2 is one month behind. The Studio is fully paid, but its lease ends in 19 days, so it needs attention too. The Mill Lane tenancy started mid-year, which is why it only has four due dates in the period.

Step 5: income, expenses and yield

Record expenses by property and category (insurance, repairs, agent fees, service charges and so on). Then:

• Gross yield = annual rent ÷ property value.
• Net yield = (annual rent - annual expenses) ÷ property value.

Flat 2 is valued at $220,000. Annual rent is $1,100 × 12 = $13,200, a gross yield of 6.0%. With $3,100 of expenses for the year, net income is $10,100 and the net yield is about 4.6%. Some landlords use the purchase price instead of current value. Either is fine as long as you are consistent.

Common mistakes

• Treating a late payment for August as September's rent and missing that the tenant is still a month behind.
• Converting weekly rent to monthly by multiplying by four. A year has 52 weeks, so it is weekly rent × 52 ÷ 12.
• Counting a deposit as rent income. Deposits are usually held separately, and how they must be handled depends on where you are.
• Overwriting the rent when it changes, which rewrites what earlier months should have been.
• Forgetting expenses paid personally, which flatters the net yield.

When a ready-made workbook saves time

The due-date counting is where hand-built sheets usually go wrong, especially with mid-year leases and month-end rent days. RentBook does that calculation from each lease's dates and due day, shows tenants in arrears and when they last paid, labels lease status, and reports income, expenses, net income and gross and net yield per property for any financial year start. It handles monthly rent only and is for record keeping, not tax, legal or accounting advice.