A common problem in modelling tasks is handling the circularity in calculating interest in profit and loss and cash flow models.  The more interest earned, the greater the cash balance, leading to a higher interest, and so on.  Many people deal with this by building in circular references into the calculations, and applying brute force via iteration.  At one level, this is a perfectly valid solution, but it causes some other issues.  It can have an impact on model calculation speed and mask the presence of other problematic circular references.

This module canvasses alternative approaches to this task, demonstrating a common button-based macro-driven solution  that can temporarily implement circularity until a stable solution is found, then replace the formulas with the hard-wired values.Whilst it removes the circularity as a general rule, the user must remember to press the button or execute the macro when any changes occur.  This causes issues with scenario analysis but is a risk factor if the update is overlooked.

The approach to resolving these calculations with non-circular formulas is demonstrated, covering both interest on borrowing and cash at bank, and catering to the transition to or from overdraft to cash at bank.