Circular references pose a particular challenge in some Excel® models; these are situations in which a formula’s result needs to be known before the formula itself can be evaluated!! As covered in my book Financial Modelling in Practice in detail, whilst Excel can resolve convergent circular references (when placed on iterative recalculation mode), sometimes it is better to try to avoid their use at all. This posting shows one way of doing that in interest calculations.One problem with circular references is where there may be two in a model. Whilst a single and convergent circular reference (as it typical in interest and many other calculations, when correctly implemented) presents no real problem (apart from those associated with the slow-down in speed due to the many iterations), the main problem with multiple references is that one of them may be wither divergent or floating (i.e. not convergent and not divergent). An simple way to test for divergent references is to radically increase the number of iterations that Excel allows (such as using 10,000 in place of the default 100), whereas for floating references it is useful to change the number of iterations (say from 100 to 101) and see if the results change.
Concerning interest calculations, is one basis these of formulae such as:
Then this can be rearranged as:
The last part i.e. Rate/(1-Rate/2) can be considered to be a “modified” interest rate, and the denominator represents a correction for compounded interest compared to the case where interest would be applied only to the starting balance and average periodic inflow.
There are other cases where such algebraic reformulation of linear situations can remove circularities without any change in results; in some cases (such as when the interest rate itself depends on the interim cash flow, as would happen with multiple debt facilities using different rates), then things become more complex and often circularities cannot be removed without simplifying the model and changing the resultant calculations. But removing circularities where this is possible (or where this does not compromise accuracy) is generally recommended.