Normally a circular reference, a cell that depends on itself through a chain, is a bug. Excel says so. It flags the cell and refuses to compute. Some legitimate financial structures really are circular. The classic one is interest on a revolving credit facility, usually called a revolver. You draw on the revolver to cover a cash shortfall, and the draw accrues interest. That interest is an expense, so it widens the cash shortfall. The wider shortfall needs a larger draw, which accrues more interest. So the model chases its own tail. Nothing has gone wrong here. These are the real economics, and they have a well-defined fixed point.
How does Excel solve a circular reference on purpose?
Excel can solve such a loop on purpose with iterative calculation. It recomputes the loop again and again, feeding each pass's output back in as the next pass's input, until the numbers stop moving and converge. Turn it on at File ▸ Options ▸ Formulas ▸ Enable iterative calculation. Set Maximum Iterations (100, say) and Maximum Change (0.001, say), which is the convergence tolerance. Excel then lets the circular model settle to its fixed point instead of throwing an error.
Work the fixed point by hand. Suppose a period opens with a cash shortfall of $100 (in millions) before interest, the revolver rate is 8%, and interest accrues on the drawn balance. Call the draw D. Then D = 100 + 0.08 × D, so D × (1 − 0.08) = 100, which gives D = 100 / 0.92 = 108.70 and interest = 0.08 × 108.70 = 8.70. Iterative calculation gets there in successive passes. It draws 100 with interest 0, then 108.00, then 108.64 and 108.69, and settles on 108.70 within a handful of steps. That's the algebraic fixed point. Real revolvers accrue on the average balance and include a cash sweep, so they carry more terms, but the loop has the same shape.
Advertisement
Why is iterative calculation risky, and how do you use it safely?
Modellers treat this switch with suspicion for one reason. Iterative calculation is a global setting for the whole workbook. Turn it on to tame your revolver and you've also silenced Excel's circular-reference warnings everywhere else. An accidental circular reference, a real bug where a formula points at its own cell by mistake, stops shouting. It settles on some meaningless number, or oscillates, and you never see the alarm.
Good practice comes in three parts. Enable iteration only in a model that needs it. Keep the intentional circularity isolated and clearly labelled. Many modellers add a circularity switch, a cell that breaks the loop to 0 on demand, so you can toggle it off to check the model still computes and to clear a corrupted iterative state. And never leave iteration on in a model that has no deliberate loop. If you inherit a workbook with iteration enabled, find out why before you trust a single output.