Unsecured loan affordability stress test spreadsheet

Download the spreadsheet

The workbook has two sheets. Stress test holds your figures in yellow cells and calculates everything else with ordinary spreadsheet formulas you can inspect. Notes explains each input and every assumption. It opens in Excel, Google Sheets, Numbers and LibreOffice.

You will need: the loan amount, rate and term (or a lender's quoted monthly repayment), annual revenue, gross margin, normalised EBITDA from your latest filed accounts, and what you already pay each year on other borrowing.

What it tests

• Revenue falls. Direct costs fall with sales, overheads don't, so EBITDA drops by revenue times the fall times your gross margin. This is usually the stress that bites hardest, because a small fall in sales is a much bigger fall in profit.
• Overheads rise. Rent, wages or energy go up while sales stay flat. Overheads are taken as revenue times gross margin, minus EBITDA.
• The rate rises. The repayment is recalculated at a higher rate for the whole term. If your loan is fixed for the whole term this doesn't apply to it; it matters for variable-rate borrowing or a planned refinance.
• A straight EBITDA haircut. The same 15% test the DSCR calculator uses, kept here so the two tools agree.
• Combined. Revenue down, overheads up and the rate up at the same time, because bad years rarely bring just one problem.

Worked example

Illustrative figures, not a benchmark: a limited company borrowing £150,000 unsecured at 12% over 48 months. 12% sits inside the advertised range for unsecured term loans on our business loan interest rates page. The company turns over £900,000 at a 35% gross margin, makes £120,000 normalised EBITDA and already pays £25,000 a year on other borrowing.

### Worked example inputs and derived figures

| Item | Value |
| --- | --- |
| New loan | £150,000 |
| Rate and term | 12% a year, 48 months |
| Monthly repayment | £3,950.08 |
| Annual repayment, new loan | £47,401 |
| Existing annual debt service | £25,000 |
| Total annual debt service | £72,401 |
| Annual revenue / gross margin | £900,000 / 35% |
| Normalised EBITDA | £120,000 |
| Overheads implied | £195,000 |
| Total repaid over the term | £189,604 |
| Total interest | £39,604 |

Source: FundBiz unsecured loan affordability stress test, illustrative figures

Monthly repayment is a standard amortising repayment (spreadsheet PMT function). A lender's quote may differ because it includes fees; enter the quoted repayment in the workbook if you have one.

### Stress scenarios: DSCR of the worked example under each downside

| Scenario | EBITDA | Total annual debt service | DSCR | EBITDA headroom over 1.25 | Result |
| --- | --- | --- | --- | --- | --- |
| Base case | £120,000 | £72,401 | 1.66 | £29,499 | Meets 1.25 |
| Revenue falls 10% | £88,500 | £72,401 | 1.22 | -£2,001 | Below 1.25, above 1.0 |
| Overheads rise 5% | £110,250 | £72,401 | 1.52 | £19,749 | Meets 1.25 |
| Rate rises 2 points | £120,000 | £74,188 | 1.62 | £27,265 | Meets 1.25 |
| EBITDA haircut 15% | £102,000 | £72,401 | 1.41 | £11,499 | Meets 1.25 |
| Combined: revenue -10%, overheads +5%, rate +2 points | £78,750 | £74,188 | 1.06 | -£13,985 | Below 1.25, above 1.0 |

Source: FundBiz unsecured loan affordability stress test, illustrative figures

DSCR = EBITDA / total annual debt service. Headroom is EBITDA minus 1.25 times debt service; a negative figure is the EBITDA shortfall against 1.25. Stress settings are illustrative, not a lender standard.

The base case looks comfortable at 1.66. But a 10% fall in revenue takes £31,500 off EBITDA, about 26% of it, and cover drops to 1.22, under 1.25. In the combined downside it is 1.06: the repayments are still just covered, with almost nothing to spare for tax or a bad month.

### How far can it fall? Worked example

| Measure | Value |
| --- | --- |
| Revenue fall that takes DSCR to 1.25 | 9.4% |
| Revenue fall that takes DSCR to 1.0 | 15.1% |
| EBITDA fall that takes DSCR to 1.25 | 24.6% |
| Largest loan at 12% over 48 months keeping base DSCR at 1.25 | £224,679 |

Source: FundBiz unsecured loan affordability stress test, illustrative figures

Revenue falls assume direct costs fall in line and overheads stay fixed. The largest loan works back from the annual repayment that leaves EBITDA at exactly 1.25 times total debt service.

So this business can lose about 9.4% of its sales before cover dips under 1.25, and about 15.1% before the repayments aren't covered at all. That is a more useful conversation with a lender, or with yourself, than a single ratio.

Web version

Same formulas as the spreadsheet. Change any figure and the scenarios update.

Reading the result

• Every scenario meets your target. The loan looks affordable on these figures. Check the combined row still holds on your weakest quarter if trade is seasonal.
• The base case passes but a single stress fails. The loan is affordable only if trading holds. A longer term, a smaller amount (see the largest-loan figure) or clearing an expensive existing facility first all lift cover.
• The base case is under 1.0. The business can't carry this repayment from current profit. Most lenders will decline, and borrowing it anyway risks the personal guarantee most unsecured loans carry; read the personal guarantee checklist before signing one.

The most common reason a lender's ratio comes out lower than your own is debt you left out: a merchant cash advance, asset finance in a trading name, or a director loan the company services. Put all of it in existing debt service.

FAQs