Rental income spreadsheet: match rent payments to bank statements

A rental income spreadsheet should compare the rent expected for each property and period with the rent actually received. When a letting agent collects rent, keep gross rent and agent deductions separate: the net bank deposit is not necessarily the rental income figure you need for your records.

Download the rental income tracker (.xlsx). It contains fictional direct-payment and agent examples, a formula for the period's rent shortfall and a separate bank-deposit reconciliation. It is a records tracker, not a tax return or legal determination of arrears.

Keep property, period and receipt date separate

The Rent sheet records Property, Rent period, Due date, Expected rent, Gross rent received, Receipt date, Agent deductions, Net expected deposit, Actual bank deposit, Deposit difference, Period shortfall and Notes.

A tenant paying on 2 October for September should not disappear merely because the bank statement changed months. Record the September rent period and October receipt date separately. Decide reporting treatment with your accountant; this tracker preserves both facts rather than making that decision for you.

Use one currency throughout. Replace all example input rows before using the totals for your own portfolio. The instructions identify formula columns and the 500-row working area.

Example: direct rent with a shortfall

A property has £1,000 due for September and the tenant pays £900. Enter £1,000 Expected rent, £900 Gross rent received, no agent deduction and £900 Actual bank deposit.

The period shortfall is £100 and the deposit difference is zero. These are different results: the bank deposit is fully explained, but the expected rent has not been received in full. Notes should record the reason or follow-up action.

Do not use that £100 alone as a legally established arrears balance. Prior credits, agreed variations, deposits, disputes or payments allocated to other periods can change the position.

Example: an agent's net payment

A second property has £1,200 expected rent. The agent collects £1,200, deducts £120 in fees and pays £1,080 into the bank.

Record the fee detail from the agent's statement and invoice. A bank converter sees the £1,080 deposit; it cannot reconstruct the £1,200 gross rent and £120 fee from that deposit alone. Keep the agent statement as supporting evidence.

Match statements without counting rent twice

Use a bank CSV or convert the bank statement PDF, then match deposits to the rent schedule. Retain the source reference and review ambiguous combined payments.

If the accounting system already records gross rent from an agent statement, importing the net deposit as new rental income would duplicate part of the income. Use the appropriate clearing or matching process. Likewise, a transfer between your own property accounts is not new rent.

For one bank payment covering several properties, allocate the deposit using the agent's remittance detail and check that allocations total the actual bank receipt. Do not enter the full combined payment on every property row.

Keep the tracker separate from tax judgments

Security deposits, mortgage principal, interest, repairs, improvements and personal use can require different treatment. The landlord bookkeeping guide explains the wider records to collect. For UK property records, start with HMRC's renting-out-property guidance.

If you are within MTD for Income Tax, the quarterly-update article covers the approaching 7 November 2026 deadline. The tracker does not submit updates or establish that a software chain meets every digital-record requirement.

Close the month

Review rent shortfalls, unexplained bank differences and missing agent statements separately. Keep a dated note of changes rather than overwriting an unresolved amount with zero. Share the tracker, supporting statements and questions with your bookkeeper through your agreed secure channel.

Convert the bank PDFs, match the deposits, and retain the gross-to-net evidence that explains what arrived.