Charging interest on the average balance makes the debt schedule circular. A closed-form formula removes the circularity, and the choice between the two conventions moves the IRR.
A term loan of 600 at 9.0% with 100% of cash flow swept to repay it leaves 438.6 of debt after five years if interest is charged on the opening balance, and 429.8 if it is charged on the average balance. The difference is 8.7. Cumulative interest is 248.6 against 239.8, and the sponsor’s IRR is 19.17% against 19.39%, a gap of 0.22 points. Both are defensible; neither is an error, but they are not the same model.
| Item | Value |
|---|---|
| Entry EBITDA / entry multiple | 100 / 10.0x |
| Entry enterprise value | 1,000 |
| Term loan / sponsor equity | 600 / 400 |
| Interest rate | 9.0% |
| Cash flow before interest, years 1 to 5 | 70, 76, 82, 88, 94 |
| Cash sweep | 100% |
| Year 5 EBITDA / exit multiple | 140 / 10.0x |
Taxes, fees and minimum cash are left out so that only the interest convention moves. Cash flow before interest is what is left to pay interest and then repay debt.
Interest = rate × opening debt. Repayment = cash flow − interest. Closing = opening − repayment.
Excel: =C5*rate then =C5-(D5-E5). No circular reference.
Year 1: interest 9.0% × 600.0 = 54.0, repayment 70.0 − 54.0 = 16.0, closing debt 584.0.
| Opening | Interest | Repayment | Closing | |
|---|---|---|---|---|
| Year 1 | 600.0 | 54.0 | 16.0 | 584.0 |
| Year 2 | 584.0 | 52.6 | 23.4 | 560.6 |
| Year 3 | 560.6 | 50.5 | 31.5 | 529.0 |
| Year 4 | 529.0 | 47.6 | 40.4 | 488.6 |
| Year 5 | 488.6 | 44.0 | 50.0 | 438.6 |
| Total interest | 248.6 |
Interest = rate × (opening + closing) ÷ 2, and closing depends on interest, so the formula refers to itself.
Closed form: closing = (opening × (1 + r÷2) − cash flow) ÷ (1 − r÷2)
Excel: =(C5*(1+rate/2)-D5)/(1-rate/2)
Year 1: closing = (600.0 × 1.045 − 70.0) ÷ 0.955 = 583.25. Interest is 9.0% × (600.0 + 583.25) ÷ 2 = 53.25, 0.75 below the opening-balance figure, because the 70 of cash flow arrives during the year and stops costing interest on the amount it repays.
| Opening | Interest | Repayment | Closing | |
|---|---|---|---|---|
| Year 1 | 600.0 | 53.2 | 16.8 | 583.2 |
| Year 2 | 583.2 | 51.4 | 24.6 | 558.6 |
| Year 3 | 558.6 | 48.8 | 33.2 | 525.4 |
| Year 4 | 525.4 | 45.4 | 42.6 | 482.8 |
| Year 5 | 482.8 | 41.1 | 52.9 | 429.8 |
| Total interest | 239.8 |
With iterative calculation switched on, a spreadsheet reaches the same answer: starting from the opening balance as the guess, it converges to within 0.001 in 5 passes for any year, and the debt after five years is 429.85, the same as the closed form to two decimals.
| Opening balance | Average balance | |
|---|---|---|
| Total interest, five years | 248.6 | 239.8 |
| Debt at exit | 438.6 | 429.8 |
| Equity at exit | 961.4 | 970.2 |
| Multiple of money | 2.40x | 2.43x |
| Equity IRR | 19.17% | 19.39% |
The average-balance model reports 8.7 less debt and 8.7 less interest, and an IRR 0.22 points higher. The opening-balance model is the conservative one: it charges interest as if no repayment had happened until the last day of the year.
At 12.0% the gap narrows, because interest takes a larger share of cash flow, less is repaid during each year and the two balances sit closer together. Debt at exit is 545.1 on the opening balance and 541.2 on the average balance, a gap of 3.9. The IRR is 16.41% against 16.51%, a gap of 0.10 points, against 0.22 at 9.0%. The convention matters most when a loan is repaid fast.
Free Excel template: LBO model.
Sixteen video lessons that build a complete LBO model in Excel on one case, from sources and uses to sponsor returns, with step files, exercises that check your answers, a 160-question quiz and the LBO Model, Pro Edition included ($149).
Interest on the average balance. On a 600 loan at 9.0% with a full sweep, the IRR is 19.39% against 19.17% on the opening balance, because less interest is charged on debt repaid during the year.
Solve for the closing balance directly: closing = (opening × (1 + r÷2) − cash flow) ÷ (1 − r÷2). In year 1 that gives 583.25 from 600.0, the same as an iterative calculation.
On this case 8.7 of debt at exit and 8.7 of interest over five years at 9.0%. At 12.0% the debt gap is 3.9.
The free companion files for Financial Modeling from First Principles build a three-statement model, a debt schedule and a buyout from blank sheets, with the checks that catch a wrong base. The buyout in this article is invented and is not in them. No account and no email address.
Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
Reading guide: corporate finance, valuation and markets → · All 1908 articles →