Project Finance Modelling

Teaching Style for Modelling

• Overall objective is to teach more complex project finance theory

and concepts through modelling.
• Don’t use really big template models. Instead work from blank sheet
and understand sophisticated issues.
• You can find models at www.edbodmer.com
• Understanding the modelling concepts is much more important than
typing formulas in excel.
• Review real models and their problems in each section

2
 Excel Functions and Time Lines – Pre-Cod
Tools and Post-Cod

Modelling how any

Modelling how the business works –
machine works investments to get
operating cash flows

General Modelling the

Accounting principles as

Topics
applied to project
depreciation expense
finance (depreciation,
and operating taxes for a
financial statement
project
linkage, working capital)

Covered Entering Fixed Debt into Incorporating DSRA,

Model to Evaluate MRA, Cash Seeps into
Funding, IDC, Fees models

Sculpting, Funding, Sensitivity Analysis

Taxes, Fees after (Structuring and Risk
Construction Analysis)
 • Review the general finance theory
and the model structure before
equations.
• Review ideas with power point
slides in Video. Do this because
modelling is not useful unless
Teaching understand the project finance
concept.
Style • Project Finance structure
discussed in Video and you
enter the structure of the model
– you stop watching the Video
and do work yourself.
• Next Video works through
financial model equations. After
watching the video, you enter
the equations. You can use the
exercise file.

4
 Selected Subjects Covered
• General Modelling Structure and Practices
• Project IRR versus Equity IRR; Alternative Valuation
Measures to IRR and Selling Projects at Different
Dates
• Allocation of Cash Flows to Developers and Gauging
Risk to Alternative Investors
• Evaluation of Existing Projects
• Incorporating Standard Deviation and Wind Variability
– in Debt Size; Effect of Fixed and Variable Costs
• Impact of Debt Terms (DSCR, Maturity, Credit Spread)
• Development Fees and Cost Padding with Debt to
Capital Constraint
5
 Why You May Want to Watch the Videos Even if You are
Experienced

• When I have looked over models made by

the largest banks and consultants, there are
many things that I think could be done much
better.
• UDF templates for resolving circular
references made pretty easy and hopefully
clearly explained.
• Unintuitive results like the tax cost of
development fees (that generate taxable
income) when DSCR drives the debt size.
 Advanced Issues Included in These Slides
• Flexible Dates with Fiscal Year
• Using Interpolate in Volumes and Capacity Analysis
• Flexible Inflation Indicies
• Pre-COD EBITDA, Taxes and Working Capital
• Prospective Capital Expenditure with MATCH
• Use of Depreciation Functions for Continuing
Capital Expenditure
• Accounting for Development Fees
• Funding with Pro-Rata or Equity Up-front with
percent of equity up-front
 Include Advanced Issues
• UDF Circular Resolution for Funding
• Inclusion of DSRA and MRA in Risk Analysis Model and
Evaluation of MRA economics
• Cash Flow Waterfalls with Default Measurement
• Prospective LLCR with Multiple Issues
• Debt Sizing with DSCR versus Debt to Capital and Template
UDF
• Debt Sculpting with DSRA changes, Interest Income, Taxes and
Tax Deductions from Subordinated Debt
• Interest Rates, Credit Spreads, PD and LGD
• Re-financing with Taxes, DSRA L/C. Alternative Timing and
Swap Breakage Cost
• Project Sales at Different Dates, Inflation Index and IRR Upside
Modelling Religion, Excel Functions,
Free Tools and Interpolate Function
General Philosophical Ideas About Modelling
• In thinking about the philosophy of modelling,
consider a presentation to client or a presentation
to your boss.
• Assume that you are trying to explain how the model
works in the context of a transaction and explaining
how the transaction works.
• Assume you will give the model to your client or your
boss for review.
• Assume that after explaining how the transaction
works you would also have to explain how the model
works.
• For this, keeping the model flexible, accurate,
structured and transparent is crucial.
10
 Financial Modelling Religion and FAST
 Various organisations have rules for modelling. One good technique for
modelling (and maybe for your life) is FAST. (General conflict between
Structure and Flexible)

F
 Flexible: Different timing, scenarios, financing techniques.
 No copy and paste macros.
A
 Accurate or appropriate.
 The balance sheet must balance
S
 Structured.
 Separate financing from operations.
T
 Transparent – short equations.
 Core model on one page.

11
What Does Flexible
Really Mean
• Can change any dates,
development periods,
construction periods, PPA
periods etc.
• No limits on goal seek
from copy and paste
macro. Copy and paste
macros simply ruin the
flexibility of a model.
• Easy to incorporate
alternative financing and
operating strategies
• Put together different
types of presentations of
sensitivity, scenario and
maybe even stochastic risk
analysis.
 Examples of Flexibility
• Change the lifetime of the project in a project
finance model
• Change the explicit period for computing
cash flows in a DCF model
• Including spinner boxes on the debt tenure,
the debt size and the interest rate in a project
finance model.

13
Creating a Flexible Summary Page to Explain a Transaction
is Part of the Flexibility Principle

• To make models flexible, use developer tools

and create little sensitivity boxes

14
 Flexible Principle and Flexible Scenario Analysis

• There are many ways to make scenario

analysis, but whatever you do, you should
make your analysis flexible:
• You could use the scenario reporter
• You could use the INDEX and Data Table
Method
• If you use some kind of VBA code, make sure it
is flexible and can handle different scenarios and
variables.

15
 Crimes of Inflexibility: Fixed Number in Sheet

• The worst case is when you cannot find the

fixed number because it is part of a formula:

16
 Accurate Principal
• You obviously want your work to be accurate, but in a
big model how can you do this.
• One way is to try to have excel do the work for you.
There are various tests you can put in the model, the
most notable of which is that the balance sheet
balances.
• You can test the balance sheet balancing with a
TRUE/FALSE switch that is only TRUE when the
balance sheets in all of the years balance.
• You can make similar mechanical checks for other
things like does the debt balance go to zero and does
the accumulated depreciation sum to the plant balance.

17
 Accuracy Principal and Audit Page
• Once you have made TRUE/FALSE tests
with in your model, you can put all of the
tests into a single page.
• If you label the tests you can show if all of
the tests pass and if not, which test failed.
• You can do this in an aggregate test and put it
in many places in your model.
• The key is that you should have excel tell you
where the errors are so you do not have to
look around.

18
 Example of Accuracy – No Balance Sheet
• Put in tests with TRUE/FALSE and include a
balance sheet in project finance even though
it is not necessary.

19
Accuracy Principle and Reasonable Operating Analysis

• Testing that you put in reasonable

assumptions and that when you change an
assumption you do not get a silly result can
be done with a sensitivity analysis where you
test one variable at time and examine the
result. This can be presented with a tornado
diagram.

20
Accurate and Appropriate

• Appropriate – models
should be geared to
objectives. May want
detail like in google maps
when walking. May not
need detail.
• Accurate – the wonderful
feeling of the balance
sheet balancing and debt
going down to zero. But Google Maps is like a
don’t overdo tests just to financial model – sometimes
the time estimates are way
impress people with your off (especially if it is the first
excel skills. time).

Sometimes you still get lost.

 Structure - Summary
• The biggest idea about structuring is to
separate the model into an operating section
and a financing section (Modigliani and
Miller).
• Also separate the physical part of operations
from revenues, expenses and capital
expenditures.
• Examine different cash flows starting with pre-
tax operating cash flows, to after-tax cash
flows, to debt cash flows, to after-tax equity
cash flows.
22
 Some Key Structuring Points
• There is no set rule, but the model should have a
nice flow. Here are some ideas:
• Put the operations – volume, capacity etc. first in any
model
• Put the revenues, expenses and capital expenditures
before any financing
• You may have a debt sizing section if the debt size
comes from sculpting, DSCR and cash flow
• Keep some kind of structured sources and uses of
funds for resolving circular references
• Financial statements come last
• Split up depreciation between financing and non-
financing items

23
 • Steps in a Structured Model
• Using of efficient and
transparent time line
Structure of • Setting up assumptions in same
order as model
Model: • Operating assumptions before
Operations any money including resource,
capacity, capacity factor and
before efficiency (heat rate)
• Operating analysis which is
Financing modelling PPA, EPC and O&M
contracts overlaid with the
operating MWH characteristics
in model to derive EBITDA,
Capital Expenditures and
Project IRR
 Keeping your Model (and your
Life, and this course) Structured
Biggest thing Finally
about structure Financing only Valuation of
– Modigliani after Project IRR Equity and
and Miller Outputs
• First capacity and • Five Parts of • Delete any outputs
volumes – NO Financing without affecting
MONEY • Debt Size anything else
• Next Cap Exp, • Debt Funding
Revenues and • Debt Repayment
Expenses – NO • Interest and Fees
FINANCING • Debt Protections
• Then Cap Exp and
• Then Don’t Forget
Operating Tax –
Project IRR Refinancing
Structure: Inputs (Assumptions),
Financial Calculations and Outputs

1 2 3
Make the Inputs Begin Inputs Core Model calculations
on ONE page.
in the same with Time Line • Why make users suffer
order and Keep time line in with trace precedent
• Show the natural flow
structure as the sight at the top • Connect the debt to
model cash flow
• Connect the debt
funding to debt
balance
 Example of Structuring Problems – Finding Financing Items on
Summary Sources and Uses

• In structuring a project finance model,

keeping a nice summary of sources and uses
is in my opinion very valuable. Here is an
example of what happens when you do not.

Refer to summary
page so can
manage circular
reference

27
Structure of Time Lines – Use Logic
and not horrible rules given to you
• Understand what is best timing in model
• Project finance is a form of debt and interest expense
depends on how you borrow or repay money. Model
should often correspond to monthly draws where
interest is increasing each month and semi-annual
repayments where the stays the same for six months
and then declines after a repayment is made.
• If you are modelling a tax equity transaction, then the
quarterly payments of taxes may drive the model and
you need to make all cash flow, income and balance
sheets on a quarterly basis.
 Structuring Time Line and Flexibility
Need Flexible Start and Fiscal Year Repaired Time Line
• Problem with time line – fixed date • Fixed time line with fiscal year and
that does not start after flexible COD
construction.

29
Structure – Keep Scenarios
Separate

Keep Do not put Put

Keep the sensitivity Do not put Put all of the

analysis separate alternative scenarios sensitivities
from the core model in the middle of the including the time
structure assumptions sheet (I series sensitivities
do this too much) on a separate page
so users can change
them whenever they
want.
Transparency
• Keep the formulas simple
• No excuse at all for long formulas
because it makes the concepts difficult
to explain to somebody not familiar
with the model.
• Long formulas come about because
you do not exactly understand what
you are doing.
 Transparency
• Idea of transparency – make the model easy to
read for other people and for you when you re-
open your workbook.
• The biggest thing about transparency is to keep
formulas simple. There is no excuse for long
formulas – you can always add rows and
explain what you are doing.
• Other points about making models transparent
include using TRUE/FALSE switches and
putting drivers of formulas in columns at the
left of the modelling rows.
32
Example of Non-Transparent Formula: This is Not Something to
be Proud of

• I don’t understand why people do not put

drivers in the left column and waste time on
sums. An example of a difficult formula to
interpret is shown below.
 Transparency and Too Many
Pages
• Please put yourself in the position of
somebody trying to understand the model.
• It is so much easier to follow a model on a
single page rather than:
• Switches on a separate page – put the
switches (or if you want to call them
flags or masks) right next to the
calculations. Why force people to
make silly traces.
• Connect the debt schedule with the
sources and uses and also the cash
flow waterfall. Why would you make
people search for basic and obvious
links.
• Once you have the core of the model build
you can of course make all of the pretty
graphs you want.
 • Show key outputs
• Cost Drivers that allow benchmarking
• Maybe need to use USD as common
currency
• Capital Cost expressed something like
Cost/Room, Cost/Sq Meter, Cost per km,
Transparenc Cost per Barrel per Day
• Levelised Operating Cost per MMCF, per
y and MWH etc.
Benchmarki • Capacity and Capacity Utilisation
• Show some measure of economics of the
ng project such as LCOE
• Show IRR’s in real and nominal terms and
maybe in different currencies. Include IRR
on project, debt and equity
• Show credit ratios including DSCR, LLCR and
PLCR
 Exercise Process
• Option 1: Enter your own titles and structure
the model
• Option 2: Use provided titles and fill in
formulas
• Option 3: Watch video and make sure you
understand what is done
 Where to Find the File
• Until I am finished with these exercises I will put
the file on the website. Subsequently I will put the
file on the google drive.
 No Best Practice Nonsense: Lazy
Rule
• There are many ways in excel
to do things. Find the fastest • Find the
and easiest way to do it.
• Often use short cut like Alt,E,
Laziest Way
IS
• Sometimes use the mouse
• Use entire row or column
when you can
• Use TRUE/FALSE instead of IF:
=1=1 is TRUE
• True is 1
• False is 0

38
 Corillary to Laziness Principle
• Do not show-off you prowess with Excel
Functions and Techniques
• A few functions are all that you need
 No Hard-Coded Numbers Except in Inputs
• This is a clear crime and limits sensitivity and scenario
– one of the basic reasons that you make the model
• An even worse crime is mixing a formula with hard
coded numbers like F5 * 1.02.
• The first can be solved with F5, paste special,
constants or with Generic Macros.

40
Not putting driving factors in the left column
• Why in the hell do people put the sums in the
left column. It would be much better to show
the factors that are driving the model.
•

41
 • Shift, CNTL, R to copy to the
RIGHT
• CNTL, ALT, C to colour and
Generic format
Macros • Must Enable Macros
• Should say CNTL,ALT,C on

Files to the bottom of excel

Use and
Open • Should say SHIFT, CNTL, A
Read on the bottom
PDF to • Enable Macros
• Use Acrobat
Excel • Use Google Chrome

42
 Time and Space Test for Lookup with Entire Rows instead of
Locked Data

• There is a program on the disk where you can

test the “heaviness” of functions. This
example demonstrates that using an entire
row or column does not either slow things
down or make the size of the file larger.
Part 1: Time Lines
 Project Finance Model versus Corporate Modelling

