The first thing most people do when Excel shows ‘Circular Reference’ in the status bar is hit File > Options > Formulas > Enable iterative calculation—then shrug and keep working. That’s dangerous. You’ve just told Excel to ignore an error and start looping until it gives up. Not all circular logic is safe. Some formulas blow up in iteration. Others converge silently—and wrongly.
The Setup
You’re building a cash flow forecast for Q2 at TerraFusion Labs, a hardware startup. Finance needs to calculate monthly loan interest based on the ending balance, but ending balance depends on interest paid. Classic circular dependency: interest → payment → ending balance → interest.
Here’s the raw input table (A1:E10):
| Month | Starting Balance | Principal Payment | Interest Rate (monthly) | Interest Due |
|---|---|---|---|---|
| Apr-24 | $247,500 | $12,000 | 0.42% | =D2*B2 |
| May-24 | =E2+B2-C2 | $12,000 | 0.42% | =D3*B3 |
| Jun-24 | =E3+B3-C3 | $12,000 | 0.42% | =D4*B4 |
| Jul-24 | =E4+B4-C4 | $12,000 | 0.42% | =D5*B5 |
| Aug-24 | =E5+B5-C5 | $12,000 | 0.42% | =D6*B6 |
| Sep-24 | =E6+B6-C6 | $12,000 | 0.42% | =D7*B7 |
| Oct-24 | =E7+B7-C7 | $12,000 | 0.42% | =D8*B8 |
| Nov-24 | =E8+B8-C8 | $12,000 | 0.42% | =D9*B9 |
The Challenge
Cell B3 says =E2+B2-C2. Cell E2 says =D2*B2. So B3 depends on E2, which depends on B2 — but B2 is static. No problem yet. But look at B4: =E3+B3-C3. And E3 is =D3*B3. Now B3 feeds E3, and E3 feeds B4… and B4 feeds E4, which feeds B5. It’s not circular *yet*. But if you change E2 to =D2*(B2-E2) — meaning interest is calculated on average balance (starting + ending)/2 — then E2 depends on itself. That’s when Excel throws the red flag.
Without iterative calculation, Excel refuses to compute. With it enabled? Excel runs up to 100 loops (default max iterations) or until change falls below 0.001 (default max change). It doesn’t validate correctness — just convergence.
Walking Through It
We’ll fix the average-balance interest formula in row 2 — and show what happens step-by-step when iterative calculation kicks in.
Step 1: In cell E2, replace =D2*B2 with =D2*(B2+E2)/2. Excel immediately warns: “Cannot resolve circular reference.” Status bar shows “Circular References: E2”.
Step 2: Go to File > Options > Formulas. Check Enable iterative calculation. Set Maximum Iterations = 3, Maximum Change = 0.01. Click OK. (Shortcut: Alt+T+O, then F, Tab ×3, Space).
Step 3: Watch what Excel does under the hood — not what it displays, but how it computes:
| Step | Action | Result (E2) | Shortcut |
|---|---|---|---|
| 1 | Initial guess: E2 = 0 | 0.00 | — |
| 2 | Plug into formula: 0.0042 × (247500 + 0)/2 | 520.75 | — |
| 3 | Next pass: 0.0042 × (247500 + 520.75)/2 | 521.82 | — |
| 4 | Next pass: 0.0042 × (247500 + 521.82)/2 | 521.84 | — |
After 3 iterations, Excel stops at $521.84 — within 0.01 of prior value. That’s the number you see. But note: it didn’t solve algebraically. It approximated using successive substitution.
Now extend this to May. In E3, enter =D3*(B3+E3)/2. Excel auto-applies same logic. Starting balance B3 pulls from prior row’s ending balance — which now includes iterative interest. The whole column recalculates top-down, each row iterating internally before moving on.
The Result
With iterative calculation enabled (Max Iter = 3, Max Change = 0.01), here’s the final output for rows 2–9:
| Month | Starting Balance | Principal Payment | Interest Rate | Interest Due |
|---|---|---|---|---|
| Apr-24 | $247,500.00 | $12,000.00 | 0.42% | $521.84 |
| May-24 | $236,021.84 | $12,000.00 | 0.42% | $497.49 |
| Jun-24 | $224,519.33 | $12,000.00 | 0.42% | $473.13 |
| Jul-24 | $213,002.46 | $12,000.00 | 0.42% | $449.02 |
| Aug-24 | $201,471.48 | $12,000.00 | 0.42% | $424.92 |
| Sep-24 | $189,916.40 | $12,000.00 | 0.42% | $400.84 |
| Oct-24 | $178,337.24 | $12,000.00 | 0.42% | $376.77 |
| Nov-24 | $166,733.01 | $12,000.00 | 0.42% | $352.72 |
What Could Go Wrong
Three real issues we saw last week in our finance team’s loan model:
- Mistake #1: Forgetting to reset Max Change. Someone left it at
0.000001— so Excel ran 100 iterations every time, freezing the sheet for 4 seconds on refresh. Their ‘quick forecast’ took 22 minutes to recalc after adding 3 more columns. - Mistake #2: Using iteration for non-convergent math. One analyst tried
=B2*1.05+E2where E2 referenced itself. That diverges — grows infinitely. Excel hit 100 iterations and returned garbage ($2.8M interest on a $250k loan). No warning. Just wrong numbers. - Mistake #3: Copying formulas without checking dependencies. They dragged E2 down, but B3 still pulled from static E2 instead of the new iterative E2. So only April used iteration; May–Nov used old logic. The model looked clean — but the total interest was off by $1,842.
Here’s what to do next — right now:
| Action | Where | Why |
|---|---|---|
| Check status bar for “Circular References” | Bottom-left corner | If present, don’t enable iteration yet — trace the link first (Formulas > Error Checking > Circular References) |
| Set Max Iterations = 10, Max Change = 0.01 | File > Options > Formulas | Safer default: catches divergence faster than 100/0.001 |
| Add a note in cell A1: “Iterative calc: ON — avg-balance interest only” | Top-left corner | Prevents future users from assuming it’s safe for other circular logic |
