Asset finance balloon and residual value working model

Download the spreadsheet

The workbook has three sheets. Model holds your figures in yellow cells, shows the cost of the agreement and runs three end-value scenarios side by side. Schedule lists every month: the payment, the finance owed, the asset's value and the equity. Notes explains each input and the method. The formulas are ordinary spreadsheet functions (PMT, RATE, IRR) that work in Excel, Google Sheets and LibreOffice.

You will need: the quote (price, deposit, term, balloon, and either the interest rate or the monthly payment, plus any fees) and your own estimate of what the asset will sell for at the end of the term.

How the model works

1. Amount financed is the price less the deposit.
2. Monthly payment repays the amount financed down to the balloon, not to zero, at one twelfth of the annual rate each month. It is the spreadsheet PMT function with the balloon as the amount left at the end, with payments a month after signing or, if you choose, in advance.
3. Interest is everything you pay in monthly payments and the balloon, less the amount financed. Running the same agreement with no balloon shows how much of that interest the balloon causes.
4. APR-equivalent is the annual rate at which the payments, the balloon and any fees, discounted back to the start with each month counted as one twelfth of a year, are worth the amount financed. This is the equation UK consumer credit APRs use for credit not secured on land (FCA Handbook CONC App 1.2), rounded to one decimal place.
5. Asset value starts at what the asset would fetch on day one and falls by the same percentage each month to the value you expect at the end. Equity is that value less the finance owed after the month's payment. When it is negative, selling the asset wouldn't clear the finance.

If your quote gives a monthly payment but no rate you trust, type the payment into the optional box and the model finds the rate from it (the spreadsheet RATE function).

Worked example

Illustrative figures, not a quote or a market benchmark: a £60,000 machine with a £6,000 deposit, 48 monthly payments at 9% a year, an £18,000 balloon (30% of the price) and no fees. You expect it to sell for £21,000 at the end of the term.

### Worked example: cost of the agreement

| Item | Value |
| --- | --- |
| Amount financed | £54,000 |
| Monthly payment | £1,030.86 |
| Monthly payment with no balloon | £1,343.79 |
| Monthly saving from the balloon | £312.93 |
| Total of 48 monthly payments | £49,481 |
| Interest with the balloon | £13,481 |
| Interest with no balloon | £10,502 |
| Extra interest caused by the balloon | £2,979 |
| Total payable, including deposit | £73,481 |
| APR-equivalent | 9.4% |

Source: FundBiz asset finance balloon working model, illustrative figures

Every figure is derived from the stated inputs with the formulas described above. The APR-equivalent is above 9% because 9% is a nominal rate charged monthly: twelve monthly compounding steps of 0.75% make 9.4% a year.

The balloon saves £312.93 a month, £15,021 over the term, and then asks for £18,000 in one go. The difference between those two, £2,979, is the interest on money you kept borrowing for longer.

Finance owed against what the asset is worth

### Finance owed, asset value and equity by year: worked example

| Month | Finance owed | Asset value | Equity |
| --- | --- | --- | --- |
| 0 | £54,000 | £60,000 | £6,000 |
| 12 | £46,172 | £46,150 | -£22 |
| 24 | £37,610 | £35,496 | -£2,113 |
| 36 | £28,244 | £27,302 | -£942 |
| 48 | £18,000 | £21,000 | £3,000 |

Source: FundBiz asset finance balloon working model, illustrative figures

Finance owed is the balance after that month's payment; in month 48 it is the balloon. Asset value falls by the same percentage each month from £60,000 to the £21,000 you expect at the end: an assumption, not a valuation.

The asset ends up worth £3,000 more than the balloon, which looks comfortable. But the middle of the term tells a different story: the finance owed is more than the asset is worth for 28 of the 48 months, at worst by £2,132 in month 25. If the business had to sell or return the machine then, it would have to find that gap.

Same agreement, different end values

### Residual value scenarios: equity at the balloon date

| Scenario | Market value at month 48 | Balloon due | Equity at the balloon date | Months in negative equity | Worst point |
| --- | --- | --- | --- | --- | --- |
| End value 25% lower | £15,750 | £18,000 | -£2,250 | 42 | -£7,082 (month 28) |
| Your figure | £21,000 | £18,000 | £3,000 | 28 | -£2,132 (month 25) |
| End value 25% higher | £26,250 | £18,000 | £8,250 | 0 | £1,967 (month 20) |

Source: FundBiz asset finance balloon working model, illustrative figures

Same £60,000 asset, £6,000 deposit, 48 months at 9%, £18,000 balloon. Only the expected end value changes; the value path is recalculated from £60,000 to each end value.

A 25% miss on the resale value turns £3,000 of equity into a £2,250 shortfall on the day the balloon is due, and stretches the negative equity to 42 months. The monthly payment and the interest are the same in every row: the balloon moves the resale risk onto you.

Same asset, different balloons

### Balloon size: monthly payment, interest and equity

| Balloon | Monthly payment | Interest | Months in negative equity | Equity at the balloon date |
| --- | --- | --- | --- | --- |
| £0 (0%) | £1,343.79 | £10,502 | 0 | £21,000 |
| £9,000 (15%) | £1,187.33 | £11,992 | 0 | £12,000 |
| £18,000 (30%) | £1,030.86 | £13,481 | 28 | £3,000 |
| £27,000 (45%) | £874.40 | £14,971 | 40 | -£6,000 |

Source: FundBiz asset finance balloon working model, illustrative figures

£60,000 asset, £6,000 deposit, 48 months at 9%, expected end value £21,000. The balloon percentages are illustrative inputs, not market norms.

Each step up in the balloon buys a lower monthly payment with more interest and at least as many months under water. Once the balloon passes what the asset is likely to be worth at the end, the equity at the balloon date goes negative before the market has moved at all.

Three more things that change the answer

• Fees. Add a £250 documentation fee at the start and a £150 option to purchase fee with the balloon (illustrative amounts) and the APR-equivalent rises from 9.4% to 9.7%. The monthly payment doesn't change, which is why fees are easy to miss.
• Payments in advance. Taking the first payment at signing lowers each payment to £1,023.19 and cuts the months in negative equity to 20, because the balance starts falling a month earlier.
• A lower day-one value. An asset can be worth less than the price paid as soon as it is delivered, once the dealer's margin is lost. Start the value at £54,000 (10% below the price, an illustrative figure) and the negative equity runs for 40 months, at worst £4,117. That is why the smooth value path in the model is best read as the optimistic case.

Web version

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

Reading the result

• Price the monthly saving. Set the extra interest against the monthly saving. If the lower payment is what makes the asset affordable, that may be worth it; if it's only a nicer number, you're paying for a lump sum problem later.
• Check the lower scenario, not your figure. If the balloon date shows a shortfall when the end value is 25% lower, have a plan for the gap before you sign: cash set aside, a refinance you expect to qualify for, or a smaller balloon.
• Mind the middle of the term. Negative equity only costs you if you have to sell, return or refinance early. If the asset could become surplus to the business before the end, a smaller balloon or a larger deposit shortens that window.

If the balloon is already close, see balloon payment options and what to do when an asset finance balloon is due.

FAQs