Understanding Circular References

What is a circular reference? #

A circular reference refers to a specific formula construction in spreadsheets like Microsoft Excel and Google Sheets that occurs when a formula in a cell refers back to that same cell or refers to other cells that depend on the original cell. This creates a loop in the formula that cannot be immediately resolved. What Is Circular Reference in Excel?

When an Excel formula refers back to its own cell, either directly or indirectly, it creates a circular reference.

There are two types of circular references, direct and indirect:

• Direct circular references occur when a formula in a cell refers to its own cell. For example, you put the formula B1 + B2 in the cell B2, the formula is unable to calculate a result. This type of circular reference is a simple formula construction error and should always be fixed.
• Indirect circular references occur when a formula in a cell refers to a cell that is calculated based on the original cell. For example, you put the formula B1 + B2 in cell B3, and the formula in cell B2 is 1 + B3. This type of circular reference can be trickier to find and debug, sometimes can be resolved by using iterative calculations, often can be eliminated by creating a new formula using algebra, and sometimes is unavoidable.

I've spent countless hours working through circular references; let's dive into those options for handling circular references a bit more to help make it easier for you to figure out what to do.

Instead of counting sheep, I fall asleep by working through Excel circular references.

— taylor (@tdavidson) October 27, 2017

How to find a circular reference #

Microsoft Excel will identify a circular reference by a warning message popup that says:

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.

Once you click ok, then a few things can happen, namely:

• Excel may display a zero, or the last calculated value, and it may stop recalculating the cell with the circular reference. Meaning, if you ignore the circular reference, you cannot trust the calculations because it will stop recalculating that cell and other cells that draw from that cell.
• Excel may show the warning message again if you continue to create new formulas referencing the original cell with a circular reference
• If you create additional formulas with circular references, Excel may not display the warning message again.
• The error message may continue to appear in other workbooks if a workbook with a circular reference is open.

Excel has tools to help you track down the source of a circular reference to help you review and debug the formulas. The status bar in the bottom will report the cell reference where the circular is located, green arrows should appear on the screen to show you what cells the formula is referencing, and you can use trace precedents to trace through the formulas to figure out what's happening. From there, you can figure out if you want to change the formula or use iterative calculations.

Google Sheets will identify a circular reference with an error message in the cell, saying:

Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings.

Tracking down the source of circular references in Google Sheets is a bit tricker, as it does not have the same tools to identify the source of the circular or trace precedents and dependents as Excel, so you'll have to unravel the formula yourself.

Of course, those warning messages won't appear if you have iterative calculations turned on, so let's discuss that next.

Using iterative calculations #

Both Microsoft Excel and Google Sheets can use iterative calculations to evaluate these types of formulas. This means that the spreadsheet program will attempt to calculate the formula multiple times, using an estimate for the final result each time, until the difference between the estimate and the actual result is small enough to be considered accurate.

In Microsoft Excel, you can turn on iterative calculations by going through the menu:

File > Preferences > Calculation > Enable iterative calculation (PC) or Use iterative calculation (Mac), check the box

In Google Sheets, you can turn on iterative calculations by going through the menu:

File > Settings > Calculation > Iterative Calculation (select on)

In both programs you will be presented with a couple options to define the threshold and the max number of iterations. You can use them to define the parameters for how the program uses iterations, although I find in most cases you won't need to modify those settings.

To understand how spreadsheet programs use iterative calculations, essentially the spreadsheet is attempting to calculate the formula, then running the calculations again, and again, etc., until eventually it (a) reaches the max number of iterations in the settings, or (b) reaches a solution to the formula.

Circular references can be useful in some cases because they can simplify the formulas needed to solve some problems. There are good kinds of circular reference, if used appropriately.

However, circular references can also cause problems if they are not used correctly. If the formula does not converge on a stable result, the spreadsheet program may continue to iterate indefinitely, causing the program to crash or slow down significantly (remember the spreadsheet is doing the calculations many times to find the answer, and if the result of that calculation is leveraged in other calculations, it can make the spreadsheet work significantly slower). Additionally, circular references can be difficult to troubleshoot because the formula depends on itself, making it harder to understand how the final result was calculated.

In situations where circular references are not able to converge on a stable result, and rewriting the formulas into algebra are not possible, then we can use tools like Solver to help manually identify solutions, or leverage more advanced techniques like linear programming.

Rewriting formulas using algebra #

In many cases, circular references can be solved by rewriting the formulas to use algebraic techniques. This is why using circulars are often considered to be lazy or ignorant ways to create formulas.

Let's go through a specific example. Circulars often come up in building cap tables because of the nature of what we are often trying to calculate, and a particular way I often see them is when calculating share prices or option pools.

A common requirement is to assume an option pool of a certain size of the postmoney capitalization. Let's assume that we want 10% of the postmoney capitalization to be in an option pool. One way that people commonly first attempt to do this is to calculate the option pool by multiplying the option pool percentage by the number of existing shares. So if there are 10,000,000 shares currently, we would calculate 10,000,000 * 10% = 1,000,000 shares, resulting in 11,000,000 shares in total. But, 1,000,000/11,000,000 is 9.09%, not 10%, because we did not account for the dilutive impact of the new shares.

shares prior to round + shares issued to new investors + new options created in the roun = total fully diluted shares after the round

We could build the formulas to calculate the number of new options by multiplying the total fully diluted shares after the round * 10%, and turn on iterative calculations. And that would work (in my opinion, totally fine). Or we could calculate the new options as:

