Formulas discussed: Algebra, VBA (Copy, Paste, Select, Range, If..Then..End If, For..Next
The workbook used throughout this post is available here.
When building a model, it is not unusual to, either accidentally or deliberately, build a circular reference. A circular reference is where the result of a formula depends on itself. This could be as simple as B5 = B4+B5, or a complicated loop where cell A references B, which references C, which references D, which references B and E, while E references A, as in the diagram below:
Excel allows you to solve circular references by undertaking an iterative approach, where it repeatedly calculates the model until a steady state is reached. This can be turned on through the Options menu if using Windows, or Preferences if using a Mac. The number of iterations it undertakes can also be controlled. This sounds wonderful, so what’s the problem?
The two major problems are that:
1. your model will now be a lot slower (it will fully calculate a number of times for each single recalculation); and
2. it may not necessarily work. Unless you keep a keen eye on the status bar at the bottom of your window, you may not realise either that there is a circular reference or that it hasn’t calculated (if it says “Calculate” straight after it’s already run a calculation, then chances are it isn’t being fully solved).
These then lead to other problems such as any model audit opinion being qualified and you not knowing if you’ve inadvertently introduced another circular reference somewhere else in the model.
Download the workbook to work through this example. Section 1b) “Containing a circular reference” has a small example of a common circular problem. In this example, Interest paid on a loan is dependent on the average balance of the loan, Tax is dependent on the amount of interest paid, and the average loan balance is dependent on the amount of cash available for repayment, which is itself a function of Interest and Tax payments.
What you’ll see is, if circular reference solving is turned off, an error message should display telling you there is a circular reference, and hopefully producing the arrows to tell you where it is. Keep in mind that it will only display one circular reference error at a time (there are four in this spreadsheet, one for each time period). If solving circulars is turned on, then the model should solve, hopefully with an interest bill of 129 in the first period.
A solution – Macros
A commonly used solution to solving circular references is to create a macro in VBA to control the iteration process. At its simplest, this involves breaking the circular reference at a single point through copying the calculated values in one row into another row, and then having the rest of the model refer to that new row (which has been pasted as values, and not as a formula).
In workbook example 1b) “Circular reference removed by a macro” the break is made at Interest (row 71). Row 71 is copied into row 72, and it is row 72 that flows through the rest of the calculations (rows 50 and 60). Row 73 compares the difference between rows 71 and 72 to see if a steady state has been reached yet. The macro controlling the iteration is “BreakCircularReference.” Before creating the macro it is important to give certain areas of your model defined names, as if new rows or columns are inserted or deleted, VBA will not adjust the cell references, but defined names are automatically updated. In this example “CB_calc” is the calculated interest cells, “CB_pasteval” is the first interest value cell, and “CB_diff_check” is the difference checking cell.
Note that for this particular macro to work, you must have Excel’s calculation method set to Automatic rather than Manual (this can also be controlled through code within the macro, but was left out of this example to simplify its construction for macro novices).
At its simplest, the macro copies one row, pastes it to another location and repeats. This is the middle section of the BreakCircularReference subroutine, from “For” down to “Next” (excluding the If..Then..End If section.
For counter = 1 To countmax Application.Goto Reference:="CB_calc" Selection.Copy Application.Goto Reference:="CB_pasteval" Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Next counter
The If..Then..End If is put in to check whether a solution has been reached, as if it has, there is no point continuing to iterate.
If (Range("CB_diff_check").Value < difference_threshold) Then Exit Sub End If
The last line of code displays an error message if the model has not solved after the 100 iterations.
Macro pros and cons
Using a macro gives the model developer the ability to better control the circular reference solving process, through displaying error messages, better notifying a model user that a circular exists, and through controlling the error threshold and number of loops. However, model accuracy is now dependent on the end user running the macro at appropriate times, or realising that they have to do this in the first place.
This risk could be mitigated through having a cell on each worksheet (somewhere around A1) that prominently tells the user (through size and colour) that the macro must be run. The developer can also set the macro to run automatically every time the model calculates through calling the macro through the Workbooks declarations, but this can slow things down for the model user as the macro will run all the time. Coding must also be modified so that an infinite loop is not inadvertently created.
Another solution - Maths (oh no!)
If the circular reference is not too complicated, then it could be solved by high school algebra, if you can remember much of it. In the provided example, this can be done. An important thing to remember is to get your signs right - in this example, we have modelled interest, tax and principal repayments all as negative numbers, so that the subtotal and total rows (such as profit before tax, and cash available) are simple sums.
Utilising the same financial statements as in example 1b) “Containing a circular reference,” produces this formula flow (ignoring the rate assumptions).
We used the following notation in the solution:
E = EBIT I = Interest payment N = Profit before Tax T = Tax payment C = Operating cashflow (pre-tax) P = Principal repayment Bo = Opening debt balance Bc = Closing debt balance ri = Interest rate rt = Tax rate rp = The amount of available cash that is dedicated to principal repayments
While in some ways this is quite an elegant solution (no circulars, no macros) it has one glaring problem - it looks really complex. While each of the starting formulas were quite simple, the ultimate formula for interest is reasonably complicated (row 103). The accuracy of the model is now subject to the accuracy of the developer’s algebra and I’m sure we all know how easy it is to get our pluses and minuses mixed around (and I did it here when I was typing it out in Word’s equation editor, even after I got it right the first time around with pen and paper). And any errors here will likely go uncorrected - this is exactly the sort of formula a user or reviewer will decide they can’t be bothered trying to figure out, or may make a mistake with themselves. Even an auditor is likely to have a number of questions. Having said that, more people are familiar with algebra than with VBA, and so may follow this line of solution better than a macro solution.
For simple circular references, such as a simple bank account where interest is dependent on the average balance, with no other inflows, this may be the best solution (although a case where interest is calculated on the average balance is usually an error in and of itself, but that’s a debate for another time).
Unsolvable circular references
There are circular references that are essentially unsolveable through either an iterative approach (whether Excel’s built in solving capability or through macros) or through a mathematical approach. A common occurrence of this in financial models, particularly in project finance, would be where a Loan Life Cover Ratio (“LLCR”) drives a cash sweep (if a ratio is breached, all available cash is used to reduce debt), as in example 2 “LLCR and cash sweeps” in the attached workbook.
In this example, if the LLCR in any time period is below the sweep threshold, then it uses cashflow to reduce debt. However, as future periods will now have lower interest payments, and hence higher tax payments, the cash available for debt repayments is altered, which alters the NPV of future cashflow, and ultimately, the LLCR that triggered the sweep in the first place. Sometimes, such as in period 2, the LLCR is boosted by enough that the sweep should no longer occur, the LLCR reverts back to its original value, which should cause a sweep, and the cycle repeats. The model will constantly flick between two binary states, neither of which is stable, or which a macro can solve. You can see this in action by manually changing cell J155 between 1 and 0 and observing how the LLCR goes above and below the threshold level of 1.30x.
This event will not always occur when using an LLCR sweep mechanism (it doesn’t happen in the first period for example) but its potential occurrence must be taken into consideration. So how do you fix it?
The simple answer is that you usually don’t. In a simple example such as this, with only four time periods and with simple financial statements, you could essentially run the model for each time period to see whether a sweep should occur, then have another instance of the model running further down the sheet with the sweep on (if applicable) and then have that instance be used for later time periods. However, this is obviously ugly, prone to errors and hard to follow. The simplest solution in this example is to probably ignore the LLCR sweep mechanism and, in the model at least, rely solely on a backward-looking ratio such as a debt service cover ratio or interest cover ratio. For other instances, such as a forward-looking debt service reserve account balance, it may be best to approximate its behaviour, such as by looking at the current period’s debt service instead of future periods, or modifying the way interest is calculated on the account balance. Whilst not 100% accurate or exactly how your loan agreement is documented, it would usually be sufficient for modelling purposes.
What I hope you take away from this article is that there is no single best solution to dealing with circular references. The first step should always be to think about whether circular logic truly exists (such as interest not really being a function of an average balance), but after this, the solution will depend on the particular problem you’re dealing with, your personal skills, and who your audience is. My personal default position is to write a macro, but that is more to do with a personal bias and thinking that coding is cool, rather than it always ultimately being the best solution.
Have any questions? Post them in the comments or email me at [email protected]. We’ll also take suggestions for any future topics you’d like this blog to cover.