11.1 Circular References and Iteration

Circular references in a three-statement model

Quick Answer: A circular reference is a formula chain that depends on its own output. CFI's 3-Statement Modeling course isolates two operating loops: (1) interest on average or ending cash / revolver → interest income or expense → net income → cash → interest again; (2) the revolver / minimum-cash sweep, which draws debt if cash would go negative (or below a floor), then charges interest on that revolver, which changes cash again. Excel will not solve either loop until File → Options → Formulas → Enable iterative calculation is on. CFI still requires a Circ Switch (1/0) that multiplies interest and/or the revolver so you can break the loop, reset after a #REF! cascade, and hand the file to someone whose iteration is off. Never leave iteration on in a shared model without a switch.

A three-statement model links the income statement (IS), balance sheet (BS), and cash flow statement (CFS) so that net income flows to retained earnings and to cash, balance-sheet cash equals CFS ending cash, and every rollforward (PP&E, debt, cash) is internally consistent. That articulation is what Chapter 10 built. Circularity appears as soon as a flow (interest, a cash sweep, sometimes taxes) is allowed to depend on a stock that the flow itself helps determine.

This is not a bug. Average-cash interest should depend on ending cash. A revolver should size itself to the cash hole. The modeling problem is mechanical: Excel's default calc engine refuses to iterate, shows 0, and writes Circular References on the status bar. On a timed FMVA case that warning is easy to miss if you are staring at a revolver of $0 while minimum cash is clearly breached.

The two CFI loops

A live model usually contains both loops at once. Interest on the revolver uses the revolver balance; the revolver balance uses cash after interest. That is why CFI teaches a single switch that can zero the whole circle, not a one-off Excel option you forget to document.

Interest can be calculated on beginning, average, or ending balances:

• Beginning: Interest = r × Opening. Not circular. Understates expense when the revolver is drawn during the year to fund a cash hole, and overstates income when cash is built only at year-end.
• Average: Interest = r × (Opening + Ending) / 2. Circular. CFI's default teaching case, and the one that matches interest accruing as the balance changes.
• Ending: Interest = r × Ending. Circular and more aggressive: a year-end draw is treated as if it had been outstanding all year.

On the FMVA Excel case, read the prompt. If it is silent, average balances plus a visible Circ Switch is the CFI-consistent build.

Worked interest loop: cash interest with no revolver

Opening cash $5,000. EBITDA $10,000, depreciation $1,000, so EBIT is $9,000. Capex $8,000. Tax rate 25%. No working-capital change, no dividends, no other debt. Cash earns 2% on the average cash balance. Opening revolver is $0, and the cash hole is not large enough to require a draw.

Ignore interest on the first pass (equivalent to Circ Switch = 0):

• EBT = $9,000; tax = $2,250; NI = $6,750
• CFO = $6,750 + $1,000 = $7,750; CFI = −$8,000
• Ending cash = $5,000 + $7,750 − $8,000 = $4,750

That $4,750 is above any reasonable minimum, so the revolver stays at 0. Now turn the interest loop on. Let C1 be ending cash and I be interest income:

I = 2% × (5,000 + C1) / 2 = 0.01 × (5,000 + C1)

NI = 0.75 × (9,000 + I) = 6,750 + 0.75I

C1 = NI − 2,000 = 4,750 + 0.75I (the −$2,000 is +D&A − capex relative to NI, net of the opening-cash algebra)

Substitute:

I = 0.01 × (5,000 + 4,750 + 0.75I) = 97.50 + 0.0075I

I = 97.50 / 0.9925 = $98.24

Excel's iterator is doing this substitution up to Maximum Iterations times (default 100). You do not type the closed form on the exam. You do need to know that the first-pass NI of $6,750 is not the live answer once average-cash interest is on, and that the NI difference is only about $74 — easy to miss if you stub interest at 0 and never enable iteration.

Cross-check the cash identity with the iterated numbers: $5,000 + $6,823.68 + $1,000 − $8,000 = $4,823.68. If your live model shows $4,750 with iteration supposedly on, the Circ Switch is still 0, iteration is off, or interest is pointing at beginning cash only.

Worked revolver loop: capex blows through cash

Keep every operating assumption except capex, now $15,000, and impose minimum cash = $2,000. Interest on cash remains 2% of average cash; the revolver costs 8% of the average revolver. Opening revolver is $0.