• First, what are you modelling

• In project finance, modelling the SPV. This can
be a problem because evaluation of the counter
party to contracts may be more important than
evaluating the SPV. (This is a main point in the
Dabhol case study).
• In corporate modelling you could model
anything from a large bank to a small handbag
business where the base is financial statements

45
 History versus Contracts and Consultant Reports: Project
Finance versus Corporate Finance
Corporate Finance Project Finance
• Analysis is founded on history and • Since there is no history a series of
evaluation of how companies will consulting and engineering studies
evolve relative to the past. must be evaluated.
• Financing is important but not • The bank assesses whether the
necessarily the primary part of the project works (engineering report).
valuation. Without financing, no project.
• Successful companies expected to • Successful projects will pay of all
continue growing. debt from cash flow and cease to
• Focus on earnings, P/E ratios, operate.
EV/EBITDA ratios and • Focus on cash flow. Equity IRR and
Debt/EBITDA. DSCR.

46
Project versus Corporate Finance Modelling- Timing Issues and
History
• A key distinction between a corporate model and a project finance
model involves timing.
• Corporate Finance
• In corporate finance, the financial models should begin with uploading
historic financial statements.
• With the historic financials, you should prepare an analysis of history.
• This should include calculation of historic ROIC as well as many other ratios
that drive assumptions such as the depreciation rate and the ratio of accounts
receivable to revenues.
• Project Finance
• In project finance, the big problem is there is no historic basis for making
assumptions from financial statements.
• Various contracts and other analysis techniques should substitute for the
historic analysis.
• This could include historic analysis of commodity prices or resources such
as wind.
• Contracts that assure stable cash flows also substitute for the historic
analysis.

47
Project versus Corporate Finance Modelling- Timing Issues and
Terminal Value
• A second key distinction between a corporate model and a
project finance model involving timing involves continuing
value.
• Corporate Finance
• In corporate finance, the implicit assumption is that the company will
last indefinitely.
• You cannot make a model that goes forever, so you must make some
kind of terminal value calculation.
• The terminal value is assumed to reflect some kind of long-term
stability.
• Project Finance
• In project finance, the projects do not have an indefinite life.
• Modelling occurs for the entire life and generally assumes that SPV
will remain in place for the project life.
• Project finance modelling involves evaluating the development
period, the construction period and the operation period.

48
Project versus Corporate Finance Modelling – Credit Modelling
and Credit Ratios

• Corporate finance and project finance models are used

in different ways to evaluate credit quality.
• Project Finance
• Key analysis is of cash flows and the repayment of debt
from cash flow
• Cash flow is evaluated with the DSCR, LLCR and PLCR.
• Break-even to cash flow is evaluated
• Corporate Finance
• Corporate finance modelling from a credit perspective is
about evaluation whether a company is strong enough to
re-finance debt
• Ratios to evaluate the ability to re-finance include debt to
cash flow, debt to capital and interest cover.

49
 Project versus Corporate Finance Modelling – Valuation
Modelling and Returns
• Corporate finance and project finance models are used in
different ways to evaluate valuation and returns.
• Project Finance
• Key analysis is of IRR that measures the growth rate of cash
flows
• In particular the equity IRR is evaluated
• Valuation if made would focus on equity cash flow as the
WACC does not make sense in project finance because of
changing risk.
• Corporate Finance
• The analysis focuses on ROIC and ROE rather than IRR because
of the increase in investment with new capital expenditures.
• Valuation using free cash flow and WACC is the main output of
corporate models.

50
Project versus Corporate Finance Modelling- Changing Risk and
Stages in PF versus Explicit Period in CF

• Both corporate and project finance models

involve stages
• Corporate Finance
• Companies may have good years and bad years,
but it does not make sense to assume that there is a
continuing decline or increase in risk
• Project Finance
• Depending on the project type, there may a
continuing decline in risk
• Because of the decline risk, value changes over
time.
51
Similarity in Corporate and Project Finance Modelling

• Both types of models have financing

separated from operations
• Both models should apply FAST
• Both models should put financial statements
at the end with the balance sheet last

52
 City is Like a Corporation/Project is Business

Individual
Business or
Family is like
project Finance

53
 Family is Like Corporation, Person is Like Project Finance

Project
finance has Person is the
beginning project
and end. No
history and
no terminal
value.

Entire Family
is the
Corporation

54
 Time-Line is Crucial in Project Finance
RISK A crucial Feature of Project
finance is CHANGING --
DECLINING RISK
Time to Complete Task (months)
2 6 8 12 20 24 48 49

Financial Completion
Close Test

Sponsor
Risk

Construction

Project Technical Fuel Supply Permits

Letter Ground- Steady-State
Identi- and and Power Obtained
of breaking Operation
fication Economic Purchase Financial
Feasibility Intent Agreements Structure Commissioning
Negotiated
Financial
Agreements
Signed
Time
 Project Finance Model Structure Changes at COD
Before COD, cash flow After COD, cash flow is
is presented in the presented in the cash
sources and uses flow waterfall and the
statement last line is dividends
Father of the Commitment
bride makes Fee
commitment
to pay for
wedding

Development is Dating Pay your Bills and re-structure

Pay for Wedding
period. Probability of with Other peoples your life. Stuck with PPA type
failure is high money contract. May default.

FC is just after COD is Decommissioning

engagement Wedding Date
date Date
Cash Flow Before COD – Cash is Negative and Where Do You
Find It

• Funding Needs
• Development Cost
• Development Fees
• Capital Expenditures
• Interest During Construction
• Fees During Construction
• DSRA Funded During Construction
• Cash for Prospective Working Capital
• Total Cash Funding Needs – Uses of Funds
• Funding Sources
• Debt Financing
• Subordinated Debt Financing
• Shareholder Loan Financing
• Equity Financing
• Total Cash Funding Sources
Cash Flow After COD – Cash is Positive and Where Does It Go

• Cash Flow after COD

• Revenues
• Less: Cash Operating Expenses
• EBITDA
• Less WC Changes
• Less Taxes
• Less On-Going Capital Expenditures
• CFADS
• Less Debt Service
• Net Cash Flow
• Less Traps and Sweeps and DSRA
• Dividends to Equity Holders
Putting the • Not necessary
Two Cash • Look at cash flow statements of
corporations
Flow • Could begin with net income
Statements • Adjust for things like gains from
development fees
Together at • Can use for traditional ratios such as
FFO to Debt and FFO to Interest
the End
 Making Flexible Time Line
• Keep formulas transparent with simple tests and
use of switches
• As key is before and after COD, include the ability
to change the timing pre-COD and post-COD
• Need a Pre-COD and Post-COD switch
• Use the Generic Macros to Colour the TRUE and
FALSE
• Use SHIFT, CNTL, R to copy to the right
• Use the EDATE function a lot (you can use the
EOMONTH but not really necessary)
• Use SHIFT,CNTL,3 to Format the dates
 Don’t Use Too Many Functions
• What functions are necessary and are not necessary
• Three Key Functions for Core Model
• Lookup and not Vlookup, Hlookup or INDEX/MATCH
• Use of Index function for scenario and sensitivity analysis
• Sumif for error checking and annualising
• Offset function for DSRA and presentation
• Functions for Cash Flow Waterfall
• Use of MIN and MAX instead of IF
• Necessity for sub-totals
• Date Functions
• Use of ALT, EIS short-cut
• Use of EDATE and EOMONTH

61
 Exercise 1: Work with Dates (Use EDATE function)

• Enter dates for:

• Start of development
• Months of development
• Financial Close
• Months of Construction
• Commercial Operation
• Operating Period
• Decommissioning Date
 Un-Believable Bad Practice by Really Big Bank – Different
Time Lines

• For capital accounts that are part of the

calculations, use annual. For other accounts,
use quarterly.
Actual Models: There is No Need At All For Complex Formulas
in Timelines

• Note how comes from another sheet, no

documentation to the left, long formula.

64
 Actual Models – No Flexibility
• Note that the number 11 is fixed

65
What Not to Do in Time Lines – Different Time Lines for
Different Core Calculations
 Inconsistent Time Lines in Calculation Sheets

• For IRR and financial statements, you

eventually need to consolidate. So keep time
line in sight. This is from the bad PF model
video.
 Complex Formulas for Year
• You do not need such a formula for the year.
 How is this necessary
• All the formulas are complex like this.
 Anytime you have an opening and closing
balance, the opening balance comes directly
from the prior period closing balance

This means you start with the closing balance

Notes on not the opening balance

Beginning Think of new years eve party. You start the

and year with what was in your accounts at the

end of the previous year

Ending In terms of dates this is the same thing. The

start date is the previous end date plus one
Period day.

The very first end date (begin with the end) is

the start date of the model minus one day.
 • When designing assumptions
that change over time, it is
best to use the beginning of a
Notes on month.
• You could make models
EDATE with dates between a
month but that is painful
and probably not worth
it.
• When structuring
contracts, operating
expense changes, phase
in periods etc., it is best
to use dates and the
beginning of a month.
 • For each exercise including this exercise
on dates, you can try three things.
• Use the tab that is labeled “No
Titles.” To use this tab that is
Exercises coloured green, you will have to
structure the titles of the model.
This is the best thing to do
with and because structuring is so
important
without • Use the tab labeled “Titles.” If
you do this, all you have to fill in
Titles excel formulas. To begin with
titles, use the yellow tabs.
• Use the tab labeled “Completed”.
If you do this, then you can just
follow along with the video.
 Fiscal Year for making readable and understandable annual
sheets

• To make sense of data that is presented on a monthly

and then a quarterly or a semi-annual basis, it is nice to
add a separate sheet that presents annual data.
• For this you can use the SUMIF or the SUMIFS
function. These functions require the fiscal year as a
criteria.
• To find the fiscal year, you need to stop six months
(semi-annual) or three months (quarterly) after the
COD. You can use the EDATE function and then the
MONTH function.
• This month that is the subsequent month the COD
month, incremented is used to accumulate the fiscal
year.
 Painful Fiscal Year Problems
• You can use the fiscal year end as the month before
the COD end
• Edate(COD,-1)
• Then,
• You can in the annual
part of the model, use a switch for the fiscal year
change from the PRIOR PRIOR PRIOR end of
month.
• Finally, increment
the fiscal year with the
switch
 Use of AVERAGEIF, SUMIF, COUNTIF

• These functions are useful in project finance

model for:
• Converting periodic data by month to sum for a year
• Checking errors
• Counting or summing TRUE’s or FALSE’s

75
 Now, You Do It
• Use the file named David Lee
• Fill in Dates for FC, COD and Decommissioning with EDATE
• Start the Dates a few columns to the right so you can make
TRANSPARENT descriptions of what drives each row
• Create a time period with ALT E, I, S and go to about 400 to
leave enough monthly and quarterly periods
• Make a Pre-COD and Post COD switch first
• Use this switch to define the months in period
• Use the EDATE to define beginning and ending period
• Compute the incremented month and create an annual page
with the SUMIF function
• Compute the days and hours on a periodic basis and on an
annual basis
Project Finance Model Structure
 What is Meant by Model Structure
• The structure of a model is defined as different
component parts of a model that have separate parts –
for example, the debt schedule, the income statement,
the depreciation analysis and so forth.
• The structure of a model should reflect how the
transaction is evaluated and how the company works.
For corporate finance, there should be a section for
historic financials and a section for terminal value.
• In project finance, a central part of the structure is the
timing and phases including the development phase,
the construction phase and the operation phase.

78
Eight Elements of Structuring a Project Finance Model

• Separate elements of structuring a project

finance model involve:
1. setting-up the timing,
2. modelling the output and capacity of the project,
3. capital expenditures and EBITDA,
4. the sources and uses during the construction
phase,
5. the debt schedule that defines repayment and
interest,
6. the profit and loss and the cash flow waterfall,
7. the balance sheet and
8. the output statistics.
79
 Model Structure and Evaluation of the Transaction

• The structure of the model follows the way a project

should be evaluated for structuring debt (coming up
with debt size, tenor, interest rate etc.).
• The risks of operating cash flow should be evaluated
first, just as the operating module is the first part of a
model. If you don’t know the fundamental risks faced
by a project, you cannot evaluate what kind of
transaction is best for the project.
• Then the debt terms such as the tenor, the repayment
type and the interest rate should correspond to the
risks associated with operating cash flow. For
example, with more operating risk, you need more
cash flow.
80
 Structure of Debt
• In a model, debt should be structured in an
organized manner. Separate components should
be defined and laid out for:
1. the size of the debt,
2. the manner in which debt is funded,
3. the way the debt is repaid including the tenure,
4. the interest rates and fees that include the
structure of credit spreads, and
5. the credit protections including covenants,
DSRA and cash sweeps.

81
Scenario and Sensitivity Analysis in
Modelling
 Definition of Scenario and Sensitivity Analysis

• Scenario analysis involves change a number of

different variables in the model while sensitivity
analysis involves changing one variable at a time.
• Scenario analysis is a central component of credit
analysis.
• For credit, the key scenario is a reasonable downside
scenario that is not too extreme but is also not
optimistic.
• In the downside scenario, the DSCR should remain
above 1.0.
• Assumptions for the downside case should be a
central part of credit analysis reports.
83
 Stress and Worst Case Scenarios
• In evaluating credit, a number of downside
scenarios should be produced.
• In theory, the probability of a downside
scenario can be judged, but in practice this is
very difficult.
• You can label more extreme cases stress cases
or worst cases.
• In the more extreme stress cases, the LLCR
and/or the PLCR should remain above 1.0 and
there may be a single year when the DSCR
falls to below 1.0.
84
 Mechanics of Scenario Analysis
• A scenario analysis can involve using the
scenario reporter where you add a new sheet
and use some macros.
• Instead, you can use a table that includes
different cases and works with the INDEX
function along with the data table tool.
• The sensitivity analysis can be made in a few
different ways.
• One method is to use excel forms and in
particular the spinner boxes. These boxes can
push up individual variables.
85
 Sensitivity Analysis Mechanics with Spinner Boxes

• A second method of sensitivity analysis involves

