Debt schedules and circularity

11 min read

Read firstHow the statements connect. Valuation without accounting is memorization.

Every with debt in it contains a loop, and the loop is not a mistake. It is a faithful description of how the arithmetic actually works. Understanding that is the difference between fixing it and turning on iterative calculation and hoping.

The loop

Interest expense is the interest rate times the debt balance.
is after interest expense.
Cash available to repay debt comes from net income.
The debt balance is last year's balance less what was repaid.
Go to step one.

The loop is tighter when interest is calculated on the average of the beginning and ending balances, which is more accurate because debt is repaid through the year rather than on the last day.

Average balance interest, the version that creates the loop

\text{Interest}_t = r \times \frac{D_{t-1} + D_t}{2}

Interest in a period is the rate times the average of the opening and closing debt balances. The closing balance depends on the repayment, the repayment depends on cash, and cash depends on this interest figure.

Fix one: iterative calculation, with a breaker

Excel can resolve a by iterating: calculate with a guess, use the result as the next guess, repeat until the change is below a threshold. Enable it in the calculation options, set maximum iterations to 100 and maximum change to 0.001.

The risk is that any error inside the loop propagates and sticks. One division by zero and every dependent cell fills with zeros, and turning iteration off does not bring them back.

Fix two: interest on the beginning balance

Beginning balance interest, which has no loop

\text{Interest}_t = r \times D_{t-1}

Interest is the rate times the opening balance only. The opening balance is known before anything else in the period is calculated, so nothing depends on itself.

This overstates interest slightly for a company that repays debt during the year, because it charges a full year on debt that was partly repaid. On a model with modest annual paydown the difference is small. On an with aggressive sweeps in the early years it is not.

ConventionWhichever convention you use, state it on the assumptions sheet. Two analysts modeling the same company will produce different interest expense from the same inputs if one uses average balances and the other uses beginning balances, and it looks like an error until someone says which is which.

The schedule itself

A simple debt schedule, one tranche$ millions

	Y1	Y2	Y3
Opening balance	1,000	895	776
Mandatory amortization	negative 50	negative 50	negative 50
Optional prepayment from excess cash	negative 55	negative 69	negative 84
Closing balance	895	776	642
Average balance	948	836	709
Interest at 8%	negative 75.8	negative 66.8	negative 56.7
Cash available before debt service	180	186	191
Cash sweep	105	119	134

Read the pattern. Interest falls each year because the balance falls, which frees more cash, which repays more debt, which cuts interest further. That compounding is the entire mechanism behind the deleveraging bucket in an LBO , and it is why leverage creates even when nothing about the business improves.

The revolver, which keeps cash from going negative

A model with a fixed repayment schedule can produce a negative cash balance, which is impossible. The is the plug: when cash would fall below a minimum, the model draws on it; when there is surplus, the sweep repays it first.

The revolver also participates in the circularity, because drawing on it creates interest, which uses cash, which may require drawing more. Same problem, same two fixes.

Test yourself

01Explain the circular reference in a debt schedule.

Interest expense is calculated on the debt balance. The debt balance depends on how much cash was available to repay it. Cash available depends on net income, which is after interest expense. So interest depends on itself, one step removed. A spreadsheet cannot resolve that in a single pass, which is what the circular reference warning is telling you.

02Which fix would you use and why?

Beginning-balance interest for anything that will be shared or audited, because it removes the loop entirely and the model cannot enter a broken state. Iterative calculation with a circuit breaker when precision matters more than robustness, such as an LBO where the interest figure drives a covenant test. Say which you chose, because the two produce slightly different numbers.

03A model full of zeros appeared after you changed one assumption. What happened?

Iterative calculation hit a state it could not resolve, usually a division by zero somewhere inside the loop, and the error propagated through every cell that touches the circularity. Flip the circuit breaker to zero, which cuts the loop, let the model recalculate, then flip it back. Without a breaker the only way out is rebuilding the block.

Every figure in this lesson that names a company comes from Target Corporation's Form 10-K for Fiscal 2025, the year ended 2026-01-31, filed 2026-03-11. Educational material, not investment advice.