Circ Switch = 0 (interest forced to 0):

• NI is still $6,750; CFO $7,750; CFI −$15,000
• Cash before revolver = $5,000 + $7,750 − $15,000 = −$2,250
• Draw needed to land on $2,000 of cash: $2,000 − (−$2,250) = $4,250
• Ending revolver $4,250, ending cash $2,000

Turn both loops on. Ending cash is pinned at the $2,000 floor, so cash interest is no longer unknown:

Interest income = 2% × (5,000 + 2,000) / 2 = $70

Interest expense = 8% × (0 + R1) / 2 = 0.04 × R1

Cash identity with revolver proceeds R1:

2,000 = 5,000 + NI + 1,000 − 15,000 + R1 → R1 = 11,000 − NI

NI = 0.75 × (9,000 + 70 − 0.04R1) = 6,802.50 − 0.03R1

R1 = 11,000 − (6,802.50 − 0.03R1) = 4,197.50 + 0.03R1

0.97 × R1 = 4,197.50 → R1 = $4,327.32

The extra $77 of revolver versus the first pass is the circular cost of after-tax interest. If a case study asks why the revolver is a little higher than the visible cash hole, this is the reason: interest expense reduced NI, which reduced cash, which required a slightly larger draw, which required slightly more interest — until Excel's max-change tolerance is met.

A binary IF (IF(cash<0, draw, 0) that does not solve for the exact plug) can oscillate: iteration 1 draws, cash goes positive, iteration 2 pays it all back, cash goes negative again. The professional formula is a plug, not a flip:

Ending revolver = MAX(0, MinCash − CashBeforeRevolver)

when opening revolver is zero and there is no mandatory amortization. CashBeforeRevolver already includes interest, which already includes the revolver — that is the circle the switch is there to break. If opening revolver is not zero, the plug also allows a paydown out of surplus cash, but never below zero revolver and never below minimum cash.

Excel iteration and the Circ Switch

Windows: File → Options → Formulas → Enable iterative calculation. Set Maximum Iterations (Excel default 100) and Maximum Change (Excel default 0.001). Mac desktop Excel exposes the same two numbers under Excel → Settings/Preferences → Formulas (or Calculation), not under Data Tables. The FMVA final requires Microsoft Excel; do not assume Google Sheets iteration dialogs will match the case file.

Iteration on without a switch is how shared models get wrecked. A colleague with iteration off sees zeros and thinks the revolver is broken. A copy-paste accident that creates a second, unintended circle (a tax cell pointing at ending cash for no reason) is silently solved instead of flagged. Calculation slows because Excel re-walks the loop on every F9.

CFI's Circ Switch is a blue input, typically 1 or 0 (sometimes TRUE/FALSE), on the assumptions sheet:

Interest income = CircSwitch * CashRate * (BegCash + EndCash) / 2
Interest expense = CircSwitch * RevRate * (BegRevolver + EndRevolver) / 2

Some builds also multiply the revolver plug by the switch so that turning the switch off forces revolver = 0 and interest = 0, which fully resets cash to the no-financing path. That reset is the point. When a cell inside the loop returns #REF! or #VALUE!, iteration paints the entire three statements with errors — a #REF! cascade. Set Circ Switch to 0, break the dependency, repair the source (usually a deleted row, a shifted named range, or a #REF! inside an average-balance formula), then set the switch back to 1 and recalculate.

The switch is not an Excel menu item and is not Goal Seek. Goal Seek overwrites one input and stops; a Circ Switch is a live 1/0 that stays in the file so a reviewer can turn the circle off without hunting through Options. On the timed case, if the whole IS/BS/CFS goes to #REF! after you insert a row, do not start rebuilding from the revenue line. Flip the switch to 0 first.

Exam close for 11.1

• Two loops: interest through NI into cash, and the revolver/min-cash plug that feeds interest again.
• Average or ending balances are circular; beginning balances are not.
• File → Options → Formulas for iteration; 100 iterations / 0.001 max change are the Excel defaults.
• Circ Switch (1/0) multiplies interest or revolver; use it to recover from a #REF! cascade.
• Never ship a shared model with iteration on and no switch; a model with the switch at 0 should still balance (interest is just wrong on purpose).