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


Project Finance Model Exercises and Video Courses


This page includes project finance related excel files and videos that allow you to learn how to build a project finance model. Whilst I can tell you without any question at all that in person courses are much better than on-line videos as a way to learn, I have included sets of videos and exercises that allow you to understand various basic and advanced issues. In general, all of the project finance model exercises are arranged to correspond to the structure of a project finance model where timing is developed first, followed by analysis of operations, capital expenditures, revenues and operating expenditures (this matches the contract structure of project finance with an EPC contract, O&M contract and Revenue [e.g. PPA] contract). The difficulty in project finance models often arises from financing and many of the exercises deal with financing issues including cash flow waterfalls, debt repayment structuring, debt sizing, interest during construction, fees, reserve accounts, cash flow sweeps and covenants.

The files and videos are arranged by different subjects and different levels of complexity. Separate sets of videos and analyses are presented for:

(1) Building a standard project finance model from A-Z;
(2) Building a rooftop project finance model where portfolios of projects must be analysed;
(3) Dealing with circularity and other issues that can arise from sculpting debt repayments in a project finance model;
(4) Evaluating cash flow waterfall issues that arise when a monthly model is built and debt repayments occur on a semi-annual or a quarterly basis;
(5) Evaluating project finance and circularity issues associated with the construction phase of a project (not complete);
(6) Constructing a structured finance model (a close cousin of a project finance model);
(7) Analysing other project finance issues such as IRR/ROI reconciliation, flip structures, advanced timing.

For each of the subjects below video links and files that you can use in working through the issues are included. In most (not all) of the lessons, there is both a completed version of the file and a file or sheet that has blanks for you to fill in. For some of the exercises related to to project finance model exercises that are associated with sculpting, cash flow waterfalls and circularity resolution, if you can fill in all of the blank exercise, you should be considered a very good modeller.

The on-line lessons are free which may be a really bad idea for me. I have been told that if I somehow charged money for the courses you would think they are more valuable. This is complete rubbish and I expect that you can get a lot more out of these exercises than other companies that spend a lot of money on marketing and being flashy. But, if you have completed exercises and you want me to verify that you really are a good modeller I will do this for a modest fee. If you want to be registered as a modelling expert that will be published in the website and verified for your employment, send me an e-mail to edwardbodmer@gmail.com.



Lesson Set 1: Building a Standard Project Finance Model

If you are starting out in modelling and have not yet made a project finance model, the files and videos below are intended to allow you to see the project finance structure, the importance of timing assumptions, the essential nature of separating operating cash flows from financing cash flows and other issues including cash flow waterfalls. The set of videos and files also introduce you to some difficult financing issues including circularity associated with funding during construction. Files associated with the videos are shown first followed by video links that walk through the various lessons. The files included completed exercises as well as blanks for you to fill things in (and find my mistakes.) The files sometimes refer to my book "Corporate and Project Finance Modeling, Theory and Practice". I apologise in advance for the music that I put in these videos. I also apologise for the size of the screen which is too small -- these are some of the first videos that I made. I think if you turn the sound down you can avoid the music (I put this in because I did not like listening to my voice).
If you fill in all of the exercises and send me the files along with a small fee, I will publish your name on my website so you can show it to your boss or your future employer. I will also get you an official badge.

Videos associated with Lesson Set 1: Building a Project Finance Model


Subject in Project Finance Lesson

Name of Course File with Blank Exercises

Video Link







Overview of Project Finance Model Structure

Exercise 1: Basic Project Finance Model

https://www.youtube.com/watch?v=C_v8cnNw1VM
Model Timing - Basic with Monthly and Semi-Annual Payments

Exercise 2: Flexible Dates

https://www.youtube.com/watch?v=3BjZLMfmwag

Structuring Inputs for Project Finance Model

Exercise 3: Project Finance Inputs

https://www.youtube.com/watch?v=vMRP5bzP4bw
Working Analysis of Revenues, Expenses and Capital Expenditures in PF Model

Exercise 4: Project Finance Working