( shares prior to round + shares issued to new investors ) * ( 1 / ( 1 - 10%) - 1 )

Using that formula leveraging simple algebra, there's no circular reference involved, and thus no need for iterative calculations. And our goal in building models is to use this approach as much as possible, as detailed by the FAST Standard and other codifications of model building best practices.

Learning to live with circulars #

In some cases, though, trying to eliminate all circulars can be a fool's errand, or may create undesired usability issues for model users.

In my latest cap table model, I use circulars instead of algebra to make the concepts easier to understand and the math easier to follow. Earlier iterations used algebra as much as possible, but that created (1) problems with stability when making complicated edits and additions, and (2) problems with comprehension by users. Replacing the algebra with circulars makes the model easier to understand, and is still industry-compliant with how funds, law firms, and founders build and use cap tables for financing rounds.

Meaning, instead of using the algebra method above, I use the circular reference construction. I know that many may view that as wrong, but I think it depends on your vantage point and end goal. From an expert model building perspective, yes, I agree it's not the best way, but from a "how do I make this easy for thousands of people that don't want to understand the algebra" perspective, I think it makes the most sense.

In the Cap Table and Exit Waterfall Tool, I use a dropdown to select to use formulas using circular references or not. That's a stylistic choice, so that I can distribute the model with circulars turned off to eliminate the circular warning message when people open the file, and then use a flag to tell people when to select the formulas that use iterations so that people can then turn on iterations proactively.

Switching from using circulars to manual adjustments #

I'll highlight another example of circulars, this one instead from my series of template models for venture capital funds. Management fees for funds are often calculated quarterly as a percentage of the total committed capital. Since the total committed capital of the closed-end fund is a fixed number and does not change over the life of the fund, calculating fees is usually straightforward:

annual assumed percentage / 4 * the total committed capital

We can then use that to budget fees over time and budget capital calls and cashflows so that we call the entire fund over time, and total called capital will equal total committed capital without any issues. Even changing the percentage charged per year (a common requirement) is simple to do.

In some cases fund agreements will charge management fees based on assets under management, meaning that we calculate the management fee each quarter as:

value of the total capital currently invested at the end of each period * the annual assume percentage / 4

Since that value of invested capital changes every period, and since the value of invested capital depends on how much of the committed capital we can actually invest after paying expenses (including the management fee), that creates a problem for a model builder. One way is to build the formulas and just turn on iterative calculations. Usually that will work without issues, but in some cases the spreadsheet program can fail to find a single, stable solution, often because either the computer or the web browser (in the case of Google Sheets) is not powerful enough to find a stable solution, or there are multiple potential answers so the model will find different answers every time it recalculates. ^[1]

If iterative calculations do not work, then we either:

• Add a linear programming solution to the spreadsheet so that it creates constraints to help the spreadsheet software identify the right solution, or
• Change the calcs to remove the circular reference, which then means that it requires manual adjustment to get called capital close to committed capital

Linear programming solutions can be difficult for users to explain and maintain, so instead, let's talk about how to change the calculation method and the trade-off it entails.

Starting with invested capital #

In my models I typically start with the fund size (total committed capital) as a key input, and everything flows from that assumption, figuring out an investment and called capital schedule based on that fixed number. But to use assets under management, the cleanest way without using iterations is to start from an assumption of how much is invested, and then calculate up to the total committed capital. Different approach to the same goal.

I don't use that method in my template venture capital models because (1) committed capital is more common than assets under management, (2) iterative calculations usually work, and (3) the alternative approach is usually more difficult from someone to contextualize and input their assumptions. Most people think about their funds in terms of the fund size, and then use assumptions around expenses to figure out how much they have to invest, and it's easier to just assume fund size rather than alter multiple assumptions around ivnested capital and expenses to get to the fund size one wants to target. Cleanest math, but harder for practical use for most cases, in my opinion.

Manually adjusting invested capital #

Another approach is to estimate the expenses using committed capital, and then manually adjust up the invested capital amount (either using manual iterations or Goal Seek) to get called capital to equal committed capital. This manual adjustment allows for a fund manager to increase their budget to get them to equal, replacing a spreadsheet auto calc with a human adjustment to deal with the practical issue with circulars.

While this works, it is also not particularly elegant and will require a manager to manually update this after changing any assumption regarding expenses or return profile, thus it is easy to make a mistake.

Solving with algebra #

For years I said that the algebra for calculating management fees using assets under management was too hard, but in mid-December 2024 I solved it with a new methodology.

On the Forecast sheet there is a subcalculation routine that calculates assets under management as a percentage of invested capital given the capital deployment strategy and investment strategy (expectations of writeoffs and exits). The key is to separate out the forecast of management fees from the fees calculated using committed capital and the fees calculated using assets under management, do the algebra for the capital deployment and investment strategy to calculate the assets under management given $1 of invested capital, and then use the management fees incurred under the assets under management period on $1 of invested capital in the input for estimated total invested capital.

It's still doing the same thing committed capital - expenses - management fees + recycled capital but now it's using a lot more subcalculations for management fees to make it work. This now allows a fund manager to select management fees to use committed capital at any point in the fund life, and the model will adjust, without requiring circulars.

The option to use circulars still exists as a checkbox, the hope is that it no longer has to be used.

This method will work for forecasts, but for manual inputs of investment strategy or actuals tracking it will not work, at least not at this time.

People often forget that spreadsheets, in the context of modeling businesses, are better as tools for analysis than a finished, completed set of answers. The key for you in understanding how to address circular references is to understand the goals of your model, how you need to use it, and what you need to decide from it.