Circular+References+in+Corporate+Finance+and+Project+Finance

= This website is now obsolete because of being extinguished by horrible wikispaces. Please go to http://edbodmer.com for a much improved website. =

=||Home|| Book || Featured PF Models || Featured Corp Models || Featured Acq Models || PF Exercises || Corp Exercises || Download to Excel || Scenario Analysis || Tornado Diagrams || Monte Carlo || Flexible Graphs || Real Estate || Acq Exercises || Macros || Reading PDF || Waterfall Graphs || Circular Ref || Depreciation || Financial Ratio || Value Driver || Renewable || Testimony || Pricing Case Studies ||=

This circular reference page demonstrates 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.

= Lesson Set 1: Fixing Circular References in Model =

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 NEVER 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 minimium function is necessary to understand when the equity funcing will be comple and the debt funding will begin.

= = = = = = = Lesson Set 2: 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. For a series of circular reference problems, the benefits and problems of

= Lesson Set 3: 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.

media type="youtube" key="GRKoOAoLWTM" width="560" height="315"media type="youtube" key="_DhvONwJtTM" width="560" height="315" align="right"


 * 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.



= Lesson Set 4: 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

= Lesson Set 4: 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 - Equity New debt = Direct Construction + IDC - Equity IDC = Opening Balance * rate + new debt * rate/2 IDC = Opening Balance * rate + (construction + IDC - equity ) * rate/2 IDC - IDC * rate/2 = Opening Balance * rate + (construction + equity ) * rate/2 IDC * (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= media type="youtube" key="4nBP9jyoCT4" width="560" height="315" align="right" media type="youtube" key="KyVilhuy2zg" width="560" height="315"




 * || Subject ||  || 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 ||
 * || 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.

media type="youtube" key="HWDtzRWQ5cw" width="560" height="315"

=Files Associated with Videos=



=Exercise 1: Simple Circular Reference Logic Resolution -- Fees=







= = = = = = = = = =



= = = = = = = = = = = = = = = = == = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =Exercise 4: Resolution of Basic Circular Reference in Project Finance Analysis -- Debt Funding with Target Debt to Capital Ratio=