https://www.youtube.com/watch?v=Ds65g1DGbHM
Tax Depreciation and After Tax IRR with Net Operating Loss

Exercise 5: Tax Depreciation and Project IRR

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

Debt Sizing with Equal Installments and Completion of Model

Exercise 8: Incorporating Debt with Level Repayments
https://www.youtube.com/watch?v=b7OyQ3yuXOE
Computation of Financial Ratio Statistics

Exercise 9: Project Finance Statistics

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

Debt Sizing in Level Payment Case with Goal Seek and Function

Exercise 10: Debt Sizing in in Project Finance

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

Up-front Fees, Commitment Fees, Development Fees

Exercise 11: Fees in Project Finance Model

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

Debt Funding with Equity First Financing and EBL

Exercise 12: Funding of Construction

https://www.youtube.com/watch?v=iU-SBRzidbU

Cash Sweep and Credit Statistics

Exercise 13: Cash Sweep in Project Finance

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

Debt Service Reserve Account (DSRA)

Exercise 14: DSRA in Project Finance

https://www.youtube.com/watch?v=0tFgLkAXB5g

Maintenance Reserve Account (MRA)

Exercise 15: MRA in Project Finance

https://www.youtube.com/watch?v=gOA-s_ZTt-Q

Exercise Files associated with Lesson Set 1: Building a Project Finance Model


The files listed below are the associated with the project finance A-Z lesson. Unlike the case for other lessons where I put everything in different sheets of the same file, for the project finance lesson I created separate files. One sheet in the file is a completed model and another is a sheet with blanks that you are supposed to fill in. The first file is very simple, the second deals with timing and switches (some call masks or flags). The lesson then moves to computing pre-tax cash flow, depreciation and project IRR. After project IRR is established, the lesson covers financing. Financing includes project finance outputs, the five components of debt: 1 -- the debt size; 2 -- the debt funding during construction; 3 -- the debt repayment; 4 -- interest and fees between funding and repayment; and 5 -- credit enhancements.



Lesson Set 2: Leaning from models that are not Flexible, Not Accurate, Not Structured and Not Transparent

Winson Churchill said that success is going from one failure to another without loss of enthusaism: i.e. learn from mistakes and keep going. I think you can learn a lot more from looking at ways not to do thing rather than following the boring instructions above. So, I have made videos and provided files on what not to do in making financial models.




Lesson Set 3: A-Z Rooftop Solar Model and Building Portfolios

This lesson set walks you through a rooftop analysis beginning with establishing a portfolio from multiple installations to evaluating financing and the ability of the project to repay debt. Parts of the model and lesson are pretty standard but there are quite a few tricky issues. If you make it all the way through all of the lessons you may learn something about both modelling and finance. As different solar facilities on rooftops are assumed to be constructed at different times, the model involves evaluation of how to compute cash flow and production items when rates change over the lifetime of a project such as degradation. This set of videos and files also introduce you various other tricky modelling issues including using sumproduct for S-curves with asset portfolios, inflating in alternative periods, accounts receivable and bad debt when outstanding accounts receivable are longer than a month, allocation to interest during construction and difficult financing issues in a monthly analysis where cash flows can be positive or negative. Video links associated with the lessons are shown below. The second section includes the file with different sheets that should be used together with the videos.


If you really fill out each sheet for a modest fee of 40 Euro I will check your work and if you do a good job I will give you an official badge. (If you have ever taken one of my classes you do not have to pay this fee!). I have been told that the way the world works is that if you charge somebody a lot of money for something then they will feel really good about their purchase but if you give your product for free, then they will find it worthless. I think this is complete rubbish and I expect that you can get a lot more out of these exercises than other companies that spend a lot of money on marketing and being flashy. So, if you want to pay my fee of 40 Euro, I will give you an official badge and then even better you will be registered as a modelling expert on this website. You can then show the website page to your boss and ask for a raise or you can even go to an interview and put show the person who is interviewing you that you have a highly valuable certificate. If you want such a certification, sent me an e-mail to edwardbodmer@gmail.com.


