Suite of Circular Reference Solutions

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. If you have a unique problem with a whole lot of unconventional eqations I suggest that the copy and paste method should be used. If you have a project 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. 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. 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


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.