Circular references are the biggest challenge in financial modelling. Here’s a summary of the different types and common ways of dealing with them.
What is a circular reference?
A circular reference is created when a cell looks at itself, either directly or indirectly. I like to group circular references into three different types:
Type 1: unintentional errors (usually easily solved);
Type 2: logic errors (usually related to timing); or
Type 3: formula errors (when there is nothing intrinsically wrong with a formula, but a cell refers to itself, for example A = B + C, when B = A x D).
Methods of “solving” circular references
Excel will simply not calculate when a model shows “circular reference” on the status bar. Modellers generally approach this problem in three different ways, only one of which I recommend:
i) Switching the calculation method to iteration
Some modellers will switch the calculation method to iteration to “solve” circular references. Depending on the iteration settings, Excel will then attempt to find a solution by recalculating the model up to 100 times or until the iterative change is less than a small number. This approach will usually produce the correct solution for Type 3 circular references but can give bizarre results for Type 1 and 2 circulars. Using the iterative calculation option is dangerous since all future circular references are accepted which hugely increases the scope for error.
Don’t use this method: it is risky, accepts all circular references without warning and the circular argument remains.
ii) Writing a copy paste iterative macro
In some business areas it is accepted practice for modellers to write copy paste iterative macros to “solve” Type 2 and 3 circular references. Copy paste iterative macros isolate and calculate individual circular references but need to be recalculated frequently. Models containing copy paste iterative macros may look impressive but are often incomprehensible black boxes: they may include interdependent macros and are unable to give instant results for a range of sensitivities.
Don’t use this method: it is inflexible, time consuming and the circular argument remains.
iii) Solving the problem
The two methods above merely short circuit the circular issue and create their own problems. My advice is to question why a circular reference is there in the first place. I often see and hear long explanations justifying circular references. But trust me, they simply do not need to be there, even Type 3 circulars.
My three tips for removing circular references are:
- proper training to give a clear understanding of how a model fits together and why circular references need never be included;
- a clear head to follow the logic of a model and to understand timing errors; and
- basic mathematical skills to be able to develop Type 3 formulae to reach a solution.
It is often hard to get to the root of a circular reference, but this is a key skill of financial modelling. If you do have a knotty problem, then please feel free to get in touch. Solving circular references is my favourite occupation (sad, I know) but I love the challenge and have not been defeated in over 30 years of modelling.