If you are a financial modeller who uses iteration to calculate your models, then please read on.
I am always coming across financial modellers who insist that switching Excel’s calculation method to iteration is necessary for their financial models and their particular business area. However, I don’t trust any model which has iteration switched on.
What is iteration?
Iteration is a calculation setting, within Excel’s Options, which accepts and attempts to calculate all circular references.
Why would anyone switch to iteration?
Modellers switch their calculation method to iteration when they cannot solve their circular references.
What are the implications of switching to iteration?
If iteration is switched on, Excel will:
- attempt to calculate all circular references, usually 100 times, and
- accept future circular references without warning.
Why do circular references occur and can they be solved?
Circular references are caused by poor logic and therefore bring a very high risk of inaccuracies into a model.
There are three reasons for circular references:
- A simple formula error (easy to solve)
- An error in logic or timing (very common and solvable if sufficient logical thought is employed)
- Formula circulars (when, for example, simultaneous equations need to be solved through the use of algebra)
What is wrong with using iteration to solve circular references?
Admittedly, switching on iteration can calculate some circular references accurately (but only the occasional ones that fall into category 3, formula circulars above). However, having iteration switched on opens the flood gates for all circular references, which may well have an impact on the accuracy of the model. Many modelling mistakes can go unnoticed.
Is it hard to solve circular references?
Yes, it can be hard to solve circular references. However, they can all be solved by applying logic.
Is the problem of switching to iteration widespread?
I would love to know the percentage of models which are switched to iteration; it seems to be very common in certain geographical locations and business areas.
Why do modellers make excuses for needing iteration because of certain circular references?
I cannot answer this question, except to say that it is time for modellers to stop making excuses, to start using logic and to build circular reference-free models.
And yes, this applies to every model, yours too. Remember, circular references are warning you that you have a problem in your model, not that the model is particularly advanced. Don’t increase the risk of inaccurate results by masking your errors.
And next month I will explain why I don’t approve of copy paste iterative macros either.