Articles

How much does charging interest on the average balance change an LBO model?

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.

The assumptions

Illustrative buyout, all figures in millions
ItemValue
Entry EBITDA / entry multiple100 / 10.0x
Entry enterprise value1,000
Term loan / sponsor equity600 / 400
Interest rate9.0%
Cash flow before interest, years 1 to 570, 76, 82, 88, 94
Cash sweep100%
Year 5 EBITDA / exit multiple140 / 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.

Step 1: interest on the opening balance

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-balance convention
OpeningInterestRepaymentClosing
Year 1600.054.016.0584.0
Year 2584.052.623.4560.6
Year 3560.650.531.5529.0
Year 4529.047.640.4488.6
Year 5488.644.050.0438.6
Total interest248.6

Step 2: interest on the average balance

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.

Average-balance convention, solved algebraically
OpeningInterestRepaymentClosing
Year 1600.053.216.8583.2
Year 2583.251.424.6558.6
Year 3558.648.833.2525.4
Year 4525.445.442.6482.8
Year 5482.841.152.9429.8
Total interest239.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.

Step 3: what it does to the return

Exit at 10.0x year-5 EBITDA of 140 = enterprise value 1,400
Opening balanceAverage balance
Total interest, five years248.6239.8
Debt at exit438.6429.8
Equity at exit961.4970.2
Multiple of money2.40x2.43x
Equity IRR19.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.

What if: the loan costs 12.0%

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.

The common mistakes

Takeaway

Free Excel template: LBO model.

Learn to build the whole 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).

Watch lesson 1.1 and try ten quiz questions, free →

Questions readers ask

Does interest on the average balance or the opening balance give a higher IRR?

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.

How do you avoid a circular reference in an LBO debt schedule?

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.

How big is the difference between the two conventions?

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 workbooks behind this article

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.

Open the companion files →

Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →

Also on this site

Reading guide: corporate finance, valuation and markets → · All 1908 articles →