adding factors that are expressed in percentages
to the model. For example, the percentage of
demand can be added to a model where you put
in 100% for the base case.
• Spinner buttons can be added to these percentage
variables (you have to divide by 100 to make the
spinner boxes work). Then you can perform
break-even values of different important variables
like traffic can be pushed down until the DSCR,
LLCR or PLCR hits a value of 1.0.

86
 Sensitivity Analysis with Tornado Diagram
• The final method of creating sensitivity analysis
involves creating a tornado diagram.
• A tornado diagram shows which variables have the
largest effect on statistics such as DSCR and which
variables have less of an effect.
• A tornado diagram can be created from a scenario
page with the base, low and high cases are input.
Once the three cases are made, you can make many
more cases where you adjust one variable at a time.
• Once you have created a big list of cases with one
variable changing, the tornado diagram uses the
INDEX function along with the data table tool.

87
 Example of INDEX Function
• We will make scenarios for things like:
• Variation in traffic for infrastructure projects
• Variation in price for commodity projects
• Difference in availability for availability projects
• Example of Index Function

88
 Excel Formulas, Short-cuts, Tables, etc.
• The excel stuff is like a cookbook
• Just find the instructions in a recipe book
• Review instruction in folder files
• Review with other models
• Most important, WATCH VIDEOS
 Scenario and Sensitivity Subjects
• Alternative examples of adding sensitivity
and scenario analysis to a model
• Sensitivity analysis with spinner and drop down
boxes
• Scenario analysis using INDEX and table
• Tornado diagrams for sensitivity presentation
• Presentation of scenario analysis with waterfall
diagram
• Understanding of P90, P95 etc. for
computing scenario analysis in renewable
analysis
 General Idea
• So, WITH ANY MODEL AT ALL, you can:
• Press Shift F11 for a new sheet
• Add a Scenario Number
• Create a Scenario Analysis
• Create a Sensitivity Analysis
• Create a Customised Scenario Analysis
 Master Scenario Page
• Most important thing for a banker, come up
with a reasonable downside case
 Master Scenario Page
• Most important thing for a banker, come up
with a reasonable downside case
 Tornado Diagram
• Create a tornado diagram using the
TRANSPOSE function with a Data Table
O&M Inflation Index Code

FIT Inflation Index Code

Operating Expense Level

Years to Replace Inverter

Fixed Feed Iin Tariff

Low vs Base
High vs Base
Annual Degradation

Performance Ratio Code

Energy Yield Scenario Code

Construction Cost

Construction period

-2.00% -1.50% -1.00% -0.50% 0.00% 0.50% 1.00% 1.50% 2.00%

 Overuse of Vlookup
• Problem of not using INDEX

95
Part 2: Model How the Project Works
on a Physical Basis (No Money)
Capacity, Capacity Utilisation and
Volumes
Just about any project has a Once the capacity is defined,
defined amount of capacity – the manner in which you use Finally, the volumes
the maximum you can the capacity or the capacity produced can be computed
produce in some time period utilization can be specified

For example, the

The time period
capacity Traffic Volumes
can be an instant
utilization of a from Study
– MW
hotel

The time period

Electricity
could be the life The production of
Production from
of a project – oil gas reserves
Wind
reserves
 There can be a lot of
detail about how you
compute volumes in
supplementary sheets.
Level of
Detail and
Sources
of Data If you have detail, it is
not bad to put it at the
back of the workbook
in other sheets.
 Do not do what I have done too much,
Do that is making different downside or
not do upside cases for volumes and capacity
utilization in the core assumptions sheet.

Don’t
Mix Up
Scenarios Put
Put only one scenario in the model and
allow different scenarios to be input in

in the the scenario page.

Core
Model
See the subsequent section on scenarios
See for this.
Set-up Assumptions with Model
Dates

Sometimes no capacity
Setting-up
until the COD and no
Other situations assumptions with Complexities
capacity at
dates
decommissioning

Some capacity and

Include the model
Here just use the volume produced Major overhauls and
start with zero to
switch variables before COD and will lost time
avoid #NA
generate income

Examples, early Include the

traffic, oil production, decommissioning
electricity from wind date with zero to
turbines turn off the capacity
 We will use short-cuts, excel enhancements,
TRUE/FALSE switches and only four functions.

The functions should be used in a way that you

are probably not used to.

INDEX

Excel LOOKUP (not VLOOKUP or HLOOKUP)

Functions SUMIF (or AVERAGEIF or COUNTIF)

EDATE

MAX and MIN for Waterfalls (not IF)

101
 Use of LOOKUP Function
• Don’t use VLOOKUP, HLOOKUP or
INDEX/MATCH with models that have a
time line. Instead, use the LOOKUP function
with an entire row as illustrated below:

102
 Works just like lookup

For UDF (user defined functions) you

Get the need to copy them – you cannot just have
another sheet open
LOOKUP
INTERPOL Allows smooth transitions rather than
ATE step functions
Function
To get lookup_interpolate into your
model, go to the website and open the
file named interpolate lookup and follow
the instructions.
 Enter Volumes and Capacity in the Model with Scenarios

• How does the project work – use the example

of volumes in traffic cases. Use LOOKUP
and LOOKUP_INTERPOLATE

104
 More Extreme Case of Not Putting Assumptions in Order

• Show the assumptions in one column and put

operations before financing, not like this
example.
 Example of Not Using LOOKUP
• Using painful INDEX, MATCH when
LOOKUP is much simpler
Example of What Not to Do in Model with Respect to Capacity
and Volumes

• Do not start with P&L and put in history

switch if you have actual data.
What Not to Do – Assumptions Not in Same Order as the Model
Logic

• Note that after the dates, comes the

depreciation rates

108
Example of Capacity and Volumes in Models
• Amazingly, No Layout of Capacity and
Volumes in Model
 Now, You Do It
