This page introduces some ideas about solving circular references. I first list the detailed articles in other pages associated with the various issues. Then I include some materials that were on my old website. There are a lot of file references that I have not yet uploaded to this website. If you want access to these files, send me an e-mail at edwardbodmer@gmail.com and request the library.
The first 90% of making a project finance model is easy. You develop time lines, detailed sheets that may count every pencil that you buy for the project and every 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. 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.
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.
Playlist of Videos Discussing Circular References
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.
Fixing Circular References in Financial Models
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)
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.. n 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. In an annual model interest can be computed on the average balance. Developing a user defined function is
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.
Circular Template.xlsm
Read Array.xlsm
General Principles of User-Defined Functions
To begin the process of solving circular references by functions, I demonstrate some of the principles of user defined functions. I have tried to read books on VBA and I have used a lot of discussion boards. I find these very difficult or sometimes to easy to be useful. I don’t care about making range’s dimensioned and using complex syntax. All I want to do is make a loop and compute some numbers that can be outuput to excel. This is the idea of my introduction. So, here are some general principles about functions:
1. The name of the function must be defined with and = sign. For example if you make a function named BONJOUR with Function Bonjour, then somewhere in the function you must have the statement BONJOUR = something (for example, BONJOUR = “Bonjour Monde”.
Function bonjour
bonjour = “Bonjour Monde”
end function
2. You cannot use RANGES from the excel file. For example, you cannot use RANGE(“A1”) in your function. Instead, you must read the variable into excel. If you want to put the date into your BONJOUR function and the date is in excel, you must read the date into your function. To do this you would enter something like FUNCTION BONJOUR(Todaydate).
3. You should understand the difference between SCALAR and ARRAY variables. If you read a series of inputs into the function, they are array variables such as the series of capital expenditures, or EBITDA over time. If you read a single variable like the debt to capital ratio, this is a scalar variable that must not have an index like debt_to_capital(i).
This circular reference page demonstrates how to resolve circular references through using functions rather than non-transparent and circular resolution clumsy copy and past macros, goal seeks or solver methods.
Complex Circular Reference Problems in Project Finance and a “Sort-of” Template:
Introduction to PDF Method for Solving Circular References
Years ago I was teaching a project finance modelling course and we distributed pre-course surveys to people attending. One of the common responses was the desire to have project finance models without the dreadful and un-transparent copy and paste macros. I tried algebra. I tried a reverse logic method. Then I somehow stumbled on the user function method. I tried on more and more difficult problems. The user-defined to solve circular references can always work. When you use this UDF method, you can run goal seeks, solver, data tables, dynamic graphs and all of the other excel tools that are good for bidding analysis. It can be a bit difficult but it when it works you feel like you have really accomplished something.
http://www.youtube.com/embed/GRKoOAoLWTM http://www.youtube.com/embed/_DhvONwJtTM
Making Long-Circular Reference Problems Less Difficult
The problem with solving circular references with user-defined functions is that writing the functions can be painful. This compares to copy and paste macros that are quite simple. My hope now is to make the process less painful through using a template and explaining common problems. My suggestions for now are not to make the user defined functions shorter. Instead, I wonder if you can apply transparency, flexibility, structuring and accuracy principles to the UDF’s. So, to make things more transparent, add a whole bunch of output lines as shown below. Second, to make things more flexible, try to create a macro that has a log of different possibilities. Third, to make things accurate, include checks in the outputs. Finally, be careful and structure the coding in the UDF.
The transparency, auditability and the flexibility of the UDF process depends a lot on the structure of your excel analysis. The more structured, transparent and flexible your model, the easier the UDF function will be to implement.
The exceprt below demonstrates how the output from a user-defined function can be comprehensive, structured and transparent. It can then me be used to audit the model.
Understanding Arrays and Scalars
Sometimes when working with a function you get the horrible #VALUE output. When you get this #VALUE in the function output, it is generally the result of a problem with an array variable. To understand the description, when I use the word array, I mean a variable that has multiple values and may be dimensioned as an array variable in the code (e.g. EBITDA(1000). When I mention the word scalar variable I mean a variable that only has one value (e.g. debt_percent). The excerpt below illustrates differences between array variables and scalar variables. Array variable can be read in from excel as variables with more than one value.
Now, back to problems that can lead to #VALUE:
1. You entered a scalar when you should have entered a scalar [When you enter data in a model, make sure to understand what is an entire row — an array — versus a single number — a scalar.]
2. You have a dimensioned variable that you are treating like a scalar [When programming the UDF, the array variables must have a dimension. Make sure all the variables have an index (e.g. EBITDA(i))]
3. You have a loop that starts as a negative number [If you make a loop around 1 to 10 but put the array as i-2, then the variable will be undefined].
4. You have a circular reference in the function [If you are referring to a variable that has a circular reference, you can get a value].
5. Have not defined variables
The excerpt below shows what happens if you have made a mistake that leads to the value problem — it is often a problem with an array function.
The excerpt below illustrates a problem that can cause the dreaded #VALUE!. If you look carefully you can see that the variable equity_draws in the third row does not have an array. Somehow you have to find this problem in a long program.
The last excerpt shows how to check the program so that you can find the problem that is causing the #VALUE! problem. The process involves stopping the function and making sure that you get zeros and not values. After you move the end function to different places, you should be able to isolate the problem.
Applying the Template in Your Project Finance Model:
Step 1: Find the hard-coded row that is used with a copy and paste
If you have a fixed row that is the basis of the copy and paste macro, one way to fix things is to attempt to compute the row with a user-defined function. Typical fixed rows are the total funding needs during the construction period and/or the CFADS during the operation period. You can insert a few lines below the fixed line for total uses of funds by period or alternatively the total CFADS. An example of a model with a fixed line is shown below. Insert about 25 lines below the fixed line.
Step 2: Copy the template function
Download the circular template file above if you need to resolve circular references during the construction period. To read the macro into your file go to an existing macro or make a new blank macro (turn on the record button and then turn it off). Then go to blank macro and copy the contents with ALT F8 etc. (this sheet)
Step 3: Work with only pure cash flow and set the fixed line to the line from the function
Find all of the interest and fees and development fees and set them all to zero. Make sure that you have switches in the model for construction and development (if no development then use no development switch template).
Make sure that you have a line for capital expenditures that includes the costs that do not depend on debt or the total project cost (i.e. not
The switches are the key to being able to read entire lines of data without pressing the F4 a lot.
Compute the difference and make sure the difference is zero.
Use the SHIFT and F3 to find the function names.
Don’t forget the SHIFT, CNTL, ENTER
Check the assumptions
Step 4: Link the hard-coded line to the function output
Step 5: Zero out all interest rates and verify the model
Copy the key assumptions next to the original assumptions. Then zero out all of the interest and fees. The difference between the function results and the computed total sources should be zero.
When you use the function, it is an array function with may rows and columns. Copy the default titles from the function as shown below.
Step 6: Start putting in things that are a function of the total cost
Begin with development fees that can be a percent of total cost or begin with the interest while zeroing out the fees. It is generally best to use an array for the fees.
Start with one tranche and senior
Step 7: Begin working on things that are a function of total project cost and financing
Here I walk through some circular reference examples. If you complete the examples, you can earn a badge in circular reference and receive a circular reference certificate that will get you a very big raise. This circular reference page demonstrates how to resolve circular references through using 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
IDC and Fee Circular References in Annual or Quarterly Model:
Introduction
I generally think that IDC and fees should be computed by assuming that cash flows occur at the end of the period. Thi is consistent with paying suppliers and construction staff at the end of the month (you could even make a model have a 1/2 month if this is consistent with the way money is paid and money is paid). Using the opening balance eliminates many circular references. However, in some cases an annual model can be useful. In this case it is not reasonable to assume that the ending balance is used.
In this set of equations
New construction debt = Total funding – EquityNew debt = Direct Construction + IDC – EquityIDC = Opening Balance * rate + new debt * rate/2IDC = Opening Balance * rate + (construction + IDC – equity ) * rate/2IDC – IDC * rate/2 = Opening Balance * rate + (construction + equity ) * rate/2IDC * (1 – rate/2) = Opening Balance * rate + (construction + equity ) * rate/2
IDC = [Opening Balance * rate + (construction + equity ) * rate/2]/[1-rate/2]
ears ago I was teaching a project finance modelling course and we distributed pre-course surveys to people attending. One of the common responses was the desire to have project finance models without the dreadful and un-transparent copy and paste macros. I tried algebra. I tried a reverse logic method. Then I somehow stumbled on the user
Circular References in Project Finance Model
Videos and Files Explaining How to Solve Circular Reference Problems in Financial Models
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.
Circular Exercise 1 – Fee Exercise.xlsm
circular functions 2.xlsm
Circular Exercise 7 – Fees.xlsm
Circular Exercise 1 – Cash Flow Sweep without Taxes.xlsm
Circular Reference 2 – Cash Sweep.xlsm
Bridge Loan Circular Exercise.xlsm
Circular Exercise 6 – Resolving Funding 1.xlsm
Exercise 26 – Funding Cascade and Circularity.xlsx
Circular Exercise 6 – Resolving Funding 2.xlsm
Circular Reference 2 – Cash Sweep.xlsm Circular Exercise 8 – DSRA.xlsm
IDC and Circularity with Real Estate.xlsm
IDC and Circularity with Real Estate – Copy and Paste.xlsm
IDC and Circularity with Real Estate – Function.xlsm
| Excel Exercise File | Video | Chapter Reference | Page Reference | ||||||
| Circular Resolution Alternatives Simple Fee Case | Circular Reference 1: Fee Exercise | https://www.youtube.com/watch?v=DR7ehE9VgcY | Chapter 37 | 468 | |||||
| Circular Resolution for Cash Sweep in M&A | Circular Reference 2: Cash Sweep in M&A | https://www.youtube.com/watch?v=4nBP9jyoCT4 | Chapter 37 | 470 | |||||
| Circular Resolution for Cash Sweep in M&A with Taxes | Circular Reference 2: Cash Sweep in M&A | https://www.youtube.com/watch?v=rkwbgE6nzxE | Chapter 37 | 475 | |||||
| Circular Resolution for Capitalised Interest and Fees | Circular Reference 3: Capitalised Interest | https://www.youtube.com/watch?v=04pXy2KhL8Y | Chapter 40 | 501 | |||||
| Circular Resolution in Corporate Model | Circular Reference 4: Corporate Model | https://www.youtube.com/watch?v=HWDtzRWQ5cw | Chapter 38 | ||||||
| Circular Resolution in Corporate Model with Target Capital Structure | Circular Reference 5: Corporate Model with Target Cap Structure | https://www.youtube.com/watch?v=su5A0yp8-CE | |||||||
| Circular Resolution in Project Finance Funding with Pro-Rata 1 | Circular Reference 6: Funding in Project Finance 1 | https://www.youtube.com/watch?v=wNmQiudv43U | Chapter 40 | 501 | |||||
| Circular Resolution in Project Finance Funding with Pro-Rata 2 | Circular Reference 6: Funding in Project Finance 2 | https://www.youtube.com/watch?v=pzZCMtVyBdE | Chapter 40 | 503 | |||||
| Circular Resolution in Project Finance Funding with Pro-Rata 3 | Circular Reference 6: Funding in Project Finance 3 | https://www.youtube.com/watch?v=2xWvx-ReFx8 | Chapter 40 | 504 | |||||
| Circular Resolution in Project Finance Funding with Fees | Circular Reference 7: Funding in Project Finance – Fees | https://www.youtube.com/watch?v=Wzj5EmZnQM0 | Chapter 40 | 504 | |||||
| Circular Resolution in Project Finance Funding with Equity First | Circular Reference 6: Funding in Project Finance Equity First | https://www.youtube.com/watch?v=4ZhiVxXl0Ic | Chapter 40 | 505 | |||||
| Circular Resolution in Project Finance Funding DSRA | Circular Reference 8: Funding with DSRA Included | https://www.youtube.com/watch?v=lXGPf-4dkWo | Chapter 43 | 552 | |||||
| Creating Function to Solve Circular Reference | Brazil BNDES Structure | https://www.youtube.com/watch?v=ehCpew9KZ20 | Chapter 40 | 501 | |||||
| DSRA and Funding without Circular Reference | Exericse 101 – DSRA Set-up | https://www.youtube.com/watch?v=coDja2GnkKc | |||||||
| DSRA and Funding with Copy Paste and Data Table | Exericse 101 – DSRA Set-up | https://www.youtube.com/watch?v=RdHjSgTiov0 | |||||||
| DSRA and Funding with Goal Seek, Data Table and Copy Paste Macro | Exericse 101 – DSRA Set-up | https://www.youtube.com/watch?v=5frnM5DV6NM | |||||||
| Real Estate Construction Loan with Copy and Paste Macro | IDC and Circularity in Real Estate with Copy Paste | https://www.youtube.com/watch?v=eOmnx8Dow0g | |||||||
| Real Estate Construction Loan with Function | IDC and Circularity in Real Estate with Function | https://www.youtube.com/watch?v=buGo-1-T7BQ | |||||||
| Resolving IDC and Fees in Construction with a Function | Operating Cash During Construction | https://www.youtube.com/watch?v=xRtiDPMhHJc | |||||||
| Corporate Model with Average Interest, Min Cash | Exercise 1c – Corporate Model – Circular Reference | https://www.youtube.com/watch?v=2WIf30VUCSE | Chapter 12 | 141 | |||||
| Combining Dynamic Goal Seek and Circular Reference | Exercise 1d – Corporate Model – Target Cap Str | https://www.youtube.com/watch?v=vtYnkvyoeaw | Chapter 12 | 141 | |||||
| Simple Fee Function to Resolve Circularity | |||||||||
| Circular Resolution in Sculpting with LLCR without tax | Circular Reference 9: Sculpting and LLCR | Chapter 41 | 521 | ||||||
| Circular Resolution in Sculpting with LLCR with tax | Circular Reference 10: Sculpting and LLCR | Chapter 41 | 532 | ||||||
| Circular Resolution with Sculpting and Taxes | Circular Reference 11: Sculpting and Taxes | Chapter 41 | 532 | ||||||
| Circular Resolution for Sweep and DSRA | Circular Reference 12: Sweep and DSRA | Chapter 43 | 552 | ||||||
| Circular Resolution Level and Annuity | Circular Reference 13: Level and Annuity | Chapter 42 | 541 | ||||||
| Circular Resolution Sculpting and Funding | Circular Reference 14: Sculpting and Funding | Chapter 43 | 552 | ||||||
| Circular Resolution Sculpting and DSRA Interest | Circular Reference 15: Sculpting and DSRA Interest | Chapter 43 | 552 | ||||||
| Sculpting, DSRA and Income Taxes | Exercise 17: Debt Sculpting – Advanced | Chapter 43 | 552 | ||||||
| …………………………………………………………………………………………………………… | … | …………………………………………………………………………………… | … | …………………………………………………………………………………………….. | … | ………………………………………. | … | ………………. |
Circular References in Project Finance Model
Videos Associated with Corporate Circular Reference
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. In an annual model interest can be computed on the average balance. Developing a user defined function is not that difficult to resolve the circular reference because no loop is required. The video and files below illustrate how to solve this circular reference.
Files Associated with Videos
Circular Reference Exercise 3 – Corporate Modelxlsm.xlsm
Exercise 10 – Target Capital Structure with Function.xlsm
Circular Reference Exercise 3 – Corporate Modelxlsm.xlsm
Exercise 4: Resolution of Basic Circular Reference in Project Finance Analysis — Debt Funding with Target Debt to Capital Ratio
Circular Execise 3 – Circular Logic from Fixed Debt to Captial Ratio.xlsm
Circular Exercise with Financials.xlsm
debt sizing.xlsm
Resolution of Circular References in Bank Model
Simple Bank Model with Circular Exercise.xlsm
This model applies the principles in the financial library and solves|the debt funding and the debt sizing problems
Financial Model Function Library.xlsm
Model with Resolved Circularity.xlsm
Project Finance Concepts Advanced.xlsm
Circular Exercise 1 – Cash Flow Sweep without Taxes.xlsm