Circular references in Excel are generally bad news:
- They are slow to calculate
- They can be hard to detect
- An intentional circular reference can mask an unintended circular reference,
- They do not always converge
- The Status Bar always shows calculate even in Automatic Mode
What are Excel Circular References?
Excel circular references occur either when a formula refers to itself or when a chain of formulas links back to it’s starting point.
Most of the time circular references are created by mistake (unintended circular references) and should be corrected. They can also be used to solve iterative or goal-seeking problems in Finance and Engineering.
Why circular references are slow to calculate.
Excel uses a special iterative calculation method to solve circular references. First a standard recalculation is done which identifies the circular formulas that cannot be resolved.
Then Excel repeatedly calculates all the unresolved circular formulas using a sheet-by-sheet left-to-right top-to-bottom calculation process.
The calculation only stops when either
- The maximum number of iterations has been reached
- or the largest change in values caused by an iteration is less than the maximum change
You can control these values using Options–>Formulas.
This calculation process is slow because:
- The calculation is restricted to using a single core/cpu (single-threaded calculation)
- The formulas are calculated repeatedly.
Detecting Unintended Circular References.
If ‘Enable Iterative Calculation’ is checked and you create a circular reference then Excel does not tell you that you have created a circular reference.
If ‘Enable Iterative Calculation’ is NOT checked and you enter formula that creates a circular reference:
In Automatic Calculation Mode Excel shows this message:
In Manual Calculation Mode no message is shown, but the next time you press F9 to recalculate the status bar shows a circular reference message:
If the circular reference is on the active sheet the message shows one of the cells in the circular reference loop:
If the circular reference is not on the active sheet then Excel does not tell you where the circular reference is (but if you select any sheet with a circular reference the status bar shows the cell reference).
You can also use Excel’s circular reference tool on the Formulas Tab:
But none of Excel’s built-in tools will always show you where the circular references are or detect all of the circular references.
I recommend Jan Karel Pieterse’s RefTree Analyzer too for detecting circular references (warning: the detection process is slow on large workbooks.)
http://www.jkp-ads.com/RefTreeAnalyser01.asp
Controlling Circular References
A useful technique recommended by Stephen Bullen is to add an IF switch to your circular reference loops so that you can disable them.
A1:=1
B1:=IF(A1<>0,C1,A1)
C1:=0.1*E1
D1:=100
E1:=D1+B1
If A1 is zero and iteration is disabled then Excel will not regard this calculation as a circular reference, so any circular reference detected is probably intentional. Set A1 to 1 and enable iteration to request Excel to solve using iteration.
Note that not all circular calculations converge to a stable solution. Another useful piece of advice from Stephen Bullen is to test the calculation in manual calculation with the number of iterations set to 1. Pressing F9 will single-step the calculation so you can watch the behaviour and see if you have genuinely converged to the correct solution.
Removing Intended Circular References
Because circular references are slow and difficult to check it is a good idea to remove them wherever possible. Most circular references in financial models can be replaced by simple non-circular formulas.
Unrolling cash flow Interest calculations
You can calculate interest compounded a number of times on a cash balance amount using circular references:
Total cash = Total Cash * (1+interest%)
Or you can unroll the calculation into steps:
Interest accumulated once per period:
Total Cash=Cash before Interest *(1+Interest%)
Interest compounded N times per period:
Total Cash=Cash before Interest *((1+Interest%)^N)
Fee calculated as % of final cost including Fee
Using circular references:
Fee=Fee% * Total Cost
Total Cost=Costs Excluding Fee + Fee
A little algebra rearranges this to avoid the circular reference:
Fee=Fee%*Costs excluding fee / (1-Fee%)
Total Cost=Costs excluding fee + Fee
Tax deductible as % of Profit after Tax
This is an example of circular references from John Walkenbach’s Excel Bible. A company decides to give a % of their after tax profits to charity. But the charity donation is tax deductible, so the charity donation and the tax and the after tax profit are all inter-related.
Using circular references:
Gross Profit=Revenue-Expenses
Profit Before Tax=Gross Profit – Charity Donation
Tax= Profit Before Tax * Tax%
Charity= Profit after Tax * Charity%
Profit after Tax=Profit Before Tax – Tax
Some algebra rearranges this to avoid the circular references:
Gross Profit=Revenue-Expenses
Profit Before Tax=Gross Profit – Charity Donation
Tax= Profit Before Tax * Tax%
Charity= Profit after Tax * Charity%
Profit after Tax=Gross Profit / ((1/1-Tax%) + Charity%)
Conclusion
- Circular References should be avoided wherever possible
- Most Financial circular references can be eliminated
Excel and UDF Performance Stuff 