Excel circular references occur when a formula refers back to its own cell or a cell that directly or indirectly depends on it. Finding and fixing circular references is important because they can cause incorrect or unstable calculations. Here's how you can find a circular reference in Excel: 

1.     Click on the "Formulas" tab in the Excel ribbon.

2.     Click on the "Error Checking" dropdown arrow, then click on "Circular References."

3.     Excel will highlight the cell or cells that contain the circular reference. The cell(s) will have a colored border, and a "Circular Reference" warning message will appear in the status bar at the bottom of the Excel window.

Once you've identified the circular reference, you can then edit your formulas to eliminate the circular reference.

Financial modeling best practice: Using a macro to break a circular reference

You do not necessarily need a macro to break a circular reference in Excel. However, in complex financial models with many interdependent formulas, finding and resolving circular references manually can be time-consuming and prone to error. In such cases, a macro can automate the process of breaking circular references and save time and effort.

Moreover, a macro can be customized to fit specific needs. For example, a macro can be designed to break circular references only in certain parts of a workbook or to break circular references in a specific order to minimize the impact on other formulas.

Overall, while it is possible to break circular references manually, using a macro can streamline the process, reduce errors, and ensure that all circular references are resolved.

How to break a circular reference using a macro?

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.

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.

Stand out of the competition and learn how to build an advanced project finance model for renewable energy investments:

If you're looking to invest in renewable energy, you need a comprehensive financial model dashboard that summarizes all the key investment metrics relevant to your decision-making process.

This financial model dashboard for renewable energy investments is designed to help you make informed investment decisions with ease. It includes a detailed breakdown of the project's capital structure, allowing you to understand the financing mix of the project and the risks associated with each layer of the capital stack.

The dashboard also considers critical investment metrics such as IRR and NPV on both a levered and unlevered basis, giving you a complete picture of the project's profitability.

In addition to these essential investment ratios and multiples, the dashboard details the payback periods, CFADS/EV, Revenue/EV, EV/MWp, and EV/MWh. These metrics are crucial in evaluating the financial viability of the project and determining its potential for generating returns over the long term.

The dashboard also features eye-catching charts that provide a visual representation of the project's cash flow generation and cash on cash returns over its entire asset lifetime. This feature will give you valuable insights into the project's cash flow patterns and help you make informed investment decisions.

With all these critical investment metrics and data points in one place, this financial model dashboard is the ultimate tool for renewable energy investment decision-makers.

How to build a project finance model from scratch?

Do you want to learn how to build a project finance model from scratch? Then check out the Advanced Renewable Energy Financial Modeling course.