Three statements Updated November 2026

Circular references

A circular reference arises when interest depends on the debt balance, which depends on cash flow, which depends on interest. Excel solves it with File, Options, Formulas, Enable iterative calculation (100 iterations, max change 0.001), and models add a switch that sets interest to the opening-balance method when the circle must be broken.

Syntax

=IF(circ_switch = 1, rate * AVERAGE(opening, closing), rate * opening)

Part	What it is
`circ_switch`	Input cell: 1 to allow the circular (average balance) calculation, 0 to break it
`Max iterations`	How many times Excel recalculates; 100 is typical
`Max change`	Stops iterating when results move less than this; 0.001 is typical

Examples

Opening debt 1,000, closing 900, rate 5%, switch = 1
=IF(1=1, 5%*AVERAGE(1000,900), 5%*1000)

Result: 47.50

Same with switch = 0
=IF(0=1, 5%*AVERAGE(1000,900), 5%*1000)

Result: 50.00

Enable iteration (Windows)
File > Options > Formulas > Enable iterative calculation; Max iterations 100; Max change 0.001

Result: circular formulas resolve instead of showing a warning

Common mistakes

Leaving iteration on with an unintended circular reference somewhere else, which hides a real error; keep the switch at 0 while building.
A model showing #REF! or huge numbers everywhere after a circular blow-up: set the switch to 0, let it recalculate, then set it back to 1.
Interest on the average cash balance and the average revolver balance in the same model without iteration enabled.

Practice Circular references

These drills check your answer on live data, free, no account needed.

Interest on the opening debt balanceA term loan is repaid in equal amounts. Fill the opening balance for later years (row 5), the closing balance (row 7) and interest expense (row 8), charging the rate in B2 on the opening balance of each year.
Cash and revolver, fully linkedNow link the cash too. Each year starts with last year's closing cash, adds the cash flow, then draws on or repays the revolver to keep the minimum in B2. Fill rows 7 to 12 (the first opening cash and opening revolver are set).

Questions

Should I avoid circular references altogether?

Many banks require it: use interest on opening balances, or on the average balance with a circuit breaker. Both approaches are accepted if the switch is clearly labeled.

Why does Excel show a circular reference warning on open?

Iterative calculation is a workbook-level setting stored with the first workbook opened in the session. Open the model first, or turn iteration on and save.

Interest on the opening balanceInterest = rate x opening balance avoids a circular reference; rate x average balance is more accurate but circular. Both formulas, an example, when to use.
RevolverA revolver draws when cash falls below the minimum and repays from excess cash. The MIN and MAX formula, a two-year worked example and avoiding circularity.
Debt scheduleA debt schedule rolls each loan forward: closing = opening + draws - repayments, with interest on the opening balance. Layout, example, amortization, sweeps.
Three-statement modelHow a three-statement model links net income, depreciation, capex, working capital, debt and cash so the balance sheet balances. Build order, links, check.