Videos associated with Lesson Set 2: Rooftop Solar Model





Subject

Sheet Name in Course File

Video Links









Rooftop Model Overview -- Introduction to Course File, Videos and Exercises

Overview

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


Rooftop Model Inputs -- Structure and Organisation

Assumptions

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


Setting-up Portfolio Equations in Rooftop Model

Portfolio Set-up Exercise

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

Computing Capacity and Generation for Solar Facilities

Production Exercise

https://www.youtube.com/watch?v=WD-z71Bqtls


Degradation Part 1 - Vintage Array

Degradation Example

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

Degradation Part 2 - User Defined Array Function

Array Functions

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


Degradation Part 3 - Application of Function in Model

Degradation Calculation

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


Calculation of Capital Expenditures and SUMPRODUCT Trick

Capital Expenditures

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


Revenues, Periodic Inflation, Accrued Bad Debts

Revenue Calculation

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


Working Capital in Monthly Model with Forward Calculations and OFFSET

Accounts Receivable

https://www.youtube.com/watch?v=47TzvXmLYjE


Operating and Maintenance with Function to Compute by Age

Operating and Maintenance

https://www.youtube.com/watch?v=crUa-TN1LKw


Compute Cash Flow, Depreciation and IRR and Operating Taxes

Pre-tax and After Tax Cash Flow

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

Overview of Financing

Financing

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

Financing 1: Cash Flow and Balance Sheet

Financing

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


Financing 2: IDC and Fees with Financial Statements

Financing

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

IDC Accounting and Cash Balance Issues

Financing

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

Scenario Analysis

Scenario Exercise

https://www.youtube.com/watch?v=Pu-idlY-5lc



Badge 2.PNG










File associated with Lesson Set 2: Rooftop Solar Model


Exercises associated with the rooftop model that you can complete are included in a single comprehensive file which is the first file named "rooftop solar course file" listed below. The file includes many pages that work through the steps of completing a model. The completed exercise discussed in the video is on one sheet named the monthly consolidated model. The exercise is shown on a later sheet (with a yellow tab). Key take away points are shown at the top of exercise page that you can work through. If you make an effort to fill in the entire model you should be proud of yourself and I will send you an official badge. I have included the generic macros that has SHIFT,CNTL,R and SHIFT,CNTL,C which are used a lot in the videos.




Lesson Set 3: Comprehensive Sculpting and Circular Reference Resolution

