Handle Circular References and Iterative Calculation in Python

Most circular references in spreadsheets are mistakes, and Excel warns about them for good reason. Some are intentional. The classic one in finance: interest earned depends on the average cash balance, but the closing balance includes the interest. Another: a bonus pool of 10% of profit after the bonus. Modellers make these work by switching on iterative calculation, which tells the spreadsheet to recalculate the loop repeatedly until the numbers stop changing. A Python script that generates or edits such a model has to set that switch in the file — otherwise every cell in the loop shows an error — and a script that only reads the numbers is often better off solving the loop itself. This page covers detection, the workbook setting and the Python solution. It belongs to Working with Excel Formulas in Python.

Prerequisites

pip install openpyxl pandas

LibreOffice (soffice) is used to recalculate the generated workbooks and show what a spreadsheet application does with them.

Step 1 — Build a model with a deliberate loop

from openpyxl import Workbook

def cash_model(path, iterate):
    wb = Workbook()
    ws = wb.active
    ws.title = "Cash"
    ws.append(["", "Q1", "Q2", "Q3", "Q4"])
    ws.append(["Opening balance", 100000, "=B5", "=C5", "=D5"])
    ws.append(["Net cash flow", 25000, -40000, 15000, 30000])
    ws.append(["Interest (4% p.a. on average balance)"] + [f"=0.04/4*({c}2+{c}5)/2" for c in "BCDE"])
    ws.append(["Closing balance"] + [f"={c}2+{c}3+{c}4" for c in "BCDE"])
    ws["G1"], ws["G2"] = "Rate", 0.04
    if iterate:
        wb.calculation.iterate = True
        wb.calculation.iterateCount = 100
        wb.calculation.iterateDelta = 0.0001
    wb.save(path)

cash_model("cash_no_iter.xlsx", iterate=False)
cash_model("cash_iter.xlsx", iterate=True)

Each quarter's interest is a quarter of 4% on the average of the opening and closing balances, and the closing balance is opening plus cash flow plus interest. Interest needs closing, closing needs interest: row 4 and row 5 refer to each other in every column. Each quarter's opening balance is the previous quarter's closing balance, so the loop runs through the whole model.

Step 2 — See what happens with and without iteration

import subprocess
from openpyxl import load_workbook

for f in ("cash_no_iter.xlsx", "cash_iter.xlsx"):
    subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc", f],
                   check=True, capture_output=True)
    ws = load_workbook(f"recalc/{f}", data_only=True)["Cash"]
    print(f"{f:18} interest Q1 = {ws['B4'].value!s:>10}   closing Q4 = {ws['E5'].value}")

Without iterative calculation, every cell in the loop is an error — LibreOffice stores #VALUE! in the file (its internal "circular reference" error) and Excel shows a circular-reference warning and zeros. With the three calculation settings in the file, the spreadsheet loops until changes fall below iterateDelta or iterateCount passes have run, and the balances converge: Q1 interest of 1,130.65 and a Q4 closing balance of 134,336.45.

Step 3 — The three settings openpyxl writes

wb = load_workbook("cash_iter.xlsx")
calc = wb.calculation
print("iterate:", calc.iterate, "| max iterations:", calc.iterateCount, "| max change:", calc.iterateDelta)

These map to Excel's File › Options › Formulas › Enable iterative calculation, Maximum Iterations and Maximum Change. They are stored per workbook, so the setting travels with the file — but Excel applies the setting of the first workbook opened in a session to all workbooks open at the same time, which is a common source of "it works on my machine" with circular models. A delta of 0.0001 is fine for currency; 100 iterations is far more than a well-behaved loop like this one needs — it converges in a handful. Calculation settings in general are discussed in Recalculate Excel Formulas Without Excel in Python.

Step 4 — Find circular references from Python

Before turning iteration on, know which loops exist: an accidental circular reference hidden among deliberate ones is the most dangerous kind, because iteration silences its warning too. A small dependency graph built from the formulas finds them:

import re
from openpyxl.utils import range_boundaries, get_column_letter

REF = re.compile(r"\$?([A-Z]{1,3})\$?(\d+)(?::\$?([A-Z]{1,3})\$?(\d+))?")

def cells_in(match):
    c1, r1, c2, r2 = match.groups()
    if c2 is None:
        return [f"{c1}{r1}"]
    min_col, min_row, max_col, max_row = range_boundaries(f"{c1}{r1}:{c2}{r2}")
    return [f"{get_column_letter(c)}{r}" for c in range(min_col, max_col + 1) for r in range(min_row, max_row + 1)]

def find_cycles(ws):
    graph = {}
    for row in ws.iter_rows():
        for cell in row:
            if isinstance(cell.value, str) and cell.value.startswith("="):
                graph[cell.coordinate] = {ref for m in REF.finditer(cell.value) for ref in cells_in(m)}
    cycles, state = [], {}
    def visit(node, path):
        state[node] = "active"
        for nxt in graph.get(node, ()):
            if state.get(nxt) == "active":
                cycles.append(path[path.index(nxt):] + [nxt])
            elif nxt not in state:
                visit(nxt, path + [nxt])
        state[node] = "done"
    for node in graph:
        if node not in state:
            visit(node, [node])
    return cycles

