In this page I suggest alternative solutions to the circular reference problem using a suite of solutions. I do not suggest there is only one ideal solution for one circular reference problem. One of the solution is the old fashioned copy and paste macro where you created a fixed row. If you have a unique problem with a whole lot of unconventional eqations I would begin with the copy and paste macro. If you have a project financed investment with a single debt issue and you only want to solve the funding circular reference (i.e. the IDC, Fees and DSRA) with a UDF, you do not have to implement the large UDF solution with a template model. If you have an LBO model or a corporate model where the circular reference comes about from averaging interest expense, you can use a relatively simple UDF approach that I explain below. But if you have a whole lot of debt issues with things like standby debt, LC fees on un-funded equity, VAT debt and other items, then you may want to implement the comprehensive solution. If you have a big monster model, then I suggest with a goal seek combinded with a copy and paste model then I demonstrate how a user defined function can dramatically improve the speed of the model relative to a copy and paste approach (in all of this discussion I assume that the using the iterative button is not acceptable).
The files below include some examples were we show how the UDF template works and how you can write your own UDF code for more simple cases. The initial file includes the UDF template that we update on a regular basis. Finally, if you are adding re-financing to a model, I suggest an alternative and separate UDF to the large one.
.
.
.
.
.
.
.
.
Copy and Paste Macro
I hope to have an open mind about some things. There are clearly some situations where you should use the old fashioned copy and paste macro. Further, because of the closed minded nature of auditors, you will probably want to show the copy and paste solution next to the UDF solution. The big deal with copy and paste is to use the .VALUE when you refer to a range. I know that some people put the copy and paste solution in a different page, but I suggest you lay out the key areas where the copy and paste will be necessary. In a project finance model, I suggest a table with sources and uses laid out clearly. The screenshot below illustrates what I am talking about:
The file with both the UDF and the copy and paste solution in included in the file attached to the button below.
I have found that some people are very good at the bureacuracy with using the .VALUE and then putting the copy and paste in a separate sheet. But they make a complete mess. People compute circular references on future DSCR tests when sizing debt; they make the whole process very difficult to follow.
.
Extremely Simple Example with UDF
The very simple example that I use is one with fees during construction. I make this example where the fees do not depend on accumulated debt but only on the debt issued in one period. This is cheating and the problem can be solved with simple algebra (Total = Total without fees/(1-fee percent)). But I find this example a good way to illustrate the UDF compared to the copy and paste method.
Goal Seek and Data Table Works
Function fees1(fee_pct, debt_pct, cap_exp)
difference = 999
Count = 1
Do While difference <> 0
Count = Count + 1
last_fees = fees1
total_funding = cap_exp + fees1
debt_draws = total_funding * debt_pct
fees1 = fee_pct * debt_draws
difference = last_fees - fees1
If Count > 100 Then Exit Do
Loop
End Function
Examples where Circular References Do Not Depend on Accumulated Amounts – Corporate Model and LBO Model
The most common problems with circular references in general models are the surplus financing problem and the average interest expense or interest expense problem. In these problems the financing must be re-computed and if taxes depend on financing, then the taxes must be also computed.
.
.
Reading Variables from a Large Block So you Do Not Have to Define Separate Variables in the Function
.
This shows a simple example of reading a block. There is a function that reads in the two lines name A and B. In this case only the data is read in. In our examples the entire line is read in. When you read in the data make sure to put in one name that is defined as an array. Note that you do not have to define the array. Once you read in the array, split it up to the rows. Then you can make some calculations.
Finally, you can put the output in a block. Define the output block with rows and columns. You need both even if there is one row and/or one column. Then, finally define the name of the function as the output. Of course output can be named something else.
.
.
Option Base 1
Option Explicit
Function Marc(Inputs2 As Variant) As Variant
Dim A(300), B(300) As Double
Dim TotalRows, TotalCols, Col, output(3, 3) As Double
TotalRows = Inputs2.Rows.Count
TotalCols = Inputs2.Columns.Count
'MsgBox TotalRows
For Col = 1 To TotalCols
A(Col) = Inputs2(1, Col)
B(Col) = Inputs2(2, Col)
output(1, Col) = A(Col) * 2
output(2, Col) = B(Col) / 2
output(3, Col) = A(Col) * B(Col)
Next Col
Marc = output
End Function
Use of Template with Complex Models
I have spent much more time developing a template that can solve complex project finance problems.
Circularity from Sculpting and Re-financing
Re-financing is after. Re-financing involves sculpting. Re-financing can occur multiple times.
Do the re-financing at the end.
Need a medium number of inputs — the EBITDA, the depreciation, the tax rates, the timing of the re-financing, the initial NOL, the term of the new finncing, the interest rates on the new financing.
Retaining Values from Copy and Paste from Scenario
.
<code>Range("comit_fee_output").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Select Range("comit_fee_output").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Value = Range("comit_fix").Value If debug2 = True Then MsgBox " Copying Commitment Fee to the Sensitivity Page " Range("ACT_FIXED_CIRC").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Select Range("ACT_FIXED_CIRC").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Value = Range("Fixed_Act").Value Range("Def_Int_Start").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Select Range("Def_Int_start").Value = Range("fixed_int_def").Value</code> Range("comit_fee_output").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Select
Range("comit_fee_output").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Value = Range("comit_fix").Value
If debug2 = True Then MsgBox " Copying Commitment Fee to the Sensitivity Page "
Range("ACT_FIXED_CIRC").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Select
Range("ACT_FIXED_CIRC").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Value = Range("Fixed_Act").Value
Range("Def_Int_Start").Range(Cells(Scenario, 1), Cells(Scenario, end_col)).Select
Range("Def_Int_start").Value = Range("fixed_int_def").Value.