ModeldrillInterest on the opening balance
Interest expense in a model is either rate x opening debt balance (simple, no circularity) or rate x average of opening and closing balances (more accurate, but circular when repayments depend on cash). Most models start with the opening balance method and add an average-balance switch later.
Syntax
Interest = Rate x Opening balance or Rate x AVERAGE(Opening, Closing)| Part | What it is |
|---|---|
Rate | Annual interest rate for the tranche; divide by 12 for monthly periods |
Opening balance | Debt at the start of the period, linked to last period's closing |
Examples
Opening 1,000, closing 900, rate 5%
=5% * 1000Result: 50.00
Average balance method
=5% * AVERAGE(1000, 900)Result: 47.50
Switch in $B$1: 1 for average, 0 for opening
=IF($B$1=1, rate*AVERAGE(open, close), rate*open)Result: lets you turn circularity off while building
Common mistakes
- Using the closing balance alone, which understates interest in a year of drawdowns and creates circularity for no gain.
- Applying an annual rate to a monthly opening balance without dividing by 12.
- Using average balance with a cash sweep and no iteration enabled, which gives a circular reference warning and zeros.
Practice Interest on the opening balance
These drills check your answer on live data, free, no account needed.
- Interest on the opening debt balanceA term loan is repaid in equal amounts. Fill the opening balance for later years (row 5), the closing balance (row 7) and interest expense (row 8), charging the rate in B2 on the opening balance of each year.
- Cash and revolver, fully linkedNow link the cash too. Each year starts with last year's closing cash, adds the cash flow, then draws on or repays the revolver to keep the minimum in B2. Fill rows 7 to 12 (the first opening cash and opening revolver are set).
Questions
Why do interviewers ask about interest on the average balance?
To test whether you understand the circularity: average balance needs closing debt, closing debt depends on cash, cash depends on interest. The fix is iteration or a switch.
Is the opening balance method accurate enough?
For annual models with modest repayments, the difference is small (50 vs 47.5 above). Use average balances when debt changes a lot within the period.
Related
- Debt scheduleA debt schedule rolls each loan forward: closing = opening + draws - repayments, with interest on the opening balance. Layout, example, amortization, sweeps.
- Circular referencesInterest on the average debt balance makes a model circular. How to enable iterative calculation in Excel, add a circularity switch and fix a broken model.
- RevolverA revolver draws when cash falls below the minimum and repays from excess cash. The MIN and MAX formula, a two-year worked example and avoiding circularity.
- EBITDA to net incomeNet income = EBITDA - D&A - interest - taxes. Each step of the bridge (EBIT, EBT, tax at the rate, net income) with exact numbers and the tax-on-losses rule.