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.

Download Excel model

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:

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 days31-60 days61-90 days91-120 daysOver 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.

Worked example: facility terms and stress sizes
InputExample figure
Advance rate85%
Age limit90 days from invoice date
Concentration limit30% of the debtor book per customer
Dilution rate2%
Already drawn from the facility£190,000
Concentration stressLargest customer grows to 50% of the book, total unchanged
Ageing stress50% of every age bucket paid a month later
Dilution stressDilution rises to 6%
Advance haircutAdvance 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.
Worked example: debtor book of £291,000 across 8 customers
Customer0-30 days31-60 days61-90 days91-120 daysOver 120 daysDisputed or contraTotalNote
Customer A£62,000£38,000£12,000£0£0£0£112,000Largest 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,000Also a supplier: contra balance
Customer E£9,000£7,000£5,000£3,500£2,500£2,500£27,000One 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,000Slow 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.
Worked example results: availability £204,751 in the base case against £190,000 drawn
ScenarioWhat changesDebtor bookOver age limitDisputed or contraConcentration excessLargest customer removedEligible debtDilution deductionAdvance rateAvailabilityChange vs baseHeadroom
Base caseYour book and terms as they stand£291,000£14,000£6,500£24,700£0£245,800£4,91685%£204,751£0£14,751
ConcentrationLargest customer grows to 50% of the book, same total£291,000£11,380£6,500£58,200£0£214,920£4,29885%£179,028-£25,723-£10,972
Ageing50% of every age bucket paid a month later£291,000£29,750£6,500£18,700£0£236,050£4,72185%£196,630-£8,122£6,630
DilutionDilution rises from 2% to 6%£291,000£14,000£6,500£24,700£0£245,800£14,74885%£196,394-£8,357£6,394
Advance haircutAdvance cut by 10 points to 75%£291,000£14,000£6,500£24,700£0£245,800£4,91675%£180,663-£24,088-£9,337
Largest customer ineligibleFunder stops funding your largest customer£291,000£14,000£6,500£24,700£87,300£158,500£3,17085%£132,031-£72,721-£57,970
CombinedConcentration, ageing, dilution and advance haircut together£291,000£27,100£6,500£50,405£0£206,995£12,42075%£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

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.

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.

AP

Adam Parker

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:

Is Your Facility Built for Your Ledger?

If one customer or slow payers keep squeezing what you can draw, a different facility structure may fit better. Tell us about your business. We pass your details to one or more of the finance partners we work with, who come back to you with quotes. Free, no obligation.

Step 1 of 3 · Your business

Start typing and we'll search Companies House.

Free to you. We may be paid by the finance partners we introduce you to. This could be a fee or commission, depending on the partner and the finance arranged. It does not change what you pay. See our privacy policy.

FreeNo obligationNothing to pay us

How we make money: Market Invoice is an independent comparison service, not a lender. We may be paid by the finance partners we introduce you to. This could be a fee or commission, depending on the partner and the finance arranged. It does not change what you pay. How we are funded.

Debtor Book Stress Test FAQ

How is this different from the borrowing base calculator?

The borrowing base calculator works out what your ledger funds today from a handful of totals. This model takes the ledger customer by customer, split by age, and reruns the same sum under six stresses, so you can see which change would hurt most and whether any of them would leave you owing the funder money. Use the calculator for a quick figure and this model before you rely on the facility for a payroll or a large supplier payment.

What does negative headroom mean?

It means you have drawn more than the facility would allow after the stress. It is often called an overpayment or over-advance. Your facility agreement should say how quickly you would have to pay the difference back. In the worked example, 4 of the 6 stressed scenarios leave the business over-advanced, and losing funding on the largest customer does the most damage at -£57,970.

What is dilution and how do I work out my rate?

Dilution is the part of what you invoice that never turns into cash for reasons other than bad debt: credit notes, settlement discounts, rebates, returns and disputes. A rough rate is credit notes and other reductions over the last 12 months divided by invoices raised over the same period. The model deducts it from eligible debt, which is one of several ways funders allow for it.

Why does the concentration stress keep the book the same size?

Because the risk you are testing is dependence, not growth. If your largest customer's share rises because other customers are buying less, your debtor book stays roughly the same but more of it sits above the concentration limit. Setting the share lower than today's shows the opposite, how much cash diversifying would free up.

Should I age by invoice date or by due date?

Match whatever your facility letter uses for its age limit. The model counts the buckets from invoice date. If your funder's limit runs from the due date, fill the buckets by days overdue instead and set the age limit to the same basis.

Are the example figures typical of the market?

No. The customers, balances, terms and stress sizes are invented to show how the model behaves. They are not any provider's terms and not a view on what a normal debtor book looks like. Replace them with your own figures.