Tracing a circular reference
Iteration is on. The status bar says Calculate. The IRR cell points at a cash flow that points back at the IRR. Stop typing. Map the cycle first.
A circular reference in a live deal file is rarely a cute Excel warning you can dismiss. It is a closed loop that keeps recalculating until someone forces iteration, freezes a seed, or ships a number nobody can defend. The junior who toggled iteration to "make it converge" did not invent a method. They hid a dependency.
Partners do not ask whether Excel can iterate. They ask which cell closes the loop, which sheet owns the seed, and who signed that the circular path was intentional. If you cannot answer those three questions, you do not have a circular model. You have an unfinished one.
Map the cycle before you break it
Open the circular-reference dialog and walk every hop. Write the chain on paper or in a scratch sheet: sheet, cell, next cell. Do not repair mid-walk. A half-fixed loop becomes a different bug with the same name.
- Start at the IRR or NPV output that refuses to settle.
- Follow precedent arrows until you return to a cell you already listed.
- Mark the shortest closed path. Longer paths are usually noise around the real cycle.
- Name the seed: the cell that should be an input, not a formula that peeks at the output.
- Only then decide whether the circular path is intentional financing logic or an accidental self-reference.
Break order that survives a partner read
Intentional circulars exist in some leverage and interest loops. Accidental ones show up after a paste into a cash-flow bridge. Treat intention as a claim you have to prove, not a default. If the claim is weak, break the loop and rebuild with an explicit seed.
- Freeze or extract the seed value into a clearly labeled input cell.
- Rewrite the dependent formulas so they read the seed, not the final output.
- Turn iteration off and confirm the workbook calculates cleanly.
- Re-enable only if the model documentation says the circular path is required and reviewed.
- Re-run the read-back and confirm the circular flag clears or is marked intentional with a named owner.
What iteration hides
Iteration can make a broken chain look stable. The status bar finishes. The IRR prints. The print pack looks done. The loop is still there. The next person who changes a timing assumption will inherit a workbook that only works while Excel is busy chewing itself.
If the file only converges with iteration on, write that down like a risk, not like a feature.
What Moraton names
The add-in reports the circular path by row and sheet. It does not decide whether the loop is good modeling. It does not certify the IRR. A person still signs off. That is the product: a read-back that makes the cycle loud enough to own.
Midnight checklist
Map the hops. Name the seed. Break or document. Turn iteration off to prove the file stands alone. Sign the flag. Then send upstairs.
