Iterative Calculation (Excel Parity)

Some spreadsheets intentionally depend on a value computed from themselves: a circular interest calculation, a mutual fixed-point, an accumulator that advances one step per recalc, or a self-referential timestamp. Excel supports these through iterative calculation — it re-runs the circular group until the numbers settle or a pass budget is exhausted.

Formualizer ships the same behavior under CyclePolicy::Iterate (RFC #113). It builds on runtime cycle detection: iteration requires CycleDetection::Runtime, and selecting the iterate policy promotes detection to runtime automatically.

Enabling iterative calculation

Iterative calculation has two knobs, with Excel's defaults:

• max_iterations — total passes per cycle per recalc (Excel default 100). 1 means each member evaluates exactly once per recalc (the accumulator contract). 0 is a config error.
• max_change — the absolute convergence threshold (Excel default 0.001). A member is settled when |Δ| < max_change. Negative or non-finite values are config errors.

use formualizer_eval::engine::CycleConfig;use formualizer_workbook::{Workbook, WorkbookConfig};let mut cfg = WorkbookConfig::interactive();// Runtime detection + Iterate { max_iterations: 100, max_change: 0.001 }cfg.eval = cfg.eval.with_cycle(CycleConfig::iterate_excel_defaults());// ...or explicit knobs: CycleConfig::iterate(50, 0.0001)let mut wb = Workbook::new_with_config(cfg);

Convergence semantics, in user terms

• max_change is absolute. A member converges when its value moves by less than max_change between passes — there is no relative/percentage mode. The comparison is strict (|Δ| < max_change), matching Excel.
• Members run in a stable order, committing as they go. Within a pass the engine evaluates each cycle member in order and commits each result before the next member runs (Gauss–Seidel), so later members in the same pass already see the updated values.
• Hitting the cap is not an error. When a cycle does not converge within max_iterations passes, the engine keeps the last values and moves on — exactly like Excel. Telemetry records it as a "capped" cycle, but no #CIRC! is stamped.

Worked examples

Circular interest / mutual fixed-point

A pair that settles on a fixed point: B1 = 0.5*A1 + 0.5*C1, C1 = 0.5*B1 + 0.5*D1, with A1 = 10, D1 = 20. The exact solution is B1 = 40/3 ≈ 13.3333, C1 = 50/3 ≈ 16.6667. Drag the max-change slider to watch convergence tighten or loosen.

import formualizer as fzcfg = fz.EvaluationConfig()cfg.cycle_policy = "iterate"wb = fz.Workbook(config=fz.WorkbookConfig(eval_config=cfg))wb.add_sheet("S")wb.set_value("S", 1, 1, 10)   # A1wb.set_value("S", 1, 4, 20)   # D1wb.set_formula("S", 1, 2, "=0.5*A1 + 0.5*C1")  # B1wb.set_formula("S", 1, 3, "=0.5*B1 + 0.5*D1")  # C1wb.evaluate_all()assert abs(wb.get_value("S", 1, 2) - 40 / 3) < 0.01t = wb.last_cycle_telemetry()print("converged:", t.converged_sccs == 1)   # did it converge?print("capped:", t.capped_sccs)               # 0 -> settled before the capprint("final delta:", t.max_abs_delta_at_stop)

The accumulator (=A2+1, one step per recalc)

With max_iterations: 1, a self-referential cell advances exactly one step per recalculation request. Click Recalc to tick it up by one each time.

import formualizer as fzcfg = fz.EvaluationConfig()cfg.cycle_policy = "iterate"cfg.iterate_max_iterations = 1   # one pass per recalcwb = fz.Workbook(config=fz.WorkbookConfig(eval_config=cfg))wb.add_sheet("S")wb.set_formula("S", 1, 1, "=A1+1")wb.evaluate_all()assert wb.get_value("S", 1, 1) == 1.0   # one stepwb.evaluate_all()assert wb.get_value("S", 1, 1) == 2.0   # another step# The accumulator never converges, so each recalc caps at one pass.assert wb.last_cycle_telemetry().capped_sccs == 1

The self-referential timestamp

A cell that stamps "now" the first time it is touched and then holds steady is the iterative form of an audit timestamp: =IF(A1=0, NOW(), A1). With iteration enabled and the input guard transitioning once, the cell records a value and subsequent recalcs leave it in place (the live edge keeps reading the already-stamped value). This is the iterative-calculation idiom behind Excel's "timestamp on entry" trick.

Retained fixed points

The engine does not re-run a cycle that has already settled exactly. Every iterating cycle used to be redirtied at the end of each recalc, so circular cells re-evaluated on every recalculation; a workbook with thousands of circular cells paid for all of them on every recalc, even when nothing had changed.

A cycle is retained clean across recalcs when the recalc that iterated it stopped on an exact fixed point: every member reproduced its previous value exactly — |Δ| == 0 for numbers (not merely |Δ| < max_change), identity for text, booleans and errors, and never a NaN identity — before the max_iterations cap, with no volatile and no dynamic-reference (INDIRECT, OFFSET) member. Running such a cycle again with the same inputs cannot change a value, so the engine serves the values it already has.

A retained cycle runs again as soon as the dirty graph reaches one of its members — a precedent edit, a formula change, a structural edit, a name rebinding — or when a config knob that could change its result changes between recalcs (cycle policy or tolerance, date system, workbook seed, volatile level, deterministic mode, range-expansion limits), or when a function one of its members calls is registered or replaced. Everything else keeps the old per-recalc behavior: tolerance-only convergence (|Δ| < max_change but non-zero), capped cycles including the max_iterations: 1 accumulator above, and volatile cycles such as the self-referential timestamp.

Measured in release on 78 ring cycles of 120 members each (9,360 circular cells) plus three downstream column families:

Values and checksums are identical either way — retention only removes work that could not have changed an answer.

Two CycleTelemetry fields report it: reused_sccs counts the retained cycles that had no dirty member at the start of the request and were therefore served without a re-run, and reused_scc_members counts their members. EngineBaselineStats::retained_scc_members (Rust) reports how many members are currently retained.

import formualizer as fzcfg = fz.EvaluationConfig()cfg.cycle_policy = "iterate"wb = fz.Workbook(config=fz.WorkbookConfig(eval_config=cfg))wb.add_sheet("S")wb.set_formula("S", 1, 1, "=IF(B1>2, 7, B1+1)")  # A1: climbs 1,2,3 then holds 7wb.set_formula("S", 1, 2, "=A1")                  # B1wb.evaluate_all()                                 # iterates until A1 = B1 = 7 exactlyassert wb.last_cycle_telemetry().reused_sccs == 0wb.evaluate_all()                                 # retained: no passes at allt = wb.last_cycle_telemetry()assert t.reused_sccs == 1 and t.iterated_sccs == 0# A converged-within-tolerance cycle (|Δ| < max_change but not 0) is NOT# retained: e.g. "=0.5*B1+10" / "=A1" keeps re-running every recalc.

There is no switch to turn retention off. It only removes work that could not have changed an answer, and a retained cycle is invalidated by exactly the machinery that invalidates every other formula, so a case where it should be disabled is a bug worth reporting rather than a configuration.

Reading convergence telemetry

The engine records a CycleTelemetry snapshot per recalc, reset at the start of every evaluation request. Useful fields for answering "did it converge?":

The Python binding exposes this via Workbook.last_cycle_telemetry() (see examples above). The WASM binding is config-only today — it accepts the iterate config but does not yet surface telemetry.

XLSX calcPr round-trip

Iterative calculation is part of the workbook file format. Formualizer round-trips the <calcPr> element (spec §9):

• Save: when the active policy is Iterate, the written .xlsx carries iterate="1" iterateCount="<max_iterations>" iterateDelta="<max_change>". Files saved with iteration enabled open correctly in Excel with the same settings.
• Load: when a loaded .xlsx has iterate="1", the engine adopts Iterate { max_iterations: iterateCount ?? 100, max_change: iterateDelta ?? 0.001 } and promotes detection to Runtime. Files saved by Excel with iteration enabled open correctly here.
• File-wins precedence on load. A loaded file with iterate="1" turns iteration on even if your config requested the Error policy — the file's calculation settings take precedence. A file with iterate="0" (or no <calcPr>) leaves your configured policy untouched.

Related

• Circular References and Runtime Cycle Detection
• Cycle Configuration Reference
• Workbook Edits and Batching
• Python API Map