This page provides an introduction to methods that can resolve circular references in project finance models. There are alternative ways of solving the problem including using the iteration button, using the copy and paste method and creating a user defined function.
Once you move to more advanced issues in project finance including sculpting, re-financing, alternative debt sizing, balloon payments, equity bridge loans, debt service reserve accounts, cash sweeps and other complications, I can assure you that circular references will arise in project finance models. And these circular references will generally make a mess of your analysis. But if you use either a copy and paste macro or the iteration button in excel, you cannot say that your model is flexible (no goal seek), transparent (how do all the macros work) or stable (the disasters that can happen with iteration buttons).
The first 90% of making a project finance model is pretty easy. You develop time lines, detailed sheets that may count every pencil that you buy for the project and every hour of maintenance outage. But then, at least if you are making your project finance model to evaluate alternative debt structures, you hit a wall. The wall is created by circular references. If you use common methods (the iteration button or the copy and paste macro) you have destroyed your model. My position is that the only way to keep your model flexible (so you can make good scenario analyses and goal seeks) and to keep your model transparent (so users can see what is going on) and to make it accurate (test all the equations that are difficult) is to make a user defined function.
The file below includes UDF functions to resolve all circular references. You can use this file to see how to evaluate a number of structuring issues associated with debt sizing, debt funding, debt repayment and interest rates. The methods in this file are described in detail elsewhere in webpages of this section. You can download the file by pressing the button.
A couple of ways that parallel model works is demonstrated below. The first graph demonstrates that by pressing a couple of buttons and moving spinner boxes up and down, you can evaluate a whole bunch of structuring elements. This would be just about impossible without the parallel model. You would have to create a really big page of copy and paste results and then wait a long time for each element.
Philosophy of Circular References
I have discussed the philosophy of circular references over the years with participants in my courses and others. One man insisted that there is no such thing as circular references in the real world and there should not be circular references in a financial model of the real world. There are some films that address the issue of circular references, the most famous of which is Groundhog Day with Bill Murray. It is possible to argue that these are fiction. In politics, it is easier to argue that there are real circular references ad recent events have demonstrated the world does not always progress to some kind of more democratic society. Instead, the world can regress to something a bit like politics in the 1930’s. When arguing with your significant other there are probably a whole lot of circular references that are impossible to break.
To explain circular references in a project finance model, consider a case where the woman below is asking to make a loan from your for a really expensive Massaratti. She wants a loan for a million euro. You say ok, but she has to pay you fees of 50%. The lady says fine, but now she needs a loan of 1.5 million euro. You say ok, but she needs to pay you fees of 750 thousand. She says fine, but she needs a loan of 1.750 million. You say ok but your fee goes up. This silly example demonstrates the problem of circular references. Excel does not deal with these circular references well because the calculations must go around and around.
In project finance, unless you fix just about everything including the amount of debt, the amount of equity, the DSRA and other debt related items, a circular reference will invariably arise. To really solve the circular references, you need to understand a lot more than a few excel tricks. In particular, you need to fully understand the sculpting formulas and you need to not force things when mathematical formulas can be used.
Four Solutions to Circular References
This and the next few pages demonstrate how to resolve circular references through using user-defined functions rather than non-transparent and circular resolution clumsy copy and past macros, goal seeks or solver methods. For a series of circular reference problems, the benefits and problems of various different circular reference resolution methods are illustrated beginning with the iteration button, moving to the goal seek method and finally to two different circular reference methods that use functions.
The first circular reference resolution method that involves pressing the iteration button is demonstrated with files that demonstrate how things can go wrong from the un-stability of your model. The file shows when the iteration button can be reasonable and how you can fix things when things go wrong. The second circular reference resolution method in which you create a copy and paste macro that copies from COMPUTED to FIXED, COMPUTED to FIXED and sets the calculation formula to the fixed item is pretty simple. The important thing is to understand where the circularity comes from and minimise the copy and paste macros. The third circular reference resolution attempts to solve circular references using algebra. This circular method can result in long and tedious formulas but can also be elegant. In some situations like for IDC with an annual model or for simple fees, this can be the best approach. The fourth circular reference method applies user defined functions with a structured approach that can solve any circular reference problem. This is the method that is most documented and it involves re-programming equations in excel that result in the dreaded blue arrows.
Say that somebody was really lazy and used the iteration button. Your task is to fix the circular references. Here are some suggestions:
First, fix easy things with algebra
If the circular reference is coming from a simple fee calculation or from a contingency calculation, just change the calculation to a formula using some simple algebra as shown below.
Total = Total x Contingency + All Else
Total – Total x Contingency = All Else
Total x (1- Contingency) = All Else
Total = All Else / (1 – Contingency)
If you have an annual model, you can use the following formulas to resolve the IDC problem with average balance. In the equations below, ob stands for opening balance of the debt. The last equation is the equation you can use for IDC in an annual model.
- draws = cost x debt pct / (1-int rate/2 x debt pct – fee pct x debt pct)
- new debt = total funding – equity
- new debt = construction + idc – equity
- idc = ob * rate + new debt * rate/2
- idc = ob * rate + (construction + idc – equity ) * rate/2
- idc – idc * rate/2 = ob * rate + (construction + equity ) * rate/2
- idc * (1 – rate/2) = ob * rate + (construction + equity ) * rate/2
- idc = (ob * rate + (construction + equity ) * rate/2)/(1-rate/2)
You can download a file that uses this method to solve the interest during construction problem in an annual model by clicking on the button below.
Second, make sure the closing balance is NOT part of the interest expense calculation
Look around for the model for the interest expense or interest income calculations. If the calculation uses the closing balance, change the calculation. Make the interest on the opening balance which implicitly assumes that the repayment etc. occurs at the END of the period. If you are worried about this assumption change the timing of the model. For example, change an annual model to a quarterly model, at least for the debt schedule.
Third, in PF Model make sure there is a summary sources and uses statement with IDC, Fees and DSRA funding during construction
The IDC, fees and DSRA cannot be fixed with a simple copy and paste macro. To fix the IDC first use the clumsy copy and paste method. This involves the following steps:
First make a copy and paste near the sources and uses statement. Name ranges and copy the COMPUTED TO FIXED with paste special. Do this with recording a macro. In this section I explain how you can resolve a couple of circular references that arise in corporate models. The most classic circular reference comes from interest expense and interest income that is affected by financing over the course of the year.
Note: please do not make your macro password protected as if it is something fancy. In fact it ruins your model and destroys the ability to efficiently make scenario analysis.
Fourth, Change the Copy and Past Macros to User Defined Functions
Making a user-defined function is the second-best alternative after the algebraic option. For some resolving some circular reference problems like the classic circular reference from interest expense and interest income, the process is relatively simple. In these cases the blue lines and the circular reference are all a single column and the item in question does not depend on the prior year. For the IDC, fees and DSRA funding, the problem is that IDC defines project cost and you need to work through the entire construction period to find the IDC. This implies that a loop is necessary.
To make the mechanics of user-defined functions easier, a file named “Circular Template” is included here. This file has a lot of the generic programming that you will need to solve the circular reference problem where loops are necessary. So, if you want to build a user-defined function with a loop, first download the file. Next, change the name of Read Array per the instructions and start defining period by period variables as well as accumulated variables. In the example below, the equity is funded before debt during the construction period and a minimum function is necessary to understand when the equity funding will be complete and the debt funding will begin.
Sad Situation that People Still Want Copy and Paste Macros
It is very sad for me, but a lot of people are not interested in making a UDF to solve the circular reference problem. While I am strongly opposed to making copy and paste macros, I have included a video below where I go through how to solve the problem with this method. If you want to get straight to the point you can go skip to minute number 7 in the video below.
In this video if you go to about minute number 9, you can see how the make a copy and paste macro in the context of taxes and sculpting. I am reluctant to do this. These copy and paste macros are total B.S. and mess-up your model.
Example of Project Finance Model with Parallel Model to Resolve Circular References
The model that you can download by clicking on the button below resolves circular references without the iteration button or copy and paste macros. This makes the model faster, more transparent, more accurate and much more flexible. I have been working on this for a long time and I am going to post a template model that makes it easy to add to your model that has circular references. Please do not think this is difficult to do as I will be adding a lot of examples and videos that demonstrate you can easily add a page to your model and get rid of your copy and pasted values. You can add up to 30 different debt issues; you can use all kinds of different debt sizing, debt funding, debt repayment and interest rate structures. You can put in balloon payments, you can sculpt where changes in the DSRA are in the numerator or denominator of the sculpting formula.
I am continue to perfect this approach so it is easy to implement and can handle more possible of debt and taxes. You can see my progress if you have downloaded the google drive. The location of the files on the google drive is demonstrated in the screenshot below.
The screenshots below illustrate a few concepts about what the parallel model concept can do. The first screenshot demonstrates the parallel model page that you add to your model. This has a model that can be entirely independent of your model and used to test the accuracy of your model (the A in FAST). Data from the parallel model like the CFADS, total funding and DSRA flows can be connected to your model. This will eliminate the circular references in your model and make your model more transparent (T in FAST). By solving the circular references your model will be much more flexible and fast (the F in FAST). Finally, the parallel model outputs are structured so you can see all of the key cash flows of during and after construction. Making the parallel model in the two screenshots is done with a template and you do not have to re-enter the stuff.
The screenshot below illustrates some of the inputs that you put in the parallel model. Please note that all of these inputs come from somewhere in your model. More importantly, many of the inputs that you may not care about like withholding taxes on debt issues or the percent of EBITDA cap on tax can be left out. It should only take you a couple minutes to fill in the inputs. Note that some of the inputs are scalar variables with single inputs while other variables are time series variables that some people simply call time variables.
The next screenshots demonstrate some of the things that you can do with the parallel model concept that you could hardly imagine if you were using a copy and paste routine. The examples do not show that you can use a goal seek when you change anything (i.e. you can find the required price to give you an IRR with different structures. The first screenshots illustrate the types of funding analysis that you can do. In the first example there is equity first funding.
The second example shows what happens when you click the EBL switch button. In this case the is funded by a loan with an interest rate of 2%. Note that even with a short construction period of 26 months, the equity IRR increases from 9.71% to 10.66%. There is no copy and paste routine to run, there is no time to wait.
The third screenshot of the summary page shows the case where a part of the funding is from equity up-front and a part is funded using a pro-rata funding. This causes a lot of circular reference, but you can use the spinner button and change the equity up-front percent. You can immediately see what happens to the equity IRR.
The next screenshots demonstrate how the parallel model can be used to make your model flexible in presenting results during the repayment periods. The graph shows cash flow and debt service. In the first demonstration a fixed amount of debt is used from the option. The parallel model allows you use different assumptions — fixed debt, debt from debt to capital, debt from DSCR or debt from minimum criteria — without pressing circular reference buttons.
The next screenshot demonstrates that you can change the repayment from sculpting a repayment schedule input. In this case, you can see the effect on the DSCR and the equity IRR from different assumptions. Again all of this stuff can be incorporated in your model by adding a parallel model page and using the template.
The third picture of the results of the model illustrates what happens when you change the debt sizing method to DSCR.
The next screenshot shows the effect of using the DSRA changes in the DSCR for sculpting. The DSRA changes could be classified as part of the numerator or a deduction in the denominator. If you tried this with a copy and paste routine you would end up with a real mess. The parallel model can apply different DSRA techniques by applying some mathematical formulas rather than forcing things. If you look carefully at the screenshot you can see that DSRA deposits in debt service is checked, meaning that the DSRA changes are included in the denominator of the DSCR for sculpting purposes.
The final screenshot demonstrates the effect of a balloon payment. If a balloon payment is used, there will be more debt for the given DSCR. This will have a different effect if equity up-front is used or the DSCR is used to finance the debt. There are painful circular references from the balloon payments as with the other factors.