This page introduces my preferred approach to resolving circular references using a concept that I call the parallel model. This parallel model concept is one of the most important ideas on this website both in terms of theory and practical implementation in real models. The general idea of the parallel model is to do at least four things. First, you can get around the limitations of excel in terms circular reference problems. Second, it is an auditing tool that allows you to verify project calculations by summarising data with a set of consistent inputs and outputs. Third, management can see results from alternative models in a clear and consistent format. Last and not least, without adding a lot of programming to your model, you can quickly test alternative strategies like re-financing, adding mezzanine debt, changing approaches the DSRA treatment in the sculpting, adding a balloon payment structure, including EBL and shareholder debt financing, sculpting debt with multiple issues, constraining sculpting with the average debt life and other subjects.
The parallel model process involves opening a template sheet and linking a few variables from your model and running a second model in parallel (the linking process should only take a few minutes). The idea of the parallel model is that you can make the calculations in your own project finance model in whatever structure you want. Then you go to your model and find things like interest rates, EBITDA, model flags. These things are put into the parallel model as inputs to the UDF. Finally, you link variables from the parallel model page to items like interest during construction, CFADS and funding needs that are often fixed from copy and paste macros.
History of the Parallel Model Concept
I hope that by browsing through the history that made me arrive at this parallel modelling concept you can understand why I think it is such an important idea for the project finance modelling community. The time line below illustrates some of key events that have led to the UDF method. The timeline demonstrates that many different factors had to be put together to enable this method to be used in an efficient and realistic manner in your models. I did not put in the timeline dates because I of course don’t remember them. But this is not a project that has taken a few months or even a year. I think I must have started this model around the financial crisis in 2008.
Concerns about Circular References
I have been teaching classes in project finance modelling since 1997 (my classes back then were not too good). In preparing for classes, I used to give out a pre-course questionnaire where I asked what students wanted me to focus on. For example, I would ask if participants wanted to create models from A-Z models or whether they wanted to deal with more advanced issues like circular references (illustrated below). A few people asked how to avoid impossible to understand macros for circular references and how to avoid using the iteration button in excel.
I was arrogant and I thought that I could find a better method than the copy and paste method or the very dangerous (geferlish) iteration button. I had a disastrous class using the iteration button with #VALUE!’s and #REF’s that I could not get rid of and decided never to use the iteration button again. I remember whenever we tried to add a scenario analysis or anything a little different the whole model crashed and then we had to start over. I had a lot of nightmares about this and I still get very nervous with the dreaded blue lines.
Initial Efforts to Solve Circular References with Mathematical Formulas and Backwardation
I began to try to solve the problem after reading the comments from my students and my disastrous. First I tried to find mathematical equations that could solve the problem that can be used in corporate modelling and simple things like contingency fees. For things like a contingency payment that is a percent of the cost of the project including the fee is a very simple example of this. But when solving things like IDC, these equations became very long as shown in the excerpt that I still have in the resource library.
I remember trying to solve the commitment fee problem while riding on my brother’s boat on the Amazon (his website) and it was impossible for me. I gave up on the mathematical equations and decided that I had wasted a lot of time. Then I tried a method where you can find equations for the closing balance and then back into the opening balance that some people call backwardation. This method seemed promising at first and then quickly became impossible. Maybe it was best to just use the copy and paste method.
Big Moment of UDF Discovery for Solving Circular References
You have heard stories about remembering exactly you were when big events happen. I remember that I was going to take an accounting exam in a little farm town when I heard about the killing of John Lennon. I remember the hotel were I was when I saw the Fukushima tsunami and I also remember in what hotel I was staying at when I heard the depressing news about Brexit and Donald Trump’s election.
When I was working on a dynamic goal seek where you make a UDF function that automatically computes the goal seek without pressing any menu items, I tried a similar technique for a simple circular reference. And it worked. This was a really big moment in my old age. I remember exactly what couch I was sitting on when the UDF technique with a little iteration button worked. Then I took a shower while I was shaking with excitement. I then tried it with more and more complex applications that included for next loops. And, the UDF method still worked. I dropped the other methods involving mathematical equations and started working on real applications.
Implementing UDF in Project Finance Models
To implement the UDF technique, you have to work backwards across all of the dreaded blue lines in the circular reference. You start by making an equation for the item that causes the circular reference and then add more and more equations. With this you can solve circular references that for example come from IDC that also affect depreciation and therefore taxes and therefore CFADS and therefore sculpting which then affects debt and IDC itself. There can be a lot if inputs and equations for solving the circular reference and you must input them all.
I demonstrated with quite a few models that it is totally possible to do this. But the required inputs and the equations can be difficult as shown in the adjacent screenshot. You can see the inputs that you have to put into your user defined function. These are a lot more inputs than you would have to put in any function in excel. I moan a lot about long formulas being non-transparent. I was a hypocrite to suggest that you would every realistically do this (except Hedieh who did make models like this). If you have to re-write these equations each time you make a model, it is frankly not very realistic.
UDF’s and Advanced Project Finance Issues
These days some of my clients are not very interested in boring old blah blah blah about quickly making a model and computing the IRR’s, DSCR’s, LLCR’s etc. Instead they are interested in things like what is the effect on the investor and the lender from balloon payments, inclusion of changes in the DSRA on sculpting, how to evaluate strategies when there is both a debt to capital constraint and a DSCR construction, how to sculpt multiple debt issues, what is the effect of re-financing and these kind of more advanced issues.
I have included a screenshot of the place where you can find some of the advanced issues on this website. When creating exercises for these models such as for balloon payments or analysis of putting changes in the DSRA in CFADS or debt service, there are a lot of circular references. The UDF technique worked well as a way to make the models flexible (with spinner boxes etc.) and with goal seeks and with simple data tables. So, even if the UDF function was not too realistic in big models, the UDF discovery for me was not a complete waste of time.
Meeting with Hedieh and Template Concept
I tried to be realistic realistic about the prospects for this UDF stuff. I touted the method in videos and I demonstrated the approach in my classes. But nobody seemed very interested. I was a bit depressed but I just think about Churchill’s saying that success is going from one failure to another without lack of enthusiasm. There was one person who was still interested in the concept — Hedieh Kanfard.
She suggested a meeting in Istanbul and worked on the models. We were able to implement part of the concept for the funding circular references in a real model that she was working on. She was even able to copy the code into other models she created and it still worked. She suggested making a template UDF that could be applied in alternative situations. At first I thought about how I do not like template models (I tried them in the past). Then I reluctantly told Hedieh that I would try to make a template UDF.
Hedieh later introduced me to Danny and Kenny who I could tell did not believe the concept. They told me they were trying to develop innovative new project finance modelling methods.
Reading Data from Tables and Making the UDF Concept Manageable
When I started working on the template model suggested by Hedieh I ran into a problem. To make a flexible template you would need to read in a whole lot of different variables and to make it usable, the method would have to deal with many different options including multiple debt issues and therefore be able to read in many variables. But there is a limitation for UDF functions involving the number of variables that can be read in — only 128 characters.
This limitation made it virtually impossible to make a usable template. Then, when sitting at a cafe, I googled the limitation and found someone who suggested using a table for reading in data. This changed everything. Reading a table into a UDF was painful to develop but I think it can make the parallel model realistic to use. I wish I could thank the man who made the suggestion on google.
Structuring a Flexible and Comprehensive Parallel Model
Once the process to easily read information into the UDF was solved, I worked for a long time creating a comprehensive template model that . The UDF can output anything you calculate and this output can be structured consistent with they way project finance models should work. The outputs can work all the way through a model to the equity IRR after EBL, shareholder loans and re-financing. This allows you to verify your model. But more importantly, you can try a whole bunch of advanced things like balloon repayments, multiple re-financing, DSRA moves in CFADS, using a letter of credit in lieu of a funded DSRA, multiple sculpting, mezzanine debt, EBL’s alternative debt sizing and other things without re-doing your model. In terms of verifying your model, at first when I checked the differences between the UDF model and my excel models, many of the mistakes were in the UDF model. But after working very hard and making my mistakes, the parallel model can find mistakes in your model now. That’s why I am arrogantly claiming that this concept represents the future of modelling.
Video Demonstration of Parallel Model (with Beetoven’s 9th)
The video below illustrates how you can implement the parallel model concept in your model in a couple of minutes. I have not made you listen my voice while you see how it works. Instead, you can listen to Beetoven’s Ode to Joy. In subsequent pages you can see how this concept works in a whole bunch of different models and advanced project finance concepts.
Explanation of Parallel Model Features in Associated Web Pages
When I first developed some UDF’s I made the UDF’s with really long lists of inputs that were painful to implement. But the process worked. Then I met with my friend Hedieh and she told me to make a template model. My first reaction was that template models really suck. But then I realised that she was absolutely right. I have tried to convince you that the parallel model/UDF concept is realistic to implement and can be applied to many different circumstances in the web pages associated with this section. My goal is to make you interested enough in the concept to become motivated to be a really advanced and efficient modeller. So here is a demonstration of how easy the process is to make.
First, with your financial model open, open the first file below that you can download by clicking on the box. This file contains the UDF code for making a parallel model. Once you have opened the file, copy the code to your model (in English you can use the ALT, E, M short-cut) and click the button for creating a copy.
To illustrate how this process works, I have included a second file that is a very simple project finance model. As usual, the simplest model is a solar model (although I seen amazingly long and absurd models for simple projects – an impeachable crime). The file that I will use in the example is available for download by clicking on the button below.
- The first exercise with the parallel model involves working with simple annual model with a one period construction duration. This case is used to demonstrate that the parallel model can be applied for case with no circular reference or a case with circular references only for DSRA and fees. In this case, the model is annual model.
- The second exercise with the parallel model involves working with multiple debt issues in the context of a simple model. In this exercise, the parallel model is attached with multiple debt issues in alternative sculpting scenarios. In the first case there is no sculpting. In the second case there is one sculpted issue and multiple non-sculpted issues.
- The third exercise using the parallel model addresses re-financing. Re-financing can be tricky if taxes are combined with sculpting to determine the amount of debt that can be supported at alternative assumed repayment dates.
- The fourth exercise using the parallel model addresses various more complex issues including periodic construction on a monthly basis, semi-annual operations, an EBL, withholding taxes, tax timing, alternative depreciation for financing costs, multiple debt issues with sculpting and other factors.
Overview of Step by Step Process for Applying the Circular Reference Template
To make the process work, you just follow the few steps below:
Step 1: Copy the template sheet into a new sheet of your model. The template has the required inputs that go into the UDF (most of which can be left blank or unchanged) and outputs generated from the UDF. After you have opened both of the models, the first step of copying the parallel model to your project finance model and using the copy check box is illustrated in the first screenshot.
Step 2: Copy the UDF functions related to the parallel model into your model. First go to the copy macro button in the parallel mode (not your model) and the press the copy macro button. After pressing the button, the VBA code will appear in a box. Go to the box an press CNTL A first and then CNTL C. This is illustrated in the first screenshot below. After pressing CNTL A and CNTL C, you should exit the screen so you can paste the code. To do this press the x or the close button on the screen.
Step 3: Once you have copied the code, go back to your model. Find the VBA page with the functions and then copy the functions to a VBA page in your file. Then type any name for a macro in the box shown below. This could be something like TEST or COPYPASTESUCKS. Then, delete anything that excel puts there and make sure you paste the VBA code to the top of the page. More specifically, the OPTION BASE 1 should be at the top of the page. The two screenshots below illustrate the process of copying the UDF from the parallel model to your model.
Step 4: Make a timeline in the new page of your model that contains the UDF. The timeline should include switches for the development, construction and operation period. The periods in the time line (e.g. months during construction) should be consistent with interest calculations in the base model.
Step 4: Go to the input section of the UDF and find the required inputs such as periodic interest rates, EBITDA and DSRA requirements. These inputs should be lined from the financial model. (It is possible that you may have to make a couple of calculations in your model to compute things like effective interest rates on different debt tranches).
Step 5: Enter the function and use the SHIFT, CNTL, ENTER sequence
Step 6: Link various circular references from your model such as funding needs, CFADS, and DSRA to the parallel model
To illustrate how the circular reference model works in general, I have presented a short discussion of the inputs and outputs below along with a discussion of how to
Inputs to the Circular Reference Parallel Model
Inputs to the parallel model are shown in the screenshot below. Most of these inputs could be left alone at the default values.
The second set of inputs shows how to get data into the parallel model.
Model outputs created by the parallel model are shown below. To produce this output page you use the SHIFT, CNTL, ENTER sequence after you enter the function arguments.
You may ask, if it is so good why in the hell would I put this stuff on the website for free when the method is a radical change in the way project finance models work. (Sorry about the arrogance). You can make your complex model, but resolve all of the circular references.
So, here is my marketing concept. If I do not publish the method it will not get any traction and nobody will use the idea. But I also think that if you really want to use the method you need an intensive course that explains how the general concept works; how to create UDF programs for sculpting and tax issues; how to read the template UDF; and, most importantly, how to modify the UDF for your particular circumstance.
Problems with Copy and Paste