The third lesson set describes a host of issues associated with debt sculpting in project finance (maybe you can think of this as a lesson in art). Sculpting debt and sizing debt together with accounting for interest income on a debt service reserve account, fees on letters of credit, multiple debt issues, income taxes with interest expense deductions, alternative timing of operating and financing cash flows, consideration of DSRA changes as cash flow available to pay debt service and other issues. To effectively and atristicly solve problems with these issues I think you need two things. First, you should understand equations for sculpting (not only the basic sculpting equations of Target Debt Service = CFADS/DSCR and Debt at COD = NPV(Interest Rate, Target Debt Service). There are different equations for changing timing, reflecting multiple debt issues, reflecting debt fees, and incorporating changes in the DSRA. Second, because of multiple circular refereces that arise from taxes, interest income, fees and changes in the DSRA I think that you should use my method of functions rather than clusmsy copy and paste macros. This will make you scuppting much more elegant from an artistic perspective. This lesson set walks through very basic sculpting exercises to modelling challenges that I think are quite difficult.
The lesson begins with fairly simple cases without taxes, letter of credit fees or interest income from a debt service reserve account. These lessons use the fact that target debt service = cash flow/DSCR and the present value of debt service equals the amount of the debt. After working through mathematical equations for sculpting, more complex items are addressed. An initial issue is how to use sculpting if a debt constraint applies. Other issues include how to apply models where monthly operating cash flows are used and the debt repayments are semi-annual. Some of the most difficult issues involve interest income and fees on letters of credit. The various lessons evaluate pros and cons of copy and paste macros relative to functions for solving circularity that arises with sculpting. The different lessons build up to a final case includes options for funded DSRA accounts, net operating loss, bullet repayments and other issues. If you can work through all of the exercises in this lesson I think you can be considered a genuine top notch modeller. I have completed just about all of the videos for this lesson but they are in the process of editing the files.


Videos associated with Lesson Set 3: Comprehensive Sculpting Analysis


Videos for sculpting lessons are divided into two sections. The first section is the comprehensive set of lessons that begins with a simple case and moves to complex and quite difficult issues. All of the videos in the top set refer to the file name "sculpting course final". The second set of videos is a bit redundant and includes my earlier attempts to explain sculpting in a less organised manner.

Subject in Structured Sculpting Course

Sheet Name in Sculpting Course File

Video Link





Sculpting Course Overview -- Introduction to Course File, Videos and Exercises

Overview


Basic Sculpting with No Tax, No DSRA, No Bullet

NPV-Basic

https://www.youtube.com/watch?v=kmY9wZPa1zI
Use of LLCR with Debt Capacity Constraint

Debt to Cap and LLCR

https://www.youtube.com/watch?v=Yq3t3veRZdE
Multiple Debt Issues and Sculpting

Multiple Debt Issues

https://www.youtube.com/watch?v=Qd480DSUTHk
Sculpting Discount Rate Adjustment with Monthly Model

Sculpting and Timing

https://www.youtube.com/watch?v=RZr2a7PEOiQ
Sculpting and Debt Fees

Sculpting and Fees

https://www.youtube.com/watch?v=kAZczGh4U9w
Sculpting DSRA and Interest Income

Interest Income on DSRA

https://www.youtube.com/watch?v=caiP0UpLiwo
Sculpting and Fees on Letter of Credit for DSRA

LC Fees and Copy/Paste Macro

https://www.youtube.com/watch?v=gWoOhZM4-2A
Function for LC Fees or Funded DSRA with Interest Income

LC Fees, Interest Income

https://www.youtube.com/watch?v=7HZA9EDcBaw
Sculpting with Income Taxes

Sculpting and Taxes Function

https://www.youtube.com/watch?v=Gdbc6-Vx1cw
Sculpting with Taxes Adjusted for Depreciation

Taxes and Depreciation

https://www.youtube.com/watch?v=4xiIPJgGfac
Sculpting with Bullet Payment and DSRA used as final payment

Sculpting with Bullet Payment and DSRA

https://www.youtube.com/watch?v=MypbC_Kc_qE
Changes in DSRA as Cash Flow Available for Debt Service

DSRA Movements

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





Other Sculpting Videos

File for Other Sculpting

Video Link





Modelling Debt Sculpting - Simple Introduction

Exercise 16: Debt Sculpting - Basic

https://www.youtube.com/watch?v=gAlIIzgIUyc
Debt Sculpting - Changing Interest Rates and Varying Tenor

Exercise 16: Debt Sculpting - Basic

https://www.youtube.com/watch?v=bVfFiJ5WUeM
Debt Sculpting with Interest Income on DSRA

Exercise 16: Debt Sculpting - Basic

https://www.youtube.com/watch?v=Z5B6r-oelBo
Sculpting and Income Taxes - Copy and Paste Method

Exercise 16: Debt Sculpting - Basic

https://www.youtube.com/watch?v=91Mzu0jhM5U
Sculpting and Income Taxes - Function Method

Exercise 17: Debt Sculpting - Advanced

https://www.youtube.com/watch?v=E4TT6tymIVE
Sculpting with Negative Cash Flows and Pct of Time Function

Negative Sculpting.xlsm

https://www.youtube.com/watch?v=YsmN_KFKVeo
Sculpting with Negative Cash Flows and Constraints

Negative Sculpting 1.xlsm

https://www.youtube.com/watch?v=cZf9Cr_gqOE
Sculpting and Leverage Constraint

Sculpting with Constraint.xlsm

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

Files associated with Lesson Set 3: Comprehensive Sculpting Analysis



Exercises associated with sculpting are included in a single comprehensive file which is the first file named "sculpting course." The file includes many pages that work through the various different issues where the completed exercise shown on the video is on one sheet and the exercise is shown on the subsequent sheet (with a yellow tab). Key take away points are shown at the top of exercise page. If you can fill in each of the exercises including the functions and macros you should feel really good. Other files are a mishmash of different sculpting exercises and files that I have constructed over the years that are probably redundant but may help you out a bit.





The videos use a few key formulas to derive debt repayments and the level of debt from a target DSCR that are described below:

Basic Formulas
One of the main ideas about the repayment process in project finance is that the modelling is much more effective when you combine formulas with other excel techniques. If you try and solve these things with a brute force method that uses a copy and paste method or goal seek things will get very messy. Formulas used for repayment and debt sizing are listed below: The fundamental two sculpting formulas are:

(1) Target Debt Service Per Period = CFADS/DSCR
(2) Debt Amount at COD = PV(Interest Rate, Target Debt Service)

Non-Constant Interest Rates
However this is by no means the only formula you should use when working on repayment. In cases when the interest rate changes, a simple present value formula cannot be used. Instead, an interest rate index can be created that accounts for prior interest rate changes as follows:

(3) Interest Rate Indext = Interest Rate Indext-1 x (1+Interest Ratet)
(4) Debt Amount at COD = ∑ CFADSt/Interest Rate Indext

Use of LLCR with Target Debt to Capital
One of the first issues is how to compute sculpted debt repayments when debt is sized with the debt to capital ratio and the DSCR is not given. When the Debt is Sized by Debt to Capital the LLCR can be used to size the debt. Formulas in this case include:

(5) Target Debt Service = CF/LLCR
(6) LLCR = NPV(Interest Rate, CFADS)/Max Debt from Debt to Capital
(7) DSCR Applied = MAX(Target DSCR,LLCR with Max Debt)

Multiple Debt Issues
Another issue arises when there are multiple debt issues and one of the debt issues (defined as Last) is used for sculpting. In this case the basic formula can be adjusted and the process if straightforward. You can start with the DSCR formula and derived the debt service for the last formula.

(8) DSCR = CF/(Other DS + Last DS)
(9) Other DS + Last DS = CF/DSCR
(10) Last DS = CF/DSCR - Other DS
(11) DS for Last Facility = CF/DSCR - Other DS

Discount Rates and Model Timing
When a monthly model is used but the debt repayment is semi-annual, the discounting can become more complicated. In general, when you are working with interest rates you simply divide by the number of months in a period. However when you are discounting target debt service to arrive at the amount of debt, you need to use different discount rates. The adjusted equations for discounting the target debt service is shown below.

(12) Annual: Rate for Discounting in Semi-Annual Model = (1+Annual Rate/2)^(1/6)-1
(13) Monthly: Rate for Discounting = (1+Monthly/12)^(1/12)-1

Adjusting Sculpting Equations for Debt Fees
Debt fees such as the fee on a letter of credit is part of debt service. To include the fees in the sculpting equations, you should subtract the fees when you compute the net present value of debt, as the fees reduce the amount of debt service that can be supported by cash flow. To make the sculpting work you should also make the repayment lower by the fees as shown below:

(14) Repayment = CFADS/DSCR - Interest - Fees
(15) Debt = NPV(Interest Rate, Debt Service-fees) = NPV(rate, Debt Service) - NPV(rate, Fees)
Note Debt Service in the above equation means debt service without fees and debt is reduced by PV of fees

Adjusting LLCR for Debt Fees
The sculpting analyses include calculation of the LLCR to evaluate whether the debt to capital constraint is driving the constraint. In this case the PV of CFADS is not the correct numerator for the analysis. Instead, the PV of the LC fees should be added to the denominator of the LLCR as follows:

(16) LLCR = PV(CFADS)/(Debt + PV of LC Fees), where
(17) Debt = Project Cost x Debt to Capital

Sculpting and Changes in the DSRA balance including Final Repayment
After working through letters of credit for the DSRA, taxes, interest income and other factors that cause difficult circular references, the final subject addressed is using the DSRA to repay debt. A similar result occurs when changes in the DSRA are included in CFADS. Incorporating these changes in a financial model without massive circularity disruptions can be tricky, but it can be solved by separately computing the present value of changes in the DSRA. Changes in the DSRA can be modelled using the following equations:

(18) Debt Adjustment = PV(Interest Rate, Change in DSRA/DSCR)
(19) Repayment = Repayment from Normal Sculpting + Change in DSRA/DSCR

Lesson Set 4: Structured Finance and Project Finance

The fourth lesson set evaluates issues associated with structured finance. Structured finance involves the same cash flow waterfall issues with special purpose vehicles that are part of project finance modelling. This lesson walks through building a structured finance case along with the associated risk analysis. The final case includes options for funded DSRA accounts, net operating loss, bullet repayments and other issues. If you can work through all of the exercises in this lesson I think you can be considered a genuine top notch modeller. I have completed just about all of the videos for this lesson but they are in the process of editing the files.


Videos associated with Lesson Set 4: Building and Analysing Structured Finance Models


Subject in Structured Finance Analysis

File Associated with Structured Analysis

Video Link





Creating Strutured Finance Model

CDO Exercise 1

https://www.youtube.com/watch?v=6giYxjcmCUU
Two Way Data Table with Structured Finance Model

CDO Exercise 2

https://www.youtube.com/watch?v=iGnjWYW-zIc
One Way Data Table with Structured Finance Model

CDO Exercise 2

https://www.youtube.com/watch?v=Tip6i7pYEt4
Break Even Analyis with Strctured Finance Model

CDO Exercise 3

https://www.youtube.com/watch?v=TSDpLt0-k3I
Scenario Analysis with Structured Finance Model

CDO Exercise 4



Files associated with Lesson Set 4: Structured Finance Analysis


The following files are associated with the structured finance analysis. The pdf file explains some of the theory behind underlying the analysis and how models can be used. The main modelling task is to evaluate cash flow waterfalls.







Lesson Set 5: Semi Annual Debt Repayment in Monthly Model and Cash Flow Waterfalls

This lesson involves calculation of debt service accounts and cash flow sweeps in a case where the model is monthly but the debt payments are semi-annual. I think some of the videos are somewhat long and detailed. But if you look carefully at the sheets you can see how cash flow is accumulated in a debt service account and then released to various other accounts depending on the debt repayment period, covenants and cash flow sweeps. For this lesson I did not include separate exercises when I was making the videos.

Videos associated with Lesson Set 5: Monthly/Semi-Annual Analysis and Cash Flow Waterfalls


Subject

File with Subject

Video Link





Computing Repayment Months for Semi-annual debt

Monthly Debt

https://www.youtube.com/watch?v=Z-e1aQhXUUc
Constructing Monthly Time Line with Switches for Repayment

Monthly Debt

https://www.youtube.com/watch?v=JYzSdN99chg
Debt Service Account to Accumulate Cash Until Next Repayment

Monthly Debt

https://www.youtube.com/watch?v=sskJxtdbWBw
Waterfall - Default on Debt Payment in Monthly Model

Monthly Cash Flow Waterfall

https://www.youtube.com/watch?v=W4oAuEmrcZs
Debt Service Reserve Account in Monthly Model

Monthly Cash Flow Waterfall

https://www.youtube.com/watch?v=6FfRT3A9DkI
Monthly Debt Analysis with Waterfall - Lock-up Covenant

Monthly Cash Flow Waterfall

https://www.youtube.com/watch?v=pwn7Y0-A4mQ
Monthly Debt Analysis with Waterfall - Cash Sweep Account

Monthly Cash Flow Waterfall

https://www.youtube.com/watch?v=1aSoJuG5s5I
Roll-up to Annual for Monthly Cash Flow Waterfall

Quarterly and Annual Roll-up

https://www.youtube.com/watch?v=xPCCuzgmC1Y
Roll-up to Quarterly for Monthly Cash Flow Waterfall

Quarterly and Annual Roll-up

https://www.youtube.com/watch?v=5PVn_gjQNOw


Files associated with Lesson Set 5: Monthly/Semi-Annual Analysis and Cash Flow Waterfalls






Lesson Set 6: Selected Tricky Subjects in Project Finance Models

This lesson describes some difficult details of Project Finance models. Subjects include S-curves with delay or acceleration in construction; dealing with interest during construction, fees and the associated funding needs and construction; equity bridge loans; and, sculpting in situations where the amount of debt is given.



Videos associated with Lesson Set 6: Tricky Subjects in Project Finance Models


Miscellaneous Project Finance Exercises

Files with Exercises

Video Links





Converting from Monthly to Quarterly and Monthly/Semi-Annual

Exercise 7: Advanced Timing

https://www.youtube.com/watch?v=G3dEapTxaqk
Reconciliation of IRR and ROI

Exercise 6: ROI and IRR Reconciliation

https://www.youtube.com/watch?v=yD0LfAAoxPU
Multiple Equity Owners in Project Finance

Exercise 22: Equity Allocations


Multiple Equity Owners and Cash Flip

Exercise 23: Flip Exercise Pre-tax

https://www.youtube.com/watch?v=gFERDTjqtXg
Find the flip period with fraction of year

Flip period.xlsm

https://www.youtube.com/watch?v=IqghCkfMg1I
Create a payback function with period fractions

Payback.xlsm

https://www.youtube.com/watch?v=7Cmbh_XMnks
Annual Fees and IDC

Annual IDC Fees.xlsm

https://www.youtube.com/watch?v=mUrS-bFk2SE
End of Quarter Function

End of Quarter Function


Half Year and Monthly Function

Half Year and Month Function


Percent of Time Function

Percent of Time Function


Complex Working Captial in Project Finance Model

Gas Plant Expample

https://www.youtube.com/watch?v=v5BXBpnFbXQ
Computing the outages from periodic maintenance periods

Gas Plant Expample

https://www.youtube.com/watch?v=0Xt7p5z3o18


Files associated with Lesson Set 6: Tricky Subjects in Project Finance Models


Timing is one of the keys to project finance. Different stages have different risks and must be defined in a model. Exercise 2 demonstrates how to structure inputs in a basic project finance model (it is associated with the project finance inputs video). The second exercise addresses how to structure a model with flexible dates so that different construction periods and project lives can be included in the analysis. The flexible dates and the advanced timing exercises demonstrate alternative ways to deal with different timing structures.







Corporate Finance involves comparing the ROIC with WACC while project IRR is computed in project finance. This file demonstrates how to reconcile the ROIC and the IRR over the life of a project (it is associated with the IRR reconciliation video). The project IRR is related to WACC (althogh it is not very useful to evaluate WACC in project finance) and should be computed after tax. After-tax IRR is computed without IDC but after depreciation on the base plant. This file demonstrates how to compute after-tax IRR in a project finance model (it is assocated with the project finance after-tax IRR video).



The exercises below address various issues associated with cash flow waterfalls. Exercise six addresses the issue of adding cash sweeps to a model. The first part of the exercise shows how to model a basic cash sweep and evaluate the benefits of the cash flow sweep when cash flow falls off a cliff. One of the key issues with cash sweeps is to not include the cash sweep in the debt structuring part of the model. The fourth exercise illustrates how to create a cash flow waterfall that includes a default on debt and repayment of the default. Including provisions for a default on debt allows computation of the break-even point for things like delay in construction and other items. It also enables the evaluation of debt IRR for alternative types of securities. Exercise eight describes various modelling issues associated with cash lock-up accounts.





Sizing debt is a key issue in a project finance model. This file demonstrates basic sizing from the DSCR (it is assocated with the debt sizing video).













Related Pages on the Site Related to Project Finance:


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