Calculating historical loss rates

How do you calculate historical loss rates?

1. Take snapshots. At the end of each past quarter, record the receivables in each ageing bucket: current, 1 to 30 days past due, 31 to 60, 61 to 90 and over 90.
2. Follow each cohort. Track those balances over the following months, usually 12, until each invoice is paid or written off.
3. Attribute write-offs to the starting bucket. An invoice that was current at the quarter end and later written off counts as a loss from the current bucket, whatever bucket it passed through.
4. Net off recoveries received after write-off.
5. Divide and average. Loss rate = written off / balance, for each bucket and quarter, then average across quarters, weighting by balance.

A loss rate calculation from eight quarters

A distributor has eight quarters of ageing snapshots with their outcomes. In the first quarter, for example, it had 776,000.0 of current receivables, of which 3,500.0 was later written off, a loss rate of 0.45%. Adding up all eight quarters:

The rates rise steeply with age: an invoice more than 90 days overdue is about a hundred times more likely to be lost than a current one. The History sheet of the Loss rate builder (Excel) holds the quarter-by-quarter data and calculates balance-weighted and simple averages, and the highest quarter for context.

Which period should loss rates come from?

Long enough to be representative, usually two to five years for short-term receivables, and ideally including a weaker period. Very old data may no longer reflect the current customer base or credit policy. Quarters too recent for their outcome to be known, typically the last four, are left out of the calculation, because their write-offs are not yet complete.

Balance-weighted or simple average?

A balance-weighted average gives each quarter influence in proportion to its size, which is usually the better measure. A simple average treats every quarter equally, which can give too much weight to small, unusual quarters. Here the two are almost identical; when they differ a lot, the reason should be understood before choosing.

What if a company has little history?

A new company, or one that has rarely written off a debt, may have too few losses to calculate reliable rates. Options include using several years of data combined into one pool, using loss rates from comparable companies or industry data, or starting from the rate implied by customers' credit ratings or credit insurance terms. Whatever the source, the reasoning should be documented and the rates refined as the company's own history builds up. A loss rate of zero is rarely supportable for any bucket.

How are recoveries and credit notes treated?

Recoveries after write-off reduce the loss. Credit notes for returns, pricing disputes or rebates are not credit losses and should be excluded from write-offs, otherwise loss rates are overstated; they are dealt with through revenue.

What happens next?

Before relying on the rates, check them against the provision matrix mistakes auditors find. Historical rates describe the past. IFRS 9 requires them to be adjusted for current conditions and forecasts; see forward-looking adjustments to a provision matrix. Rates should also be calculated separately for groups of customers with different risk; see segmenting receivables and the IFRS 9 provision matrix.

This guide is general information. It is not tax or legal advice for your situation.