• Begin with case that has date analysis
complete and work on capacity and volumes
• Use the Look-up function for capacity with
different dates
• Import the Lookup Interpolate Function and
understand how to import the function into your
sheet (you cannot use a function in the same way
as a macro (e.g. with generic macros)
Part 3: Modelling Pre-tax Free Cash Flow,
Capital Expenditures, Revenues, Operating
Expenses and Working Capital
 • In project finance this means
that capital expenditures and
development costs can be the
How Just first monetary items entered.
About Any • Modelling the S-curve can be
Business or painful in terms of inputs or
when you create curve-fitting
Person Works
– Make • To be negative with respect to
delay, assume that the project
Investments to is finished and stays idle until
Generate Cash the COD – this means that
you do not change the S-
curve, but you do change the
COD.
 Often, the Model is Overlaying Contracts on the Way a Machine
Operates

• SPV is a separate corporation in the middle:

• This is what you make the model of and it may be
irrelevant
• SPV signs a lot of contracts that should be illustrate
with solid lines
• The contracts should be labeled (e.g. concession
contract, EPC contract, PPA contract, O&M
contract, Loan Agreement, Shareholders agreement)
• Contracts should be consistent with each other with
back-to-back contracts
• Diagram should show direction of money and start
with revenues (no revenues, no project)
• Quality of off-takers should be shown on the
diagram in the circles
• Insurances and guarantees should can be
demonstrated
113
Making Money in Different Places by Receiving Money from PPA
Contracts; Dispatchable Plant

Off-taker
pays money PPA – Four Part Tariff
EPC Contractor: LD for Delay Risk
for PPA
Could be Sponsor Fixed Capacity Charge at FC
Contract O&M Charge
Fixed
Contract Heat Rate
Price
Capacity Charge with Index
Contract
Availability Penalty
with LD
Special
Purpose
Corporation Fuel Sponsor –
(IRR) Supply Fuel Mgmt.
Contract Fee
Contract
with
Guaranteed
Heat Rate Loan
and Shareholde Agreement
Availability r Agreement
O&M Contractor
– Could be Penalty
and Fixed Lenders
Sponsor
Fee Sponsor –
Wants EIRR
on SPV
 Output Based Project Versus Availability Based Projects

Off-taker
pays money PPA – One Part Tariff
EPC Contractor: LD for Delay Risk
for PPA
Could be Sponsor Performance Ratio Penalty
Inflate prices and Index to USD
Fixed
Price
Contract
with LD
Special Developer –
Purpose Receives a
Volume Not Corporation Fee
Covered by (IRR)

Contract
Contract with
Guaranteed
Loan
Performance Shareholde Agreement
Ratio r Agreement
O&M Contractor
– Could be Lenders
Sponsor
Sponsor –
Wants EIRR
on SPV
Working Capital after Revenues and Expense
• Working capital inputs are simple
• Delay in collecting revenues from the time revenues are
billed
• Expressed in days receivables outstanding
• Delay in paying expenses from the time expenses are
recorded
• Generally not to complicated
• Revenue or expense delay as percent of days in period
divided by the total days in the period
• Becomes complicated when the delay is longer than the
period. For example, when the period is monthly and the
delay is 70 days.
• Setting-up opening and closing balance for working
capital
 Process – Use Time Switches Near the Calculations

• Don’t put time switches in places that are

difficult to find
• Use switches to compute period numbers
• Can use ROUNDUP technique
• Compute Working Capital Balances
• End with Pre-tax Project IRR
 Exercise 3: Given Inputs Compute and Cap Exp to Compute
Project IRR

• Use switches and input values for Capital

Expenditures and EBITDA (use TRUE and
FALSE)

118
 Examples of Non-Transparent Revenue and Expense Analysis

• Even a really fancy model has overly

complicated formula and does not have
drivers in the left hand side
Examples of Un-Structured Revenue and Expense Analysis

• Arranging Assumptions – Here the

assumptions are out of order. PLEASE put
the assumptions in the same order as the
model and do not mix up expense and
revenue assumptions.
 Obvious Mistake by Big Bank – Inputs Mixed Up with
Calculations

• How could they do this with mixing up

calculations and inputs in this manner.
Putting fixed numbers in the core
calculations is a big crime.
 Common and Disgusting Example of Including a Separate Page or
a Separate Section of Flags, Masks, Switches

• The idea of transparency is to see what is going on. If

you stop the PPA after a certain period and then have to
go to another page to find how it works, this defeats
the transparency principle.
Problems with Lookup and Range Name for Data Table

• Example of formula that is difficult to

interpret.
 Formulas for Indexing
• Put formulas next to where you use them –
this should make you cringe.
 Somewhat Complex Item
• Create a flexible S-curve with the Weibull
distribution.
• The Weibull can be just like normal or can be
skewed.
• You can keep the construction constant while
delaying the COD.
 Note on Speed and Size of SUMIF with Total Line

• The flow items in free cash flow – Capital Expenditures,

Revenues, Expenses and Working Capital Changes can
be presented in an annual page with the SUMIF function.
The size and time test demonstrates that using the
SUMIF with the entire row or column does not slow
things down.
Items not Addressed in This Case that You Can Find Elsewhere

• Working capital where the days of working

capital extend beyond the period of the model
• Exchange rates where some of the items are
paid in one currency and others are paid in
another currency. Put PPP and deviation from
PPP exchange rates including inflation rates at
the top of the of the revenue/expense and
capital expenditure analysis.
• Non-contract or merchant period where
expenses may depend on the level of prices
(real options and terminal value).
127
 Now, You Do It
• Use the page after the volumes and capacity
have been established.
• Work through inflation rates:
• Begin in correct period
• Formula: (1+annual rate)^(months/12)-1
• Working capital from days in period
• Include pre-tax free cash flow here – do not
wait until the end of the model and try to go
backwards.
 IRR problems that Apply to All IRR’s in Project
Finance
(Project IRR, Equity IRR, Debt IRR, Pre-tax IRR)
 Why Do People Use IRR in Project Finance
• You want to find a single number that evaluates a
project and can be compared to other numbers (e.g.
compare the IRR of 10% on a project to yields on
stocks).
• Return on investment does not work because of the
manner in which depreciation is computed –
depreciation accumulates and net investment
declines. This means that ROI increases over the
life of a project.
• No necessity to compute the cost of capital, which is
very tricky in project finance because of changing
risk and there is not a constant cost of capital.

130
 Problems with IRR
• There are many fundamental problems with any
IRR (project IRR, equity IRR, etc.) that are due
to the mathematics of computing the IRR and the
assumed re-investment rate in the IRR
calculation. These include:
• If there are no middle cash flows, the IRR works fine.
• If the IRR is high, the long-term out-year flows have
no or little value
• The IRR does not account for changing risk of
projects.
• The IRR only corresponds to ROI when the discount
rate on the investment is the IRR

131
 Possible Alternatives to IRR
• There are various alternatives to the IRR, but the
NPV is not a very good alternative. All of the
alternatives require a discount rate.
• One alternative is computing the weighted average
return on investment. If the IRR is higher than the
cost of capital, the ROI is higher than the IRR.
• Another alternative is the ratio that measures the
present value of cash flows relative to the amount of
the investment. This is like the price to book ratio in
evaluating a stock.
• Compute a holding period IRR with alternative
assumptions about the buyer IRR when the project is
sold.

132
 Weighted Average Return on Investment
• Compute the invested capital and then the
present value of the invested capital
• Create a weighting factor for each year from
the present value of the invested capital
• Compute the period by period return on
invested capital (use the opening balance for
the invested capital).
• Multiply the weighting by the return on
investment to derive the weighted average
ROI

133
 Weighted Average ROIC and IRR
• Examples of IRR and alternatives. Note the
increase in ROIC relative to the IRR

134
 Example of Analysis with Changing Discount Rate

• Table shows the effect of different

assumptions with respect to changing
discount rates. Could do this with the WROI

135
NPV of Future Value Compared to Value of Investment

• The mechanics of this method involve

computing the PV of the future cash flow and
dividing that number by the amount of the
investment.
• This method does require a discount rate (but
you use a changing discount rate to reflect the
future risk).
• You can compute this with unleveraged cash
flow or equity cash flow
• The ratio is analogous to the price to book ratio
or the enterprise value to the invested capital
136
PV of Cash Flow compared to P/B and EV/Invested Capital

• The final method uses the formula:

• Theoretical Formula: P/B = (ROE-g)/(k-g)
• When the P/B = 1, then the ROE = k
• 1 = (ROE-g)/(k-g)
• k-g = ROE –g
• k= ROE
• A regression equation of P/B = ROE can be
created
• P/B = A + B x ROE
• When P/B = 1, then 1=A + B x ROE
• or
• ROE = (1-A)/B, which is an estimate of k
 Insurance Companies: Note the Relation Between ROE and P/B

• The P/B ratio is

not much above
1 for insurance
companies
which suggests
that cost of
capital is not
much lower than
ROE.

138
 Cost of Capital for Insurance Companies Using P/B and ROE
Regression

• Note two things. First, the cost of capital is

higher for insurance companies than for many
other companies.
 Using Idea that when M/B = 1, ROE = Cost of Equity

• This sort of analysis requires similar companies – you cannot do

this for Dow 30 for example.
• Formula is M/B = (ROE-g)/(k-g)
• When the M/B = 1, then the ROE = k no matter what the g. For
utilities cost is 5.1% to 4.88% depending on regression assumption.
 IRR Presentation in Models
• Equity IRR should be a lot higher than
project IRR if the interest rate is below the
project IRR
 Results that Do Not Make Sense in Actual Model

• In the case below, the project IRR is higher

than the equity IRR. This should only
happen when the Interest Rate is higher than
the project IRR.
 Very Typical to Have no Project IRR which is a Key Ratio for
Evaluating Project Economics

• Here only the equity IRR shown and

irrelevant WACC
 Returns to Investors Come from Company Rate of Return and
Growth

• This illustrates how you can compare

different indices and adjust for exchange
rates. Notice the difference between a return
of 7% and 3.6%
 Fundamental Economic Question of Economic Growth and
Stock Value Growth

• Can stocks out-grow the overall economy in the

long-run.
• Eventually, stocks should reflect corporate profits.
• Corporate profits should reflect overall economic
growth, otherwise there will be nothing but corporate
profits – nothing for anybody who does not own
corporations
• What has been the return on stocks (with dividend
re-investment) relative overall economic growth.
• If stocks do not grow faster than the economy, then
investors are not compensated for taking risk.
Stocks and Inflation – Note the Equity IRR for Stocks (This IRR
is not Biased)

• In evaluating any financial model, you must

be careful with inflation. Note the rate of
return with and without inflation.

146
 Returns to Investors Come from Company Rate of Return and
Growth

• Increase in stock price in past years has been

dramatic; difference between bonds and stocks
– equity risk premium – has been nowhere near
CAPM estimates. EMRP = 3.62%-2.91%.
 Stock Prices, Bonds and Equity Market Premium

• Evidence of an equity market risk premium is

difficult to find until the very recent increase in
stock prices. Here the EMRP = 3.35%- 3.02%.

148
 Stock Prices, Bonds and Equity Market Premium

• Recent increase in stock values has effects on

the distribution of income as profits are
higher than economic growth. Note the
increase in P/E ratio.

149
Explosion in Amazon Share Price – Can it be Explained with a
Financial Model

Understand the dramatic effect of different

rates of return. What is really possible
 Inverse of IRR impacts is the
Dramatic Changes in Value from
WACC

• Note the range in

values in the analyst
report
• The range is less when
a terminal value
multiple is used, but
the range is still very
high
• The high range exists
even though there is a
tight range in discount
rates
 In finance courses, valuation is
computed from after-tax free cash flow

Taxes are computed without any

distortions from financing

After Tax Tax effects of IDC depreciation, fee

Free Cash amortization, interest are excluded

Flow If project finance is all-equity IRR, some

include shareholder loans

By computing depreciation, you can put

together financial statements
 • Depreciation, capital allowance and fixed
asset module
• Notion of structured models with
separate section for depreciation
Depreciation analysis
• Use of timing switches for depreciation
and Capital and/or capital allowance
Allowance • Introduction to verification and auditing for
testing balances
for Tax • Benefits of separating deprecation on
interest during construction and fees from
other depreciation and amortisation
• Calculation of after-tax project IRR
Accounting is not different • Calculation of project IRR assuming
just because it is project alternative sale dates earlier than the
finance. You still capitalise retirement date
assets that last more than
one year.

153
 • For calculations of IRR, DSCR, LLCR
etc., the tax depreciation is
important
• Only reason for book depreciation
Tax is income calculations for
presentation to management.
Depreciation • If you really want to track return,
and Book you can use economic
Depreciation depreciation.
• This is an advanced issue that
will be addressed in the
tracking actuals section
• Tax Depreciation and VDB
154
Replacement Costs and
Remaining Life
Replacement
Depreciation
capital
can occur over
expenditures
remaining life
can occur from:
• Expansion of wells • Need some kind of
• Inverters function
• Overhaul costs • Array is painful
• Extraordinary costs • Errors from assuming
tax deductible in
current period
155
 Accounting and Taxes
• Of course taxes can be very complex and
involve all sorts of adjustments when
multiple jurisdictions are involved.
• But in general, taxes are in one way or
another derived from accounting, meaning
that you should begin by understanding a few
accounting principles.
Timing of Post-COD Capital
Expenditures

01 02 03 04
To model post-COD Then use IFERROR or Finally, use LOOKUP to Note, do not confuse
capital expenditures ISNA to make get the data into the the actual expenditure
that only occur for one TRUE/FALSE switch model. with the MRA account.
single period, use the The MRA comes after
MATCH function with a debt is added to the
zero for an exact match. model.
 Depreciation Function – Varying Rate
Function where the depreciation has an array and different rates by age
(e.g. declining balance)
Function depreciation(capital_expenditure, depreciation_rate) As Variant

asset_life = depreciation_rate.Count ' Find Life from the depreciation rate array
cap_exp_periods = capital_expenditure.Count ' See how many cap exp periods model
ReDim Depreciation_Expense(cap_exp_periods) As Single ' Make a new array

For model_year = 1 To cap_exp_periods ' loop around each period

For vintage = 1 To cap_exp_periods ' make a second loop - square

age = model_year - vintage + 1 ' calculate the age of each exp the diagonal)

If (age > 0 And age <= asset_life) Then ' Only when asset is alive
Depreciation_Expense(model_year) = _
capital_expenditure(vintage) * depreciation_rate(age) +
Depreciation_Expense(model_year)
End If

Next vintage Note that the vintage is used for the capital expenditure
Next model_year

depreciation = Depreciation_Expense
End Function
 Depreciation Function – Remaining Life
Function depreciation_remaining_life_3(capital_expenditure, remaining_life, max_life, factr)
As Variant ' When the output is an array define as Variant
cap_exp_periods = capital_expenditure.Count ' See how many capital expenditure periods are modelled
Dim Depreciation_Expense(5000) As Single ' Make a new array variable that is the output
Dim dep_rate(5000, 5000) As Single
For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset
If remaining_life(vintage) >= max_life Then
adjusted_life = max_life
dep_rate(vintage, 1) = 1 / adjusted_life * factr
For j = 2 To adjusted_life
dep_rate(vintage, j) = WorksheetFunction.Vdb(1, 0, adjusted_life, j - 1, j, factr)
Next j
Else
adjusted_life = remaining_life(vintage)
If adjusted_life < 1 Then adjusted_life = 1
dep_rate(vintage, 1) = 1 / adjusted_life * factr
For j = 2 To adjusted_life
dep_rate(vintage, j) = 1 / adjusted_life
dep_rate(vintage, j) = WorksheetFunction.Vdb(1, 0, adjusted_life, j - 1, j, factr)
Next j
End If
Next vintage
 Depreciation and Remaining Life - Continued
For model_year = 1 To cap_exp_periods ' loop around each and make a square
For vintage = 1 To cap_exp_periods ' make a loop to evaluate asset by asset
age = model_year - vintage + 1 ' calculate the age of (the diagonal)

If (age > 0 And remaining_life(vintage) <> 0) Then ' Only when asset is alive
Depreciation_Expense(model_year) = _
capital_expenditure(vintage) * dep_rate(vintage, age) +
Depreciation_Expense(model_year)
End If

Next vintage ' Note that the vintage is usef for the capital expenditure
Next model_year

depreciation_remaining_life_3 = Depreciation_Expense
End Function
 Plant Balances
• When computing depreciation, compute the
plant balances (you can use the plant
balances for straight line depreciation). Plant
balances will be plopped in the balance sheet.

161
 Taxes and MAX/MIN
• To carryforward taxes in a simple way:
• Understand that you do one thing when the taxable income
is positive and another thing when the taxable income is
negative.
• Use the MAX function for testing positive or negative
• Use the MIN function for capping amounts and not letting
amounts fall below zero.

162
 Development Fee and Depreciation
• Development fee is simple if you pay the fee to a
third party
• You record the cost of the purchase as a capital
expenditure and depreciate the cost
• The seller can experience a gain on the sale of the
asset which could be taxable if he does not have a
carryforward.
• If the development fee is with yourself
• You write-up the asset
• As this is non-cash, there must be an income offset
• In theory the asset write-up can cause taxable
income, but I do not see this in models.
Development Fee and Tax
Payments

Tax treatment can conflict with

The tax treatment of
financing strategy:
development fees, interest
• Development fees increase taxes if
during construction, taxes are paid on the gain from the
amortization of fees, asset write-up
depreciation, interest on • It is not good to extend the
shareholder loans and other construction period with multiple
items depends on the specific projects from a tax standpoint if
interest deductions for taxes are
tax rules of a country. delayed.
 Owner’s Cost and Tax Payments
• Owners costs are similar to development fees in terms
of taxes from the perspective of the entire corporation.
• Consider a hypothetical example of the CEO’s salary.
• Let’s say the CEO thinks about the project a few times
when he goes to sleep.
• You then allocate part of the CEO’s salary to the project
and increase the project cost with an account called
owner’s cost.
• The CEO’s salary would be deducted immediately if it
was not allocated to the project.
• By allocating the CEO’s salary to the project, if it is
capitalised, the tax deductions are delayed.
 Examples of Tax Errors in Models
• Taxable income on development fee
• Capitalization of major maintenance costs for
tax purposes
• Understanding that delay in recognizing
interest expense (i.e. continuing IDC) is
costly as is not associating pre-COD
EBITDA with depreciation.
• Including the effects of shareholder interest
on corporate taxes (interest is taxed but
dividends are not).

166
Example of Problems with Depreciation In Models – Completely
Un-necessary Matrix with Straight Line Depreciation

• No need for vintage with straight line

depreciation. Seem to be showing off excel
prowess.

Note how this is a

square
 Problems with Depreciation in Models
• As usual, a big problem is non-transparent and
long formulas
 Items not Addressed in Taxes
• Expiration of NOL after a certain period – this
is very painful because you must keep track of
when the NOL is created and you cannot put it
all in a big bucket. See other examples.
• Tax credits from grants and whether these
should be treated as a reduction in asset cost or
as a contribution of equity. (A contribution of
equity is much better for gearing ratios).
• Differences in deprecation rates for different
classes of assets (this is simple but tedious)
 Now, You Do It
• Use MATCH, IFERROR and INDEX for
putting the future capital expenditures into
the correct periods from the input data
• Retrieve the depreciation functions and
compute depreciation of the future capital
expenditures
• Create a plant balance and accumulated
depreciation account from capital
expenditures.
IRR Flips and Developer Incentives

171
 Introduction to IRR Flips and Developer Incentives

• A structure can be developed where you distribute

different amount of dividends depending on the IRR. A
structure can be set-up where more of the total cash
flow goes to the developer once IRR targets are met.
For example, the developer receives 1% of the
dividends if the IRR to the other investor (called the
senior investor or the sponsor investor) is less than 7%.
This compares to the investment made by the developer
of 3% of cash project costs. When the IRR to the senior
investor increases to above 7%, then the developer
receives 9% of the total cash flow. This 7% occurs
until the senior investor earns a return of 9%, after
which the percentage of total dividends distributed to
the developer increases to 36%.
172
 Inputs for IRR Flips

• The table below illustrates how you could

set-up the inputs in your model.

173
 Setting-up Tracking Accounts
• One of the keys to modelling a structure like the one shown above is
to set-up accounts that keep track of the return earned by the senior
investor.
• Note that it is unnecessary to set-up the same accounts for the
developer as returns to the developer are not used in establishing the
cash flow flips and changing dividend distributions.
• To set-up accounts that keep track of the senior return, you can
accumulate cash flows with a return and evaluate how much cash can
be distributed as dividends before the hurdle occurs. The dividends
in turn depend on the balance in the account plus the accumulated
amount associated with measuring the return.
• Note that in the screenshot, the cost of capital is computed from the
opening balance multiplied by the IRR hurdle rate.
• Note also that that there is a tracking account for each hurdle rate.
Finally, the are established from a cash flow waterfall analysis
discussed below.

174
 Tracking Account Illustration
• The example below shows three tracking
accounts for different hurdles. It is a
little tricky to model the incremental
cash flows from the different hurdles.

175
Cash Flow Waterfall to Alternative Investors
• The tracking account uses the amount of dividends to measure
the rate of return to the senior investor resulting from the
different tranches. The dividends to the senior investor are
computed using a MIN function to make sure that dividends
are not paid after the hurdle return is met. These dividends
must be computed using the total amount of the cash flow.

•.

•.

176
 Summary of Step-by-Step Process
• Set-up Initial Account with Cost of Capital Tracker
• Include Basis for Developer Cash Flow as Senior/Senior Pct.
• Once you have the Developer basis, the allocation is easy

• For subsequent tranches, start with incremental cash flow that is the basis for
allocations. This is total less the developer basis (Total equity cash - basis
from part 2)
• Compute total to senior with MIN function and include cash flow from prior
tranche
• Compute Incremental senior as well as total senior. Incremental is senior
from this tranche - senior from prior tranche
• Use the Incremental Senior to compute basis for developer allocation as
incremental/senior Incremental percent
• Compute the developer amount as the basis x Developer percent

• Re-start from point 4 for incremental tranche

177
 Sensitivity Analysis of Different Structures
• Once the IRR's are computed for the senior and subordinated
tranches, sensitivity analysis can be performed to evaluate
both the senior and developer return assuming alternative
levels of income.
• In the first case there is a relative moderate increase in the
developer IRR relative to the sponsor IRR. The sensitivity
analysis is shown on the next slide.
• You can compare the first and last IRR's on the table for
different structures.
• In the third case there is more variability in the developer
IRR than in the other cases and at the same time there is less
variability in sponsor IRR than the other cases.
• The graphs demonstrate that you can come with all sorts of
structures to separate the risk.

178
 Moderate Incentive Structure
• This graph comes from a data table made
with VBA

179
 Low Incentive Structure
• This graph comes from a data table made
with VBA

180
 More Extreme Incentive Structure
• This graph comes from a data table made
with VBA

181
Evaluation of Operating Projects
 Updating Investments with Actual Data - Introduction
• Evaluating actual results compared to the financial model is addressed
in this section.
• Issues include:
• Presenting actual and forecast results effectively;
• Adjusting for actual results during the construction period; and,
• Using economic depreciation to gauge the performance of a
project.
• A project finance model becomes obsolete the day after the plant
begins operations. After you receive historic data, you should prepare
new forecasts from the actual results. Updating forecasts of a project is
more like corporate finance modelling
• If you update capital expenditures and debt draws before the COD, the
subjects are more complex and can give rise to difficult issues of
circularity. After COD, the complex issues involves how to measure
performance using economic depreciation rather than tax or accounting
depreciation.

183
 Example of Updating the Model

• The process of evaluating actual results involves

presentation and involves efficiently putting data together.
In addition there is a bit of forecasting involved. I have
made a very simple to illustrate this process. In the
screenshot below I use a small piece of a financial model to
get started. Items in this model will be compared to actual
data and the new forecast.

184
 Put Historic Data in a Separate Place
• I suggest you put the actual data and perhaps the
budget data in yet another sheet. If the titles are
different you may have to do a little work putting
a sheet that has a master title list. In this case I
assumed that the titles are the same. The acutals
are represented in the sheet below.

•.
185
 Use an Historic Switch
• The next task is to compare the actuals and the forecast.
The key to do this is to use the magic HISTORIC
SWITCH. This allows you to compare actuals to forecast
and it allows you to update a forecast in an automated
manner. You can use the AVREAGEIF with the forecast
switch and use the historic average to get the forecast.
You can also apply all sorts of forecasts with regression,
exponential smoothing etc. You can also apply
conditional formatting to show what is the history and
what is the forecast. To do this you should use the NEW
RULE and USE FORMULA in the conditional formatting.
This is illustrated in the video below. An illustration of
the HISTORIC SWITCH and conditional formatting is
shown in the screenshot below.

186
 Computing Theoretical Depreciation
• Theoretical depreciation is not used, but it can
address various problems with the IRR.
• To compute the theoretical depreciation, you
can begin thinking about a situation with
constant cash flows or gradually increasing cash
flows that grow at a constant rate. For these
situations, you can use the PMT function to
evaluate the economic depreciation.
• For other cases, you can use a series of goal
seeks to find the change in the NPV of the
project over time.

187
 Adding Actuals to a Model
Building a Model During Construction Period without
Actuals that is flexible for pro-rata and equity first
Difference between adding actuals in pre-COD and
post COD.
Quarterly summary, user defined function and actuals.

Use of Historic Switch and Different Formulas in

History.
Remaining values and automatic updates.

188
What Not to Do
I have seen models where people manually update
the models with actual data by typing over the model
values.

This presentation works through how to avoid this

issue and automate updating of the models with
actual.

Some of the concepts such as incorporating a historic

switch are quite simple. Other concepts such as
adjusting the circular reference function are difficult.

189
 Remaining Construction
70.00

60.00

Illustration of Revised 50.00

40.00

Construction Expenditure
30.00

20.00

10.00

0.00

• The actual and revised line (the blue Actual and Revised Construction Forecast
Original Foreast without Historic Adjustment

lines) demonstrate how the forecasted

construction expenditures are adjusted
so that the total (underneath the
integral of the lines) is the total EPC Remaining Construction

cost.
50.00

45.00

40.00

35.00

30.00

25.00

20.00

15.00

10.00

5.00

0.00

Actual and Revised Construction Forecast

Original Foreast without Historic Adjustment
Formulas for Adjusting
Construction Expenditures
Original S-Curve

• Multiplied by

TRUE/FALSE Switch

• Divided by

SUMPRODUCT of S-Curve over Projected Construction Period

• Multiplied by

Remaining Construction

191
Formula for Remaining
Construction

Begin with Total Opening Balance of

EPC Cost Work-in-Progress
• Subtract • Hold Constant in Last
Historic Year
• The amount of construction expenditures during the historic actual period is the difference in the
balance sheet of work in progress.
• The future amount of work in progress is the prior balance plus projected construction.

Historic Construction
193
 Actual and Projected Debt Draws
45.00

40.00

Illustration of Debt
35.00

30.00

25.00

Balance Adjustment 20.00

15.00

10.00

5.00

• The actual and revised amount of debt 0.00

draws are shown on the blue lines in

Actual and Revised Debt Draw Forecast Original Foreast without Historic Adjustment

two different assumed scenarios with

respect to actual debt.
• The projected debt is adjusted so that 60.00
Actual and Projected Debt Draws

the total amount of the debt is 50.00

40.00

30.00

20.00

10.00

0.00

Actual and Revised Debt Draw Forecast Original Foreast without Historic Adjustment

194
 Simple Example Illustration of Remaining Funding and
Remaining Debt

• This example
demonstrates how
the total funding
and the remaining
debt at the date
after the end of the
historic period.
• The key is
computing the
adjusted debt
percent.

195
 Remaining Debt Funding and Remaining Funding

• Remaining debt is the amount of the debt

commitment computed in the summary
sources and uses less the opening balance of
the debt from the balance sheet.
• Remaining debt is the total project cost less the
amount already funded (where funding
includes IDC, fees, DSRA and other items).
• Circular references are a big deal in this
calculation as the total project cost and the
total debt commitment is driven by the debt
size itself.
196
 Formulas for Adjusting Debt Draws to Meet Debt to Capital
Criteria

• Illustration of the computation of debt

funding percent after the historic period from
remaining debt and remaining funding.

197
Session 5: End-to-end Model with
No Debt and Financial Statements

198
 Philosophy of Setting Up Model to Balance Sheet
• Structure model with multiple cash flow statements, income statement and balance
sheet
• Include Summary Sources and Uses even though it will not be necessary until debt
is added
• Set-up pre-COD cash flow statement with funding needs – cash, and funding
sources.
• Pre-cod income net of tax is a source of cash
• Model Profit and Loss Statement
• Include pre-COD income
• Include depreciation
• Compute Net Income
• Make Cash Flow Waterfall after COD
• Do not double count items in construction section
• Last line is the dividends
• Compute Equity Balance
• Equity Funding from Pre-COD cash flow
• Net Income from P&L
• Dividends from Cash Flow Waterfall
• Put together Balance Sheet (and Consolidated Cash Flow if you want)
 Include development fees
and development costs.
Structure like balance sheet Could include gain on
development fee detailed
models

Summary
Sources
Include working capital pre- Income less taxes is on the
COD as well as EBITDA and equity side of the balance
taxes pre-COD sheet like common equity

and Uses
Use SUMIF with
TRUE/FALSE and entire rows The last line of the summary
to find EBITDA, Working sources and uses statement
Capital and Equity over the is equity contributions
Construction Period
 Example of Using SUMIF with TRUE/FALSE in the Summary
Sources and Uses

• Note how the entire row is selected and then

the TRUE is the criteria. The sum row is the
final row. Use for pre-COD EBITDA
 Cash Flow Pre-COD – Uses and Sources of Funds, How do you
get the money into the project

• Understand that there are two cash flow

statements
 Profit and Loss Statement
• Once have depreciation you can compute the
P&L
 Cash Flow After COD
• Note how the cash flow waterfall is a second
cash flow statement that begins after COD.
The P&L does not have this distinction.
 Model Verification
• Use TRUE/FALSE (or 1/0) together with
AND or PRODUCT
• Can overdo the checks
• For balance sheet, the balance at the end of
the life should go to zero as well as the assets
equaling liabilities in each period.
• While working on the model, put the balance
sheet test at the top.
 Balance Sheet
• All Balance Sheet items should come from
totals – no calculations other than sums
• Not that does not balance in example.
Advantages of Putting Balance Sheet in Early
• After basic balance sheet balances, you can
add individual items that you add
subsequently like debt, IDC, fees, MRA,
DSRA, cash sweeps.
• When the balance sheet does not balance,
search for the difference number. If you
begin and do this one by one it will be easier.
 Examples of Bad Balance Sheet From Forcing Balance Sheet

• Worst Error is forcing the balance sheet to

balance with a cash account
• Just about as bad is not putting a balance
sheet in the model. This is quite common
 Problems with Balance Sheet – Putting Calculations in Balance
Sheet Rather than Using Balances

• Every item in balance sheet should already be

defined.
A Pretty Good Model, But Formulas in Balance Sheet

• Put balance sheet on core model page

Section 6: Adding Debt to Model – Debt
Draws with Given Amount of Debt
Financing Assumptions and Equations – Five Separate Items

• Building a model from A-Z – Financing Assumptions and

Equations
• Five Parts
• Debt Size
• Debt Funding
• Debt Repayment
• Interest and Fees
• Credit Protections
• Model Sections
• Uses and Sources Summary
• Funding: Uses and Sources Period by Period
• Debt Schedule: Repayment
• Debt Schedule: Interest and Fees
• Cash Flow Waterfall and Equity Cash Flow
 Understanding the Difference between Structuring and Risk
Analysis

• Risk Analysis
• Debt structure given. Use the model to evaluate
downside cases and whether debt can be repaid
using DSCR, LLCR and PLCR. Could also use
to evaluate upside from re-financing.
• Structuring Analysis
• Determine the debt size, the debt repayment
structure, the interest rates and debt protections
from evaluating the consulting reports and
engineering analysis of the project.
 Term Sheet and Debt Size
• Debt Size

• For this exercise, assume that the debt size is

given as a fixed amount. Debt sizing will be
addressed in a subsequent chapter.

214
 Debt Size Inputs and Structuring versus Risk Analysis

• Later, we will develop alternative ways to measure

debt size.
• If the model is used for structuring, alternative and
flexible methods should be used.
• If the model is used for risk analysis, then you can
have a simple fixed input for the amount of the debt.
 Debt Funding from Term Sheet
• Term sheet on funding from Pro-rata or
Equity Up-front
 Flexible Funding Inputs and Capitalised Interest

• To make the model flexible, you can make an input for the
percentage of equity funded up-front.
• Funding is affected by whether the banks allow you to
capitalize interest and fees. Capitalised Interest means that
you do not pay interest to the bank during construction, but
it rather accrues to the debt balance.
• Note the difference between capitalised interest and IDC
or Interest During Construction which is sometimes also
called capitalised interest. It is called capitalised interest.
Capitalised interest from an accounting standpoint just
means that the interest (whether paid or accrued to the debt
balance) is not accounted for as an expense, but rather as a
part of the plant which will be depreciated after the COD.
 Example of Inputs for Funding
• The inputs for funding seem easy, but they
can cause a lot of pain in modelling.

From term sheet:

 Basic Repayment with Fixed Scheduled
• After the debt size and funding are established,
the inputs for repayment come next.
• Subsequent sections will address sculpting and
how to structure the repayment of debt. For
now, we are structuring the location of debt in
a model and we assume that the repayment
profile is given.
• If the debt is used for risk analysis rather than
structuring debt, the debt repayment will be
given.
 Term Sheet Inputs for Repayment of Debt
• Debt repayment involves tenor and method.
Details of this will be addressed in the later
sections.
 Inputs for Maturity and Pre-Determined Repayment

• In this case, use MIN function for the smallest

maturity
• Also use MIN with opening accumulated balance
to cap the amount (use 1-accumulated balance)
and accumulate with calculation itself.
 Interest Rates in Term Sheet – Base Rate (LIBOR)

• Interest rates include a base rate and a credit

spread.

• There is also a hedging requirement.

 Capitalisation of Interest and Fees
• The up-front fee and the commitment fee can
also be defined as below.
Credit Spreads in the Term Sheet – The First Thing to Look For

• These margins are extremely high. They are

increasing to encourage re-financing. Use of
these margins in the base case is irrelevant.
Inputs for Interest Rate and Credit Spread – Include Sensitivities
in Separate Sheet

• Put the credit spread from the time sheet even

though it is inconsistent with the base case.
• Will add alternative scenarios in scenario
page.
Step 1 - Entering Debt Into Model
• The first step is
adding rows to the
summary sources and
uses of funds
statement. Include
IDC, Fees and Debt
Amount as shown
below.
• Note that IDC and
Fees are not entered
yet and Capitalised
Interest is part of
funding sources.

226
 • Include development fees as uses of
funds even though may not be cash
Notion of outflow
• But, include income from
Including development fees as source of funds
Non-Cash • Include IDC and fees as uses of funds
whether they are paid or capitalised
Items in • If they are capitalised, include them
Sources as sources of funds and also make
sure the debt balance is reduced by
and Uses the capitalised amounts if the fixed
debt is directly or indirectly input.
 Equity Adjustments
• Often, income during construction and development fee
(income) are included as part of equity funding even
though these items do not involve cash that has really
been funded by equity holders.
• The first step should be to count how much is in the
equity funding obligation per the loan agreement. This
can be after the sources and uses of funds statement.
• The second step is to make a detailed period by period
funding analysis that is consistent with the equity
funding requirement.
• The final step is re-adjusting the funding analysis to
determine the actual cash that equity holders contributed
in order to compute equity IRR.
Step 2: After the Sources and Uses, Compute Cash Debt Issued
and Up-Front Equity

• In addition to the sources and uses, compute

the up-front equity from the total equity
issued and the cash debt funded adjusted for
capitalised interest and fees.
 Step 3: Funding Needs and Sources
• Include capitalised interest and development
fees as both a source and use like in the
summary sources and uses.
 Step 4: Compute the Equity Balance and the Remaining Equity to
Find Debt and Equity Draws

• Use the MIN function with the funding needs

and the remaining equity balance that has not
been drawn.
 Step 5: Debt Schedule, Fees and IDC

• The last step in the funding analysis is to put

together the debt schedule.

232
 Tricky Issues with Equity
• Typical is to treat pre-COD income as equity.
This means that the equity that is committed
includes the pre-COD income.
• Development fees are also typically included
in the equity balance.
• For debt, if interest and/or fees are
capitalised, then you need to distinguish
between debt that funds cash and debt that
must be repaid from the interest
capitalisation.
Leave Out the IDC, Interest and Fees from Financials and Check
the Balance Sheet

• There will be circular references from IDC

and fees, but make sure everything is set-up
first.
• Do not include IDC and fees in the sources
and uses statement.
• Include debt in the balance sheet and check
to make sure the balance sheet balances.
 Why Make Life So Painful with Different Sheets

• Press CNTL ~ to get formulas

Don’t Make the Cash Flow Waterfall Painful with Meaningless
Accounts

• These things mess up the model even if some

classes recommend

236
Section 7: First Circular Reference from
Debt – Funding with Fixed Debt and
Problems with IDC and Fees
Skip Over the Copy and Past Macro or the Iteration Button and
Move to UDF

• Try and get straight to the point

• Do not waste time with copy and paste macro
• Do not waste time explaining technical
details of the UDF function
• Illustrate use of the function first
• Demonstrate advantage of function with goal
seek
 Philosophy
• Open your mind and try different ways
• Don’t give up when something does not work
perfectly
• Redundancy is a good way to verify things
• Apply FAST and efficiency principles in
other aspects of modelling
 With IDC and Fees, Finally, a Circular Reference

• If equity is funded up-

front, then (even if debt is
given) there can be a
circular reference. The
amount of equity is driven
in part by IDC, and fees.
• But the IDC and fees are
driven by the timing of
debt.
• The timing of debt is
driven by the project cost
and up-front equity.
• …………………
 Excuses for Copy and Paste Macro Instead of a User Defined
Function

• The UDF is too complex

• The auditors will not understand it
• There are too many arguments in a function
• You cannot verify the function
• Writing the function from scratch is very
difficult
 This is like Africa Excuses
• Make UDF same as the excel – as complex as the
excel – no more, no less
• The UDF is in fact an auditing tool – if the UDF is
not consistent with the excel, there is a good chance
that there is a mistake in excel. Further, the UDF
gives almost the same outputs and can be tested like
a copy and paste macro.
• You can verify the function by printing out all of the
detail.
• You do not have to re-write the function if it is
written in a transparent way. Further, you can write
it so it can be easily modified.
 Template Issues
• I generally do not like the idea of a template
model.
• Not flexible because difficult to change
• Not structured and can mess up your structure
• Not transparent because cannot understand all of
the equations
• Attempt to get around these issues with
flexible and as transparent as possible
structured
 Other Advantages of Template
• Flexibility
• Inherent ability to test structuring sensitivities
that are so difficult with copy and paste macros
• Accuracy
• Built in test for model. Like Airplane with two
engines – have two tests instead of one.
• Built in auditing for the model
 Copying Template
• Copy the template with tables and
instructions
• Base table (optional)
• Debt Options Table
• Debt Structure Table
 Notion of Optional Variables
• You do have to When an item is not bold it is an
enter any of the optional item. You can skip over
variables such as items and the process is more
the development flexible.
switch, working
capital changes etc.
• These variables are
assigned to FALSE
or zero in the
function.
• The optional
variables hopefully
allow a more
flexible process.
246
 Incorporating the UDF in Your File
• Copy the UDF with ALT-
F8 like you do for other
situations (e.g.
Interpolated,
Depreciation).
• Set-up a Block as the
Output for the Function
• Copy any number to block
and then enter the function
• Use the SHIFT, CNTL,
ENTER to put the
function together
 Implementing the UDF
• Implement the UDF with entire rows and
columns
 FAST Principles and UDF Functions
• Try to make flexible to handle alternative
structuring and debt techniques as well as
multiple debt issues.
• Try to make flexible so it is not difficult to add
different calculations to the UDF
• Make tests to verify the accuracy of the
calculations
• Make structured with different parts of the
program
• Try and document calculations and code so you
can understand techniques
249
 Difficulties with Function
• The user-defined function requires you to
define each input.
• There is a limit on the number of variables
that you can read into a user-defined
function.
• If there are many debt issues, you will run
out of variables.
• To resolve the issue, I use a table where you
must re-enter the debt data.
• This means that work is required to re-structure
and define inputs.
Finding the Solution for Too Many Variables
• Just about to give up.
• Horrible function with too many variables.
• Found alternative solution with table at the
very bottom of discussion.
 Reading In Tables
• I have put in a general table for issues
associated with debt and specific issue by
issue items. The table below illustrates
general items. Note there are some scalars
and some time series variables.
 Reading in Debt Issues
• There can be a number of different
parameters with debt facilities and there can
be many different debt issues. These can be
modified.
 Link Inputs and Items in Model to Table
• Part 1: Debt Options
• Development Cost
• Up-front Equity Percent
• Total Senior Debt
• DSRA parameters
• MRA Parameters
• Part 2: Debt Features by Issue
• Percent of Senior/Sub Debt
• Interest Rate
• Up-Front and Commitment Fee
• Repayment
 Mimic Funding Calculations in the UDF
• Start with project uses and sources – equity is
the remainder in the sources (do not know the
IDC, fees, DSRA yet)
• Move to financing during construction and
mimic calculations with option for pro-rata or
equity up-front
• Next move to repayment with IDC
depreciation and sculpting
• As with excel sheet, do not use un-necessary
functions and techniques
 Illustration of the Function
• You can work through the function as it will
be very similar to the file we have been using
 Diagram of Testing Process
• Use key variable like project cost
• Remember the project cost from last iteration
• Define as last project cost
Assign last project
cost to project cost

Test if new
Re-do all of
project cost is Yes, stop
the
equal to last
calculations
project cost

No, continue
 Illustration of Iteration Test
• Make sure do not pass in first iteration.
 Structure of UDF
• Read in variables with option to leave out
items.
• Define the timing and the column numbers
• Begin with the uses of funds (even though
you do not know IDC etc.)
• Then loop through the construction period
• Define IDC etc. from opening balance
• Define the funding needs
• Work through pro-rata and equity first funding

259
 Getting Data into the Function
• Attempt to be flexible
• Different debt sizing
• Different funding
• Different repayment
• Different interest rates
• Multiple debt issues
• Alternative tax rates
• Alternative definition of CFADS
• Allow balloon payments
 Issue of Reporting in Function
• The typical application of a function is to
find a single variable or an array of variables.
In this function I print out a whole lot of
variables so you can document problems.
When the array index
is Count, the entire
row is presented
 Iteration Button in Some Models
• Example of circular reference in model from
a really really big bank.
Bad Alternative to UDF – Macro with Solver
• Example of using solver with circular
reference.

This is really not

impressive (even
though I used to do
it).
 Addiction to Macro Buttons
• To many macro buttons ruin transparency
and flexibility
 Example of Long Copy and Paste
• Copy and paste becomes longer and longer
Macro for Copy and Paste
 Section 8: Cash Flow Waterfall –
Sweeps, Traps, Defaults, LLCR and
PLCR
 Repayment in Risk Analysis
• Another chapter will address debt repayment and
sculpting in the context of structuring analysis.
• The fundamental aspect of risk analysis in this
chapter is connection of debt schedule to cash flow
statement. This connection can be in the form of:
• Cash Sweep
• Default and Repayment of Default
• Cash Traps with Covenants
• Cash used from MRA and DSRA’s
• The mechanics other than the MRA and the DSRA
are addressed in this context.
 Examples of Sweep in Term Sheet
• Cash sweeps are at the bottom of the
waterfall and can employ complicated
formulas. Note the term “cash flow available
for distribution.”
 Example of Cash Trap in Term Sheet
• A cash trap uses the DSCR to limit
distributions. Note that after monies are
trapped in a reserve account, they are used to
pre-pay debt in the same way as the cash
sweep.
 DSRA and MRA
• The DSRA and MRA are cash accounts that
are used to further protect creditors.
 Modelling Issues
• First, get the repayment of debt using a fixed
repayment schedule
• Make sure the closing balance goes to zero
• Scheduled repayment in cash flow and
balance the balance sheet.
 Cash Flow Waterfall Modelling Issues
• Step 1: Create sub-totals in the cash flow
• Step 2: Use the MAX and MIN functions in
the cash flow statement to cap the cash flow
(the MIN function) and to do one thing if
cash flow is negative and another thing if
cash flow is positive.
• Step 3: Connect various amounts in cash flow
statement with the debt (or MRA/DSRA)
schedules
 Illustration of Cash Flow
• Include sub-totals so can program MIN/MAX
 DSRA Calculations
• First, compute the repayments using PPMT
function or simple level repayment.
• Next, calculate the debt service and the
required debt service reserve that is either the
basis for the DSRA or the letter of credit that is
the basis for the DSRA.
• If the DSRA account is funded with cash (i.e.
not an LC), then compute the funding during
the pre-COD and the post-COD period.
• I do the same thing in the UDF function using a
second and third loop
 Circular References and DSRA
• Three types of circular references from the DSRA:
• 1. With cash sweep because:
• Cash sweep affects interest expenses and debt service
• DSRA driven by debt service
• DSRA affects the cash sweep
• 2. If the debt level is driven by project cost because:
• DSRA affects project cost
• Project cost drives debt
• Debt drives the DSRA
• 3. Pro-rata funding
• DSRA is part of funding needs
• Funding needs drive the debt and equity issuances and IDC
• IDC drives the debt funding available

276
Resolution of Cash Sweep
Problem

Method 2: Adjust interest

Two ways to resolve with expense for debt service to
Method 1: DSRA after Cash
economic logic and without include only interest from
Sweep in Waterfall
any UDF, copy and paste etc. next year’s scheduled debt
service

Separate interest expense to

compute DSRA requirement
Now the cash sweep is not a where interest expense is
function of the DSRA computed from earlier opening
balance and scheduled (but not
actual repayment)
Illustration of Debt Service without Prospective Interest on Cash
Sweep

• Go backwards and re-compute interest on

previous debt balance that does not include
the effects of the cash sweep.
 Illustration of Method Moving Cash Flow Items

• You could cheat and put the DSRA below the

cash sweep. In this case the interest expense
is not affected by the DSRA moves which go
straight to dividends.
Circular Reference with DSRA and Funding
• The funding of the DSRA can cause
problems with circular reference, because:
• The DSRA affects the total funding needs
• The percent of debt is affected by the DSRA
• The DSRA changes when the amount of debt
changes.
• This circular reference can be resolved by:
• The iteration button – disaster
• The copy and paste – even worse
• The UDF function without template
• The UDF template
DSCR, LLCR and PLCR to Gauge
Credit Quality
 Idea of Risk Allocation Matrix and Use of DSCR, PLCR and
LLCR to Measure Break-Even

• Risk allocation matrices will be used to demonstrate how the

DSCR and LLCR can be used to determine acceptable
unmitigated risks:
• The formula:
• break-even cash flow reduction = (DSCR-1)/DSCR.
• Also break-even cash flow over life of loan
• BE reduction = (LLCR-1)/LLCR
• BE reduction for Project Life = (PLCR-1)/PLCR
• Different project finance structures that involve:
• availability payments versus output-based revenues;
• commodity price (merchant) risk;
• traffic or volume risk (pipelines), and
• resource risk (wind, solar and run of river hydro) will be derived.
• For each of the project finance types, an illustrative risk
allocation matrix and project diagram will be developed.

282
 LLCR Problems with Different Maturities
• If interest rates are the same for different debt
issues – even if they change, then the PLCR
can be computed prospectively with the
SUMPRODUCT or SUM method.
• Computing the LLCR is similar, but the
LLCR is different for different debt issues.
 Sub-totals in Cash Flow
• I suggest putting the closing balances in
separate accounts and use sub-totals in the
cash flow.
 Cash Sweeps and Not Connecting the
• Long formulas and not connecting to correct
sheet.
 Enter Simple Debt Structure in Model
• Enter simple debt structure
• Fixed Debt to Capital
• Level Repayments
• Fixed Interest Rate
• Use the MIN function for testing debt repayment
• First, make sources and uses
• Second, make corkscrew
• Third, make simple cash flow
• Fourth, compute the Equity IRR
 Too Complex Formulas in Cash Flow
• Keep in one page and formulas should only
have MAX and/or MIN

287
Section 9: Risk Analysis with Model
– Defaults, LLCR and PLCR
 Fundamental Formulas for Credit in Project Finance for DSCR,
LLCR and PLCR

• DSCR = Cash Flow Available for Debt Service/[Debt Service]

• PLCR = PV(Cash Flow Available for Debt Service)/PV(Debt Service)
• LLCR = PV(Cash Flow Available for Debt Service over loan life)/PV(Debt Service)
• Debt at COD = PV(Debt Service using Debt Interest Rate)
• Therefore,
• PLCR = PV(Cash Flow Available for Debt Service)/Debt - DSRA
• LLCR = PV(Cash Flow Available for Debt Service over loan life)/Debt – DSRA

• Theory
• Minimum DSCR measures probability of default in one year
• LLCR measures coverage over the entire loan life even if project must be re-
structured
• PLCR measures coverage over the entire project life and the value of the tail
DSCR versus LLCR versus PLCR
Level Payment and Tail

Min DSCR < LLCR < PLCR

Sculpting and Tail

DSCR = LLCR < PLCR

Sculpting and No Tail

DSCR = LLCR = PLCR

 Valuation Metrics in Project Finance and
Corporate Finance

• Project Finance Investment • Corporate Finance Valuation

• Equity IRR •P/E Ratio
• Project IRR •EV/EBITDA
• Equity NPV •Projected Dividend and Earnings
• Project NPV •Free Cash Flow
• Project Finance Debt • Corporate Finance Debt
• DSCR •Times Interest Earned
• LLCR •Debt to EBITDA
• PLCR •Debt to Capital
• Liquidity • Corporate Finance Liquidity
• Debt Service Reserve •Current Ratio; Quick Ratio
 • If there is no interest, taxes or capital expenditures, then the Debt/EBITDA
measures the time to repay the loan.
• Eurotunnel 2003:
• Debt 6,365,028
• EBITDA 298,619
Debt to EBITDA 23.10

Time to Repay • Interest 340,386

and • Capital Expenditures

• Working Capital Change
41,118
2,360
Debt/EBITDA • Taxes 0
Free Operating Cash Flow to Debt (61,850)
Debt to Free Operating Cash Flow Infinity

Implication: Debt to EBITDA does not really measure how long it takes to
repay debt
 • Formulas for Break-Even: Say that you want
to know how big the DSCR should be to
cover for an availability payment that could
be reduced by 20%.
• The formulas below are for DSCR; you could
also use LLCR and PLCR
You Can Go the • Break-even cash flow = (DSCR-1)/DSCR
Other Way to • BE = (DSCR-1)/DSCR
Find the DSCR • BE x DSCR = DSCR – 1
• DSCR – BE x DSCR = 1
• DSCR * (1-BE) = 1
• DSCR = 1/(1-BE) or 1/.8 or 1.25
• Note: Be careful with fixed costs. If an oil
project has fixed costs you have to make a
more complex formula
293
 Compute DSCR, LLCR and PLCR
• Assume cash flow available for debt service
is the EBITDA
 Issue with Prospective LLCR and DSCR
• The problem is that you need to continue re-
starting the PV factor in each prospective
period.
• You could do this with a big matrix but this
would take up a lot of space and be painful.
• Instead, can make a function that moves
forward.
• If the interest rate does not change, this is not
necessary.
 Complex LLCR in Models
• LLCR and effective interest rate
Example of Using IF statements in Waterfall
• How could anybody understand this thing
with XNPV inside the formula and then
raised to a power.
Section 9: DSRA and MRA
 Selected Issues with DSRA and MRA
• Fundamental set-up not so difficult
• DSRA issues together with a cash flow sweep
• MRA issues with taxes
 Section 10: Debt Sculpting and
Circular References from CFADS
Session 10: Debt Size from DSCR
or Debt to Capital
 Different Debt Sizing Options – Need to Work Through
Sculpting
Case 1: ' Case 1 is when senior debt is given; senior fixed_total defined in funding
If senior_fixed_total <> 0 Then
LLCR = pv_cfads / senior_fixed_total
End If ' Only if did not enter fixed debt
If LLCR = 0 Then LLCR = 1
DSCR_Applied = LLCR ' DSCR_Applied is used in sculpting

Case 2: ' Case 2 is where the debt comes from a debt percentage
If senior_debt <> 0 Then
LLCR = pv_cfads / senior_debt
End If ' Only if did not enter fixed debt
If LLCR = 0 Then LLCR = 1
DSCR_Applied = LLCR

Case 3: ' Case 3 is from the input DSCR for sculpting

senior_debt = pv_debt_service
DSCR_Applied = DSCR_Input

Case 4: ' Case 4 is the tricky one where different constraints are used
If senior_debt <> 0 Then
LLCR = pv_cfads / senior_debt
End If ' Only if did not enter fixed debt
If LLCR = 0 Then LLCR = 1
DSCR_Applied = WorksheetFunction.Max(LLCR, DSCR_Input)
End Select

302
 General Idea of Optimising Project Finance Debt

• The general idea the project finance debt falls

somewhere around BBB- and how credit
spreads are driven by the probability that the
DSCR will fall below 1.0.

303
 Examples of Target DSCR for Alternative Industries

The DSCR standards or benchmarks should have

an footnote that says “to meet BBB- criteria”
Session 11: Identifying risks when
building and Analysing a model
 Presentation
• No Equity or Debt IRR
 Sensitivity Analysis
• Why use the OFFSET
 Show where Sensitivity Comes From
• The input sheet can become a bit mixed-up.
Either use range names or show sheet colour.

Some are
inputs and
others come
from different
places
 Swaps and Average Life
• The average life of debt measures something like a
simple payback. It does not depend on the interest rate
and it measures the time at which the debt will be paid
off in half.
• For 10 year debt with equal installment repayments, the
average life would be 5.
• In this case you could make 10 swaps, each with a
different swap maturity.
• To approximate this you could use a 5 year swap.
• The average life can be computed two ways:
• ∑ Repayment x Period of Repayment/Total Debt
• or
• ∑ Debt Balance/Total Debt
 Use of Interest Rate File and Forward Interest Rates

• To make forward
interest rate 3 Month Treasury [Final Value 1.23 ] vs
projections, you could 6.00
6 Month Treasury [Final Value 1.36 ]

theoretically compute
6 Month Treasury
3 Month Treasury
5.00

forward rates. 4.00

3 Month Treasury
3.00

2.00

• Take one example 1.00

0.00

with one and two year

1-Dec-02
1-May-03
1-Oct-03
1-Mar-04
1-Aug-04
1-Jan-05
1-Jun-05
1-Nov-05
1-Apr-06
1-Sep-06
1-Feb-07
1-Jul-07
1-Dec-07
1-May-08
1-Oct-08
1-Mar-09
1-Aug-09
1-Jan-10
1-Jun-10
1-Nov-10
1-Apr-11
1-Sep-11
1-Feb-12
1-Jul-12
1-Dec-12
1-May-13
1-Oct-13
1-Mar-14
1-Aug-14
1-Jan-15
1-Jun-15
1-Nov-15
1-Apr-16
1-Sep-16
1-Feb-17
1-Jul-17
1-Dec-17
yields. Begin with
interest rates from the
interest rate file.
 Short-term interest rates have risen, but the
Compute longer term rates have remained more
constant.
Implied
Forward Rates
5

• To compute forward rate if have 4

one year and two year yield: 3

• One year yield = 1yr is given 2

• Two year yield = 2yr is given 1

• Implied forward rate is from: 0

1-May-05

1-Nov-05

1-May-06

1-Nov-06

1-May-07

1-Nov-07

1-May-08

1-Nov-08

1-May-09

1-Nov-09

1-May-10

1-Nov-10

1-May-11

1-Nov-11

1-May-12

1-Nov-12

1-May-13

1-Nov-13

1-May-14

1-Nov-14

1-May-15

1-Nov-15

1-May-16

1-Nov-16

1-May-17

1-Nov-17
1-Feb-06

1-Feb-07

1-Feb-08

1-Feb-09

1-Feb-10

1-Feb-11

1-Feb-12

1-Feb-13

1-Feb-14

1-Feb-15

1-Feb-16

1-Feb-17
1-Aug-05

1-Aug-06

1-Aug-07

1-Aug-08

1-Aug-09

1-Aug-10

1-Aug-11

1-Aug-12

1-Aug-13

1-Aug-14

1-Aug-15

1-Aug-16

1-Aug-17
• Value = Interest 1/(1+1yr) + 3 Month Treasury 6 Month Treasury 1 Year Treasury 2 Year Treasury

Interest2/(1+2fw)^2 + 5 Year Treasury

30 Year Treasury
7 Year Treasury
#N/A
10 Year Treasury 20 Year Treasury

Par/(1+2fw)^2
• Value = Par x 2yr/(1+1yr) + Par x
2yr/(1+2fw)^2 + Par(1+2fw)^2
Risks in Electricity Generation Operation
• Capital Cost
• Operating Cost
• Capacity Factor and Resource Analysis
• Availability
• Efficiency and Heat Rate

312
 Other Risks
• Risks of uneconomic plants
• Computation of LCOE
• Evaluation of political risks
• Benchmarking capital and operating costs with
model
• Financial and economic risks
• Risks of exchange rate changes
• Financial risks in interest rate changes
 Benchmarking Costs
• General (and not very good) sources of cost
comparison:
• International Energy Agency
• Energy Information Agency
• Lazard
• Difficulties
• Changes in cost
• Regional cost
• Units of measurement (wind example)
• Use of models – finding information
 Session 10: How to produce an
analysis report for communication
 Essentials in Summary Page
• What is essential and not essential in summarising project
finance
• Displaying and understanding key operating drivers
• Capital Cost per kW
• Levelized cost per kWh
• Levelized cost of fixed and variable O&M
• Plant availability
• Plant efficiency
• Capacity Factor and (resource in renewable)
• Understanding and presenting sources and uses to paint picture
before construction
• Understanding and presenting CFADS versus debt service after
construction
• Adding spinner boxes and drop down boxes for effective display
of sensitivity and scenario analysis
 Electricity Cost Drivers
• Renewable
• Cost per kW
• O&M cost per kW-year fixed
• Including and excluding other costs
• Capacity factor or yield (kWh/kW)
• Carrying Charge
• Thermal
• Cost per kW
• Heat Rate (energy content in (BTU)/output (kWh)
• Fuel price per energy content
• Fixed O&M cost per kW-year
• Variable O&M cost per MWH
• Availability Factor
• Carrying Charge
• Should be presented, but generally are not
 Use of LCOE for Comparing Items
• General LCOE formula:
• Nominal LCOE = NPV(Revenues)/NPV(MWH)
• Real LCOE = NPV(Revenues)/NPV(Real MWH)
• Can use formula this for any item:
• Nominal Fixed O&M = NPV(Fixed
Cost)/NPV(kW)
• Real Fixed O&M = NPV(Fixed Cost)/NPV(Real
kW)
• Variable O&M = NPV(Variable Cost)/NPV(kW)

•.

318
Session 11: Project finance in
Developing Country Context
Effect of Loan Structuring Provisions
on Bidding for Projects
 Effects of Debt Structure on the Bid Price
• The effects of:
• Debt sizing,
• Debt funding
• Debt tenor,
• Debt repayment type, and
• Debt pricing (interest rates and fees)
• Debt Protections

Context of alternative technologies.

Items of a term sheet such as the minimum DSCR,
maximum debt to capital, step-up credit spreads, debt
sculpting, debt funding, DSRA’s, MRA’s and cash sweeps
used to evaluate financial impacts of various financing and
timing issues on the required bid price for a project.

321
 Definition of Capital Intensity
• Capital intensity is not just the amount of
capital spent on a project
• It is the capital relative to operating costs
• It includes the lifetime of the project
• Formula:
• Capital Intensity = Capital/Revenues

322
Illustration of Effects of Debt Structuring on Capital Intensive and
Non-Capital Intensive Projects

323
Alternative Debt Provisions, Bidding and Carrying Charge Rate

324
Effects of Financing on Bid Price – Capital Intensive

Waterfall Chart for Low Cost Solar with Tracker

90

80
74.64

70
(5.78)

60

50 (15.77)

(8.55)
40 (2.18)

30.56
(8.75)
30 (0.57)
(1.43)
(1.05)

20

10

0
High Longer Tenor Higher Debt Lower Sculpting and Reduced IRR - No Taxes - Ballon - 25 Low IRR - Best
Financing - 5 versus 15 Percent - Interest Rate - Inflation 7,50% versus 0,00% versus versus 20 5,50% versus Financing
Cost 68.86 85,00% versus 3,50% versus 42.36 17,00% 25,00% 31.61 7,50% Case
74.64 50,00% 7,00% 33.61 33.04 30.56 30.56
53.09 44.54

325
Effects of Debt Provisions on Fuel Intensive Diesel Technology

Waterfall Chart for Diesel

180

157.91
160
(2.71)

140
(14.46)
(7.84)
(2.21)
120.00
120 (8.41) (0.39) (0.85) (1.04)

100

80

60

40

20

0
High Longer Higher Debt Lower Sculpting Reduced IRR No Taxes - Ballon - 25 Low IRR - Best
Financing Tenor - 5 Percent - Interest Rate and Inflation - 7,50% 0,00% versus 20 5,50% Financing
Cost versus 15 85,00% - 3,50% 130.69 versus versus 121.04 versus Case
157.91 155.20 versus versus 17,00% 25,00% 7,50% 120.00
50,00% 7,00% 122.28 121.89 120.00
140.74 132.90

326
With Good Financing Structure can Achieve Low Costs
Part 12: Interest and Fees: Step-up Credit Spreads,
Swap Rates and Hedging
 Discussion of Interest and Fees
• Consistent with the discussion of debt as having five
components, interest and fees between the time debt
draws occur and debt is fully repaid is the next topic.
• Interest rates consist of credit spread and base rate.
• Debt IRR is the money the lenders receive including
fees, relative to the amount funded by lenders
• Credit spreads can include step-ups – why they are
present in many transactions and what they mean in
terms of re-financing.
• Loan agreements often require hedging and interest
rate swaps.

329
 Use of Floating Rate Debt
• Project Financings are generally funded on a floating-rate
basis due to the necessity for:
• Flexibility in the timing of draw downs
• Flexibility in early repayment.
• Floating rates computed as the LIBOR average for the prior
six months.
• 86% of Project Finance Loans are floating rate.
• But the floating rate loans can be fixed with interest rate
swaps.
• Because of flexibility in take downs and repayments, there
would be significant interest rate risk with fixed rate
transactions.
• Extension risk
• Contraction risk
 Swap Settlements
• Bank financing in project finance generally uses floating interest
rates rather than fixed rates (e.g. LIBOR plus 150-200 basis
points).
• Because floating rate financing can create risks particularly in
projects with tight debt service cover such as PFI, projects often
use interest rate swaps to convert floating rates to fixed rates.
• Swaps that convert floating rate to fixed rate debt involve:
• Establishing a notional amount that corresponds to the face amount of
the loan;
• Paying interest on the floating rate loans;
• Receiving settlements on the swap if the floating interest rate rises so
that the effective interest rate is fixed;
• Paying settlements on the swap if the floating interest rate declines so
that the effective interest rate is fixed.
• The net value of the swap is generally zero when the swap is
established.
Example of Pricing and Changing Credit Spreads
Step-up credit spreads encourage re-
financing. To not assume re-financing in
a base case or upside case in
inconsistent with the whole idea of
increasing rates.
Part 12: Credit Enhancement: DSRA, MRA, Cash
Flow Sweeps and Covenants
 Financial Enhancements – Alternative Definition

• Cash flow capture (dividend lock-up, cash trap) covenants

• Cause debt to be re-paid early or debt service reserves to be built-up
if debt service coverage ratios are low. Bad time covenant.
• Cash flow sweep covenants
• Cause debt to be re-paid early or debt service reserves to be built-up
if cash flow is high (or low). Good-time covenant.
• Debt service reserves
• Assure debt service can be paid if market prices or other risks cause
cash flow to be low for an extended period of time.
• Subordinated debt and mezzanine finance
• Protects the cash flow coverage of senior debt instruments.
• Contingent equity or sponsor guarantees
• Provide for additional equity funding in downside cases.
 Example of Covenants
• DSCR Target
• Minimum Senior DSCR of 1.20x in Base Case
• Lock-up Covenant
• Minimum Senior DSCR for the previous 12
months to be greater than 1.10x for distribution
• Event of Default
• Minimum Senior DSCR of 1.05x
• Standard Covenant
• Senior Debt not to exceed 80% of the total
project costs
 What Covenants Cannot and Can Do
• Covenants cannot increase the operating cash
flow of a project
• Covenants cannot make a project that does
not have enough cash flow to avoid default
• Covenants cannot make a bad project into a
good project
• Covenants can change the timing of
dividends
• Covenants and DSCR can force liquidity into
a project
Investors Need Some Dividends Before All Debt is Paid Off

• The timing of debt service (i.e. loan interest payments

and principal repayments) is one of the biggest factors
that drives the rate of return for equity holders in a
project. If the debt service is structured to allow no
dividends until all debt is paid, return will be lower. This
will generally be unacceptable to sponsors.
• The faster investors in a project are paid dividends, the better
their rate of return.
• Investors therefore do not wish cash flow from operations of
the project to be devoted to lenders at the expense of these
dividends.
• Lenders, on the other hand, generally wish to be repaid as
rapidly as possible. Striking a reasonable balance between
these conflicting demands is an important part of loan
negotiations.
 Covenants and Structural Enhancements Cannot Make a Bad
Project into a Good Project

• The most important aspect of the underwriting process is determining

whether the plant is economically sound. This means that the cost
structure and the technology of the plant must be viable.
• However, once a plant is determined to be economically viable, the
credit quality of a transaction can be enhanced by various structural
features – covenants, debt service reserves, liquidation damages,
subordinated debt, contingent equity etc. The potential for structural
enhancements to improve the credit quality of a transaction is
described in the statement by Standard and Poor’s below:
• Project structure does not mitigate risk that a marginally economic project
presents to lenders; structure in and of itself cannot elevate the debt rating of
a fundamentally weak project to investment-grade levels. On the other hand,
more creditworthy projects will feature covenants designed to identify
changing market conditions and trigger cash trapping features to project
lenders during occasional stress periods.
 Covenants and Cash Flow Waterfall

• A cash flow waterfall defines the priority

of uses of cash flow that is received for a
project.
• The important part of a cash flow
waterfall is what happens if there is not
enough cash flow to pay all expenses,
debt service and debt service reserve
requirements. It is the area after senior
debt payments and before dividends
• If sufficient cash is available to pay
dividends, the cash flow priority defines
how and when a distribution can be made.
 Modelling of Cash Flow Waterfall
• Set-up Cash Flow Working from EBITDA to CFADS
• Take away senior debt service assuming that debt
service is paid
• Use a lot of sub-totals for cash flow after debt
service, cash flow before default, cash flow before
use of DSRA etc.
• Use MAX(number,0) or Max(-number,0) to test for
what to do when sub-total is positive or negative
• Use MIN(subtotal, opening balance) to limit the
amount of sweep, DSRA use, repayment of default
etc.

340
 Example of Cash Flow Priority
• All revenues accrued on and after the Commercial Operation
Date will be deposited with the Trustee into the Operating
Revenue Account. The Trustee will withdraw amounts on a
monthly basis and make deposits in the following priority, but
only to the extent funds are then available in the Operating
Revenue Account:
• (1) the operations and maintenance expenses for the Project for such month,
subject to certain limitations;
• (2) the Tax Equalization Account
• (3) (A) an amount that will not be less than the amount of interest on the Bonds to
become due on such Interest Payment Date, and (B) an amount that will not be less
than the amount of principal or sinking fund payment to become due on such
principal or sinking fund payment date;
• (4) an amount, if any, sufficient to cause the amount on deposit in the Debt Service
Reserve Account to equal the Debt Service Reserve Account Requirement;
• (5) an amount, if any, sufficient to pay amounts due pursuant to the Working
Capital Facility;
• (6) an amount equal to the balance of the Operating Revenue Account shall be
deposited into the Surplus Account and will be transferred monthly to the
Operating Revenue Account.
 Example of Lock-up and Cash Flow
• Amounts in the Surplus Account will be annually transferred
on the first business day of January to the Distribution
Account and distributed to the Partnership within 90 days
thereafter if:
• the Debt Service Coverage Ratio for the Project is equal to or
exceeds 1.20 to 1.00 for the calendar year preceding the
distribution date and is projected to be equal to or exceed 1.20 to
1.00 for the current calendar year;
• the Partnership does not have knowledge, or could not reasonably be
expected to have knowledge, of the occurrence and continuance of
an event of default …;
• Working Capital Facility and the Waste Supply Support Facility
have been fully restored.
• If not so distributed, amounts in the Distribution Account
shall revert to the Surplus Account.
 Theory of Lock-up and Cash Flow Sweep
• Cash Lock-up (dividend lock-up, cash trap) is a “bad time” covenant.
It stops dividends when there is not much cash left anyway.
• Cash lock-up – if things are getting bad, do not allow dividends and
try to get a little more protection for things getting even worse.
• Program lock-ups from historic DSCR with a switch variable.
Prospective lock-ups cause a circular reference that is probably not
worth solving.
• Cash sweeps can be though of as a “good time” covenant. They can
limit dividends when there is a lot of cash available and protect the
lender for later periods when there is less cash.
• Cash sweeps are programmed with MAX/MIN functions and sub-
totals
• MAX so the sweep occurs only when cash flow is positive
• MIN to make sure you do not sweep too much cash flow
• It would not make sense to have some formula for a cash sweep that
prepays debt when some low level of DSCR occurs – this is redundant
with the lock-up. Ratios like Debt/EBITDA make work better.
Volatility and Risk Reduction from Cash Flow Sweeps

• A cash sweep covenant only makes sense in

situations where the cash flow is volatile and/or there
are potential downward trends in prices.
• Think about a sudden 2008 type decline in cash flow.
Lenders do not like to have paid dividends only to later
have a default
• If cash flow is always low there is no cash flow to sweep
anyway. Here the sweep will not help.
• If cash flow is always high, there is no need for the cash
sweep.
• To assess the effectiveness of the covenant, cases
that incorporate realistic price volatility and potential
price trends must be run in the model.
 Example of Risk and Return Analysis for Cash Flow Sweep

Sweeps really help when there is a sudden decline in

cash flow – when you would have paid dividends
otherwise. A sweep would have reduced the default in
the example below.

Dividends Default
Default

Repayment of
default
 Economic and Financial Analysis of Cash Sweeps, Reserve
Accounts and Covenants
• Cash sweeps, reserve accounts and covenants can have
negative effects on the equity IRR of a project.
• Methods to consider the risk benefits to the bank versus the
costs to sponsors are addressed.
• Mechanics of cash sweep with different triggers and theory of
what kinds of transactions would be relevant for cash sweep
(e.g. hydro but not solar because of volatility) are addressed.
• The theory of what kind of triggers make sense
(Debt/EBITDA but not DSCR and operational triggers).
• Contrast between cash sweeps and cash trap covenants. As
with other issues, the effects of cash sweeps on equity returns
should be addressed with and without re-financing
assumptions.

346
Importance of Re-financing Analysis with Cash Sweep

• Cash Sweeps seem to dramatically reduce the

cash flow
• But after the prepayments from the sweep (or
even before), the project can be re-financed
• You can even lock-in interest rates if you are
worried about interest rate risk.
• Again, re-financing changes everything – you
can get you super dividends when you re-
finance.

347
 DSRA and Liquidity
• DSRA is built to get liquidity into the project because holding
cash is very expensive – often 6 months of debt service which
is arbitrary
• Return on cash is about zero and opportunity cost of funds is equity
or debt IRR
• You can sometimes use a letter of credit instead of cash.
• Letter of credit should have a parent guarantee
• Paying an LC fee costs much less than the opportunity cost of funds
• If debt size is driven by the DSCR and not the debt to capital,
then the DSRA is funded by equity and not debt. This is
because the level of debt is given.
• If the debt to capital is high and the equity contribution is low,
the DSRA can be very costly to the equity IRR because of
high debt service and low equity.
Using the DSRA as the Final Repayment in Sculpting

• Bankers should not care if the DSRA is

funded by debt or equity – the idea is just to
have liquidity when temporary bad things
happen or to have time to restructure.
• You can make the last repayment the DSRA.
In this case, with sculpting, the amount of the
cash flow increases and the debt also
increases. This has a small positive effect on
the equity IRR as shown in the next slide.

349
 Example Using the DSRA as the Final Repayment in Sculpting

• The example below shows the effect of using the DSRA in

sculpting debt. The left hand side includes DSRA and the
right hand side does not. Without DSRA the IRR is 12.65%.

350
 Use of LC Instead of the DSRA
• The example below shows that with a high debt to
capital ratio driven by sculpting and a high IRR,
the DSRA in LC can make a big difference to the
equity IRR – 11.96% to 14.92% as shown below.

351
 Debt Service Reserve Language
• On the Closing Date, an amount equal to 10% of the original principal amount
of the Bonds will be deposited in the Debt Service Reserve Account of the
Debt Service Reserve Fund from the proceeds of the Bonds.
• The amounts in the Debt Service Reserve Account will be used only for the
purpose of making payments into the related Interest Subaccounts, the
Principal Subaccounts and Sinking Fund Installment Subaccounts for the
Bonds
• If a disbursement is made under a Debt Service Reserve Account Facility, the
Trustee shall apply amounts transferred from the Operating Revenue Account
to the applicable Debt Service Reserve Account to either cause the
reinstatement of the maximum limits of such Debt Service Reserve Account
Facility. The Trustee will apply moneys on deposit in a Debt Service Reserve
Account prior to any drawing on any Debt Service Reserve Account Facility.
• In the event that any amount shall be withdrawn from a Debt Service Reserve
Account for payments into an Interest Subaccount, Principal Subaccount or
Sinking Fund Installment Subaccount or there exists a deficiency in a Debt
Service Reserve Account which is to be reinstated, such withdrawals shall be
subsequently restored from Revenues available on a pro rata basis after all
required payments have been made into such Interest Subaccount,
 Part 14: Other Project Finance Subjects: IRR
problems, Risk and Value Changes over Life of
Project, Resource Analysis and Debt Sizing
 A Little Theory about Valuation and Risk of Projects
• Valuation theory with respect to projects generally involves risk reduction as
a project progresses through phases.
• In Europe, there are many stories (but not much data) about how insurance
companies purchase existing projects with operating history and are willing
to accept equity IRR’s as low as 5-6%.
• The idea behind a low cost of capital for mature projects is the following:
• During the development stage, expenditures occur with large risks associated with
permitting, problematic wind studies, construction cost over-runs, ability to secure tariffs
etc. The required equity IRR during the development stage can be 15% to account for the
project not being successfully methods.
• Once the development is finished or in late stages, the risk is reduced by a large margin.
However there are still risks associated with successfully completing construction at budget
and on time. The reduced risk during the construction phase may reduce the required
equity IRR to something like 12%
• After construction, the remaining risk for a project with a fixed price contract is that the
estimated wind production will not be met. Given this risk, the discount risk is still above
the cost of capital for bonds and may be in the range of 8-10%.
• Once operating history is available, the risk is not much higher than the debt cost or the
interest rate on long-term bonds. With bonds yielding below 3%, a return of 6% provides a
good premium for risk.

354
 Re-financing and Early Project Sale
• Timing strategies and sales value. How
different types of projects have differences in
risk reduction over time, and why wind
projects probably have more of a risk
reduction than other electricity projects.
Show how the effects of changing risk and
selling to a Yieldco can be demonstrated with
measuring IRR over time with changing
buyer IRRs. Demonstrate how optimal
holding periods can be computed with
various IRR hurdle rate assumptions.

355
 Verification of Cost of Capital from Published Data in Yieldco
Reports

• As part of this task we have reviewed detailed financial data of

Yieldco’s including prospectuses and annual financial reports. One
of the last companies that we investigated was Brookfield
Renewable Energy Partners (BEP). In its notes to financial
statements, discount rates that are applied to both contractual cash
flows and non-contracted cash flows in asset valuation are
presented. It is assumed that the cost of capital represents after tax
cost of capital although this is not specified in the report.

356
 Equity Returns and Re-Financing

Equity IRR with and without Re-financing

50.0%

44.6%
45.0% Re-Finance
No Re-Finance
40.0%
37.3%
E
q 35.0%
u
29.2%
i 30.0%
t
25.0%
y 21.7%
20.0% 18.9%
I 16.0%
R 15.0%
R
10.0% 7.8% 7.7%

5.0%

0.0%
Low Base High Very High
Traffic Scenario
 Transaction Multiples from Yieldco IPO’s

• For valuation of assets the most relevant multiple is the EV/EBITDA

ratio. This is because the EBITDA is not affected by financing and
because the EV/EBITDA ratio can be computed from IPO’s of
Yieldco’s. For Yieldco projects that have minimal capital
expenditures and small or no growth in cash flow, the EV/EBITDA
can be used to derive an implied pre-tax IRR and an overall cost of
capital (this is further explained in the appendix). The IRR’s from this
analysis are lower than the low case pre-tax cost of capital
assumption.

358
 Equity Returns for Tollroads

• The following slide shows equity returns over time

and how they have come down