for cycle in find_cycles(load_workbook("cash_no_iter.xlsx")["Cash"]):
    print(" -> ".join(cycle))

The search reports one loop per quarter, such as B4 -> B5 -> B4. It only follows same-sheet references written as A1 addresses — cross-sheet references, defined names and INDIRECT need more parsing — but that covers most models. Review the list: every loop on it should be one somebody intended. A wider view of formula dependencies is in Map Formula Dependencies in a Workbook with Python.

Step 5 — Solve the loop in Python instead

A report that only needs the numbers should not depend on a spreadsheet's iteration settings. The same loop can be solved directly. Fixed-point iteration mirrors what the spreadsheet does:

import pandas as pd

flows = [25000, -40000, 15000, 30000]
q_rate = 0.04 / 4

def quarter_fixed_point(opening, flow, tol=1e-9):
    interest = 0.0
    for i in range(100):
        closing = opening + flow + interest
        new_interest = q_rate * (opening + closing) / 2
        if abs(new_interest - interest) < tol:
            return new_interest, closing, i + 1
        interest = new_interest
    raise RuntimeError("did not converge")

rows, opening = [], 100000.0
for q, flow in zip(["Q1", "Q2", "Q3", "Q4"], flows):
    interest, closing, n = quarter_fixed_point(opening, flow)
    rows.append({"Quarter": q, "Opening": opening, "Interest": interest, "Closing": closing, "Iterations": n})
    opening = closing
print(pd.DataFrame(rows).round(2).to_string(index=False))

Each quarter converges in seven iterations to a tolerance of a billionth, because the loop's "gain" — half of a 1% quarterly rate — is tiny. Better still, this loop has a closed form. Substituting closing into the interest formula and solving for interest gives interest = r·(2·opening + flow) / (2 − r):

opening = 100000.0
for q, flow in zip(["Q1", "Q2", "Q3", "Q4"], flows):
    interest = q_rate * (2 * opening + flow) / (2 - q_rate)
    closing = opening + flow + interest
    print(f"{q}: interest {interest:,.2f}  closing {closing:,.2f}")
    opening = closing

The algebraic answer matches the fixed-point one to the cent and needs no loop at all. The same rearrangement works for the bonus example: a bonus of 10% of profit after bonus is 0.1 × profit / 1.1.

Step 6 — Compare the workbook with the Python result

When a workbook keeps its circular formulas for the people who use it, check the spreadsheet's converged values against the exact Python figures:

ws = load_workbook("recalc/cash_iter.xlsx", data_only=True)["Cash"]
sheet_interest = [ws[f"{c}4"].value for c in "BCDE"]
python_interest = [r["Interest"] for r in rows]
for q, a, b in zip("1234", sheet_interest, python_interest):
    print(f"Q{q}: sheet {a:,.4f}  python {b:,.4f}  diff {abs(a - b):.6f}")

The differences are below the iterateDelta set in the file — the spreadsheet stops iterating once changes are smaller than that, so its answer is accurate to roughly that precision. A test that asserts the difference stays below a cent catches the day someone accidentally adds a second, divergent loop.

Common pitfalls

• Turning iteration on to hide an error. It silences accidental loops too; list every cycle first.
• Expecting the setting to be per file in Excel. The first workbook opened sets the mode for the session.
• Too large a delta. A delta of 1 on currency amounts leaves visible rounding; use 0.001 or smaller.
• Divergent loops. If a loop's gain is above 1 — a formula that multiplies the circular value by more than one — iteration never settles; rewrite the model.
• Reading cached values from a script-built file. openpyxl stores no results; recalculate first.

Conclusion

Deliberate circular references need iterative calculation, which openpyxl sets through wb.calculation.iterate, iterateCount and iterateDelta; without it the loop cells show errors. Find every cycle from Python before switching iteration on, so accidental loops are not silenced. For reporting, solve the circularity in Python — by fixed-point iteration or, better, algebraically — and use the workbook's iterated values only as a cross-check.

Frequently asked questions

How do I enable iterative calculation with openpyxl? Set wb.calculation.iterate = True and, optionally, wb.calculation.iterateCount (maximum iterations) and wb.calculation.iterateDelta (maximum change) before saving the workbook.

Why do my circular formulas show errors after Python writes the file? Iterative calculation is off by default, so spreadsheet applications treat the loop as an error. Enable it in the workbook's calculation settings, or remove the circularity.

How can I find circular references in an Excel file with Python? Build a graph from each formula cell to the cells its formula references, then search the graph for cycles with a depth-first search. Cells that lead back to themselves are part of a circular reference.

How do I calculate interest on an average balance without a circular reference? Solve for interest algebraically: with quarterly rate r, interest = r × (2 × opening + flow) / (2 − r). The closing balance is then opening + flow + interest, with no loop needed.

Related

• Up one level: Working with Excel Formulas in Python — writing, auditing and recalculating formulas.
• Find Formula Errors in a Workbook with Python — locating error values after a recalculation.
• Build a Cash Flow Forecast Report — a model where this circularity appears.
• Goal Seek in Python Instead of Excel — solving for an input numerically.