Bank Statement Conversion for Landlords
Tracking rent across several properties from bank statements, identifying arrears, and preparing figures for a tax return.
Landlords with more than two or three properties hit a specific problem: rent arrives from many tenants on different dates, sometimes partially, and matching it to properties by eye stops working.
Identify rent by reference, not amount
Matching on amount fails as soon as two tenants pay the same rent, or one pays a partial amount, or a rent increase takes effect mid-year.
The reference is more reliable. Most standing orders carry a reference the tenant or agent set up once and never changes — often the property address or a tenancy number.
Finding arrears
The useful view is a pivot: rows are property or tenant reference, columns are month, values are sum of credits. Gaps and short payments become visible immediately.
| Pattern in the pivot | Means |
|---|---|
| A blank cell | No rent received that month |
| A value below the expected rent | Partial payment |
| Two values in one month | Catch-up payment, or a split payment |
| A value in the wrong month | Late payment crossing a month boundary |
For the tax return
Rental income is taxed on the amounts due or received depending on jurisdiction and basis, and expenses need allocating per property. Both need the transactions split by property, which brings you back to the reference column.
Mortgage interest, agent fees, repairs and insurance all need attributing to a specific property. A description-only export makes this manual; a reference column makes it a lookup.
Separating property expenses
Rental income is the easy half. Expenses are harder, because they arrive from many suppliers and often cover several properties at once — an insurance policy across a portfolio, or a repair bill that covers two flats.
Those need apportioning, and the apportionment is a judgement you make rather than something the bank data contains. Recording it as a column at the time is straightforward; reconstructing it a year later is guesswork.
| Expense type | Usually attributable to |
|---|---|
| Mortgage interest | One property directly |
| Letting agent fees | One property, usually a percentage of rent |
| Repairs | One property, from the invoice |
| Portfolio insurance | Apportioned across properties |
| Accountancy fees | Apportioned, or treated as general |
Keeping it current
Doing this monthly rather than annually matters more for landlords than most, because a repair invoice you cannot attribute to a property in January is one you will not attribute correctly in the following January either.
Frequently asked questions
What if tenants pay without a reference?
Match on payer name plus amount, which is less reliable. It is worth contacting them to add a reference — it saves work every year thereafter.
How should I handle expenses covering several properties?
Apportion them and record the split as a column at the time. It is a judgement the bank data does not contain and cannot be reconstructed later.
Can I track several properties in one spreadsheet?
Yes. Add a property column driven by a lookup on the reference, then pivot on it.
Related posts
- Bank Statement Conversion for Freelancers and Contractors
- Bank Statements for a Rental Application: What Gets Checked
- Bank Statement Conversion for Small Business Bookkeeping
Convert a statement now
Bank Statement PDF to Excel, or see every format. More in the guides.