Circular references arise in an Excel financial model when a first cell feeds into the result of a second cell, but the first cell also depends on the result of the second. Both cells depend on each other. A circular reference results.
Manufacturing a circular reference in a financial model
It’s easy to create circular references by mistake in a model. In case you haven’t worked with circular references before, here is a very simple exercise that sees you create a circular reference. Open a new spreadsheet. In say A2 type “=A3”. Now in cell A3 type “=A2”. Ordinarily an error message will pop up in Excel. If you wish, you can download this Excel spreadsheet: circular references, which contains an example of a circular reference.
Dealing with circular reference error messages
You can click on “OK” to make the circular reference error go away, but when you’ve created a circular reference by mistake what you should also do is click “undo” in your spreadsheet. If you have created the circular reference by accident, most likely your financial model will stop delivering the results you expect it should. You need to go back a step and find a different way of solving the problem.
Circular references in financial modelling
Circular references can often arise around debt and cash balances in financial modelling. For example, cash flow in the model is going to to depend on interest income on cash balances. But cash balances are going to depend on cash flow for the year. Hence a circular reference. To break the circular reference in this case, what we could do is set the model up so that interest income is a function of last year’s rather than this year’s cash balances. That means interest income is going to be modelled slightly less accurately, but for many models interest income is a small component of cash flow. If you wanted more accurate modelling what you could do is turn your annual model into a monthly model. That would reduce the size of any error.
Financial modelling: using deliberate circular references
So far the message has been: “circular references are bad”. You need to go back a step and find a way around them. However, advanced financial modellers know that you can actually work with deliberate circular references in a model. Using deliberate circular references in a financial model comes with a serious health warning, but first let’s make sure you know how to work with a deliberate circular reference.
Financial modelling with deliberate circular references: an example
In this example, we’re going to pretend that we want interest income to be a function of the average of last year’s and this year’s cash balances. With this year’s cash flow and cash balance a function of this year’s interest income, and with interest income a function of this year’s cash balance, a circular reference is going to result. Previously we suggested breaking the circular reference by making interest income a function of last year’s cash balance, but it results in slightly less accurate modelling of interest income. Now we’ve decided that it’s important that we model interest income more accurately. As a trade off, we’re going to create and live with a deliberate circular reference.
Financial modelling with iteration “on”
This Excel spreadsheet: circular references, contains the example above. To get the financial model working with a deliberate circular reference, you need to turn iteration “on”. In Excel 2003 go “Tools”, “Options”, “Calculation” and turn Iteration on by marking the check box next to “Iteration”. In Excel 2007 go the Office button (top LHS) “Excel Options”, “Formulas” and tick “Enable iterative calculation”. In Excel 2010 you can achieve the same thing from the “File” menu: “Options”, “Formulas”, and tick “Enable iterative calculation”.
Some thoughts about iteration
With the deliberate circular reference, and iteration on, the model’s results are no longer garbage. You can check the results manually if you like (e.g. on a calculator or in another spreadsheet). The value for interest income really is the average of the opening and closing cash balances. But cash balances are a function of interest income and the circular reference remains. So what’s happening? Iteration is like a turbo-charged version of Excel’s “Goal Seek” function. What iteration is doing is very quickly trying lots of different values in the model, and finding something that works.
Iteration – the health warning for financial modelling
We’re not necessarily expecting you to agree with us, but our advice is not to use iteration. Our advice is not to work with deliberate circular references in your model (although we know the advanced financial modellers are not going to listen to us). What’s the problem with deliberate circular references? The problem occurs when you have a deliberate circular reference in your model (e.g. around debt or cash balances) and you’ve turned iteration on, and then you create a second circular reference in your model by accident without realising it. With iteration on, no circular reference warning will pop up. You could go on modelling for hours before you realise that you’ve created that second circular reference by accident and your model is spitting out garbage (that’s the voice of bitter experience). For that reason, our advice is to remove circular references from your model (but we know the advanced financial modellers out there aren’t going to listen to us!).
Deliberate circular references + accidental circular reference: tracking the error in a financial model
If you are working in a financial model that has a deliberate circular reference, and you eventually work out that there may be another accidental circular reference in your model, here’s how to track it. Save the financial model as a new version. Find the place in the model where you have the deliberate circular references. Delete those out of the model completely. Turn iteration off. Close down all spreadsheets. Open up the new spreadsheet without the deliberate circular references. With iteration turned off, if there’s an accidental circular reference in your financial model, Excel’s circular reference warning should now alert you to its presence.
Financial modelling course tips: circular references
You can download and view some accompanying notes here:
Here are our course tips for working with circular references in a financial model:
- Don’t do it! Undo any circular references in a model. Find another way around the problem. If you leave them there it’s a recipe for unhappiness (see the financial modelling health warning above).
- If you do decide to work with a deliberate circular reference, turning iteration on will help your model deliver results. To be safe what you should really do in a structured model build up, is add that deliberate circular reference in and turn iteration on at the very last step of your modelling. If you can do most of your modelling without the circular reference, and without iteration on, then Excel’s circular reference warning will help you should you create a circular reference by mistake.
- If you do have a model containing a deliberate circular reference, never ever work with iteration turned on. When working with the model, take a copy of the circular reference and paste it to one side. Turn iteration off and delete out the circular references. With iteration turned off you will now get your regular warnings should you create an accidental circular reference. As the very last step of your model build, paste the deliberate circular reference back in and turn iteration on.
- Write a note for the poor ignorant mug inheriting your model who doesn’t know about deliberate circular references and doesn’t expect models to contain them. It’s not hard but it’s very charitable: “There is a deliberate circular reference at line…”. You will likely save some poor soul a lot of anxiety.
About this online training material
You’re looking at free online material on Excel functions extracted from our financial modelling course training.