Debtor Book Stress Test
A free spreadsheet that works out how much cash an invoice finance facility would let you draw against your debtor book, then reruns the sum under six stresses: your largest customer taking a bigger share, customers paying a month later, more credit notes, a cut in the advance rate, the funder dropping your largest customer, and the first four together. It shows the cash each stress takes away and whether you would end up owing the funder money. In the worked example below, a facility with £14,751 of headroom is over-advanced in 4 of the 6 stressed scenarios. No email needed to download.
Download the stress test
Excel workbook (.xlsx), opens in Excel, Google Sheets, LibreOffice or Numbers. Five sheets: how to use, terms and stress sizes, results, your debtor book, and the workings for every scenario. Room for 30 customers. Free under a Creative Commons Attribution licence.
Need a plain grid to import elsewhere? The CSV version has the debtor book columns and the example rows, with no formulas.
To stress-test a debtor book for invoice finance: for each customer, take the balances inside the funder's age limit, subtract disputed and contra amounts, and cut anything above the concentration limit (a % of the whole book). Total that as eligible debt, deduct dilution (eligible debt x dilution %), and multiply by the advance rate to get availability. Headroom is availability minus what you have drawn. Then change one input at a time (a bigger largest customer, slower payment, higher dilution, a lower advance rate, the largest customer made ineligible) and compare. Negative headroom is the amount you would be over-advanced. More detail and scope
Covered here:
Stress-testing the cash an invoice finance facility would make available against a debtor book, customer by customer, under changes in concentration, ageing, dilution and advance rate.
Not covered here:
a single snapshot of funded availability (see Borrowing Base Calculator), what a customer's share of the book means on its own (see Debtor Concentration Checker for UK Invoice Finance), keeping the aged debtors report itself (see Aged Debtors Report Template), and the cost of a facility (see Invoice Finance Fee Comparison Spreadsheet)
Why stress-test a debtor book
On invoice finance the cash you can draw is not fixed. It is recalculated every time the funder reads your ledger, and it moves with the ledger's shape as well as its size. Four things can take it down while your sales stay the same:
- Concentration. Once one customer's balance passes the concentration limit, the excess is not funded. If that customer grows while others shrink, more of the book falls above the line. The debtor concentration checker explains the limit on its own; here you see what it does to cash.
- Ageing. Invoices past the funder's age limit drop out of eligible debt. A few customers paying a month later can push balances over the limit without any bad debt at all.
- Dilution. Credit notes, discounts and disputes mean some invoiced value never arrives as cash. When that rises, funders lend less against the same book. Our explainer on dilution in factoring covers where it comes from.
- The advance rate itself. A funder can reduce the advance at a facility review, or after the other three have moved.
The model also runs one sharper case: the funder stops funding your largest customer, for example after its credit limit is withdrawn. If you have drawn close to your limit, any of these can leave you over-advanced, owing the funder the difference at the moment cash is already tight. Knowing which stress bites hardest tells you what to watch in your own ledger.
Try it in your browser
The same model runs below, starting with the worked example. Overwrite any figure and the results update. Four blank rows are there for extra customers; the download has room for 30. We note the figures you enter and the result, so we can see whether the tool helps; this is never linked to an enquiry (see our privacy policy).
Facility terms
Stress sizes
Debtor book (balances by age from invoice date, £)
| Customer | 0-30 days | 31-60 days | 61-90 days | 91-120 days | Over 120 days | Disputed or contra | Total |
|---|---|---|---|---|---|---|---|
| £112,000 | |||||||
| £45,000 | |||||||
| £40,000 | |||||||
| £25,000 | |||||||
| £27,000 | |||||||
| £18,000 | |||||||
| £12,000 | |||||||
| £12,000 | |||||||
Results by scenario
| Scenario | Eligible debt | Availability | Change vs base | Headroom |
|---|---|---|---|---|
| Base case Your book and terms as they stand | £245,800 | £204,751 | £0 | £14,751 |
| Concentration Largest customer grows to 50% of the book, same total | £214,920 | £179,028 | -£25,723 | -£10,972 |
| Ageing 50% of every age bucket paid a month later | £236,050 | £196,630 | -£8,122 | £6,630 |
| Dilution Dilution rises from 2% to 6% | £245,800 | £196,394 | -£8,357 | £6,394 |
| Advance haircut Advance cut by 10 points to 75% | £245,800 | £180,663 | -£24,088 | -£9,337 |
| Largest customer ineligible Funder stops funding your largest customer | £158,500 | £132,031 | -£72,721 | -£57,970 |
| Combined Concentration, ageing, dilution and advance haircut together | £206,995 | £145,931 | -£58,820 | -£44,069 |
On the base case you would be over-advanced if the advance rate fell below 78.9% or dilution rose above 9.1%.
Negative headroom is the amount you would be over-advanced. Example customers, balances and terms are invented to show how the model works, not any provider's terms.
Worked example
A business has £291,000 owed by 8 customers and has drawn £190,000 from its facility. Its largest customer, Customer A, owes £112,000, 38.5% of the book. One customer is also a supplier, so there is a contra balance, and another has an invoice in dispute. The customers, balances, terms and stress sizes are invented to show how the model behaves, and they are the example rows in the download.
| Input | Example figure |
|---|---|
| Advance rate | 85% |
| Age limit | 90 days from invoice date |
| Concentration limit | 30% of the debtor book per customer |
| Dilution rate | 2% |
| Already drawn from the facility | £190,000 |
| Concentration stress | Largest customer grows to 50% of the book, total unchanged |
| Ageing stress | 50% of every age bucket paid a month later |
| Dilution stress | Dilution rises to 6% |
| Advance haircut | Advance rate cut by 10 points |
Illustrative terms and stress sizes chosen to show how the model behaves. Not any provider's terms and not a forecast.
View as plain-text Markdown
### Worked example: facility terms and stress sizes | Input | Example figure | | --- | --- | | Advance rate | 85% | | Age limit | 90 days from invoice date | | Concentration limit | 30% of the debtor book per customer | | Dilution rate | 2% | | Already drawn from the facility | £190,000 | | Concentration stress | Largest customer grows to 50% of the book, total unchanged | | Ageing stress | 50% of every age bucket paid a month later | | Dilution stress | Dilution rises to 6% | | Advance haircut | Advance rate cut by 10 points | Illustrative terms and stress sizes chosen to show how the model behaves. Not any provider's terms and not a forecast.
| Customer | 0-30 days | 31-60 days | 61-90 days | 91-120 days | Over 120 days | Disputed or contra | Total | Note |
|---|---|---|---|---|---|---|---|---|
| Customer A | £62,000 | £38,000 | £12,000 | £0 | £0 | £0 | £112,000 | Largest customer |
| Customer B | £28,000 | £14,000 | £3,000 | £0 | £0 | £0 | £45,000 | |
| Customer C | £21,000 | £9,000 | £6,000 | £4,000 | £0 | £0 | £40,000 | |
| Customer D | £15,000 | £8,000 | £2,000 | £0 | £0 | £4,000 | £25,000 | Also a supplier: contra balance |
| Customer E | £9,000 | £7,000 | £5,000 | £3,500 | £2,500 | £2,500 | £27,000 | One invoice in dispute |
| Customer F | £12,000 | £6,000 | £0 | £0 | £0 | £0 | £18,000 | |
| Customer G | £7,000 | £3,000 | £2,000 | £0 | £0 | £0 | £12,000 | |
| Customer H | £4,000 | £2,500 | £1,500 | £1,000 | £3,000 | £0 | £12,000 | Slow payer |
Invented customers and balances, aged from invoice date.
View as plain-text Markdown
### Worked example: debtor book of £291,000 across 8 customers | Customer | 0-30 days | 31-60 days | 61-90 days | 91-120 days | Over 120 days | Disputed or contra | Total | Note | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Customer A | £62,000 | £38,000 | £12,000 | £0 | £0 | £0 | £112,000 | Largest customer | | Customer B | £28,000 | £14,000 | £3,000 | £0 | £0 | £0 | £45,000 | | | Customer C | £21,000 | £9,000 | £6,000 | £4,000 | £0 | £0 | £40,000 | | | Customer D | £15,000 | £8,000 | £2,000 | £0 | £0 | £4,000 | £25,000 | Also a supplier: contra balance | | Customer E | £9,000 | £7,000 | £5,000 | £3,500 | £2,500 | £2,500 | £27,000 | One invoice in dispute | | Customer F | £12,000 | £6,000 | £0 | £0 | £0 | £0 | £18,000 | | | Customer G | £7,000 | £3,000 | £2,000 | £0 | £0 | £0 | £12,000 | | | Customer H | £4,000 | £2,500 | £1,500 | £1,000 | £3,000 | £0 | £12,000 | Slow payer | Invented customers and balances, aged from invoice date.
| Scenario | What changes | Debtor book | Over age limit | Disputed or contra | Concentration excess | Largest customer removed | Eligible debt | Dilution deduction | Advance rate | Availability | Change vs base | Headroom |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Base case | Your book and terms as they stand | £291,000 | £14,000 | £6,500 | £24,700 | £0 | £245,800 | £4,916 | 85% | £204,751 | £0 | £14,751 |
| Concentration | Largest customer grows to 50% of the book, same total | £291,000 | £11,380 | £6,500 | £58,200 | £0 | £214,920 | £4,298 | 85% | £179,028 | -£25,723 | -£10,972 |
| Ageing | 50% of every age bucket paid a month later | £291,000 | £29,750 | £6,500 | £18,700 | £0 | £236,050 | £4,721 | 85% | £196,630 | -£8,122 | £6,630 |
| Dilution | Dilution rises from 2% to 6% | £291,000 | £14,000 | £6,500 | £24,700 | £0 | £245,800 | £14,748 | 85% | £196,394 | -£8,357 | £6,394 |
| Advance haircut | Advance cut by 10 points to 75% | £291,000 | £14,000 | £6,500 | £24,700 | £0 | £245,800 | £4,916 | 75% | £180,663 | -£24,088 | -£9,337 |
| Largest customer ineligible | Funder stops funding your largest customer | £291,000 | £14,000 | £6,500 | £24,700 | £87,300 | £158,500 | £3,170 | 85% | £132,031 | -£72,721 | -£57,970 |
| Combined | Concentration, ageing, dilution and advance haircut together | £291,000 | £27,100 | £6,500 | £50,405 | £0 | £206,995 | £12,420 | 75% | £145,931 | -£58,820 | -£44,069 |
Source: Market Invoice debtor book stress test
Computed by the model on this page from the debtor book and terms above. Figures rounded to the nearest pound. Negative headroom is the amount over-advanced.
View as plain-text Markdown
### Worked example results: availability £204,751 in the base case against £190,000 drawn | Scenario | What changes | Debtor book | Over age limit | Disputed or contra | Concentration excess | Largest customer removed | Eligible debt | Dilution deduction | Advance rate | Availability | Change vs base | Headroom | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Base case | Your book and terms as they stand | £291,000 | £14,000 | £6,500 | £24,700 | £0 | £245,800 | £4,916 | 85% | £204,751 | £0 | £14,751 | | Concentration | Largest customer grows to 50% of the book, same total | £291,000 | £11,380 | £6,500 | £58,200 | £0 | £214,920 | £4,298 | 85% | £179,028 | -£25,723 | -£10,972 | | Ageing | 50% of every age bucket paid a month later | £291,000 | £29,750 | £6,500 | £18,700 | £0 | £236,050 | £4,721 | 85% | £196,630 | -£8,122 | £6,630 | | Dilution | Dilution rises from 2% to 6% | £291,000 | £14,000 | £6,500 | £24,700 | £0 | £245,800 | £14,748 | 85% | £196,394 | -£8,357 | £6,394 | | Advance haircut | Advance cut by 10 points to 75% | £291,000 | £14,000 | £6,500 | £24,700 | £0 | £245,800 | £4,916 | 75% | £180,663 | -£24,088 | -£9,337 | | Largest customer ineligible | Funder stops funding your largest customer | £291,000 | £14,000 | £6,500 | £24,700 | £87,300 | £158,500 | £3,170 | 85% | £132,031 | -£72,721 | -£57,970 | | Combined | Concentration, ageing, dilution and advance haircut together | £291,000 | £27,100 | £6,500 | £50,405 | £0 | £206,995 | £12,420 | 75% | £145,931 | -£58,820 | -£44,069 | Source: Market Invoice debtor book stress test Computed by the model on this page from the debtor book and terms above. Figures rounded to the nearest pound. Negative headroom is the amount over-advanced.
What the example shows
- Only 84.5% of the book is eligible even before any stress. Of £291,000 owed, £14,000 is past the 90-day age limit, £6,500 is disputed or contra, and £24,700 of Customer A's balance sits above the 30% concentration limit. Availability is £204,751, leaving £14,751 of headroom over the £190,000 drawn.
- Slower payment and higher dilution each eat about half the headroom. With 50% of every bucket paid a month later, availability falls by £8,122; with dilution at 6% it falls by £8,357. Neither tips the business over on its own.
- Concentration and a cut advance each tip it over. If Customer A grows to 50% of the same-sized book, the excess over the limit rises to £58,200 and headroom becomes -£10,972. A 10-point cut in the advance leaves it at -£9,337. The break-even advance rate is 78.9%, so any cut of more than 6.1 points would do it.
- Losing the largest customer is the worst single case. Largest customer ineligible takes £72,721 off availability, leaving the business £57,970 over-advanced. That is the number to have an answer for if one customer is this large.
- Together they leave a real hole. All four main stresses at once take availability to £145,931, £58,820 below the base case and £44,069 short of what has been drawn. That is a little less than the four single changes added up, because each stress works on a book the others have already shrunk.
How each line is calculated
Every figure in the workbook is a visible formula, with one block per scenario on the Workings sheet, so you can check it.
- Concentration stress: the largest customer's balances (largest by total in your book as entered) are scaled so it reaches the share you set, and every other customer is scaled down in proportion so the book stays the same size.
- Ageing stress: the share you set of every age bucket moves into the next older bucket. Balances already over 120 days stay there. The book stays the same size.
- In-age debt = balances in the buckets inside the age limit.
- Eligible before the limit = in-age debt minus disputed or contra amounts, never below zero.
- Concentration excess = any part of a customer's eligible debt above the concentration limit % x the whole debtor book.
- Eligible debt = eligible before the limit minus concentration excess, added up across customers. In the ineligible scenario the largest customer's eligible debt is set to zero.
- Dilution deduction = eligible debt x dilution %.
- Availability = (eligible debt minus dilution deduction) x advance rate.
- Headroom = availability minus what you have already drawn.
- Break-even advance rate = drawn / (eligible debt minus dilution deduction), on the base case. Break-even dilution = 1 minus drawn / (eligible debt x advance rate).
What it does not cover
This is a simplified borrowing base. Facilities are not all built the same way: some hold a dilution reserve or lower the advance rate rather than deducting dilution, some treat a customer's whole balance as ineligible once part of it is too old, many set different concentration limits for named customers, and some hold a separate availability reserve. Set the Terms sheet to match your facility letter. The model does not add fees or interest, does not model bad debt or credit insurance, and does not say how a funder would respond to an over-advance, which depends on your agreement and your relationship with them.
To build the debtor book, start from an aged debtors report. For a quick single figure rather than scenarios, the borrowing base calculator runs the base case from a few totals. Our explainers on debtor concentration and concentration risk in factoring cover why funders set limits at all.
Founder & Managing Director, Muswell Rose, founder and PSC of Best Business Loans Ltd
Adam was managing director of Penny, a UK invoice finance business, from July 2020 to November 2023. His career runs through insurance, mortgages, commercial finance and fintech lending. He writes the Market Invoice library. Career record.
Last updated: