Excel - Circular References

In this tutorial, you'll learn how and why circular references come up in Excel and what to do about them when you see them. By breakingintowal... "Financial Modeling Training And Career Resources For Aspiring Investment Bankers"
What are circular references?
They crop up when a cell's INPUT depends on a cell's OUTPUT - meaning that Excel runs in an endless loop!
Most common example: Interest expense on debt.
Question: How do you calculate interest expense, anyway?
Should you use the beginning balance each year, the ending balance, or the average balance?
The beginning balance is OK... but you have a problem if you use the
ending or average balance.
PROBLEM: Then, the interest expense depends on how much debt is
repaid in a given year...
...but the amount of debt repaid in a given year also depends on
the interest expense!
So Excel doesn't know what to do and can never calculate the
number.
Why bother calculating interest this way?
Mostly to be more accurate - better to use the average debt balance over the course of the year because that's closer to what the company actually pays.
How do you get around this calculation problem?
Easiest solution: Just check "Enable Iterative Calculations" under the Options menu (Formulas) (Alt + T + O on PC or CMD + , on Mac)
Better Solution: Build in the option to use the average debt balance or the beginning debt balance.
Some groups / firms / industries won't even accept financial models that include circular references - so if you do it this way, you can remove circular references more easily later on.
To build in this option, create an input cell that only allows a 1 or 0.
Then, in the interest expense formulas, use the average debt balance if that input cell is set to "1" and use the beginning debt balance if it's set to "0" and circular references are therefore disabled.
You can check this by looking for the "Calculate" label in the bottom-left window of Excel. It should be displayed if circular references are enabled, but it should NOT be there if circular references are disabled.
MENTIONED RESOURCES
youtube-breakin...

[@sevenstar7305]

Thankyou so much. This video was of great help, I was struck there for days about what to do of circular references. This really helped.

[@financialmodeling]

Thanks for watching!

[@ajay150675]

Thanks. That worked like a charm

[@SnowNska686]

well done video. I especially liked the reference to the J Crew LBO model

[@financialmodeling]

Thanks for watching!

[@janakadissanayake9350]

How can we use a macro to rectify this?

[@financialmodeling]

You cannot. All macros can do is change how calculations with circular references are estimated, but they can't "resolve" something where A depends on B and B depends on A... it makes no logical sense.

[@edtse98]

For the IF function switch to work does iterative calculations need to be enabled?

[@financialmodeling]

It should be enabled in any Excel file that could potentially have circular references.

[@jamesjiang7819]

could you please explain the 1 and 0 for circular reference switch? Why it showed as Yes and No? Thank you.

[@financialmodeling]

Custom number formatting in Excel. Press Ctrl + 1 and go to Number and then Custom to see it.

[@reynardonainggolan1298]

Awesome explanation. So, we don't need to use Macro

[@financialmodeling]

Macros can be useful in Excel, but circular references can be dangerous when combined with macros, VBA, sensitivities, etc., so we recommend avoiding circular references whenever possible.

[@reynardonainggolan1298]

@@financialmodeling thanks for the reply. What do you mean dangerous? It is being less transparent?

[@financialmodeling]

@@reynardonainggolan1298 Macros do not work properly when there are circular references. Circular references also greatly slow down sensitivity tables and other formulas/calculations that repeatedly refresh.