There's a specific failure mode in investment banking that experienced analysts dread and junior analysts don't know to prepare for: the model that works perfectly in testing, produces sensible outputs in review, and then returns #REF! or #VALUE! errors the moment you open it on a client's laptop during a presentation.
The cause, almost always, is a circular reference that wasn't deliberately designed — or one that was, but without proper handling of the conditions that break it.
Circular references aren't inherently wrong in financial models. They're unavoidable in certain structures. But uncontrolled circularity is one of the most consequential model errors because it can produce outputs that look correct while being fundamentally wrong — or outputs that crash at precisely the wrong moment.
What a Circular Reference Actually Is
A circular reference occurs when a formula in a cell refers to itself, either directly or through a chain of other cells.
Direct circularity: Cell A1 contains a formula that references A1. Excel cannot calculate this and by default returns zero — silently, with no error message visible unless you know to look for the circular reference indicator.
Indirect circularity: Cell A1 references B1, B1 references C1, and C1 references A1. The chain is longer, but the problem is identical. Excel iterates through the chain and — if iterative calculation is not enabled — returns incorrect values or crashes.
In financial models, indirect circularity is the common case. It typically spans multiple worksheets and involves five or more cells in the chain. An analyst who encounters a #REF! error in a large model may spend hours tracing the chain before finding the source.
The Four Circular Loops That Appear in Every IB Model
Loop 1: Revolver and Cash Interest
The most common intentional circularity in an LBO or three-statement model:
- Cash balance depends on free cash flow available after debt service
- Revolver draw depends on the cash balance — the model draws the revolver when cash falls below a minimum threshold
- Interest expense depends on the revolver balance
- Free cash flow depends on interest expense (it reduces net income and taxes)
The circle closes: cash → revolver → interest → FCF → cash.
This circularity is unavoidable if you want the model to reflect how the revolver actually works. The correct solution is to enable iterative calculation in Excel (File → Options → Formulas → Enable iterative calculation) and set a maximum iteration count of 100 with a maximum change of 0.001.
The dangerous mistake: building this structure without enabling iteration, or building it with iteration enabled but a maximum change that's too large, producing approximate rather than exact results.
Loop 2: Cash Interest on Average Debt Balance
Technically correct interest calculation uses the average of beginning and ending debt balance for the period, not just the beginning balance.
- Interest expense = Average debt × Interest rate
- Average debt = (Beginning balance + Ending balance) / 2
- Ending balance = Beginning balance − Principal payments − Cash sweep
- Cash sweep depends on FCF, which depends on interest expense
The circularity: interest expense → cash sweep → ending debt balance → average debt → interest expense.
This is a second-order circularity nested inside the primary revolver loop. Many models use beginning balance only to avoid it — a simplification that understates interest expense in periods of significant debt repayment.
Loop 3: Pension and Post-Retirement Obligations
In models for companies with defined benefit pension plans:
- Pension interest cost is a component of pension expense
- Pension interest cost is calculated on the projected benefit obligation (PBO)
- PBO is affected by the settlement and curtailment transactions
- Settlement transactions depend on cash available after other uses
- Cash available depends on pension expense (which includes interest cost)
This circularity is less common in IB models but appears frequently when modelling capital-intensive industrial businesses with legacy pension obligations — exactly the kind of business that attracts LBO interest.
Loop 4: Operating Lease Circularity (Post-IFRS 16)
Under current accounting standards, operating lease ROU assets are depreciated and lease liabilities are accreted at the implicit rate. When a model calculates:
- EBITDA (before depreciation and lease interest)
- Lease depreciation added back in cash flow
- Lease liability interest calculated on the outstanding liability
- Lease liability reduced by cash payments
- Cash payments depending on FCF and cash balance
The interest accretion creates a minor but structurally present circularity in any model that properly handles IFRS 16 lease treatment.
The Circuit Breaker Design: Making Circularity Deliberate
The right architecture for a model with intentional circular references has three components:
1. An iteration flag cell
A single cell at the top of the model (typically on the Assumptions sheet) labelled "Circular Reference On/Off" containing either 1 or 0. All circular formulas reference this flag:
=IF(CircularFlag=1, [formula with circular reference], [simplified alternative])
When the flag is set to 0, the model uses simplified (non-circular) formulas — useful for debugging, for sharing with recipients who have iteration disabled, and for stress-testing whether the circularity is materially affecting outputs.
2. A convergence check
A cell that calculates the difference between the first and last iteration of the key circular output (typically ending debt balance or ending cash). If iteration has fully converged, this difference should be near zero. If it's larger than a threshold, the model has not resolved the circularity fully.
3. Error traps on the downstream outputs
Key model outputs (equity proceeds, IRR, MOIC) should include IFERROR wrappers that flag when the underlying circularity has produced a calculation error:
=IFERROR(IRR_calculation, "CIRCULAR ERROR — CHECK ITERATION SETTINGS")
This ensures that a model opened with iteration disabled does not silently produce wrong outputs — it explicitly shows that something is wrong.
The Iteration Settings Danger
This is where most circular reference disasters originate: Excel's iteration settings are not saved with the file.
When an analyst enables iterative calculation on their machine and sends the model to a colleague or client, the recipient opens the model with iteration disabled — because that's their Excel default setting. The model now either:
- Returns zeros for all circular cells (the Excel default when iteration is off)
- Shows
#VALUE!errors that cascade through downstream calculations - Produces plausible-looking wrong numbers because the zero values flow through to final outputs without obvious error indicators
The industry workaround is to build the circuit breaker flag (described above) and document it clearly at the top of the model. Some banks go further and use a VBA macro that forces iterative calculation on when the file opens — but this requires the recipient to have macros enabled, which is its own set of complications.
The safest approach for sharing: always include a model documentation cell that states "This model uses iterative calculation. To enable: File → Options → Formulas → Enable iterative calculation. Set maximum iterations to 100."
The Audit Process: Finding Hidden Circularity Before It Finds You
Most circular references are discovered accidentally — the model crashes. Professional modellers audit for circularity proactively, before delivery.
Step 1: Excel's circular reference finder
Formulas → Error Checking → Circular References shows the cell(s) Excel identifies as the source of circular references. This catches direct circularity but often misses long-chain indirect circularity if iteration is enabled — because Excel successfully resolves the iteration and doesn't flag it as an error.
Step 2: The dependency trace
For each major output cell, use Formulas → Trace Precedents to trace the dependency chain. In a large model, this quickly becomes unmanageable manually — but it's the only way to confirm that no unintended circularity exists.
Step 3: The zero-out test
Set the circular reference flag to 0 (disabling the intentional circularity) and compare outputs to the full circular model. The difference tells you:
- How large the circularity effect is (small differences suggest the approximation is acceptable in downside scenarios)
- Whether any cells that should be zero under the flag are not (suggesting unintended circularity elsewhere)
Step 4: The cold-open test
Close the model. Open a new Excel instance with default settings (iteration off). Open the model. Observe what happens. If the model crashes or produces zeros in key cells, the circuit breaker design is insufficient.
The LBO Model — Where Circularity Has the Highest Stakes
In an LBO model, the cash sweep creates the most consequential circularity: available free cash flow is swept to repay optional debt beyond mandatory amortisation. Debt repayment reduces the ending debt balance, reducing interest expense in the next period, increasing free cash flow, increasing the optional prepayment — a positive feedback loop.
This loop is the analytical heart of the LBO model. It shows how quickly the business can deleverage under different operating scenarios. Getting it wrong — by breaking the circularity or by resolving it incorrectly — directly affects the calculated IRR and MOIC.
The specific calculation that breaks most first-time LBO models:
Optional prepayment = MAX(0, Free Cash Flow − Mandatory Amortization − Minimum Cash Balance)
The MAX(0,...) formula ensures the model doesn't prepay more than available cash, but it also creates a discontinuity in the circular loop — the gradient across the minimum cash threshold is zero, which can cause iteration convergence problems in scenarios where cash is right at the threshold.
Professional LBO models handle this with a small smoothing function or by setting the minimum cash threshold slightly higher than zero, ensuring the MAX never creates a hard boundary at the iteration breakpoint.
What Breaks at the Worst Moment
The conditions that most reliably cause circular reference failures in a live setting:
Different Excel version: Excel 2019 and Excel 365 handle certain circular reference scenarios differently. A model built in Excel 365 may behave unexpectedly in Excel 2019 — not fail completely, but converge to slightly different values.
Shared file versus local file: When a model is opened from a SharePoint or Teams location rather than a local drive, certain Excel calculation modes behave differently. Iteration settings in particular can reset.
First open after email attachment: Email clients sometimes modify Excel files in transit (particularly .xlsx format). Opening an Excel file from Outlook directly rather than saving it first can trigger a recalculation mode that clears iteration settings.
The professional protocol: always save the file locally before opening, verify iteration settings before presenting, and never open a model for the first time in a client meeting.
Closing: Architecture as Risk Management
The circular reference problem in financial modelling is ultimately an architecture problem. Models that handle circularity correctly — with deliberate design, explicit flags, convergence checks, and error traps — are robust to the conditions that break models at inconvenient moments.
At Meritshot, the Investment Banking programme covers financial model architecture at the level practitioners actually need: not just how to build the components, but how to build them so they don't fail under real-world conditions — different machines, different Excel settings, inherited models, time pressure. Students build LBO models with full debt schedules, cash sweeps, and properly controlled circular references, and learn the audit protocols that professional model reviewers use before any model goes to a client.
The goal is not analysts who can build models in controlled conditions. It is analysts who can build models that work under the conditions that actually exist.
Explore the Meritshot Investment Banking Programme →
This article was written by the Meritshot content team. Meritshot trains professionals in Data Science, AI Engineering, Full Stack Development, Investment Banking, and Cyber Security through hands-on, practitioner-led programmes.
