A landlord with four properties usually knows two numbers precisely: total rent received, and roughly what they spent. Both are portfolio-level, and portfolio-level numbers hide the thing that actually matters.
Consider a real pattern. Four flats, combined annual rent of £61,200, combined costs of £19,400, so the portfolio returns £41,800 before finance. Healthy enough. Nobody is worried.
Broken down per property:
| Property | Rent | Costs | Net | Cost ratio |
|---|---|---|---|---|
| FLAT-12 | 14,400 | 3,100 | 11,300 | 22% |
| FLAT-18 | 16,800 | 3,420 | 13,380 | 20% |
| HSE-04 | 18,000 | 4,180 | 13,820 | 23% |
| FLAT-27 | 12,000 | 8,700 | 3,300 | 73% |
FLAT-27 is consuming nearly three quarters of its rent in costs and contributing £3,300 to a £41,800 result. It is also, almost certainly, the property its owner describes as "fine, bit of a hassle sometimes".
The portfolio number conceals it completely. Only the per-property split surfaces it, and surfacing it is the entire reason to keep records in a spreadsheet rather than a shoebox.
The structure
Three tabs. Keep them separate; the temptation to put everything on one sheet is what makes these unmaintainable.
Tab 1 — Transactions. Every cost, one row each.
| Column | Example |
|---|---|
| Date | 2026-03-14 |
| Property ID | FLAT-27 |
| Vendor | Harrow Heating |
| Description | Replace failed combi boiler, like-for-like |
| Amount | 1,840.00 |
| Tax component | 368.00 |
| Category | Repairs |
| Treatment | Repair / Improvement / Unsure |
| Tenancy state | Tenanted / Void / Pre-first-let |
| Receipt URL | link |
Tab 2 — Income. Rent received per property per month, plus any other receipts (deposit deductions, insurance payouts).
Tab 3 — Properties. One row per property: ID, address, purchase date, purchase price, current mortgage, acquisition costs. This is the tab that makes yield calculations possible and the one most people never build.
The property ID rule
Short, stable, never the address.
"Flat 27 Riverside Court" will be typed as "Flat 27, Riverside Court", "27 Riverside", and "Riverside 27" across a single year, and each variant becomes its own row in every summary. FLAT-27 either matches or it does not.
Lock it with data validation pointing at the Properties tab. This single decision prevents more reconciliation work than anything else in the setup.
The four numbers worth calculating
With those three tabs, a summary tab gives you the comparisons that drive decisions.
Cost ratio per property. Costs divided by rent. This is the headline. Anything materially above your portfolio average deserves investigation, and anything above 50% deserves a hold-or-sell conversation.
=SUMIFS(Transactions!Amount, Transactions!PropertyID, $A2) / SUMIFS(Income!Amount, Income!PropertyID, $A2)
Gross yield. Annual rent divided by purchase price, from the Properties tab. Useful for comparing acquisitions, less useful for operational decisions.
Net yield. Rent minus costs, divided by purchase price. This is the number that tells you whether a property is earning its capital, and it is frequently a long way from the gross figure that was quoted when you bought it.
Cost per category per property. A pivot table of property against category. This is where the diagnosis happens — FLAT-27's 73% is caused by something specific, and the pivot names it.
Reading the diagnosis
Running that pivot on FLAT-27 produces something like:
| Category | Amount |
|---|---|
| Repairs | 4,920 |
| Letting fees | 1,680 |
| Void period costs | 1,340 |
| Insurance | 420 |
| Compliance | 340 |
Two of those lines tell a single story. Letting fees of £1,680 against annual rent of £12,000 implies the property has been re-let more than once. Void costs of £1,340 confirm it. This is not a maintenance problem, it is a tenant retention problem, and the £4,920 of repairs is probably downstream of the same cause — each turnover generating remedial work.
That is a different decision from "the boiler keeps breaking". Tenant retention is addressable; a structurally expensive property may not be.
Treatment and tenancy state
Two columns in the transactions tab exist for tax reasons rather than management reasons, and they are worth maintaining even though they produce no interesting charts.
Treatment distinguishes a repair, which generally reduces this year's taxable profit, from an improvement, which generally affects your position when you sell. The distinction depends on whether the work restored the property or enhanced it beyond its previous condition, and the rules vary by jurisdiction. Allow "Unsure" as a value — a filterable list of ambiguous items is a short conversation with an accountant, while a column of confident guesses is a problem nobody can see.
Tenancy state matters because work done before a property was first let, or during a void, can be treated differently from work during a tenancy. One dropdown selection at entry resolves a question that otherwise requires cross-referencing tenancy agreements against invoice dates.
Record both when the invoice is in front of you. The description on a contractor's invoice is specific; a summary written three months later is not.
Keeping it current across a portfolio
The failure mode with multiple properties is not forgetting to record a cost. It is recording it against the wrong property, which is worse than not recording it at all because it corrupts two comparisons simultaneously.
The defence is capturing at the point the invoice arrives, while the context is unambiguous. Scanning as they come in — Scan2Sheet extracts the vendor, date, amount, and tax component and files the row with the invoice image linked — means the only manual input is the property dropdown, the treatment, and a one-line description. Twenty seconds, with the invoice in front of you, when you still know which property it relates to.
The annual review
Once a year, with the summary tab open, four questions:
- Which property has the highest cost ratio, and is the cause operational or structural?
- Which property's net yield is furthest below its gross yield, and why?
- What did improvements total this year, and is that figure recorded somewhere it will be found at sale?
- Which costs appear on every property, and are any of them negotiable at portfolio level?
That last one is frequently worth money. Insurance, letting agency fees, and compliance checks are often arranged property by property, and the portfolio view is the first time anyone sees that they are being bought four times at retail.
Build the Properties tab first, even if the transaction history is incomplete. Without it, none of the yield comparisons are possible, and those are the numbers that change what you do rather than merely describing what happened.