Introduction to Project Finance Modeling in Excel

This is a guest post written by Paramdeep from Pristine. Chandoo.org runs Financial Modeling School program in partnership with Pristine Careers. Visit Financial Modeling School to join our training program.

Greetings!

It’s been long time since we interacted on Chandoo.org. Actually I was very busy teaching the 105 awesome students for financial modeling in Excel. We all worked together to create some easy and some complex financial models. I found the journey to be quite exciting and enriching (From the feedback that I got, my students too didn’t find it bad either 😉 )

During the interaction, I found that a lot of students were looking for financial modeling around the project financing as well. So we thought why not introduce financial modeling for project finance.

In this post I will speak about some of the key aspects of a project finance model and why it can be different from modeling a normal company.

What is so Special About Project Finance?

An organization is an on-going entity – the basic assumption being that it would continue business for time immemorial. Project finance is different. It exists for a limited duration (though that duration is usually long – 20 to 30 Years!) and the project is structured in a special purpose vehicle (SPV). The SPV exists for a specific period of time and after the purpose of the project is solved, the SPV is dissolved. That means that the horizon of analysis is NOT time immemorial but a known time frame for which the SPV is formed.

The other typical difference is that these are long gestation projects, with almost no cash flow in the beginning and a lot of parties involved. This basic difference in the structure creates a lot of issues, Principal – Agency problem because of large number of parties, everybody trying to maximize their returns in a short time, propensity to wait decreases significantly and the initial project gestation period is critical to success of project.

Summarizing, Project Finance is the financing of

• Often long-term, industrial projects
• Increasingly those which provide public services or infrastructure
• Based upon complex financial and contractual structures commonly involving many legal entities

Two main types of Project Financing

• Greenfield – a fresh start
• Brownfield – expansion of an existing project

Key Aspects of Project Finance

I will highlight some of the other key aspects of Project finance.

• Separate Entity (Parent) and SPV Status – Risk of the transaction is generally measured by the creditworthiness of the project itself rather than that of its owners (Sponsors).
• Project Finance debt is often termed as “non-recourse”– That means the financial institutions cannot go to the parent level to get their money back. The money has to be generated at the SPV level. Typically these loans are secured by the project assets and the core project contracts.
• Timing of Cash Flow: The cash flows from the project comes only after the project is fully complete (takes more than a single financial year for completion) and are usually the sole means of repayment of the borrowed funds
• Multiple parties involved
  • Sponsors
  • Contractors
  • Suppliers
  • Governments
  • Global financiers
• Long Gestation: From inception of an idea to Financial Close, a Project Finance deal can take years to negotiate
• Identifying Risks: The success of the project depends a lot on identifying risks, allocating them appropriately and ensuring that the responsible parties are adequately incentivized to manage their risks efficiently
  • Construction time, costs & specification
  • Operational cost, reliability
  • Supply reliability, quality, cost
  • Off-take volume, price
  • Political environment, war, local hostility, currency in-convertibility
  • Socio-environmental responsibilities

Modeling a Key Risk – Delay in Project Implementation

Because of the structure of the project one of the key risks in the project is the risk of delay. Delay in project can significantly reduce the IRR (what is it?) of the project and completely take it off track! There are multiple ways in which projects can be delayed and each would have its own repercussions on the return. These can be (The list is not exhaustive):

• There is an implementation risk (Delay because you could not execute the project in time)
• Delay in start of project (Could be because of regulatory approvals – Regulatory Risk)
• Delay in project because no funds were available (or delay in arranging the funds)
• Delay in collection of revenues (You built at the right time, but could not sell – Selling risk)

Typically each of these delays would have different affect on the cash flows. For example, if you are implementing a real estate residential project and you are not able to sell the homes – you have incurred all your cash outflows and your inflows are delayed. This would spell doom for the returns. On the other hand, if there is a delay in staring the implementation because of non acquisition of land, typically your cash outflow is not there so the affect would not be that large.

Why are delays more relevant for certain kind of investments?

Typically for long gestation projects – Roads, Real Estate, Power, Telecom, etc. there is a huge upfront investment. Even if there is a small delay in getting back the cash flows, it makes your project financials look very bad.

The Case of ABC Housing Co.

ABC housing company is planning to start the project on 1 Jan 2002 and is expecting to complete the construction over a period of 3 years. The construction cost is expected to be USD 1000 Mn. It is expecting to hold the property for a period of 5 years, in which it would get a lease rental of USD 100 Mn each year. After 5 years, it would sell the property at a value of USD 2000 Mn.

Modeling delays in Excel – Playing around with the date function

If you are planning to incorporate delays in your model, date function comes in very handy. For example, if you are expecting the construction period of 1 year, you can just put this as a parameter in your date (for years) and change his to see, if the construction period increases, what would be the effect on the project IRR.

The simple concept I have used here is to make the counter as a counter from the start date. The EOMONTH function calculates the end of month date of the particular month for which the date is given.

Now, I want to see if this is a construction year of not, I can just compare the number of years!

Some assumptions to start with

I am assuming that once the project starts, the cost incurred during the construction period would be uniform. So if the construction is completed over a period of 3 years, each year 33% of the cost is incurred and if it is completed over 5 years, then 20% of the cost is incurred each year.

To model this in excel, I compare the current year with the time period when the construction started and allocate the cost.

Similarly for Leasing I assume that it gets leased out one year after the construction period and after the leasing is complete, ABC is able to sell the property at the desired price

Calculating the Cash

There are two ways in which you generate cash – by lease and sale of property. The cash is consumed by your Capital expenditure.  Assuming there are no taxes, cash generated is just a combination of all these.

Calculating the Return

As discussed last time, Internal Rate of Return (IRR) is an important aspect that all investors look at. Once we have the cash, we can simply ask excel to calculate the IRR for us. But in this case, since we have the exact year of cash with us, we will not use simple IRR function, but the XIRR function in Excel. This would take into account that all cash flows might not be at the year end.

Scenario Analysis

Now that we have the model with us, we can change the construction period and see its effect on the IRR. If we want to do a complete scenario analysis, we can used Data Tables in Excel to generate a complete scenario analaysis.

Related Tip: Learn how to work with Data Tables & Monte-carlo Simulations in Excel

To create the data table, the structure of the table should look like:

Where 1, 2, 3 .. etc are the years of construction and the cell is linked to the IRR that we had calculated earlier.

Then select the data tables option within the data tab > What if Analaysis

Select the changing cell as the years of construction (In this case the change is in a column)

You can see that there is a significant change in the IRR of the project with a change in construction period.

So if you construction period is delayed from 3 years to 5 years, your IRR would change from 15.6% to 13.1% (and hence the investor interest would simply die out!!).

Templates to download

I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers!

You can download the same from here [mirror].

You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Also you can download this filled template [mirror] and check, if the information you recorded, matches mine or not! 🙂

I am just doing that for the single sheet model and recommend that you do the same for multi-sheet model as a homework problem. If you face any issue, post your excel with the exact problem and we can discuss the way to move forward.

Next Steps

This series gives you a flavor of how financial modeling is done and an idea about specific nuances in modeling for long gestation projects. In the next part of this, we will learn how the interest payments of a project should be modeled.

Join our Financial Modeling & Project Finance Classes

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.

Please click here to learn more about the program & sign-up.

For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com