Circular reference

In a model with a revolver the loop is unavoidable arithmetic rather than a mistake. Interest is properly charged on the average of the opening and closing balances, because the balance moved during the year. The closing balance depends on how much cash was swept against the debt. And the cash available to sweep is struck after interest has been paid. Three cells, each waiting on another.

It has an exact answer, which is worth knowing because it removes the mystique. The loop is a linear equation and can be solved by hand in two lines, and a spreadsheet's iterative calculation reaches the identical number by feeding its own output back in until the change falls below a tolerance.

The reason practitioners are wary is what happens when something inside the loop breaks. A division by zero, or a deleted row the loop passes through, propagates around the circle and is stored as the starting value for the next pass. Fixing the original cause does not clear it, because there is no longer anything clean to iterate from. That is what a circularity switch exists to prevent.

The same category of problem appears elsewhere in finance and is resolved the same way. The equity weight in a weighted average cost of capital depends on an equity value the model is trying to produce, and a synthetic credit rating depends on an interest charge that depends on the rating. Either iterate to convergence or accept a non circular proxy and say which you did.