Interest circularity is the check AI tools fail most often
A circular reference happens when interest depends on cash and cash depends on interest. Every AI tool in the leading benchmark skipped the loop instead of solving it. The test is a second calculation: iterative and algebraic answers must agree, here to 5×10⁻¹³.
In its 2026 ranking of AI tools for financial modeling, Wall Street Prep set four of them the same task: build a fully integrated three-statement model for Apple, three years of history from the 10-K and four years of forecasts from consensus, formatted the way a bank would format it. The tools scored 5.9, 5.5, 4.4 and 2.5 out of ten. A low-tier junior analyst scored 6.4.
Then the article published the results by category, and one category was not a ranking at all. Its heading reads: All failed.
The exact wording matters, because it tells you what the failure was. On circularity the article states that "no tool forecasted interest income and expense from cash and debt balances," and therefore "there was no circularity embedded in the model." On debt it is just as direct: "None handled debt correctly," and "Most relied on plugs rather than proper integration."
The loop was not built badly; it was never closed. The tools wrote three statements on one page and left interest as a number that does not respond to the balance it is charged on. Closing that loop is what makes a three-statement model a model rather than a set of schedules that happen to sit next to each other.
What that test measured
The task was deliberately ordinary. Take published filings, build the historical statements from them, forecast forward on stated assumptions, and make the balance sheet balance in every period. The grading was done the way a training program grades an analyst: speed, historical accuracy, formatting, sourcing, and whether the forecast structure actually holds together.
Circularity is a gate in that last category rather than a point of style. If interest is forecast off the cash and debt balances, and the balances depend on interest, then any correct answer has to solve a loop. A model that avoids the loop avoids the problem, and it shows up immediately in the numbers: interest income is zero or constant, debt never responds to cash, and the balance sheet is made to balance by a plug.
For the test in this series I used the same structural test on a much smaller subject: a four-year set of accounts for a synthetic company, delivered as PDFs rather than filings, with the answer key withheld until after the build. That is a cross-check inside one workbook, not an external audit, and the distinction is set out below and again in how each model is checked.
Why a tool omits the loop rather than getting it wrong
Getting the loop wrong leaves evidence. Omitting it does not: a constant interest line is a perfectly well-formed cell, it reconciles, nothing turns red, and the balance sheet balances because something else is absorbing the difference. That is what a balance sheet that will not balance looks like from the other end. To an analyst opening the file, the workbook looks finished. The failure is only visible if you click the interest cell and ask what it references.
The same omission explains the scores: the four tools produced something that reads like a three-statement model, and the twelve ties are mostly satisfied, because the ties test arithmetic, and arithmetic was fine.
Why the loop exists: interest depends on the debt balance
Charge interest on the opening balance and there is no loop. Opening debt is known; interest is rate times that number; done. This is what a model looks like when it is built to avoid circular references rather than to answer the question.
The loop appears the moment interest is charged on an average balance, which is what a facility actually does. Average debt is the mean of opening and closing debt. Closing debt depends on how much was repaid. Repayment depends on cash available. Cash available is after interest. Interest depends on average debt. The chain closes on itself in four steps.
Write it out and the loop is one variable rather than a tangle. Let the rate be r, the opening balance B₀, the closing balance B₁, and interest I:
Substitute the second line into the first and I appears on both sides. That substitution is the whole circularity: not a modeling error, and not avoidable by being careful, but a property of charging interest on a balance that the interest itself moves.
Where it shows up: revolver, cash sweep, minimum cash
Three structures produce it, and almost every real model has at least one.
A revolver draws only what is needed to keep cash above a minimum, so the draw depends on the shortfall, and the shortfall depends on interest on the revolver. A cash sweep repays debt from surplus cash, so repayment depends on cash after interest, and interest depends on the balance after repayment. A minimum cash balance with interest earned on surplus has the same shape in the opposite direction: the surplus earns interest, the interest increases the surplus.
The version in the workbook built for this series is the third. Debt is not a revolver here: a single facility amortizes evenly, 3,400 at the start repaid at 680 a year to nil in FY2030. With debt out of the loop, cash remains: the model lets cash earn the same 6.16% it pays, so interest received depends on average cash, and closing cash depends on interest received after tax.
K is the net cash flow before any interest income, t is the 25% tax rate. Same loop, different balance. The algebra does not care which account is circular; only the sign of the feedback changes.
The three ways to handle it, and what each costs in auditability
Three standard responses exist, and they are usually presented as a matter of preference. They differ in what a reviewer can reconstruct afterwards, which is the only thing that matters when someone else has to sign the model off.
Iterative calculation
Turn on Excel's iterative calculation (File > Options > Formulas > Enable iterative calculation; on Mac, Excel > Preferences > Calculation) and let the workbook settle on a fixed point. The defaults are 100 iterations and a maximum change of 0.001, which is exactly what the instruction for this build specified, though specifying the defaults is mostly a way of making the setting explicit in the file.
The cost is that the cell now holds an answer with no derivation. A reviewer sees a number. Whether the workbook took three passes or hit the 100-iteration ceiling is not visible in the cell, and the number changes if the setting changes. It is reproducible only by reproducing the settings, which is a different claim from being checkable. The audit trail an AI spreadsheet leaves is thinner here than anywhere else in the model.
Algebraic substitution
Solve the loop in closed form instead. For the cash version above, the substitution is short enough to do by hand:
α is the feedback gain: the fraction of each extra unit of interest that comes back as further interest, after tax. At a 6.16% rate and a 25% tax rate it is 2.31%. Because α is small, the loop converges fast: the geometric series 1 + α + α² + … is within a thousandth of its limit after three terms. This is also why iteration works at all here, and why a model with a much higher rate can fail to converge.
The cost is legibility. A reader who does not follow the algebra has to trust it. But the derivation sits in cells, each one visible, and the closed form can be compared against the live loop. That is the check in the next section.
Plug-and-check
Break the loop by calculation order: charge interest on the opening balance, or compute cash, then overwrite one cell with a hard-coded number until the balance sheet balances. The error is usually small and the workbook recalcs instantly.
The plug has the worst cost of the three, because nobody can derive it. The plug looks like every other number in the sheet, and it survives every tie-out test that does not specifically look for hard-coded cells inside a formula range. This is close to what Wall Street Prep found: "Most relied on plugs rather than proper integration."
| Method | What it buys | What it costs at review |
|---|---|---|
| Iterative | Fast to set up, handles any shape of loop | The answer has no derivation; depends on a workbook-level setting |
| Algebraic | Exact, reproducible, auditable cell by cell | Only works where the loop can be solved in closed form |
| Plug-and-check | Always converges, never errors | The plug is untraceable and invisible to tie-out tests |
What a non-converging loop looks like
A loop that does not converge raises no error. That is the whole problem.
Excel warns once, when the circular reference first appears, and then stops warning. With iteration off, the cell shows zero or the last value it calculated. With iteration on, it shows whatever the final pass produced, whether or not that pass was within tolerance. Microsoft's own documentation on circular references describes both behaviors plainly, and neither involves a failure state you would notice.
Divergence happens when the feedback gain reaches one. With α = r(1 − t)/2, a rate of 6.16% gives 2.31% and settles at once; a rate of 250% with no tax shield gives 1.25 and the value grows every recalculation. Less extreme cases fail more quietly: a loop routed through a different sheet produces no cell address in the status bar, and a loop whose gain is just under one converges slowly enough that the number keeps moving for several seconds after every edit.
The practical test is not whether Excel complains. It is whether the number stops moving. Change an unrelated input, force a full recalculation, and watch the interest cell: if it lands on the same value every time, the loop has a fixed point. If it drifts, the model is reporting a number that depends on when you looked.
Verifying the loop with a second, independent calculation
The only check that means anything is a second calculation of the same interest figure by a route that does not use the loop.
The workbook does this in the same sheet, in a block immediately below the live model. The iterative column is the one that actually drives the statements. Next to it sits an algebraic column that refers only to opening cash, the named drivers and the tax rate, never to the iterative result. Then the two are subtracted.
The result across the five forecast years, in thousands:
| Year | Opening cash | Interest, iterative | Interest, algebraic | Closing cash, iterative | Closing cash, algebraic | Difference |
|---|---|---|---|---|---|---|
| FY2026 | 338.00 | 6.11 | 6.11 | −139.92 | −139.92 | 0.00 |
| FY2027 | −139.92 | −20.87 | −20.87 | −537.18 | −537.18 | 0.00 |
| FY2028 | −537.18 | −42.49 | −42.49 | −841.38 | −841.38 | 0.00 |
| FY2029 | −841.38 | −57.95 | −57.95 | −1,038.62 | −1,038.62 | 0.00 |
| FY2030 | −1,038.62 | −66.33 | −66.33 | −1,113.32 | −1,113.32 | 0.00 |
Ten checks, two per year, one for the interest figure and one for the closing cash it produces. All ten pass at the 0.01 tolerance. The largest residual anywhere in the ten is 5 × 10⁻¹³ in thousands: double-precision noise, not a modeling difference. The workbook records both the difference and a TRUE/FALSE flag for "loop converged within tolerance," so the check is live rather than a number typed into a summary.
This is what the circular reference check should look like in any model: not a promise that the loop converges, but a second number computed without the loop, sitting next to the first, with the subtraction visible.
A limit applies here too. Two routes agreeing proves that the fixed point is the fixed point of the algebra as specified. Agreement does not prove the algebra is the right one, that 6.16% is the right rate, or that the company should run on negative cash. The model's own limits sheet says the last of these out loud: closing cash falls from 338 in FY2025 to −1,113 in FY2030, because nothing in the model funds the shortfall: no revolver, no new debt, no equity raise, no dividend cut. The interest loop converges because negative cash is charged at the same rate as positive cash. Economically that is an overdraft nobody arranged.
The loop is a structural check. It tells you the arithmetic joined up. The judgement about whether the answer is a business case sits elsewhere, which is the point of the 99 checks on this workbook and of the equity valuation that would sit on top of a forecast like this one.
Because Mica recalculates the whole workbook in place, a loop that fails to converge shows up as a number that will not settle, not as an error message.