Study guide
11.1 Circular References and Iteration
Financial Modeling & Valuation Analyst FMVA
Track your progress as you read
Finished sections get a check mark.
1 Introduction
Current Exam Facts Program Path and How to Use This Guide
2 Chapter 1: Accounting Foundations
1.1 Accounting Principles 1.2 Reading Financial Statements 1.3 Accounting Decisions in Models
3 Chapter 2: Time Value and Capital Budgeting
2.1 Time Value of Money 2.2 NPV, IRR, and Decision Rules 2.3 Capital Budgeting Cash Flows
4 Chapter 3: Cost of Capital
3.1 Capital Structure 3.2 CAPM, Beta, and Cost of Equity 3.3 WACC
5 Chapter 4: Profitability and Returns
4.1 Profitability and Margins 4.2 Returns and DuPont Analysis 4.3 Per-Share Metrics and Growth
6 Chapter 5: Liquidity, Leverage, and Cash Flow
5.1 Liquidity and Working Capital Metrics 5.2 Leverage, Coverage, and Credit 5.3 Cash Flow Statement Analysis
7 Chapter 6: Excel Fundamentals
6.1 Shortcuts, Setup, and Navigation 6.2 Core Formulas and Error Handling 6.3 Named Ranges and Workbook Hygiene
8 Chapter 7: Excel Lookups and Financial Functions
7.1 Lookup Functions 7.2 Conditional Aggregations 7.3 Financial Functions
9 Chapter 8: Excel What-If Analysis and Auditing
8.1 Goal Seek and Data Tables 8.2 Formula Auditing and Protection
10 Chapter 9: Financial Modeling Guidelines
9.1 Model Structure and Layout 9.2 Color Conventions and Formatting 9.3 Integrity Checks and Documentation
11 Chapter 10: Three-Statement Modeling
10.1 Articulating the Three Statements 10.2 Building the Income Statement 10.3 Building the Balance Sheet and Cash Flow Statement
12 Chapter 11: Circularity and Model Audit
11.1 Circular References and Iteration 11.2 Auditing and Balancing a 3-Statement Model
13 Chapter 12: Operational Modeling
12.1 Revenue and Operating Drivers 12.2 Working Capital Modeling 12.3 Capex and Depreciation
14 Chapter 13: Debt Schedules, Scenarios, and Sensitivity
13.1 Debt and Interest Schedules 13.2 Scenario Analysis 13.3 Sensitivity Analysis
15 Chapter 14: Valuation Foundations
14.1 Valuation Methods and Enterprise Value 14.2 Unlevered Free Cash Flow
16 Chapter 15: DCF Valuation
15.1 DCF Structure, WACC, and Mid-Year Convention 15.2 Terminal Value and the Equity Bridge
17 Chapter 16: Comparable Valuation
16.1 Trading Comps and Multiples 16.2 Precedent Transactions and the Football Field
18 Chapter 17: Budgeting and Forecasting
17.1 Budget Types 17.2 Forecasting Techniques 17.3 Rolling Forecasts and Variance Analysis
19 Chapter 18: Presentation and Visuals
18.1 Chart Types and Selection 18.2 Dashboards and Data Visualization 18.3 PowerPoint and Pitchbooks 18.4 Presenting Model Outputs
20 Chapter 19: Professional Ethics and Qualitative Analysis
19.1 Professional Ethics 19.2 Qualitative Business Analysis
11.1 Circular References and Iteration
Key Takeaways Explain This Topic AI
CFI's 3-Statement Modeling course isolates two circular loops: interest on average or ending cash/revolver flowing through NI into cash, and a revolver/minimum-cash sweep that redraws (and then re-interests) when cash would go negative.
Excel will not solve either loop until File → Options → Formulas → Enable iterative calculation is on; Maximum Iterations defaults to 100 and Maximum Change defaults to 0.001.
CFI's Circ Switch is a blue 1/0 (or TRUE/FALSE) input that multiplies interest and/or the revolver plug so you can break the loop, reset after a #REF! cascade, and share the file.
Never leave iteration on in a shared model without a Circ Switch: unintended circles get silently solved, colleagues with iteration off see zeros, and a #REF! inside the loop paints all three statements.
Interest on beginning balances is not circular; interest on average balances is I = r × (opening + ending) / 2 and is the CFI teaching default when the case is silent.
Last updated: August 2026
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
Loop What depends on what Typical trigger Break with Circ Switch by
Interest loop Interest → EBT → tax → NI → CFS → ending cash and/or ending revolver → interest Interest on average or ending cash, revolver, or other debt whose balance is cash-dependent Multiplying interest income and interest expense by the switch (0 = force interest to 0)
Revolver / min-cash loop Cash before financing → revolver draw or paydown → ending cash and ending revolver → interest → NI → cash before financing A minimum cash balance, or a rule that cash cannot go negative Multiplying the revolver plug (or both plug and interest) by the switch
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
- Item Circ Switch = 0 After iteration
- Interest income $0 $98.24
- EBT $9,000 $9,098.24
- Tax at 25% $2,250 $2,274.56
- Net income $6,750 $6,823.68
- Ending cash $4,750 $4,823.68
- Average cash n/a $4,911.84
- Check: 2% × average — $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
- Item Switch off (interest = 0) Converged
- Interest income $0 $70.00
- Interest expense $0 $173.09
- Net income $6,750 $6,672.68
- Revolver draw / ending revolver $4,250 $4,327.32
- Ending cash $2,000 $2,000 (floor)
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.
Revolver Plug ($): First Pass vs Iterated Average-Balance Interest
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.
Setting What it does Exam / model implication
Enable iterative calculation Allows a formula chain to use last-step values as this-step inputs Off: interest and revolver stay at 0 or at stale cached values; status bar shows a circular-reference warning
Maximum Iterations Caps how many substitution passes Excel will run Too low (for example 10 on a stubborn revolver) → residual that shows up as a BS imbalance of cents to dollars
Maximum Change Stop when every dirty value moves by less than this amount 0.001 is a tenth of a cent on an unscaled $ model — appropriate; 1.0 can stop early with a $0.50 imbalance you then chase as an audit error
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).