How to reconcile a rent roll with rent payments
By the Analistable team · Updated · 6 min read
Give every tenancy a reference that tenants use when paying, then match bank receipts to the rent roll on that reference (falling back to amount and payer name). Sum payments per tenancy for the period, compare with rent due, and list arrears and overpayments. Carry the balance forward each month so partial payments add up correctly.
Part of our guide: How to reconcile data in Excel
Match receipts to tenancies
Tenancy =IFERROR(
XLOOKUP(TRUE, ISNUMBER(SEARCH(RentRoll[Reference], [@Description])), RentRoll[Tenancy]),
"Unidentified")This finds the first rent-roll reference that appears anywhere in the bank description (“FLAT 3B SEPT RENT” contains “3B”). Make references distinctive enough not to appear by accident — “R-3B” rather than “3B”.
Arrears report
Received =SUMIFS(Bank[Amount], Bank[Tenancy], [@Tenancy], Bank[Date], ">=" & MonthStart, Bank[Date], "<=" & MonthEnd)
Balance =[@[Brought forward]] + [@[Rent due]] - [@Received]| Tenancy | Brought forward | Rent due | Received | Balance | Status |
|---|---|---|---|---|---|
| R-1A | 0 | 950 | 950 | 0 | Paid |
| R-2B | 0 | 875 | 500 | 375 | Arrears |
| R-3B | 875 | 875 | 1750 | 0 | Cleared previous arrears |
| R-4C | 0 | 1100 | 1150 | -50 | Overpaid |
Unidentified receipts
Filter receipts marked Unidentified. Usual causes: a tenant paid without the reference, a guarantor or housing benefit paid on their behalf, or one payment covers two months. Record the decision in a notes column so next month's match uses it.
This is a standard reconciliation: receipts on one side, amounts due on the other, and an explained difference.
Partial and combined payments
Because the balance is carried forward, partial payments sort themselves out: £500 of £875 leaves £375 owing, and the next payment reduces it. Combined payments (two months in one transfer, like R-3B above) also work, as long as the receipt is matched to the right tenancy. Payments from housing benefit or a guarantor need the tenancy reference added manually the first time.
Monthly routine
- Export the bank statement for the month.
- Run the matching formula and review Unidentified receipts.
- Carry each tenancy's closing balance forward as next month's brought-forward figure.
- Send arrears reminders for balances above your threshold.
Keep one sheet per month in the workbook, so the history behind every balance is visible.
Setting up the rent roll
| Field | Example | Why it matters |
|---|---|---|
| Tenancy reference | R-3B | The text tenants put on their payments |
| Property and unit | 12 Mill St, Flat 3B | Reporting by property |
| Tenant name(s) | J. Rowe | Fallback match on payer name |
| Rent and frequency | 875, monthly | Rent due for the period |
| Due day | 1 | Deciding when a payment is late |
| Start and end date | 01/03/2025 – open | No rent due before start or after end |
Keep past tenancies with an end date rather than deleting them, so a late payment from a former tenant still finds its tenancy and can be set against the final balance or deposit deductions.
Rent that isn't monthly
For weekly or four-weekly rents, work out rent due from the number of due dates in the month rather than a fixed figure. For a weekly rent due on the same weekday as the tenancy start:
Due dates in month =INT((MonthEnd - [@[First due]]) / 7) - INT((MonthStart - 1 - [@[First due]]) / 7)
Rent due =[@[Due dates in month]] * [@[Weekly rent]]A weekly rent of £200 is £800 in a month with four due dates and £1,000 in a month with five, so a tenant can look in arrears in one month and in credit in the next without anything being wrong. The running balance absorbs this if rent due is calculated correctly.
Second example: payment from a third party
R-5D's rent is £900 a month. Part of it is paid by a local authority as housing support, and the tenant pays the rest. The bank shows:
| Date | Description | Amount | Matched to |
|---|---|---|---|
| 03 Sep | LOCAL COUNCIL HB REF 77812 | 650 | R-5D (via payer mapping) |
| 05 Sep | K MORRIS R-5D | 250 | R-5D (reference) |
| Total | 900 | Balance 0 |
The council payment has no tenancy reference. Add a small payer-mapping table (payer reference → tenancy) and look there before marking a receipt Unidentified. 650 + 250 = 900, so the tenancy is fully paid.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Receipt matched to the wrong flat | Short reference found inside other text (“1A” in “R-11A”) | Use longer, unique references with a prefix |
| Arrears for a tenant who moved out | Rent still due after the end date | Set rent due to 0 after the end date |
| Balance jumps every few months | Weekly rent treated as a fixed monthly amount | Count due dates per month |
| Deposit shows as rent received | Deposit paid into the same account | Exclude deposits with a separate category |
| Totals don't match the bank | Receipts filtered to identified ones only | Include Unidentified receipts in the bank total |
Checking the month
Total rent received on the arrears report plus Unidentified receipts should equal total rent credits on the bank statement for the month. And opening arrears plus rent due minus receipts should equal closing arrears across the portfolio. If either check fails, look for a receipt matched twice or a tenancy missing from the rent roll. A tenant list against a payments list is also the same shape as matching a membership list to payments.
Doing it in Google Sheets
Google Sheets has no structured table references in the same form, so write the matching formula with ranges. SEARCH and ISNUMBER work the same way, and XLOOKUP with a TRUE lookup value over an array also works in Sheets, though some people find INDEX(FILTER(...)) easier to read. Put the rent roll on one tab, each month's bank export on its own tab, and the arrears report on a third tab that refers to both.
Common mistakes
- Matching on amount alone, which fails as soon as two tenancies have the same rent.
- Retyping brought-forward balances instead of linking them to last month's closing balance.
- Counting a deposit or a returned payment as rent.
- Ignoring rent increases mid-year, so the rent due figure is wrong from the change date.
Ageing the arrears
A single balance doesn't show how long a tenant has been behind. If you allocate payments to the oldest rent first, which is the usual approach, the balance can be split by age: the part up to one month's rent is current, the next month's rent is 1–2 months old, and so on. For R-2B, £375 owing on a rent of £875 is less than one month, so it is all current. A tenant owing £2,000 on a rent of £875 has £875 current, £875 one to two months old and £250 older than two months.
Several properties or landlords
If you manage property for several landlords, add a Landlord column to the rent roll and pivot receipts and arrears by landlord. Each landlord's statement then comes from the same reconciled data, and a receipt can only ever be allocated once.
Frequently asked questions
- How do I match rent payments to tenants in Excel?
- Search each bank description for the tenancy reference with SEARCH inside XLOOKUP, then sum receipts per tenancy with SUMIFS.
- How do I track rent arrears in a spreadsheet?
- Carry each tenancy's balance forward: balance = brought forward + rent due − received.
- What if a tenant pays without a reference?
- Match on amount and payer name, record the match, and ask the tenant to use the reference next time.
- How do I record a rent payment that covers two months?
- Match it to the tenancy and let the carried-forward balance absorb it: the balance goes negative (credit) and the next month's rent reduces it.
- What's a rent roll?
- A list of tenancies with the rent due, tenant details and lease dates — the reference you reconcile payments against.