Circular Reference Warning: "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells."

Ever encountered the below issue and wondered how to fix the problem?

When building financial models, circular references pop up regularly. Especially in a project finance-related context, a deep circularity will most certainly occur at a certain point when trying to model a sculpted debt financing structure in accordance with a target DSCR.

What is a circular reference?

A circular reference is caused by an interdependency of multiple variables when Excel tries to solve different formulas. In the case of a sculpted debt financing structure in accordance with a target DSCR, the principal amount, which is sized on a DSCR target, will influence the interest expense, which could influence the tax amount and therefore change CFADS which will again have an effect on the principal amount. You will have a circular dependency between different variables.

But how to fix the problem?

Below you can see how Excel will show you that you have a circular reference within your financial model using blue arrows.

First, we will discuss how to fix the issue in a non-best-practice way. Although this way is certainly not recommended when using financial modeling best practices, it is still often used in the project finance market - and even recommended on the official website of Microsoft. We want you to know that there is a quick & dirty fix, but we will also show you how to break a circularity properly using a VBA macro.

Go to File ⇒ Options ⇒ Formulas ⇒ Tick the box "Enable iterative calculation"

Once you enable iterative calculations and choose a sufficient number of maximum iterations, Excel should be able to do a number of sufficient reiterations to calculate the proper outcomes for the debt structure.

So why should you not use the integrated iterative calculation feature of Excel?

Complex financial models will have more than one circularity. Once you have a sufficient amount of different circular dependencies, your Excel will likely crash or even come up with inconsistent calculation outcomes.

Make sure to uncheck the Enable iterative calculation check box before you continue - this will ensure that Excel pops up with the warning box that you integrated a circular reference within your financial model.

Implementing a macro in VBA to break a circular reference

To understand how to implement a macro that breaks a circularity, look at the short line of code required within the VBA interface.

Step-by-step implementation of macro that breaks a circularity in VBA:

1. Name all required ranges within the worksheets
   
   1. Debt_principal_delta: The first named range is a delta between the actual debt principal line, which flows into the live case of the financial model, and the calculated debt principal, which does not flow into the live model anymore as it would cause a circularity.
   2. Debt_principal_paste: The second named range is the actual debt in the live model - it is a hardcoded value that cannot cause a circularity anymore.
   3. Debt_principal_copy: The last named range is the debt calculated based on a target DSCR - this is the line that Excel will copy and then paste as hardcodes in the Debt_principal_paste range. To avoid the circular reference, this line will not be linked into the integrated project finance model but will only be picked up in the VBA macro.
2. Once all the required ranges are named within the workbook, you can open the VBA interface - the shortcut to open the interface is ALT + F11. You just have to make sure that the developer is enabled within your ribbon.
3. Open a Module in the VBA interface to start writing your code.
4. Write a Sub statement and call it DebtPrincipal() in accordance with the code line above - using an apostrophe ' you can also give the macro a short explanation so that other users can easily understand what you have done.
5. Implement a Do Until & Loop line around the copy & paste line. Behind "Do Until", you need to tell Excel that you want this macro to keep solving until the Debt_principal_delta range is zero. We want Excel to keep solving the macro until the difference between the calculated & actual debt is zero. We basically outsourced the iterative process into a macro.
6. Implement the copy & paste line
   1. Implement Range("Debt_principal_paste").Value - this line tells Excel to only adjust the values of the line; no formatting or formulas are picked up.
   2. Tell Excel to take the hardcoded values from the copy range to paste them into the paste range by setting the first argument equal to the copy values: = Range("Debt_principal_copy").Value
   3. The full line should look like this: Range("Debt_principal_paste").Value = Range("Debt_principal_copy").Value
7. Don't forget to put a Loop at the end of the Do Until argument. Excel will continue this iterative copy & paste process until both ranges sum up to the same amount.
8. Assign the macro within your spreadsheet to a button and execute it.

How to build an advanced financial model for renewable energy investments?

Do you want to learn how to implement such handy VBA macros within a financial model created explicitly for renewable energy investments? Then check out the Advanced Renewable Energy Financial Modeling course.