One way to learn modelling is to relax during a peaceful evening, pour yourself a glass of wine and work through a model with 30 pages. It is hard to imagine a better evening. The excel files and videos on this approach a selection of completed project finance models that you can work through. By reviewing one or more of the project finance models available below you can understand some of the complex project finance modelling issues and maybe pick-up some ideas. (In my courses I address the more painful subject of reading real models that often have absurdly long equations and bad structuring). An important feature of most of the models is resolution of circular references related to debt funding (interest during construction, fees during construction and DSRA accounts that affect project cost and therefore the debt itself) as well as sculpting (for taxes that depend on interest expense and interest income). With resolution of circular references you can see how different financing features affect required prices in structuring of bids.
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.
Completed Solar Project Finance Models
The files below demonstrate the effects of financing and costs on the required price in a solar project finance model. The files not include some of the detailed tax aspects of solar financing in the U.S. related to partnership and tax equity which are included in a separate file below. The project finance model is not ideal and could be improved, but a lot of people seem to look at this so I have included it as the first model. The solar project finance file below includes functions to resolve circular reference so that bid prices can be evaluated with different financing techniques. I use the file sometimes by examining how bid prices are affected by the DSCR, up-front funding, debt repayment tenure and techniques (sculpting or annuity payment), interest rates and DSRA accounts. All of this can be done on the summary page using the spinner buttons. The file also includes (early) application of the scenario reporter whereby you can create a list of scenarios by changing inputs and putting the output in a separate sheet. I have also added the Cancun Solar model which works through various issues associated with estimating solar resources includes fundamental modelling structure.
If you want to model the complex features of this model that involve solving circular references associated with sculpting and funding, you should go to the ADVANCED STRUCTURING PAGE.
Solar Project Finance with Circular Reference Resolution and Analysis of Yield
Solar Project Finance Model for Mexican Project with Detailed Cost Breakdown
Link to My Youtube Channel Where You Can Look At All of the Different Videos that I have Made
Natural Gas and Coal Thermal Plant Project Finance Models
The file below demonstrates how to incorporate maintenance timing and other issues in a the model of a thermal plant (a natural gas combined cycle plant). The model demonstrates how to incorporate scheduled maintenance after running a given number of hours (e.g. 40,000 hours) and then has maintenance that lasts a certain amount of time. It has (I think) a flexible detailed print macro that you can apply to your models. I suggest looking at the print model and then the associated video that describes how to implement the macro. The third file below includes demonstrates how to evaluate a classic IPP project with risks associated with incentive elements of a PPA tariff. The file show aspects of a four part PPA tariff with risks, debt sizing and functions for resolving circularity. Risks are associated with availability, heat rate variation, O&M expenses and other factors. In addition to solving circular references with functions, the file also includes methods to present sensitivity and scenario analysis. These files are documented in videos that are listed at the bottom of the page.
The last file in the set below demonstrates the modelling of a coal project without functions for circular references. It uses a case study in the label that resulted in very high returns to U.S. investors who really did not take risk.
http://www.youtube.com/embed/fDuNgdTETF4 Gas Plant Example with Print Macro.xlsm Indonesia Gas Plant.xlsm IPP Model.xlsm Quezon Coal Plant Example.xlsm
Solar Rooftop Models
The files below illustrates how to evaluate rooftop solar projects that include a portfolio of different individual assets (it is arguably analogous to corporate finance or real estate finance). The files below include a number of tricky methods to accumulate production, capital expenditures and operating expenses with different timing of start and end for different solar roof-top assets. For example, one set of rooftops may be producing electricity and yielding positive cash flow while another set of assets is being constructed. Some of the items such as degradation, operating expenses and depreciation may vary as a function of the age of the project. This can cause difficult programming issues. The first file also includes the effect of detailed working capital movements where lags are evaluated from prospective months. Finally, both of the models include complex issues associated with financing where some projects are producing positive cash flow and others require financing and are not yet generating cash. This first file is used for a comprehensive set of videos that allow you to build a model on a step by step basis that I have modelling the solar rooftop on-line course. The video below describes how to set-up financing in the rooftop model where separate SPV’s are not structured and the financing occurs on a consolidated basis.
Rooftop Solar.xlsm
Rooftop Model with Portfolio PW 9498.xlsm
Renewable Project Finance and Price Bidding Analysis
The files below illustrate the effect of different financing structures on the on the levelised costs of alternative technology including solar. It is built with a relatively simple project finance model that evaluates carrying charges. It is meant to demonstrate that the kind of financing terms in a project finance loan document and the returns demanded by equity and debt holders have a dramatic effect on the price of solar power. The file includes a waterfall chart where you can evaluate which aspect of the financing has the most important effects on the bid price. The renewable project finance model is a relatively old model that includes a lot of instructions with data verification labels and comments.
23. Carrying Charge Analysis Revised.xlsm
Renewable Project Finance Model with Instructions.xls
Tax Equity Analysis for U.S. Renewable Energy Projects
The files below are examples of models with tax equity flip structures using alternative methods of determining the investment of tax equity investors. A tax equity structure works as a partnership where cash flows are split between alternative parties. Much of the analysis involves efficiently a cash flow waterfall where the cash flow is split in a different manner before and after the flip. To compute the timing of the flip you can set up an account that maintains the present value of the first senior facility and accumulates the cost of funds at the flip rate. This can be accomplished by using sub-totals in the cash flow statement and then using the MIN function with the balance of the account that maintains the value of the senior securities. The files include some some discussion of how the flip structures work and it demonstrates how to evaluate the flows and risks to alternative investors. The file demonstrates that you can use accounts similar to cash sweeps with subordinated debt to establish flip timing and cash flow waterfalls. The key is to use a MIN function and test the tax equity flip in two steps.
Tax equity structures can be affected by limitations on the tax deductions if an equity capital falls to a value below zero. There are different ways to define capital and the capital must be above zero from both tests in order for deductions to be allowed for the senior tax investor. If have seen a lot of absurd models that over-complicate the tests and have macros or circular references. If the cash flow waterfall is structured correctly and the timing is consistent the constraints can be modelled in a structured manner. I have not put up videos for this issue yet.
Tax Equity Analysis.xlsm
Solar Annual Model.xlsm
Infrastructure Models and Project Finance with Traffic Risk
Infrastructure projects such as toll roads, bridges, subway systems, ferries and tunnels can generate revenues from traffic in a user-pay system, fixed availability payments or some combination. These projects can be public-private partnerships with various sorts of funding, guarantees and other items from the government. As traffic has a ramp-up period and generally grows with the economy thereafter and toll rates can increase with inflation, the cash flow pattern can have a pattern with high growth rates. Various different forms of sculpting, demand notes and other features can be used to resolve low cash flows in early years. The files below demonstrate and the associated video present alternative ways to represent models of project finance of toll roads. The first model is from a HBS case study of the A2 toll road in Poland that was originally structured to take traffic risk. (Later it was converted to shadow tolls and I believe the traffic estimates were optimistic). The second file is for a PPP model and demonstrates issues with a toll road in Ghana that includes how the traffic studies are developed.
Tollway Example.xlsm
PPP Toll road Model.xlsm
Project Finance Model with Commodity Price Risk and Monte Carlo Simulation
The file and video below illustrates how you can incorporate Monte Carlo simulation into a project finance model. The model is set-up to be a model with commodity price risk. Once the project finance model is built, a time series analysis with volatility and mean reversion parameters is added to the model. The Monte Carlo simulation works with a little VBA code that re-calculates the price and by consequence also the cash flow for many simulations. The Monte Carlo simulation can be used to judge the efficacy of various structuring issues including cash flow sweeps and cash flow traps. The model below and the video demonstrates how you can incorporate either scenario analysis and/or Monte Carlo analysis to measure credit risk associated with commodity prices. The file shows that at least in theory, you can use the PLCR and LLCR with Monte Carlo simulation to measure the probability of loss. This can be adjusted for cash sweeps and other features. I am completing a page
Commodity Price Model with Monte Carlo Simulation.xlsm
Credit Analysis of Project Finance with Methane/Diesel Plant
The below file demonstrates how to use a model to evaluate credit analysis in the context of project finance. The model includes a bridge loan and alternative prospective capital investments in two phases. The credit analysis and the model includes currency risk because the bridge loan is in local currency whilst the revenues are received through a contract that is fixed in USD. This means different inflation rates must be forecast and evaluated as well as the other varibles. If you are interested in currency risk, this may be a file to review.
Credit Analysis in Project Finance.xlsm
Multiple Currency Analysis and Development Analysis in Solar Plant Model
The file below demonstrates how to systematically evaluate the effect of different cash flows in different currencies using a solar model example. The model demonstrates that the starting point is to evaluate inflation rates in different currencies and exchange rates. This files uses purchasing power parity as a starting point and then allows deviation from the parity exchange rate. The exchange rate could also be entered using future exchange rates. To implement the model, the revenues, the financing needs, the debt flows and other items are computed in dual currencies and different currencies can be chosen.
Currencies in Solar Model.xlsm
Wind Project Finance Models
The files below show how to create project finance models that evaluate wind projects. The first model includes different wind production scenarios and detailed operating expense calculations (I began by evaluating other models and it has some redundancy and bad equations). As with other models, these files include resolution of circular references related to financing and sculpting. The second file is intended to show some issues that can arise in project financing of off-shore wind. The model includes resolution of circular references, re-financing and the return that can be generated by selling a plant after the risk of the plant declines. The re-financing assumptions allow alternative timing, DSCR and credit spread assumptions.
On Shore Wind Model Example.xlsm
Off Shore Wind Project Finance Model Example.xlsm
The file below is a project model for a representative wind farm in Brazil. It includes financing techniques that are associated with BNDES lending that do not correspond to the tariff structure. I call some of the adjustments made to O&M contracts in this reverse sculpting. This happens because the debt structure is given and the cash flows are adjusted to correspond to the financing rather than the other way around. The file includes functions that resolve circular references associated with financing and the file also demonstrates the modelling of an Equity Bridge Loan during the financing period.
Brazil BNDES Structure.xlsm
Project Finance Model with Commodity Price Risk and Monte Carlo Simulation
The models below are intended to demonstrate issues associated with hydro. Hydro can have a lot of volatility in water flows but also a lot of mean reversion. It demonstrates the importance of re-financing and cash sweep structures in situations with high cash flow volatility.
Hydro Example with Flexible Debt Structuring.xlsm
Hydro Model Complete.xlsm
Evaluating Actual Relative to Forecast in Project Model
The file below demonstrates how to deal with the problem of receiving actual data after a project is placed in service. One method involves typing over existing numbers — this is horrible. A much better approach demonstrated in this model is to put actual data in a separate sheet. After the data is included in a separate sheet, the actual can be compared to the forecast. A third analysis is also required that develops a new forecast though adjusting the forecast data for actual numbers. You really then need a second model as the original project finance model is out of date on the day of commercial operation.
Actual and Forecast.xlsm
| Subject | Excel File | Video Link | Chapter Reference | Page Reference | ||||||
| Navigating and Understanding a Model | IPP Model | https://www.youtube.com/watch?v=fDuNgdTETF4 | Chapter 4 | 31 | ||||||
| Using the IPP Model | IPP Model | https://www.youtube.com/watch?v=QUjc8kyuDoE | Chapter 4 | 31 | ||||||
| Structuring and Risk Analysis | Off Shore Wind Model | https://www.youtube.com/watch?v=eQzUJL11Eyo | Chapter 39 | 491 | ||||||
| Project Finance Commodity with Monte Carlo | Commodity Price Example | https://www.youtube.com/watch?v=rOVNH7TWOaU | Chapter 21 | 278 | ||||||
| Taxes and Financing | Annual Solar Model | https://www.youtube.com/watch?v=MHoI3XzT9Xg | Chapter 11 | 135 | ||||||
| Re-Financing and FAST | Tollway Model Example | https://www.youtube.com/watch?v=I6FWvFvGSZ4 | Chapter 44 | 44 | ||||||
| Analysis of Solar Production | Solar Model Example | https://www.youtube.com/watch?v=NRgwZPewdgs | Chapter 40 | 497 | ||||||
| Alternative Technologies | Renewable Model with Instructions | Chapter 4 | 31 | |||||||
| Problems with Copy and Paste Macros | Quezon Coal Plant Case | https://www.youtube.com/watch?v=Tsm_DTdwFiM | Chapter 39 | 494 | ||||||
| Tax Equity and Flip Structures | Tax Structuring Model | https://www.youtube.com/watch?v=vnwju0-LRtU | Chapter 11 | 135 | ||||||
| Model Structure and Circular Reference Problem | On Shore Wind Model | https://www.youtube.com/watch?v=c9HVevcKNQg | Chapter 41 | 515 | ||||||
| Building a portfolio of Projects | Solar Rooftop Model | https://www.youtube.com/watch?v=q9Jny30z2GU | Chapter 47 | 583 | ||||||
| Overview of Solar Model | Solar Model Example | https://www.youtube.com/watch?v=NRgwZPewdgs | Chapter 4 | 31 | ||||||
| Resolving Circular References in Solar Model | Solar Model Example | https://www.youtube.com/watch?v=jwS-SlLOmkM | Chapter 39 | 494 | ||||||
| Creating Tornado Diagrams in Solar Model | Solar Model Example | https://www.youtube.com/watch?v=A4m_b8t2xW4 | Chapter 18 | 231 | ||||||
| Adding Inflation to a Project Finance Model | Solar Model Example | https://www.youtube.com/watch?v=A4m_b8t2xW4 | Chapter 8 | 85 | ||||||
| Including Depreciation on New Expenditures | Solar Model Example | https://www.youtube.com/watch?v=7c1tcsY6BJI | Chapter 9 | 99 | ||||||
| Cleaning up a model with Table of Contents etc. | Solar Model Example | https://www.youtube.com/watch?v=2fKnIb3yXJA | Chapter 5 | 53 | ||||||
| Using Models to Create Templates | PPP Tollway Model | https://www.youtube.com/watch?v=XXXxK-be5ik | Chapter 8 | 85 | ||||||
| Working Capital in Project Finance | PPP Tollway Model | https://www.youtube.com/watch?v=1BknIdvWVuU | Chapter 9 | 91 | ||||||
| Scenario Analysis in PPP | PPP Tollway Model | https://www.youtube.com/watch?v=6BSU7PI72rI | Chapter 17 | 207 | ||||||
| Tornado Analysis in PPP | PPP Tollway Model | https://www.youtube.com/watch?v=8ytj4YkcMOs | Chapter 18 | 232 | ||||||
| Taxes and Delay in Project Finance Model | Brazil BNDES Structure | https://www.youtube.com/watch?v=TriiaHemB8s | Chapter 9 | 99 | ||||||
| Audit Tests and Audit Page | Brazil BNDES Structure | https://www.youtube.com/watch?v=8hjfLdQTJDU | Chapter 5 | 53 | ||||||
| DSCR, LLCR and PLCR | Brazil BNDES Structure | https://www.youtube.com/watch?v=zighud2F9bI | Chapter 8 | 85 | ||||||
| Equuty Bridge Loan | Brazil BNDES Structure | https://www.youtube.com/watch?v=uqccAvWeQTo | Chapter 43 | 550 | ||||||
| Creating Function to Solve Circular Reference | Brazil BNDES Structure | https://www.youtube.com/watch?v=ehCpew9KZ20 | Chapter 40 | 501 | ||||||
| Working with O&M Expenses to Optimise Struture | Brazil BNDES Structure | https://www.youtube.com/watch?v=rpG3NGBV7xA | Chapter 44 | 44 | ||||||
| Street Lights PPP EBITDA | Street Light PPP Model | https://www.youtube.com/watch?v=L–eUUt-8yo | Chapter 6 | 55 | ||||||
| Street Lights PPP Depreciation and Taxes | Street Light PPP Model | https://www.youtube.com/watch?v=trKG59InIB4 | Chapter 9 | 94 | ||||||
| Street Lights PPP Financing | Street Light PPP Model | https://www.youtube.com/watch?v=RG7tjOPyUhA | Chapter 10 | 119 | ||||||
| Street Lights PPP DSRA and Summary | Street Light PPP Model | https://www.youtube.com/watch?v=I_Epp1t3Jfw | Chapter 43 | 550 | ||||||
| Interest Income on DSRA in Detailed Model | PPP General Model | https://www.youtube.com/watch?v=s8lGryt06S4 | Chapter 43 | 548 | ||||||
| Using Generic PPP Model | PPP General Model | https://www.youtube.com/watch?v=PyU_0DB6Ac4 | Chapter 4 | 30 | ||||||
| … | …………………………………………………………………………. | …. | …………………………………………………………… | …. | ……………………………………………………………………………………….. | …. | …………………………….. | … | ………………………. |