A provision matrix for ordinary arrears, a specific provision for the tenant in administration, and the charge that reaches the year's income.
Provide for tenant arrears in two layers: a provision matrix that applies a loss rate to each ageing bucket of ordinary arrears, and a specific provision for any tenant in insolvency, net of the deposit you hold and the dividend you expect. On an illustrative £4,000,000 rent roll with £640,000 of arrears, that gives a closing provision of £218,100, 34.1 per cent of the arrears, and a bad debt charge of £128,100 for the year, 3.2 per cent of rent.
Worked in full in The Real Estate Asset Manager by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
The provision is the line in an asset's accounts where the asset manager's judgement on occupiers turns into a number. It moves net income, it is audited, and on a multi-let asset in a weak occupier market it can take more off the year's distributable income than the void. IFRS 9 requires a simplified approach for most trade receivables and allows it, as a policy choice, for lease receivables: lifetime expected losses, usually estimated with a provision matrix. The arithmetic below is that approach, with the one tenant that does not belong in the matrix taken out of it.
| Ageing bucket | Arrears, £ | Loss rate |
|---|---|---|
| Not yet due or under 30 days | 180,000 | 2% |
| 31 to 90 days | 120,000 | 10% |
| 91 to 180 days | 90,000 | 35% |
| 181 to 365 days | 60,000 | 60% |
| Over 365 days | 40,000 | 100% |
| Ordinary arrears | 490,000 |
Separately, one tenant went into administration in the last quarter owing £150,000. The landlord holds a £50,000 rent deposit on that lease and the administrators' estimate is a dividend of 5p in the pound to unsecured creditors. The contracted rent across the asset is £4,000,000 a year. At the start of the year the provision stood at £150,000, and £60,000 of old arrears, already fully provided, were written off during the year.
The loss rates are illustrative. In practice they come from the asset's or the portfolio's own history: the share of each bucket that was eventually collected, adjusted for what you now expect of the occupier market. That adjustment is what makes the provision forward-looking rather than a record of past collections.
Collective provision = Σ arrears in bucket × loss rate for the bucket
In Excel, with arrears in B2:B6 and rates in C2:C6: =SUMPRODUCT(B2:B6,C2:C6)
| Bucket | Arrears, £ | Loss rate | Provision, £ |
|---|---|---|---|
| Under 30 days | 180,000 | 2% | 3,600 |
| 31 to 90 days | 120,000 | 10% | 12,000 |
| 91 to 180 days | 90,000 | 35% | 31,500 |
| 181 to 365 days | 60,000 | 60% | 36,000 |
| Over 365 days | 40,000 | 100% | 40,000 |
| Collective provision | 490,000 | 25.1% | 123,100 |
Three fifths of the ordinary arrears by value sit in the two youngest buckets, but they carry only £15,600 of the provision. The money is in the tail: the £100,000 over six months old accounts for £76,000. That is the normal shape, and it is why an arrears report that shows only the total tells a fund manager almost nothing.
A tenant in administration is no longer a statistical risk; the loss is largely known. Taking it out of the matrix and providing for it on its own facts is both more accurate and easier to defend to an auditor.
Specific provision = (arrears − deposit held) × (1 − expected dividend)
(150,000 − 50,000) × (1 − 5%) = 100,000 − 5,000 = £95,000, or 63.3 per cent of what the tenant owes.
The deposit is applied first because the landlord can draw on it without waiting for the administration; only the £100,000 balance is an unsecured claim. Any rent falling due during the administration period may rank differently under the applicable insolvency rules, so it should be assessed separately from the pre-appointment arrears.
| Line | £ |
|---|---|
| Collective provision, matrix | 123,100 |
| Specific provision, tenant in administration | 95,000 |
| Closing provision | 218,100 |
| Less opening provision | −150,000 |
| Add back amounts written off in the year | 60,000 |
| Bad debt charge to income | 128,100 |
Charge = closing provision − opening provision + write-offs in the year
218,100 − 150,000 + 60,000 = £128,100, 3.2 per cent of the £4,000,000 rent.
The write-offs are added back because they consumed provision that had been charged in earlier years; without them the movement in the balance understates what the year cost. The closing provision covers 34.1 per cent of the £640,000 of total arrears, which themselves equal 1.9 months of rent.
| Loss rates | Dividend 0p | Dividend 5p | Dividend 20p | Charge, 5p case |
|---|---|---|---|---|
| Half the base rates | 161,550 | 156,550 | 141,550 | 1.7% |
| Base rates | 223,100 | 218,100 | 203,100 | 3.2% |
| One and a half times | 264,650 | 259,650 | 244,650 | 4.2% |
The administrator's dividend barely matters: moving it from nothing to 20p changes the provision by £20,000. The loss rates on the aged buckets matter far more, because they apply to the whole book of ordinary arrears. That is where the conversation with the auditor, and with the property manager's credit control, should be spent.
On the asset dashboard, report arrears by occupier and by age next to the provision against each, so that a rising tail shows up before the year end does. The rent collection rate, worked in how to calculate a rent collection rate, measures the flow; the provision measures the stock left behind.
Age the arrears, apply a loss rate to each bucket, and take any insolvent tenant out of the matrix to provide for it on its own deposit and expected dividend. Here that gives a £218,100 provision and a £128,100 charge, three times the flat allowance most plans assume. The monthly dashboard with arrears by occupier is part of the free workbook for this case, and the occupier risk that drives these numbers is priced at acquisition in how to price tenant default risk into a purchase price.
It is a table of loss rates by age of arrears, applied to the balance in each bucket, used under the IFRS 9 simplified approach. In the illustrative case rates run from 2% on arrears under 30 days to 100% on arrears over a year, and £490,000 of ordinary arrears need a £123,100 collective provision, 25.1 per cent of the balance.
Yes, but only against the tenant whose lease it secures. For a tenant in administration owing £150,000 with a £50,000 deposit and an expected 5p dividend, the provision is £100,000 less £5,000, or £95,000. Netting every deposit against every tenant's arrears understates the provision when the deposits belong to tenants who pay.
Take the closing provision, deduct the opening provision and add back amounts written off during the year. In the illustrative case £218,100 less £150,000 plus £60,000 gives a charge of £128,100, 3.2 per cent of the £4,000,000 rent, more than three times a flat 1 per cent budget allowance of £40,000.
This article is one calculation from The Real Estate Asset Manager. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: real estate investing, finance and fund management → · All 1682 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.