How to manage circularity in a financial model

Circular models will not calculate. Therefore if you have a circularity in your model, there are three main options.

Option 1: Switch on iterative calculations.

You'll find this under Options / Formulas. What we're doing here is asking Excel to continue to calculate until the values converge on a solution. We tend to avoid this option.

There are a few reasons for this:

• Excel will warn you the first time you introduce a circularity. If you introduce a second circularity, you will not receive any warning. But each additional circularity will slow down your model.
• Using iterative calculation to resolve a circularity requires a convergent solution. This is not always the case, and so you can have a model that does not converge on a stable output. Every time you calculate the model, you get different results.
• Activating iterative calculations happens at the application level, meaning that all open workbooks will run in iterative mode.
• You have to keep activating iterative calculations every time you open the model.

Option 2: Find an algebraic solution.

This is sometimes possible, but not always. If possible, this is the preferred option, as it will remove the circularity from Excel.

Option 3: Create a "copy/paste" macro that breaks the circularity.

This is the option we will look at to break the circularity in our model.

There is a fourth option which is to create a User Defined Function. Ed Bodmer has written about this and has many excellent resources on this website which I highly recommend. You'll find his work at https://edbodmer.com/