How to Turn On Iterative Calculation in Excel

Iterative calculation lets Excel resolve a deliberate circular reference by recalculating until the numbers settle. Normally a circular reference is an error, but some financial mechanics, such as interest on a revolving credit line, are genuinely circular. Turning on iteration tells Excel to loop a fixed number of times until the change is tiny. It is a sharp tool that you should switch on only when the circularity is intentional.

What iterative calculation does and when to use it

By default Excel refuses to evaluate a formula that depends on itself and reports a circular reference warning. Iterative calculation changes that: Excel recalculates the circular chain repeatedly, feeding each pass into the next, until either it reaches the maximum number of iterations or the largest change falls below a threshold you set.

Use it only for circularity that is part of the math, where the answer truly feeds back on itself. The classic case is a revolver where interest depends on the average debt balance, which depends on the cash flow, which depends on the interest. Do not enable it to silence an accidental circular reference; that hides a real mistake.

Step by step

Turn iteration on at the workbook level before building the circular logic.

1. Go to File > Options > Formulas.
2. Under Calculation options, check Enable iterative calculation.
3. Set Maximum Iterations, for example 100, which is the cap on passes per calculation.
4. Set Maximum Change, for example 0.001, the smallest change that still triggers another pass.
5. Click OK; Excel now resolves intended circular references instead of erroring.
6. On Mac, find the same options under Excel > Preferences > Calculation.

Lower Maximum Change for more precision, at the cost of more passes.

A model use case

In a debt schedule with a revolver, cash available for debt service drives the revolver draw or repayment, which sets the average balance, which sets interest expense, which feeds back into cash flow. That loop only resolves with iteration on. Pair it with a circularity switch, a cell that can break the loop by forcing interest to zero, so you can clear a frozen calculation and restart it cleanly.

Keep the intentional circularity contained to a small, documented block so a reviewer can see exactly where the loop lives. Many modelers prefer a copy and paste macro that breaks the circularity entirely; iteration is the lighter alternative when you want the loop to stay live.

• Classic case: interest on a revolver tied to the average debt balance.
• Add a circularity switch cell to break and reset a frozen loop.
• Document where the intended circular block is so reviewers can find it.

The danger of masking real errors

Once iteration is on, Excel stops warning you about circular references entirely, including accidental ones. A stray self reference will then quietly converge to a wrong number or leave a hard to spot residual, instead of flashing an error. That is the main risk: the safety net is gone.

Iteration is also a workbook level setting that travels in the file, so a model with it enabled can produce slightly different results for someone whose copy has it off, and a broken loop can leave stale values until you force a full recalculation with Ctrl+Alt+F9. Turn iteration on deliberately, keep the circular logic small, and when something looks wrong, trace the dependency chain to confirm the loop is the one you intended rather than an accident.