Circular Reference

Spreadsheets normally calculate in a sequence, with each formula using results that have already been calculated. A circular reference breaks this, because a cell's value depends on itself.

In financial models, circular references often arise intentionally. A common example involves interest: interest expense depends on the average debt balance, the debt balance depends on how much cash is available to repay it, and available cash depends on interest expense.

For example, in a leveraged buyout model, the amount of debt repaid each year affects interest, which affects net income and cash flow, which in turn affects the amount of debt repaid.

Spreadsheets can solve intentional circular references through iterative calculation, repeatedly recalculating until the values settle. However, circular references can make models unstable, slow and hard to check, and errors can spread unnoticed. Many modellers avoid them by calculating interest on opening balances, or include a switch to turn the circularity off. Unintended circular references usually indicate a mistake.