You don't want to turn of the message unless you are doing this on purpose. Normally circular references are considered an error, but there are a couple of ways to use them deliberately. You get rid of them by starting at the cell noted in the status bar (bottom of screen) and walking the formula back through its references to find the circular. They can slow the financial model down as Excel will always be calculating. Future circular reference errors that are unintended could go unnoticed. I was working on a formula that had an unintended circular referenced. There was no solution, so Excel got stuck in a loop trying to solve the impossible, and my computer slowed down to a snail's pace.

1. How To Allow Circular Reference In Excel Mac
2. How To Turn Off Circular Reference In Excel For Mac Shortcut

Circular references and calculation settings

If you want to work with circular references, the calculation settings of Excel are very important. This page gives you some pointers!

If you leave them in, Excel calculates each cell involved in the circular reference by using the results of the previous iteration. An iteration is a repeated recalculation until a specific numeric condition is met. By default, Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001.

Calculation settings

The first thing that needs to be done if you want to assure your model works, is to turn on iterative computation of the file.

Iteration settings in Excel 2010

It is up to you to decide how many iterations you want Excel to do before it stops, or what precision you need before Excel stops (whichever comes first). As soon as you check the box 'Enable Iterative calculation', Excel will do a calculation of your model. After saving the file, if you open the file again you should no longer get the circular reference warning message.

If you are troubleshooting your calculation, set Maximum Iterations to 1. This gives you the opportunity to step through the calculations one at the time by repeatedly hitting the F9 key.

Which calculation settings apply

I often get this question: I have checked the 'Enable Iterative calculation' box on my file. Why do I still get the circular reference warning? To be able to understand what causes this it is important to know how Excel handles its calculation settings.

Application wide settings

Calculation settings are application-wide. That is, if workbook A needs manual calculation and workbook B needs automatic calculation and you have both workbooks open, Excel's current setting will apply to both workbooks. The same goes for the iterative calculation settings: they apply to all workbooks in your Excel session.

When you save a workbook, whichever calculation setting was applied at that time is saved with the workbook.

First-come first-serve

Excel will apply the calculation settings of the first workbook you open in a session. So if you first open workbook A (which had iteration disabled when it was last saved) and then Workbook B (with iteration enabled when it was last saved), Excel will keep iteration disabled. This explains why you do get the circular reference warning on that workbook.

Warning: When you save your workbook, the calculation settings that are currently in effect are saved with the file. This means that if you have previously set up iterative calculation and the max iterations and max change, these settings may be overwritten with the current settings.

Making sure you have the calculation settings you need

There are several ways to ensure your workbook calculates as expected:

Always open as the first workbook

Well, that one is obvious enough. Of course if your model is used by other people as well, this is not exactly fool-proof. Your users would have to be made aware of this situation, but chances are very high your calculation settings will get overwritten at some point, making your model unreliable. And even if you're the sole user, this is a big risk.

Use a bit of VBA to control calculation settings

A more reliable way to control the calculation settings is by adjusting them when your workbook loads. This means you will have to add macro's to your file, but this is straightforward enough.

I assume the file with the circular references is already open.

Open its ThisWorkbook module by double-clicking on it in the project explorer as shown below:

The Project explorer in the VBA Editor

Paste this code into the code window that opens up and modify the calculations settings so they match what you need.

Now save your file (if you are using Excel 2007 or up, make sure you change the file-type to one that can hold macro's, otherwise the macro code is discarded after you close your file!)

What is a circular reference?

A circular reference is when a cell refers to itself directly or indirectly.

Are circular references bad?

In most cases, a circular reference should and can be avoided with some planning. However, in a complex financial, I found it easier to just use circular references in certain areas.

Circular References in Financial Models

Circular references are used to help calculate cash balances. Let’s walk through two typical cases.

Interest

The cash sitting in the bank generates interest. The interest income is taxed and lowers the net income. More cash -> more interest -> more tax -> lowers net income -> effects cash.

See the example below. To determine the amount of interst, we use an average of the forecasted beginning and ending cash balances. It’s not fair to use just the beginning or the ending cash balances to calculate interest because over the time period that balance will change.

Think about it another way, if you have $100 in the bank at the beginning of the month and $200 at the end earning 2% a month, what should the interst earned be? $2 of $4? Neither, it should be something in the middle because your cash blance grew during the month on its way to $200. So for simplicity of forecasting, we just average the beginning and the end and say $3 ($100+$200)/2 * 2%. It’s not perfect, but it’s a step in the right direction.

Debt

The amount of cash shoftfall determines the borrowing needs which determines the interest expense which determines the amount of debt.

How to enable circular reference

You have to check “Enable Iterative Calculation” in Excel Options -> Formulas -> “Enable iterative calcuation”
Maximum iterations should be 100 (default).

What happens when your circular reference errors out?

This happens pretty often as you’re developing your model because if you pass an error into a circular reference, by definition it won’t be able to solve the equation.

See the example below. All the forumlas are correct, but somewhere in the process there was a mistake made and it threw off all the calculations.

You can resolve this with a commonly used “jumpstart”. All bankers use this, so it’s not some random hack. Jumpstart is a two-part formula you stick into cell where there an error would throw off the ciruclar reference.
The first part is to define a cell called jumpstart and the value will be TRUE or FALSE.
The second part is to change a row of your formulas to incorporate jumpstart. In the example below, we’ve incorporate into the average cash calculation by changing it to =IF(Jumpstart,1,(C42+C40)/2) instead of =(C27+C25)/2

How To Allow Circular Reference In Excel Mac

This means if Jumpstart is TRUE, insert 1, otherwise calculate the average. By inserting the 1, it allows the calculations to work again…by giving the calculation a “jumpstart” with a value instead of an error.

To see Jumpstart in action
1. Change the FALSE next to “See Error” to TRUE and then back to FALSE. This simulates as if an error was made and then corrected.
2. Change the FALSE next to “Jumpstart” to TRUE, this will fix the errors by making the average cash $1.00. Change back to FALSE (to turn off Jumpstart) and it will revert back to normal calculations.

How To Turn Off Circular Reference In Excel For Mac Shortcut

** Modeling Tip – If/Else is a powerful forumla. Learn it and use it.