The debt schedule and circular references

The debt schedule is where a model earns its keep. It answers one question every quarter: how much does this company owe, and what does that debt cost? Get it right and your three statements tie. Get it wrong and the balance sheet stops balancing.

It's also where you meet the circular reference, the loop that makes first-time modelers panic. Don't. It's expected, it's manageable, and knowing why it happens is exactly the kind of thing a good interviewer probes.

What a debt schedule actually does

A debt schedule is a small table, one column per period, that rolls each piece of debt from a beginning balance to an ending balance. The template is always the same:

$\text{Ending debt} = \text{Beginning debt} - \text{Repayment} + \text{Drawdown}$

Most companies carry more than one layer. A term loan and senior notes sit on top of a revolving credit facility, and each layer gets its own row. The order matters because cheaper, safer debt gets repaid on its own mandatory schedule, while the flexible piece absorbs whatever cash is left over.

Here is a clean two-tranche year:

| Line | Beginning | Repayment | Ending | |---|---|---|---| | Term loan | 500 | (50) | 450 | | Revolver | 100 | (40) | 60 | | Total debt | 600 | (90) | 510 |

The term loan pays down 50 on its mandatory amortization schedule no matter what. The revolver moves based on how much cash the business had left after everything else.

The revolver is the plug

The revolver is a line of credit the company can draw on when it runs short and repay when it has extra. In a model, it plays a specific role: it's the balancing plug for cash.

Think of the logic each period. You calculate cash generated from operations, subtract mandatory debt repayments and capex, and see where you land. If the company runs a shortfall, it draws on the revolver to keep cash from going negative. If it generates a surplus, it sweeps that cash to pay the revolver down.

That repay-when-flush behavior is the cash sweep: excess cash automatically pays down the most expensive or most junior debt first. In an LBO that sweep is the whole engine of returns, because every dollar of debt retired is a dollar of equity created.

Why the circular reference shows up

Now the loop. Follow the chain of dependencies:

1. Interest expense depends on the debt balance (more debt, more interest).
2. Interest hits the income statement, so it changes net income.
3. Net income flows into the cash flow statement, so it changes how much cash the business generates.
4. Cash determines how much debt the company can repay (or must draw).
5. Repaying debt changes the debt balance, which sends you back to step 1.

The debt balance depends on interest, and interest depends on the debt balance. That's a circle. Excel can't resolve it in a single left-to-right pass because the answer feeds back into the input.

$\text{Interest} \rightarrow \text{Net income} \rightarrow \text{Cash} \rightarrow \text{Debt paydown} \rightarrow \text{Debt} \rightarrow \text{Interest}$

This is a designed circularity, and you should be able to say that out loud. It's different from an accidental circular reference, where you fat-fingered a formula that points at its own cell by mistake. The designed loop is real economics. The accidental one is a bug.

There's a subtle trigger worth knowing. The loop is tightest when you calculate interest on the average of the beginning and ending debt balance, because the ending balance is exactly what the loop is trying to solve for. Calculate interest on the beginning balance instead and the circularity disappears, since the beginning balance is a known number from last period. Average is more precise. Beginning is simpler and blows up less often. Interviewers accept either as long as you know the tradeoff.

How to control it

You have two tools, and strong candidates mention both.

Iterative calculation. Excel can solve a circular loop by guessing, plugging the guess back in, and repeating until the number stops moving. You turn it on under File, Options, Formulas, Enable iterative calculation. That's the standard setting for any model with a real debt schedule.

A circuit breaker. Iterative calc is fragile. One stray #DIV/0! or #REF! can cascade through the whole loop and fill your model with errors that don't clear even after you fix the original mistake. So you build a switch, one cell that flips between 1 and 0. When it's 0, the interest line is forced to zero, which cuts the loop. You flip the breaker, let the errors flush out, then flip it back on. Wrapping the interest formula in an IFERROR that returns 0 is a common safety net too.

Tie it back to the statements

Whatever the ending debt balance is, it lands in three places, and all three must agree. The debt balance sits on the balance sheet. The interest sits on the income statement. The net borrowing or repayment sits in the financing section of the cash flow statement. When those tie, your balance sheet balances. When they don't, the debt schedule is almost always where the break is.

One more sanity check bankers run instinctively: divide total debt by EBITDA to get the leverage ratio. If it comes out at 12x for a stable business, something upstream is wrong. Cross-checks like that catch broken models before they reach a managing director.

For how this schedule powers returns in a buyout, see the LBO and paper LBO. For how the debt paydown reconciles inside the cash flows, see walk me through the three statements.