This page included some project finance model basics through separate and structured components. 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. This page includes my original attempt to create an on-line A-Z project finance model course. This stuff is a little obsolete and I have made a revised A-Z on-line project finance modelling page. Some of the videos are bad quality and do not address some of the more advanced ideas. But I did put the stuff into different parts and there may be some use to the original on-line A-Z project finance modelling. However, if you are looking for something very basic there may be something useful and I have not deleted the videos or the excel files. I cannot believe that many people would really take the time to work through every lesson and fill in the blanks. If you have really done this, I will put your name on the honours list page. .
Introduction – Separate Exercises and Videos
.
The first project finance exercise 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.
.
Exercise 1: Basic Model Structure
. The first video and file reviews the fundamental structure of a project finance model — how to operations from financing. The importance of timing. The general idea of financing in a model. (Warning this is a old video with some music in the background). Assumptions for the model in the file below are illustrated in the screenshot (so you can see what the exercise is about). The exercise file is available for download below the video. .
. . Excel File for Project Finance Series: Very Simple Project Finance Model where you Fill in Blanks . .
. . .Exercise 2: Model Time Line
. Most of the time, debt in project finance and interest are repaid after commercial operation on a semi-annual or quarterly basis. Before commercial operation debt is generally borrowed on a monthly basis. To get the model correct, a timeline should be constructed which first has monthly dates and then has semi-annual dates. Therefore, the second exercise addresses creating a time line which is crucial in project finance. In this and subsequent files I have included some screen shots that introduce to the exercise. Notice that there are a lot of TRUE/FALSE switches and dates. The exercise file is available for download below the video. .
Exercise 3: Structuring Inputs for Project Finance Model
. Project finance inputs should be structured in the same way as the model is built. First the dates, then the physical characteristics, then the revenues expenses and capital expenditures. The financing is only after that. The screenshot below illustrates that set-up of assumptions with some simple fixed inputs, some inputs that vary with scenarios, some inputs that are a function of age and some inputs that are a function the calendar year. The exercise file is available for download below the video. . .
Exercise 4: Operational Analysis
. The fourth exercise includes working analysis of revenues, expenses and capital expenditures in a project finance model. The EBITDA and capital expenditures are the key things to study in just about any financial analysis. While each project is different in terms of set-up of EBITDA and Capital Expenditures, the exercise addresses some general ideas. The screenshot below illustrates the completed exercise. .
.
.
.
Exercise 5: Tax Depreciation and After Tax IRR with Net Operating Loss
. This video works through basic depreciation. More advanced issues associated with capital expenditures subsequent to COD are in the revised course. The screenshot below with the conditional formatting illustrates the set-up for depreciation and use of NOL in computing taxes. .
.
.
Exercise 6: Debt Sizing with Equal Installments and Completion of Model
. This video introduces debt sizing in a rare case where there are no circular references or required goal seeks. The simple debt structure is used to demonstrate the fundamental elements of getting debt into a model and is used to complete the model so that statistics and more complex items can be included. Note that the exercise numbers are wrong on the file titles. A screenshot that shows parts of debt funding including sources and uses as well as the summary sources and uses and a debt schedule is shown in the screen shot below. .
Exercise 7: Computation of Financial Ratios in Project Finance
. There are two fundamental outputs in project finance. The IRR and the DSCR. There are variants of these statistics such as the project, equity and debt IRR as well as the LLCR, DSCR and the average life. These statistics are addressed in this exercise and video. The first screenshot taken from the exercise file demonstrates the cash flow waterfall that completes the basic model. The second screenshot illustrates the summary file with the various financial ratios. .
. .
.
.
.
Exercise 8: Debt Sizing in Level Payment Case with Goal Seek and Function
. This exercise demonstrates how to size the debt if the debt is level with a goal seek. It really stinks because you would like to make this process dynamic and automatic. Go to the page with dynamic goal seek to see how to do this. Sorry about the music in this video (I stopped this practise long ago). You can also go to the excel utilities section to see how to attach a macro to a goal seek. The screenshot below shows the buttons for the goal seek and the target DSCR. .
. Excel File for Project Finance Exercises: Use of Goal Seek to Size Debt from DSCR with Macro .
.
Sub goalseek1()
' goalseek1 Macro
'
If Range("applied1") Then
Range("differ1").GoalSeek Goal:=0, ChangingCell:=Range("fixed1")
End If
End Sub
.
.
Exercise 9: Up-front Fees, Commitment Fees, Development Fees
. The remaining exercises address details in project finance. The first issue is various banking fees and development fees. Note that development fees can be very tricky and are addressed in mode detail in the revised course and in the advanced project finance section. .
. Excel File for Project Finance Exercises: Exercise on Fees with Commitment and Up-Front Fee .
.
Exercise 10: Debt Funding with Equity First Financing and EBL
. The tenth exercise addresses funding from debt and equity using pro-rata funding. The screenshot below demonstrates the funding.
Exercise 11: Cash Sweep and Credit Statistics
Modelling a cash sweep involves connecting the debt schedule with the cash flow waterfall and use of MIN and MAX statements. The MAX is used to assure the cash flow is positive MAX(cash flow,0). The MIN statement is used to make sure the debt balance does not go negative. The screenshots below illustrates how to model a sweep.
.
. Excel File for Project Finance Exercises: Cash Flow Sweep Exercise Demonstrating MAX and MIN .
.
Exercise 12: Debt Service Reserve Account (DSRA)
. The debt service reserve modelling involves beginning with the amount of the debt service and then computing the required DSRA. Once the required DSRA is established, the required funding of the DSRA can be determined which can in turn be split between the construction period and the operating period. The amount of DSRA required to fund cash shortfalls and the top-up of the DSRA can be established. .
. Excel File for Project Finance Exercise: Debt Service Reserve Accounts in Project Finance Model .
.
Exercise 13: Maintenance Reserve Account (MRA)
.
The maintenance reserve account can be modelled with switches and the MATCH and INDEX function. The MATCH function can be set to NA when there is no maintenance period. The INDEX function can then be used with the various dates. A screenshot demonstrating inputs used in the exercise is shown below.
.
.
Equations for Interest During Construction in Annual Model with Average Debt Balance
The file below demonstrates how you can compute IDC in an annual model and avoid circular references.
.
draws = cost x debt pct / (1-int rate/2 x debt pct – fee pct x debt pct)
new debt = total funding – equity
new debt = construction + idc – equity
idc = ob * rate + new debt * rate/2
idc = ob * rate + (construction + idc – equity ) * rate/2
idc – idc * rate/2 = ob * rate + (construction + equity ) * rate/2
idc * (1 – rate/2) = ob * rate + (construction + equity ) * rate/2
idc = (ob * rate + (construction + equity ) * rate/2)/(1-rate/2)