hours

minutes

seconds

Major BIWS Course Changes Coming on Nov 2, 2020

Learn More

Circular Reference Excel – How to Find and Fix Them [Tutorial Video] (10:47)

You’ll learn about circular references in Excel formulas in this lesson, including why they come up, how to deal with them properly, and why you should generally avoid them in financial models.



For Aspiring Investment Bankers Only:

Circular Reference Excel - How to Find and Fix Them [Tutorial Video] (10:47)

Claim Your FREE Discounted Cash Flow (DCF) Video Tutorial Series

Master Financial Modeling As It Is Performed In Real Life, With Our Simple 3-Step Method

FREE INSTANT ACCESS

Circular Reference Excel: What Is It?

circular reference in Excel is a case where the input of a cell depends on the output of that same cell. Here’s a simple example:

Circular Reference Excel - Example

The contents of cell Q14 depend on the contents of cell R14 and… cell Q14.

The input of Q14 depends on its output – what’s already there – which creates a circular reference.

We know the circular reference is there because the bottom-left part of the screen now reads “Calculate”:

Circular Reference Excel - Calculate Message

If that does not appear correctly for you in Excel, or you just get a simple error message, go to the Options menu (Alt, T, O on PC or ⌘ + , on Mac), Formulas, and make sure your settings look like this:

Circular Calculations in Options Menu

When this happens – when the input of a cell depends on its output – Excel gets “confused” about what to do, and it has to start using approximations to make the calculations. For example, here’s what happens if we enter the number 2 into cell R14:

Circular Calculation - Iterations

Excel “loops” through this circular reference 100 times – per the settings in the screenshot above – to calculate the “answer,” which is 200 (since 100 * 2 = 200).

A circular reference could be direct – as it is here, where the cell’s output directly flows into the cell’s input – or indirect.

For an example of an indirect circular reference, let’s say that Q14 = R14 + S14, and that S14 = Q14 + Q13:

Indirect Circular Reference

Cell S14 depends on what’s in Q14, but Q14 depends on what’s in S14.

In real life, circular references in financial models occur most frequently with Interest Income and Interest Expense – specifically, when you calculate those by using average Cash or Debt balances rather than beginning-of-period balances.

A company’s Interest Income depends on its Cash balance, but its Cash balance changes over the year.

So, if you use the Average Cash balance (i.e., =AVERAGE(Beginning Cash, Ending Cash)), then the Interest Income is linked to both of those.

BUT the Ending Cash balance also depends on the Interest Income! More interest earned means the company’s Ending Cash Balance will be higher.

The same goes for Interest Expense and the Average Debt Balance during the year, but in the opposite direction (lower Interest Expense means more ability to repay Debt, which, in turn, means lower Interest Expense).

Why You Should Avoid Them

You should avoid circular references whenever possible because they make models and spreadsheets unstable and difficult to modify.

If you try to edit a model where many rows or columns are linked to circular references, for example, deleting a single row or column could cause cascading #REF! errors because of the iterations required to complete a circular calculation.

However, you will encounter circular references “out in the wild” in other peoples’ models, so you need to know how to deal with them.

How to Find Circular References

If you go back to the Options menu, Formulas, and you un-check “Use Iterative Calculations” Excel will display an error message and blue arrows that show all the circular references on your current sheet, along with the exact cells at the bottom of the screen:

How to Find Circular References

You can then go into these cells and change the formulas to remove the circular references.

If your file or spreadsheet has dozens or hundreds of circular references, then it may be more difficult to do this because there will be blue arrows all over.

In that case, you will have to go line-by-line and cell-by-cell to fix these. There isn’t really a “more efficient” way to do it because deleting all the circular formulas manually will create even more problems.

Excel Circular Reference Workaround: How to Avoid Them

In financial models, the best way to avoid circular references is to build in a “switch” that lets the user toggle between Average Balances and Beginning Balances when calculating items like Interest Income and Interest Expense.

In practice, the added “accuracy” from using Average Balances is so small that it’s irrelevant in nearly all cases.

We recommend the following setup as a workaround to this problem:

Circular References - Average Balance Switch

Then, whenever we calculate Interest Income or Interest Expense in the model, we use an IF statement to check the value in this named cell (1 or 0) and then use the Average Balances or Beginning Balance:

Interest Income - Circular Switch

Setting this named cell to 0 will force all formulas to use the Beginning Cash or Debt Balances, meaning that there will be no circular references, and the model should remain stable.

We follow this practice in the examples and exercises in our Excel & Financial Modeling Fundamentals course.

Master Excel & Financial Modeling

Learn Excel, valuation, and transaction modeling from the ground up - including stock pitches, case studies, deal and investment recommendations, and more.

LEARN MORE