How to find circular references in excel financial models before presentation
- Step 1Export the model as .xlsx and confirm Pro tier — Save the working model as .xlsx (CSV is not accepted and loses all formulas). This is a Pro-tier tool; ensure your account has Pro. Pro allows files up to 50 MB / 100,000 rows — comfortably above most models.
- Step 2Temporarily bound any whole-column references — Models often use whole-column ranges (
SUM(B:B)) and named ranges. These are not followed by the extractor. For the audit, convert the model's key totals to bounded ranges (B5:B200) so loops through them are detectable. - Step 3Upload and run (no options) — Drop the .xlsx on the tool. There is nothing to configure — it parses every formula cell and builds the dependency graph automatically.
- Step 4Triage the reported chains: intentional vs accidental — Expect deliberate loops (interest-on-average-debt, cash sweep, WACC feedback) to appear — they are not errors. Flag the unexpected ones: a loop that touches a hardcoded assumption or an unrelated schedule is almost always a mistake.
- Step 5Fix accidental loops; switch intentional ones correctly — Break accidental loops by re-pointing the offending formula. For intentional circularity, ensure Excel's iterative calculation is enabled (File → Options → Formulas) with a circularity/convergence switch, and verify it converges.
- Step 6Re-run before sending the model out — Audit the saved file again. A clean report ("No circular references detected") plus your documented intentional switches is the green light to present.
Common financial-model loops and detection
Typical model circularity, whether it is usually intentional, and whether the graph can resolve it to cells.
| Loop | Usually intentional? | Detectable here? |
|---|---|---|
| Interest expense ↔ average debt balance ↔ net income | Yes (iterative calc) | Yes, if schedule cells are single-cell or range-start refs |
| Cash sweep ↔ revolver draw ↔ ending cash | Yes | Yes, across the cash-flow and debt sheets |
| WACC ↔ equity value ↔ discounted cash flow | Sometimes | Yes, if not routed through named ranges |
| Bonus pool = % of net income, net income includes bonus | Occasionally | Yes |
Total row =SUM(B:B) placed inside column B | No — accidental | No — whole-column ref not matched; bound it first |
Driver via named range (=Revenue*GrowthRate) that loops back | Varies | Partial — named range not resolved; substitute cells |
Audit checklist for model circularity
Steps to get a trustworthy result given the tool's graph limits.
| Step | Why it matters for a model |
|---|---|
| Convert whole-column totals to bounded ranges before upload | Whole-column refs (B:B) are invisible to the extractor — a self-including total would pass silently |
| Substitute named ranges with explicit cells for the audit copy | Named ranges are not resolved; loops through them may be missed |
| Cross-reference reported loops against your circularity switch | Intentional interest/cash-sweep loops should be the only ones present |
| Turn iterative calc off after the audit to test for accidents | With it off, accidental loops error loudly; intentional ones you re-enable deliberately |
| Re-run on the final file | Confirms the only remaining cycles are documented and intentional |
Cookbook
Model-flavoured loop examples and the report you get, with notes on intentional vs accidental.
Classic interest circularity (intentional)
Interest depends on debt, debt on cash flow, cash flow on net income, net income on interest. This is a deliberate loop resolved by iterative calculation — expect it in the report and confirm it converges.
Debt!Interest = =AVERAGE(Debt!OpenBal, Debt!CloseBal) * Rate Income!NetIncome = =Income!EBT - Debt!Interest CashFlow!CFADS = =Income!NetIncome + Addbacks Debt!CloseBal = =Debt!OpenBal - CashFlow!CFADS Report: Found 1 circular reference chain(s): 1. Debt!Interest → Income!NetIncome → CashFlow!CFADS → Debt!CloseBal → Debt!Interest Verdict: intentional. Ensure iterative calc + circularity switch are enabled and the model converges.
Accidental bonus-pool loop
Bonus is computed as a percent of net income, but net income subtracts the bonus — a loop nobody designed. The chain points straight at the two offending cells.
Income!NetIncome = =Income!PreBonusProfit - Income!Bonus Income!Bonus = =Income!NetIncome * 0.10 Report: Found 1 circular reference chain(s): 1. Income!NetIncome → Income!Bonus → Income!NetIncome Fix: base the bonus on pre-bonus profit: Income!Bonus = =Income!PreBonusProfit * 0.10
Cross-sheet revolver / cash-sweep loop
The revolver draw covers the cash shortfall, ending cash includes the draw, and the sweep repays from ending cash. A standard intentional loop spanning three sheets — the finder follows it across sheet boundaries.
Debt!RevDraw = =MAX(0, -CashFlow!PreFinancingCash) CashFlow!EndCash= =CashFlow!PreFinancingCash + Debt!RevDraw - Debt!Sweep Debt!Sweep = =MAX(0, CashFlow!EndCash * SweepPct) Report: Found 1 circular reference chain(s): 1. Debt!RevDraw → CashFlow!EndCash → Debt!Sweep → ... (intentional cash-sweep circularity)
The silent self-including total
A total placed inside the column it sums, using a whole-column reference. Excel warns, but the extractor cannot match B:B, so the report is clean. Bound the range to catch it.
CashFlow!B40 = =SUM(B:B) (B40 is inside column B) Report: No circular references detected in this workbook. ← false clear Bound the range and re-run: CashFlow!B40 = =SUM(B5:B39) → no longer self-including, and now detectable if a real loop exists.
Comparing two model versions
Run the finder on last week's model and this week's, and diff the cycle counts in findings. A new loop that appeared between versions is a regression to investigate before the model goes out.
v1 findings: { cycleCount: 1, cycles: [ [interest loop] ] }
v2 findings: { cycleCount: 2, cycles: [ [interest loop],
[Income!NetIncome →
Income!Bonus → ...] ] }
The second cycle is new in v2 → a regression added with the
bonus schedule. Fix before circulating.Edge cases and what actually happens
Intentional interest/cash-sweep loops appear in the report
ExpectedThe tool cannot distinguish deliberate circularity from accidents — it reports all cycles. In a model with iterative calculation, expect the interest-on-debt and cash-sweep loops to show up. Document them as intentional; investigate anything else.
Whole-column totals are not detected
MissedSUM(B:B), SUM(A:A) and similar match no cell token, so a total that accidentally includes itself via a whole-column reference passes silently. Convert key totals to bounded ranges before the audit to avoid a false all-clear.
Named ranges and drivers are not resolved
PartialModels lean on named ranges (Revenue, WACC, GrowthRate). The extractor does not resolve names to cells, so a loop that travels through a named range may be missed. For a thorough audit, substitute the explicit cell range on a working copy.
Only range-start cells are followed
Known limitA schedule formula like =SUMPRODUCT(B5:B16, C5:C16) records edges to B5 and C5 only. If a loop closes through a mid-range cell (B10), it is not seen. Reference the specific cell or split the range to expose it.
INDIRECT-driven scenario toggles
Not resolvedScenario managers built on INDIRECT (=INDIRECT("Case"&ScenarioNo&"!B5")) resolve targets at runtime. The tool reads the literal string, not the computed reference, so loops that only exist under a particular scenario are not detected. Test each scenario in Excel separately.
Cross-sheet loop through a special-character sheet name
PartialSheet names like 'P&L' or '2024-Act' require apostrophe-quoting and contain characters the sheet-qualifier pattern does not allow. References into them may be mis-read as same-sheet, so a cross-sheet loop through 'P&L' can be missed. Rename audit-copy sheets to alphanumeric+underscore.
Model exceeds the row cap
RejectedPro caps at 100,000 rows. A model padded with a giant raw-data tab can exceed this. Delete or move pure-data sheets to a separate file before auditing — the formula schedules you care about are almost always well under the cap.
Same loop listed several times
ExpectedBecause detection starts from every node, one interest loop can appear as several rotated chains. Count loops by their unique cell set, not by the number of printed chains.
Frequently asked questions
Are circular references ever acceptable in a financial model?
Yes. Interest-on-average-debt and cash-sweep circularity are standard and are resolved with Excel's iterative calculation plus a circularity/convergence switch. The risk is accidental loops mixed in with the intentional ones — this tool surfaces all of them so you can confirm only the designed loops remain.
Does it follow loops across the income statement, balance sheet and cash flow sheets?
Yes. Graph nodes are keyed Sheet!Cell, so a loop running Income!B12 → CashFlow!C8 → Debt!D4 → Income!B12 is followed across all three sheets and printed as one chain — provided the sheet names are plain alphanumeric/underscore/space.
Why might it miss a loop I know exists in my model?
Three common reasons specific to models: a whole-column reference (SUM(B:B)) that matches no cell token; a named range the tool does not resolve; or a loop that closes through the middle of a range when only the range start is followed. Convert to bounded ranges and explicit cells on an audit copy, then re-run.
Will it work if iterative calculation is enabled?
Yes — that is exactly when it is most useful. With iterative calc on, Excel shows converged numbers and no warning, so accidental loops hide. Because the tool reads stored formulas, it flags every cycle regardless of the calculation mode.
How do I tell intentional loops from accidental ones in the report?
Match each reported chain against your model's documented circularity (interest, cash sweep, WACC). Chains that pass through unrelated schedules, hardcoded assumptions, or output cells you did not design are almost always accidental and should be fixed.
Does my deal model leave my computer?
No. The .xlsx is parsed in your browser with SheetJS and analysed entirely in memory. Projections, assumptions and deal data never reach a server — only an anonymous "file processed" counter is recorded for dashboard stats if you are signed in.
Can I compare circular references between two model versions?
Yes. Run the finder on each version and compare the cycle count and chains in the findings object. A new chain that appears between versions is a regression introduced by recent edits — catch it before the model circulates.
What format and tier do I need?
.xlsx only, Pro tier or higher. Save your working model as .xlsx (CSV loses formulas). Pro allows 50 MB / 100,000 rows, which covers virtually all models; trim large data tabs if you hit the row cap.
Does it check for #REF! or #DIV/0! errors too?
No — this tool only detects circular reference cycles. For #REF!, #VALUE!, #DIV/0! and #N/A across the model, use the Error Locator. The two together give you a full formula-integrity sweep before presentation.
Can I audit just one schedule instead of the whole model?
The tool always scans every sheet — there is no per-sheet option. To focus on one schedule, save a copy containing only that sheet (plus the sheets it links to) and upload that, or trace a single output cell with the Cell Dependency Map.
How are intentional loops counted if they appear multiple times?
Detection starts from every node, so one interest loop may print as several rotated chains. Treat chains sharing the same cell set as a single loop when you tally intentional vs accidental.
Which sibling tools round out a model audit?
Use the Error Locator for cell errors, the Cell Dependency Map to trace key output precedents, the Formula Explainer to document complex driver formulas, and the External Link Auditor to catch links to other workbooks that break when the model is shared.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.