I have lost count of the number of times that I have been told that financial models should not include circular references, except for “special situations” caused by the complexity of certain models.
But think again:
- Why would you need a circular reference to calculate interest?
- Why would you need a circular reference to calculate reserve accounts?
- Why would you need a circular reference to calculate debt sculpting?
In fact, why would you need a circular reference for anything?
Don’t believe the myth that switching the calculation method to iteration solves circular references. This risky method accepts all circular references without warning and Excel then attempts to solve them – with varying degrees of failure and success depending on the nature of the problems.
Don’t believe the myth that writing a copy paste iterative macro solves a circular reference successfully either. This merely freezes numbers for a temporary fix and makes models inflexible.
The only way to remove circular references is to realise that they should never be included in the first place and to solve them. Circular references simply do not exist in the real world and should not exist in any form in financial models either. Admittedly, solving circular references can at times be a challenge, but with logic and good mathematics I promise that this is achievable.