Time to confront the loop you sidestepped in the debt schedule. In true economic terms, interest accrues on balances as they move through the year, and the average of opening and closing approximates that. But closing balances depend on cash flows, which depend on net income, which depends on interest, which depends on closing balances. It's a real simultaneous system, x depending on y and y on x. Algebraically it has an exact solution. The loop is linear, and it's heavily damped because interest is small relative to flows. So how should a spreadsheet find it? There are three professional answers, and you'll implement all of them on the same model:
Method 1: interest on opening balances (what you built). Break the loop by charging this year's rates on last year's closings, which are already known. Your dependency graph stays acyclic, with no iteration, no settings, nothing to blow up. Every cell is traceable from start to finish, and the model recalculates deterministically on any machine, in any of the three programs, forever. It costs you a one-year timing error on changes in balances, so draw years under-bill interest and sweep years over-bill it. It's the default for analytical models. Build acyclic first, and upgrade only for cause.
Advertisement
How does iterative calculation solve a circular reference?
Method 2: interest on average balances + iterative calculation. Change the three interest formulas to rate × (opening + closing)/2 and let the program iterate. It computes the loop again and again, each pass feeding the previous pass's closing balances back in, until successive passes differ by less than the tolerance. Convergence is fast and safe here. Each trip around the loop multiplies the perturbation by roughly interest rate × marginal cash retention, a few percent, so errors shrink geometrically, and fifty iterations is astronomically more than you need. Its strength is the most economically faithful single-year interest. Its costs are real, though. (a) Iteration must be switched on, and the setting is per-app, sometimes per-file, so a colleague who opens your file without it sees a circular-reference error. You'll find all three menu paths in the dialect box near the start. (b) Iteration is indiscriminate. Once on, it will also "solve" any accidental circular reference you create by mistyping a range. Your typo converges into a stable, plausible-looking wrong number instead of crashing, and the loudest alarm in the workbook has been unplugged. (c) Error states circulate. If a #DIV/0! or #REF! ever enters the loop, it propagates around it and can persist even after you fix the cause. The classic cure is to zero out the loop's seed, force a recalculation with Ctrl+Alt+F9 in Excel, then restore.
What is a circularity breaker switch?
Method 3: the circularity-breaker switch (the professional synthesis). Don't choose between 1 and 2. Install both behind a switch. You typed the switch into the control panel at the very start (A!C8 = 0), and now you upgrade the three interest formulas on M to read it:
- F86:
=IF(A!$C$8=1,(F83+F85)/2,F83)*A!F58 - F87:
=IF(A!$C$8=1,(F93+F97)/2,F93)*A!F59 - F88 stays
=F86+F87; F89:=IF(A!$C$8=1,(F98+F99)/2,F98)*A!F60
Fill each right. With the switch at 0, the IF's false branch runs. That's opening balances, acyclic, the model you had. Iteration can stay off, and accidental circularity still crashes loudly, as it should. For a final precision pass, enable iterative calculation, flip A!C8 to 1, let it converge, read your outputs. If the loop ever catches an error mid-edit, flip back to 0 and the model instantly heals to its acyclic form. That's why the choice belongs in the switch and not in the settings menu. Fix, then re-flip. You'll meet a fourth pattern in older bank models. They paste the interest row as hardcoded values, a "copper pipe" that carries no live dependency at all, and re-paste after each change. It's bulletproof, auditable, and manual, and it's what Method 3 automates.
How much does the circularity change the answer?
Now measure what the fuss is worth. Flip iteration on and A!C8 to 1, and compare (Base):
| Base case | FY26 | FY27 | FY28 | FY29 | FY30 |
|---|---|---|---|---|---|
| Interest expense, opening method | 15.8 | 13.5 | 11.3 | 9.0 | 6.8 |
| Interest expense, average + iterate | 14.6 | 12.4 | 10.1 | 7.9 | 5.6 |
| Net income, opening method | 104.2 | 118.7 | 132.7 | 146.1 | 158.7 |
| Net income, average + iterate | 105.1 | 119.7 | 133.9 | 147.4 | 160.1 |
Five-year cumulative net income is 660.4 versus 666.3. That's a difference of ₹5.9 crore on ₹660 crore, about 0.9%, in a model where the revenue growth assumption alone swings net income ten times that. In the bear case the sign flips where you'd expect. FY26 draws mid-year money, so average-balance interest is higher, 16.2 vs 15.8, and the revolver peaks at 32.0 vs 31.3. Every check still passes.
Here's the professional conclusion as a rule. The circularity is an accuracy refinement of well under 1% for a normally-levered operating company. Handle it with the switch, default to 0, and spend your anxiety on the drivers instead. In LBOs with debt at 5 to 6× EBITDA, in project finance, and in distressed names where interest is a first-order flow, the loop does matter. There, Method 3 with iteration is standard. Those are also the models where a blown-up loop costs the most, and that's why the breaker switch was invented there. Some shops mandate a pure Method-1 house style so junior models can never blow up. FAST, the standard listed in the resources, bans deliberate circularity outright. Reasonable people differ. What nobody respects is a modeler who doesn't know which choice they made, or why.