Circular references arise naturally in project models.
Examples of typical circular references include:
- The DSRA is funded by a draw down at Commercial Operation Date (COD). The DSRA is sized as equal to the next 6 month’s debt service, which is in turn a function of the total debt amount, which is a function of, inter alia, the size of the DSRA…
- The interest payable during construction is a function of the amount of debt outstanding, which is a function of, inter alia, the amount of interest already paid…
Sometimes circular references can be dealt with by referencing only past cells, which can solve the circular reference all together. If this is possible, it is the optimal solution to a circular reference.
If such a solution is not possible, it is almost always possible to resolve a circular reference by iterating through successive loops until the difference between successive iterations becomes negligible.
Please (please!) do NOT enable iterative calculations in the Excel Option / Formulas settings to do this. It works, but has the downside that an error can iterate through your circular reference and become impossible to resolve, effectively trashing your model.
A better, but more cumbersome, solution is to create a copy-past values macro. If you are not familiar with VBA, follow these steps to do so:
- Copy the offending row
- Paste-values the row immediately below
- Create a named range for each row – e.g. DSRA_Copy and DSRA_Paste
- Create a sum of each row on the left (adjacent to the description of the row)
- Create a named range for each sum – e.g. DSRA_Copy_Sum and DSRA_Paste_Sum
- Record a macro
- Turn on macro recording
- Copy the offending row
- Paste-values the row immediately below
- Turn off macro recording
- Find the macro in the VBA editor (Alt + F11 opens the editor)
- Modify the macro you just created as follows:
Dim i As Integer
For i = 1 To 100
Range(“DSRA_Copy”).Select
Selection.Copy
Range(“DSRA_Paste”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _:=False, Transpose:=False
If Abs(Range(“DSRA_Copy_Sum”).Cells(1, 1)-Range(“DSRA_Copy_Paste”).Cells(1, 1)) <= 1 Then Exit For
Next
You already have the copy-paste syntax from the macro recording. The rest is as follows:
- Dim i as integer tells VBA that i will be a variable of type integer – not a problem in VBA if you leave this line out
- For i = 1 to 100 … Next is a loop which recurs 100 times. I prefer for-next loops to do-while loops because they can’t result in getting stuck in an infinite loop if the circular reference resolution doesn’t tend towards zero (or if I make a stupid error in the code!)
- If Abs(…) is a condition which tells VBA to stop executing the loop if the sums of the rows are the same. I use an absolute value because I don’t know whether the past or copy row will be bigger, and I use 1 as an acceptable degree of difference. You can change that to be bigger or smaller depending on how much precision you require
- I recommend creating another formula next to your circular reference resolution with a condition that the absolute value of the difference between the sums is less than an acceptable difference, with output “Circular reference resolved” or “Circular reference unresolved”, and applying some green and red conditional formatting to it.
- Similarly, it’s good practice to aggregate all circular references checks into a master check at the top of the Working sheet, which should always be visible.
- If you have more than one circular reference to resolve, it is fine to put more than one copy-paste into the for-next loop, and expand the conditions in the if statement using the AND boolean operator.