Circular References in Financial Models
Executive Summary
Key Takeaways
- ✓ A circular reference is a dependency loop in a financial model where a formula's value depends, directly or indirectly, on itself.
- ✓ Excel flags circular references and returns zero in the affected cells unless iterative calculation is enabled.
- ✓ Iterative calculation resolves the circularity numerically but introduces convergence risks: the solution may depend on starting conditions and may not be consistent across machines with different settings.
- ✓ Many circular references in financial models are unintentional structural errors. Others reflect genuine simultaneous financial relationships, most commonly in interest calculations and cash sweep mechanics.
- ✓ The preferred institutional approach is to eliminate circular references by restructuring formulas to use opening balances or formulaic approximations.
- ✓ Where iterative calculation is retained, convergence controls must be implemented and documented, and the model must be tested for convergence consistency.
- ✓ Automated audit tools detect circular references regardless of whether iterative calculation is currently resolving them.
Institutional Definition¶
A circular reference occurs when a formula in a financial model creates a dependency loop: cell A depends on cell B, which depends on cell A, either directly or through a chain of intermediate cells. The result is a calculation that cannot be resolved in a single pass because each cell in the loop requires the value of another cell that has not yet been calculated.
What Is a Circular Reference?¶
A circular reference is a dependency loop in a spreadsheet's calculation graph. In a correctly structured spreadsheet, the calculation graph is acyclic: each cell's value is determined by cells that have already been calculated, and the entire sheet can be calculated in a single pass from inputs to outputs. A circular reference introduces a cycle into this graph, creating a dependency that cannot be resolved in a single pass.
Direct Circular References¶
A direct circular reference exists when a cell's formula references itself. For example, a formula in cell B5 that reads =B5 * 1.05 is a direct circular reference. Direct circular references are almost always unintentional errors.
Indirect Circular References¶
An indirect circular reference exists when a cell's formula references another cell, which through a chain of dependencies eventually references the original cell. For example: cell A1 contains =B1 * 0.3, cell B1 contains =C1 + A1, and cell C1 contains =A1 * 10. In this chain, A1 depends on B1, B1 depends on A1, creating a circular dependency that involves three cells rather than one.
Indirect circular references are more common in financial models than direct ones and are harder to detect through visual inspection, because the dependency chain may extend across multiple worksheets.
Why Circular References Occur in Financial Models¶
Circular references in financial models arise most frequently from financial relationships that are genuinely simultaneous: where the value of A determines the value of B, and the value of B determines the value of A in the same period.
Interest on drawn debt. In a model that calculates interest on the average balance of a loan over a period, the ending balance affects the average balance, which affects the interest charge, which affects the ending balance if the interest is paid from the loan facility. This creates a genuine circularity that reflects the financial reality.
Cash sweep mechanics. In project finance models that include a cash sweep feature, excess cash is applied to repay debt, reducing the debt balance. But the amount of cash available for sweep depends on the interest expense, which depends on the debt balance, which depends on the sweep amount.
Tax shield on interest. In leveraged buyout models, tax is calculated on earnings before tax, which is net of interest expense. Interest expense depends on the debt balance. But the debt balance may be reduced by the tax saving (in certain structures), making the tax calculation circular with the interest calculation.
Revolving credit facility drawdowns. A revolving credit facility that funds a cash shortfall creates a circularity if the interest on the revolver is also funded from the revolver in the same period.
How Excel Handles Circular References¶
Default Behaviour¶
By default, Excel does not allow circular references. When a circular reference is detected, Excel displays a warning and returns zero in all cells involved in the circular dependency. This is a visible signal that a circularity exists. The zero values propagate through any formula that depends on the circular cells, typically creating a visible pattern of unexpected zeros in the model's outputs.
Iterative Calculation¶
Excel provides an iterative calculation mode (File > Options > Formulas > Enable Iterative Calculation) that allows circular references to be resolved by repeating the calculation process multiple times. In each pass, Excel uses the values calculated in the previous pass as the starting point for the current pass. This continues until either the values stabilise within the defined Maximum Change threshold, or the defined Maximum Iterations limit is reached.
The default settings are 100 iterations and a maximum change of 0.001. These defaults are not universally appropriate for financial models. A financial model with large absolute values (revenues and costs in the hundreds of millions) may converge to a solution where the residual change between iterations is 0.001, but that residual represents a materially large absolute difference in the context of the model's values.
Convergence Risk¶
Iterative calculation does not guarantee that the solution produced is the correct one. Some circular relationships converge to different values depending on the starting conditions. If the model is opened fresh (with all circular cells starting at zero), the iterative calculation may converge to a different value than if the same model is calculated starting from a non-zero position.
This behaviour is a material risk in financial models. A model that produces different DSCR values depending on the order in which worksheets were previously calculated, or depending on whether the model is opened fresh or refreshed from a previously calculated state, cannot be relied upon as a stable source of output.
Key Components of Circular Reference Management¶
Detection¶
The first step is confirming that a circular reference exists and identifying all cells involved in the dependency loop. Excel's Formulas tab provides an Error Checking > Circular References menu that lists cells involved in circular references. However, this tool identifies only one circular reference chain at a time and may not reveal the full extent of a complex dependency loop spanning multiple worksheets.
Automated structural analysis tools detect all circular references in a model systematically, including indirect circularities spanning multiple sheets, and produce a complete list of the cells involved in each loop.
Classification¶
Once identified, each circular reference should be classified as either intentional (reflecting a genuine simultaneous financial relationship) or unintentional (reflecting a structural error). This classification determines the remediation approach.
An intentional circular reference in a debt model should be accompanied by documentation confirming that iterative calculation has been set to appropriate parameters and that the model converges correctly under those parameters. An unintentional circular reference should be eliminated by restructuring the formula.
Resolution Without Iteration¶
Where a circular reference reflects a genuine financial relationship, the preferred institutional approach is to eliminate the circularity without relying on iterative calculation, using a technique that produces the same result algebraically.
The most common technique is the use of an opening balance rather than an average balance for interest calculations. Instead of calculating interest on the average of the opening and closing balance (which creates circularity because the closing balance depends on the interest charge), interest is calculated on the opening balance alone. This is an approximation, but for most modelling purposes the difference is immaterial and the model avoids the structural risks associated with iterative calculation.
In project finance models, debt sculpting mechanics specifically resolve the circularity in cash sweep calculations through a defined algorithm that determines the debt repayment amount from the projected cash available for debt service, without requiring iterative resolution.
Resolution With Controlled Iteration¶
Where elimination is not feasible and iterative calculation must be used, the following controls should be in place:
- Maximum Iterations and Maximum Change settings should be documented and justified for the specific model
- A convergence check cell should be included in the model, calculating the residual difference between the circular cells from one pass to the next, with a conditional format or alert if the residual exceeds a defined threshold
- The model should be tested for consistent convergence by opening it fresh and comparing the outputs to a version calculated from a previously converged state
- The audit trail should note that iterative calculation is used and that the convergence has been verified
Common Mistakes¶
| Common Mistake | Why It Matters |
|---|---|
| Enabling iterative calculation to silence a warning without investigating the cause | The most common response to Excel's circular reference warning is to enable iterative calculation in the application settings. This resolves the warning but does not resolve the underlying circular dependency. If the circularity is unintentional, enabling iterative calculation produces outputs that appear correct but are the result of the iterative engine converging on an answer rather than calculating a correct result. |
| Using default convergence settings for large-value models | A maximum change of 0.001 is appropriate for a model whose values are measured in units. In a model where revenues are expressed in millions, a residual of 0.001 per iteration may represent a material non-convergence in absolute terms. |
| Assuming that zero output from a circular cell means the circularity is harmless | When iterative calculation is disabled, circular cells return zero and propagate zero through dependent formulas. Modellers who see zero values without understanding the cause may assume the calculation is simply empty and add data or assumptions to the wrong cells in an attempt to produce output. |
| Failing to test convergence consistency | A model that has been open for some time and has been recalculated multiple times may have converged to a solution from a non-zero starting position. When the model is subsequently opened fresh on a different machine, it may converge to a different solution. This inconsistency is a material reliability risk. |
| Including circular references in sensitivity analysis | Sensitivity analysis that varies an input connected to a circular dependency requires the iterative calculation engine to re-converge for each sensitivity step. If the model does not converge identically from each starting position, the sensitivity outputs will be inconsistent. |
Best Practices¶
| Best Practice | Why It Matters |
|---|---|
| Eliminate circular references by restructuring formulas wherever possible | The absence of circular references makes a model simpler to audit, more portable across calculation environments, and more reliable in sensitivity analysis. |
| Where circular references are retained, document them explicitly | A model that includes intentional circular references should include a dedicated worksheet or comment noting the location of each circularity, its financial rationale, the iterative calculation settings used, and the convergence test results. |
| Include a convergence check in every model that uses iterative calculation | A convergence check cell that confirms the residual difference between the circular cells is within the defined tolerance provides an ongoing validation that the model has resolved correctly. |
| Test models for convergence consistency at each major version | Each time a model containing circular references is significantly revised, it should be tested by opening it fresh and verifying that the outputs match the previously converged version. |
| Avoid circular references in sensitivity analysis and scenario tables | Sensitivity tables and scenario outputs that depend on circular calculations are inherently less reliable. Where sensitivity analysis is required, restructure the model to eliminate circularity in the affected range before building the sensitivity outputs. |
Regulatory and Industry Context
The ICAEW Financial Modelling Code notes circular references as a source of model risk and recommends that models be constructed without circular references where possible. Where circular references cannot be avoided, the Code recommends that they be documented, controlled, and reviewed as part of the model audit process.
The FAST Standard prohibits the use of circular references unless explicitly required by the financial relationship being modelled. FAST-compliant models that include circular references must document them and include convergence controls.
In project finance model auditing, the presence of uncontrolled circular references in a model submitted for lender review is typically a material finding. Lenders require confidence that the model's DSCR and coverage ratio outputs are stable and reproducible. A model whose outputs depend on iterative convergence from an arbitrary starting position does not provide that confidence.
Worked Example
Scenario. A leveraged buyout model calculates acquisition debt interest as follows: interest is calculated at the weighted average cost of debt applied to the average balance of senior debt over each quarter. The average balance is calculated as the sum of the opening and closing balances divided by two. The closing balance is the opening balance minus any repayment made from free cash flow in the period. Free cash flow is calculated after interest expense. Interest expense depends on the average balance, which depends on the closing balance, which depends on free cash flow, which depends on interest expense.
This is a four-cell indirect circular reference.
Option 1 — Elimination using opening balance. The interest calculation is changed to apply the rate to the opening balance rather than the average balance. This eliminates the circularity. The difference between interest calculated on the opening balance and interest calculated on the average balance is typically less than one half of one period's interest on the repayment amount, which is immaterial for most modelling purposes.
Option 2 — Elimination using a formulaic approximation. Where calculating interest on the average balance is commercially required (for example, where the loan agreement specifies this method), the average balance can be approximated using the opening balance and a projected repayment amount that is calculated independently of the interest charge. This is a more complex restructuring but eliminates the iterative dependency.
Option 3 — Controlled iterative calculation. If neither elimination approach is acceptable, iterative calculation is enabled with a Maximum Iterations setting of 1,000 and a Maximum Change of 0.00001. A convergence check cell is added to the model. The model is tested by opening it fresh and confirming that the outputs match a previously converged version. The convergence settings and test results are documented in the model's assumption log.
The preferred approach for institutional models is Option 1 or Option 2. Option 3 is acceptable only where the financial relationship specifically requires average balance interest and where the convergence controls are fully documented and tested.
Further Reading¶
- ICAEW, Financial Modelling Code, Institute of Chartered Accountants in England and Wales
- FAST Standard Organisation, FAST Standard for Financial Modelling
- Microsoft Documentation, Remove or allow a circular reference, Microsoft Support
Continue Reading¶
Prerequisites¶
- Financial Model Auditing — the parent pillar
Related Technical Guides¶
- Circularity in Debt Models — a detailed guide to circular references specific to project finance debt calculations
- Audit Methodologies — the parent guide covering how circular reference detection fits within a model audit
- Formula Consistency — a related structural check within the same audit cluster
- Formula Error Types — the broader taxonomy of formula errors including circularity
Related Glossary¶
- Circular Reference — the glossary definition of a circular reference in the financial model context
Related Products¶
- Financial Model Audit Engine (FMAE) — deterministic structural auditing referenced throughout this guide
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
What happens when I open a model with a circular reference on a machine where iterative calculation is disabled?
Excel will display a warning that a circular reference has been detected and the affected cells will display zero. If iterative calculation is disabled in your Excel settings, enabling it for this specific model will require changing the application settings.
Can a circular reference span multiple worksheets?
Yes. Indirect circular references frequently span multiple worksheets. The dependency chain may be very long: cell A on Sheet 1 depends on cell B on Sheet 3, which depends on cell C on Sheet 7, which eventually depends on cell A on Sheet 1.
Is a circular reference always an error?
No. Some circular references reflect genuine simultaneous financial relationships. The distinction is between intentional circular references that correctly represent a financial relationship and are managed with appropriate controls, and unintentional circular references that represent structural errors in the model's logic.
Can iterative calculation produce different results on different machines?
Yes, if the iterative calculation settings differ between machines. A model shared between users who have different Maximum Iterations or Maximum Change settings in their Excel application settings will produce different results. This is a material reliability risk for institutional models.
How do I test whether my model converges consistently?
Save the model after it has converged. Close it and reopen it. Allow it to calculate from scratch. Compare the key outputs (DSCR, IRR, returns) to the saved version. If the outputs differ materially, the model has not converged to a consistent solution from different starting conditions.
Do sensitivity tables work correctly in models with circular references?
Only if iterative calculation converges consistently from each starting point in the sensitivity range. In practice, sensitivity outputs in models with circular references should be independently verified rather than assumed to be correct.
Can an audit tool detect a circular reference that my model is currently resolving correctly through iteration?
Yes. A deterministic audit tool detects the structural presence of a circular dependency in the formula graph regardless of whether iterative calculation is resolving it. The finding is the dependency itself, not its current output state.
Should iterative calculation be on or off in the Excel file settings?
If a model uses controlled iterative calculation, the setting should be embedded in the file so that the model opens with the correct settings on any machine. This can be achieved through a VBA macro that sets the iterative calculation parameters when the file opens.
Related Articles
Circularity in Debt Models
Circularity in debt models arises from the interdependence of interest expense and cash availability in the same period. In a project finance model, interest is charged on the drawn debt balance; the interest payment reduces available cash; available cash determines the repayment amount; the repayment amount determines the closing debt balance; and the closing balance determines the next period's interest charge. When a model calculates interest on the average of opening and closing balances, or when a cash sweep mechanism uses the same period's interest cost in determining sweep amounts, a circular dependency is introduced. The two principal resolution techniques are: calculating interest on the opening balance rather than the average balance, and using a defined debt repayment algorithm that determines the repayment amount without reference to the closing interest charge.
Audit Methodologies for Financial Models
Financial model audit methodologies fall into three primary categories: manual line-by-line review, automated structural analysis, and deterministic rule-based checking. Each methodology differs in scope, speed, consistency, and the types of errors it is designed to detect. The appropriate methodology depends on transaction complexity, time constraints, and institutional risk appetite.
Formula Consistency in Financial Models
Formula consistency in a financial model means that cells in the same row or column that perform the same calculation use identical or structurally equivalent formulas. In a time-series financial model, the formula in the Year 1 column of a revenue line should be structurally identical to the formula in the Year 5 column of the same line, with references shifting as appropriate across periods. A cell that contains a formula materially different from its neighbours in the same row is either performing a different calculation intentionally (which should be documented) or contains an error introduced by manual editing.
Formula Error Types in Financial Models
Formula errors in financial models fall into four principal categories: visible error values (including #REF!, #VALUE!, #DIV/0!, #NAME?, #N/A, #NULL!, and #NUM!), which display in cells and are immediately apparent; silent formula errors, which produce plausible-looking values but incorrect results; structural formula errors, which arise from incorrect model construction rather than incorrect values; and logic errors, which occur when a formula correctly implements an incorrect financial relationship. Each category requires different detection methods and carries different risk implications.