|| 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 page includes excel files and videos that describe a selection of completed project finance models. By working through 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. Some people find that a good way to really learn a model is to work through every line item of a completed model. (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.

The table below the files includes video links to a few of the project finance models that are intended to give you an introduction to the project finance models and to remove fears you may have of looking into the models. This page is intended to go hand in hand with the exercises in other pages where you learn how to build a model from A-Z. To see structured lessons on project finance, go to the Project Finance Exercises page where you can work through a host of project finance issues. The page references on the table below refer to my book "Corporate and Project Finance Modeling, Theory and Practice".




Library of Featured Project Finance Files:


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.

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.







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 Philipinnes that resulted in very high returns to U.S. investors who really did not take risk.








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





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.



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 modeling 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 structred manner. I have not put up videos for this issue yet.






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.






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






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.




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.




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





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.



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.




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.




Goto Models

Video Overviews of Project Finance Models



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 Commdity 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


………………………………………………………………………….
….
……………………………………………………………
….
………………………………………………………………………………………..
….
……………………………..

……………………….





General Comments About Project Finance Models:


Project finance models are a lot about timing and customising the financing of a project to its cash flow. Difficult aspects of project finance models involve resolving circularity associated with financing, debt sculpting, debt service reserves and cash flow waterfalls. About 95% of the model is straightforward. It is the last 5% that can drive you crazy.
The featured models below demonstrate issues including:
1. Establishing timing switches and date structures
2. Constructing operating analysis
3. Tax issues and computing IRR
4. Developing Cash flow waterfalls
5. Sculpting Debt
6. Problems of Circularity


Related Pages on the Site Related to Project Finance:


Other Featured Models
Other Project Finance Model Examples
Project Finance Model Exercises
Template Models
Scenario and Senstivity Analysis
Circular Resolution
Generic Macros
Auditing Files







=



=

Overview of Solar Project Finance Model.JPG
Video Overview of File
Clean Up Models.JPGCircular References in Solar Project Finance Model.JPGCircular Reference Page.JPGCircular Reference in Text.JPG


Depreciation in Project Finance Models.JPGTable of Contents.JPGMRA in Solar Models.JPGInflation in Project Finance Models.JPGScenario Analysis in Solar Model.JPG




Subject

Excel File

Video Link

Chapter Reference

Page Reference















Featured Project Finance Models

Navigating and Understanding a Model

IPP Model

https://www.youtube.com/watch?v=fDuNgdTETF4

Chapter 4

31


Featured Project Finance Models

Structuring and Risk Analysis

Off Shore Wind Model

https://www.youtube.com/watch?v=eQzUJL11Eyo

Chapter 39

491


Featured Project Finance Models

Project Finance Commdity with Monte Carlo

Commodity Price Example

https://www.youtube.com/watch?v=rOVNH7TWOaU

Chapter 21

278


Featured Project Finance Models

Taxes and Financing

Annual Solar Model

https://www.youtube.com/watch?v=MHoI3XzT9Xg

Chapter 11

135


Featured Project Finance Models

Re-Financing and FAST

Tollway Model Example

https://www.youtube.com/watch?v=I6FWvFvGSZ4

Chapter 44

44


Featured Project Finance Models

Analysis of Solar Production

Solar Model Example

https://www.youtube.com/watch?v=NRgwZPewdgs

Chapter 40

497


Featured Project Finance Models

Alternative Technologies

Renewable Model with Instructions



Chapter 4

31


Featured Project Finance Models

Problems with Copy and Paste Macros

Quezon Coal Plant Case

https://www.youtube.com/watch?v=Tsm_DTdwFiM

Chapter 39

494


Featured Project Finance Models

Tax Equity and Flip Structures

Tax Structuring Model

https://www.youtube.com/watch?v=vnwju0-LRtU

Chapter 11

135


Featured Project Finance Models

Model Structure and Circular Reference Problem

On Shore Wind Model

https://www.youtube.com/watch?v=c9HVevcKNQg

Chapter 41

515


Featured Project Finance Models

Building a portfolio of Projects

Solar Rooftop Model

https://www.youtube.com/watch?v=q9Jny30z2GU

Chapter 47

583


Featured Project Finance Models

Overview of Solar Model

Solar Model Example

https://www.youtube.com/watch?v=NRgwZPewdgs

Chapter 4

31


Featured Project Finance Models

Resolving Circular References in Solar Model

Solar Model Example

https://www.youtube.com/watch?v=jwS-SlLOmkM

Chapter 39

494


Featured Project Finance Models

Creating Tornado Diagrams in Solar Model

Solar Model Example

https://www.youtube.com/watch?v=A4m_b8t2xW4

Chapter 18

231


Featured Project Finance Models

Adding Inflation to a Project Finance Model

Solar Model Example

https://www.youtube.com/watch?v=A4m_b8t2xW4

Chapter 8

85


Featured Project Finance Models

Including Depreciation on New Expenditures

Solar Model Example

https://www.youtube.com/watch?v=7c1tcsY6BJI

Chapter 9

99


Featured Project Finance Models

Cleaning up a model with Table of Contents etc.

Solar Model Example

https://www.youtube.com/watch?v=2fKnIb3yXJA

Chapter 5

53


Featured Project Finance Models

Using Models to Create Templates

PPP Tollway Model

https://www.youtube.com/watch?v=XXXxK-be5ik

Chapter 8

85


Featured Project Finance Models

Working Capital in Project Finance

PPP Tollway Model

https://www.youtube.com/watch?v=1BknIdvWVuU

Chapter 9

91


Featured Project Finance Models

Scenario Analysis in PPP

PPP Tollway Model

https://www.youtube.com/watch?v=6BSU7PI72rI

Chapter 17

207


Featured Project Finance Models

Tornado Analysis in PPP

PPP Tollway Model

https://www.youtube.com/watch?v=8ytj4YkcMOs

Chapter 18

232


Featured Project Finance Models

Street Lights PPP EBITDA

Street Light PPP Model

https://www.youtube.com/watch?v=L--eUUt-8yo

Chapter 6

55


Featured Project Finance Models

Street Lights PPP Depreciation and Taxes

Street Light PPP Model

https://www.youtube.com/watch?v=trKG59InIB4

Chapter 9

94


Featured Project Finance Models

Street Lights PPP Financing

Street Light PPP Model

https://www.youtube.com/watch?v=RG7tjOPyUhA

Chapter 10

119


Featured Project Finance Models

Street Lights PPP DSRA and Summary

Street Light PPP Model

https://www.youtube.com/watch?v=I_Epp1t3Jfw

Chapter 43

550


Featured Project Finance Models

Interest Income on DSRA in Detailed Model

PPP General Model

https://www.youtube.com/watch?v=s8lGryt06S4

Chapter 43

548


Featured Project Finance Models

Using Generic PPP Model

PPP General Model

https://www.youtube.com/watch?v=PyU_0DB6Ac4

Chapter 4

30