Project Finance - Interest during Construction and Funding Requirements

Introduction

Greenfield (think of new build) project finance deals consist of both construction and operations period. In this blog tutorial we will focus on the construction phase of a greenfield project and find out how to calculate interest during construction, funding requirements and ultimately our required debt and equity drawdowns.  

Required funding during construction

You may be wondering what project costs may be incurred during a typical project finance construction phase. Here are a few of the most common costs:

• Construction costs - amounts which are usually paid from the Concessionaire (SPV) to the EPC contractor
• SPC costs during construction - administration costs, including management staff'ssalary, advisory costs, ongoing insurances etc.
• Upfront bid costs - cost incurred prior to financial close and to be recovered at financial close - advisory costs, consultants, insurances  
• Financing costs - arrangement fees, commitment fees, interest costs, equity yield (often in the form of sub-debt payments), Debt Service Reserve Funding

But how do we fund these costs?

As the majority of project finance deals have no revenue during construction, construction funding is required. 

In the most basic form funding for the above costs comes in the form of both debt and equity. The above costs are aggregated together usually on a monthly basis, with drawdowns on financing facilities assumed to occur at the end of each month period. 

Assuming that the drawdowns occur at the end of the month eliminates any circular reference related to interest during construction. There are however other gremlins (otherwise known as circularities) that we need to look out for such as commitment fees and arrangement fees. We can break these circularities quite easily, but we will save that topic for another day. 

Putting it all together- an example

Download the youtube video and spreadsheet to follow along.  

Ok, so we now know where our funding requirement is coming from and how it is funded. 

Now let's take a look at an example. Say we had the following assumptions:

Note: Let's also assumes there are no commitment fees or arrangement fees (i.e. we'll leave out the gremlins). 

Firstly let's work out our funding requirements. To do this we need to add the construction costs, SPV costs, upfront bid costs and the financing costs together. 

You'll note that we've missed out the financing costs for the moment. 

Now let's setup a corkscrew account for the debt as shown below. Remember in a corkscrew account the opening balance in the current period is equal to the closing balance for the previous period. Because we have a gearing of 80%, the debt drawdown will simply be 80% x the funding requirement. 

Given the debt balances we can now calculate the debt interest payments for each month. This will simply be 6% x opening balance x 1/12 (remember we are dealing in months). 

We can now feed this interest cost back in the funding requirement. 

The equity capital injected is then simply equal to (1-gearing) x funding requirement or equivalently the funding requirement minus debt.

If all has went smoothly you should end up with a debt amount of 121 and an equity amount of 30. 

A quick side note

In most instances, during construction there will be no free cashflow left over after utilising debt and equity. i.e. funding requirements should exactly offset debt and equity drawdowns.

If you like this tutorial you you'll love our financial modelling and excel training courses. Want more free blog tutorials? Check out Video Financial Modelling's blog. 

 

 

 

 

 

 

Free Bonus Excel Shortcuts

Free Bonus Excel Shortcuts

Blog Downloads: Youtube Video    Excel Spreadsheet       Macrostarter

Instructions for downloading your free shortcuts:

• download and copy the Macrostarter.xlsm to your desktop
• go to the windows button in the bottom left hand corner of your computer screen
• search for the folder XLSTART
• drag and drop or copy and paste the Macrostarter.xlsm file from your desktop into the XLSTART folder

Now you're ready. Open up a blank Excel sheet, then open the file you want to work with. Your Macrostarter.xlsm file is hidden in the background. 

Cool Free Excel Shortcuts

So what is contained in the Macrostarter.xlsm file???? Well to give you a clue, we think that these are two of the coolest Excel shortcuts around....

Ok, so maybe you won't be able to guess. 

Well they are....

Number formatting - puts a comma to separate thousands and also puts dashes for zeros - simply push CTRL+SHIFT+C on any number to apply

Date formatting - puts any numbers which are meant to be dates into the following format dd-mmm-yy. i.e. 31-Jan-11 - simply push CTRL+SHIFT+D

These two shortcuts are sure to save you plenty of time formatting your Excel spreadsheet or financial model. 

Do you want to become a Excel shortcut pro? If yes, then check out our Excel Shortcuts training course.

Want other great Excel and financial modelling tutorials for free? Check out the Video Financial Modelling home page. 

 

 

 

 

Excel and Financial Modelling Training Courses

For those of you who don’t know Video Financial Modelling has recently launched their face-to-face group Excel and financial modelling training in Dubai and London. 

This is an exciting time for the team and we hope it is equally exciting for you. If you or your business needs quality:

- Financial modelling training, or

- Excel training

contact us for further details or a comprehensive course guide at training@videofinancialmodelling.com. 

As part of our early birds promotion, individuals or businesses registering for the Excel or financial modelling training courses up to the end of October will receive a 20% discount. 

Take a look at our Excel and financial modelling training courses today.

Corkscrew accounts. Why they are used in financial modelling

Introduction 

Now if you have been following Video Financial Modelling for a while, you should know what a corkscrew account is. For those of you who haven’t been following us you’re about to find out how corkscrew accounts can help you to model everything from financing facilities, ledgers all the way through to depreciation accounts.

So what is a corkscrew account?

A corkscrew account is simply a method by which the opening balance of the current period is equal to the closing balance of the previous period. This is probably best illustrated by an example. 

Say we had a financing facility with a closing balance of $50 at the quarter end 31 Dec 2011. What would be the balance for the period starting 1 Jan 2012? If you guessed $50 then you’d be right. Now let’s ramp it up a notch. 

Carrying on from the above example say that you drew $30 from the quarter starting 1 Jan 2012 and ending 31 Mar 2012. What would be the closing balance of the facility if no amortisation or interest capitalisation took place? If you answered $80 ($50 for the opening balance and $30 for the drawdown) you’d be correct.

Now imagine we had the $30 drawdown as per the last paragraph but we also had a repayment of $20 (Note: that it is very unusual to drawdown on a financing facility and amortise it at the same time). What would be the closing balance? It would be $50+$30-$20 = $60. This is represented as corkscrew account in figure 1 below.

Figure 1 – Example of a corkscrew account

Keeping convention to make your life easy

As you can see in Figure 1, Video Financial Modelling has a simple convention when modelling corkscrew accounts. All additions are positive and all deductions are negative. Simple I know, but we have seen many different styles of doing this. Doesn’t it seem logical to have inflows as positive numbers and outflows as negative numbers?  

Let’s look at an example

Given the below company net profits and dividends, find the retained earnings opening and closing balances for the period if the retained earnings balance at period 0 is zero.

Ok, so firstly we need to setup a corkscrew account. As with any good corkscrew you need to link the opening balance of the current period with the closing balance of the previous period. 

Secondly you can find the closing balance by summing all the rows above. Don’t worry if there are no numbers for the net profits or dividends. They will come soon. The important thing is that you stick to convention and make sure that corkscrew outflows are negative numbers and corkscrew inflows are positive numbers. It is very hard to make a mistake if you follow this convention.

Finally add the net profits and positive numbers and the dividends as negative numbers. Remember the formula for Closing Retained Earnings?

Closing Retained Earnings = Opening Retained Earnings + Net Profits - Dividends 

You should come out with the following where the period 10 closing balance is 93.

What else can you apply this concept too?

As mentioned previously you can apply this to many different situations. A few examples come to mind including common ledgers, financing facilities, depreciation accounts. The list goes on and on.  

Like this article? Check out the Excel and Financial Modelling Training Courses at Video Financial Modelling.

 

CFADS and DSCR - Sculpting

OK, so you have grasped the project finance definition of CFADS and DSCR from our previous blog tutorial, CFADS and DSCR - Words from a Foreign Language? But what can we do with these concepts? 

Besides calculating the DSCR, for the purposes of meeting loan documentation covenants such as the lock-up DSCR or default DSCR, we can also use a target DSCR to sculpt debt repayments. 

This is probably best illustrated by example. Say we had the following CFADS for Company A over a five year period.

To follow along with the examples by downloading the YouTube video and the Excel spreadsheet. 

We would like to know how much project finance debt we could raise for Company A in each of the following three scenarios.

1. Using a credit foncier repayment profile (don’t worry we’ll talk you through the concept in a second)
2. Using a target DSCR of 1.30x
3. Using a target DSCR of 1.10x

For each of the cases we have assumed that interest on the debt is at 6% and all debt is repaid by the end of year 5. Also assume that Company A is currently all equity funded. 

Case 1 – Credit Foncier Profile

You can think of a credit foncier repayment profile like a common fixed rate household mortgage. The key here is that you pay an amount which is constant each period (usually a month) to the bank. 

Ok, great you say, but how much debt can we insert into Company A if we are using a credit foncier repayment profile? One way we could find this value is by trial and error. Let’s proceed this way so that you can get a feel for the process.

Step 1: Let’s setup a corkscrew account, by utilising the fact that the closing balance in the previous period equals the opening balance in the current period. You can see this in Figure 1.

Figure 1 – Corkscrew Account

Step 2: Let’s put a plug figure of 5 into the cell highlighted in Figure 1. See Figure 2 for the result. 

Figure 2 – Corkscrew Account with plug figure

Step 3: Now let’s calculate the interest and principal repayments for the plugged debt amount. Interest is straight forward and can be calculated by:

= Interest Rate x Opening Debt Balance

The principal repayments are calculated using an Excel function called the PPMT. The function for our purposes is:

 = PPMT(rate, per, nper, pv) where

rate is the interest rate in this case 6% pa

per is a number between 1 and nper, in this case 1

nper is the number of periods remaining

pv is the opening balance of the account

You can see the PPMT formula in action below.

Figure 3 – PPMT formula for calculating amortisation/principal for a credit foncier repayment profile 

Sum the interest and principal, to find the total debt service. This is also shown in Figure 3 above.

Step 4: Compare the debt service amount to the CFADS. If CFADS is above debt service change the plug figure in Step 2. Repeat Step 4 until CFADS = Debt Service for at least one of the periods. 

If you come to a debt amount of 54.76, you’d be correct. Hopefully you’ll notice that this is a fairly aggressive assumption. Imagine if the period 1 CFADS wasn’t 13, but instead, was 8. You wouldn’t have enough cash to meet your debt obligations. For this reason the debt amount of 13, may be considered as the maximum debt amount you could get into this credit foncier structure, given the above assumptions. 

One last figure, which should give you a good picture of what a credit foncier repayment profile looks like. 

Figure 4 – CFADS and Debt Service for a Credit Foncier profile

As you can see in Figure 4, principal payments for credit foncier profiles increase over the period of the loan. This is due to the fact that interest is calculated on lower principal balances over the life of the loan, and hence interest paid decreases over the life of the loan. As mentioned the sum of the principal and interest in a credit foncier repayment profile stay constant over the period. 

You can also see in Figure 4 there is a large portion of excess CFADS (coloured in green) that is not utilised by debt. In most cases this probably means that the debt amount using a credit foncier repayment profile would be lower than a sculpted debt repayment profile. 

Case 2 – Target DSCR of 1.30x

Now let’s look a utilising a target DSCR of 1.30x. 

Firstly we look at calculating the target debt service for each period. We do this by rearranging the DSCR calculation to:

Target Debt Service = CFADS divided by target DSCR

Based on this formula and the cash flows we come up with a target debt service as shown in Figure 5 below.

Figure 5 – Calculating Target Debt Service

Now that we know what the Target Debt Service is in each period, let’s put in a corkscrew account This is exactly the same process as we did with the credit foncier repayment profile above. Let’s also put in a plug figure of 55.

Figure 6 – Corkscrew account with a plug figure

Based on this plug figure we can work out our interest payments for each period. We can then find our amortisation or principal payment by using the following formula.

Target Amortisation = Target Debt Service – Interest (ensure that the amortisation is not positive i.e. doesn't add to the account balance)

Figure 7 shows the calculation for target amortisation.  

Figure 7 – Calculation of target amortisation

Change the plug figure until the final balance of the corkscrew account in year 5 is 0. You should get a debt amount of 187.38.

Now let’s look at Figure 8. 

Figure 8 - CFADS and Debt Service for a Sculpted Repayment profile with target DSCR of 1.30x

Can you see the difference between Figure 4 and Figure 8? There is less excess cash after debt service when using a sculpted repayment profile compared to a credit foncier repayment profile. 

Case 3 – Target DSCR of 1.10x

We are going to let you do the last question. Simply repeat the steps that we performed in Case 2 and you should come up with an answer for the debt amount of 221. Notice that we get more debt in Company A the lower the target DSCR? 

Figure 9 - CFADS and Debt Service for a Sculpted Repayment profile with target DSCR of 1.10x

In the majority of cases, higher credit risk projects will require a higher target DSCR. This will mean that the resultant debt size will be much lower. i.e. lower debt service

Like this article? Check out the Excel and Financial Modelling Training Courses at Video Financial Modelling.

CFADS and DSCR - Words from a Foreign Language?

Some of you might be wondering whether CFADS and DSCR are words from another language. Well we’re here to explain to you that they’re not. CFADS and DSCR are acronyms which are common place in project finance deals. We’ll look at each in turn. 

NOTE: You can follow the examples by downloading the accompanying Excel spreadsheet and YouTube video in the above Blog Downloads area. 

CFADS 

Cash is KING in project finance deals and CFADS is well, the heir to the throne. 

CFADS stands for cash flow available for debt service and is usually defined in the project finance loan documentation. It is used by financiers to calculate ratios and debt sizing. 

Although it can be defined in many different ways, the simplest definition is:

•  
  • Revenues
  • less: Expenses 
  • less: Capital Expenditure during Operations(1)
  • less: Tax
  • plus/minus: Net Working Capital Movements

(1) In some instances Video Financial Modelling has seen growth capital expenditure, excluded from CFADS. The reason for this is that it is a discretionary expenditure. 

In a large number of cases project finance deals exhibit:

1. A ramp-up period post construction completion. For example think of a toll road. As soon as it is completed it does not get the predicted baseline traffic. People don’t automatically switch to using the toll road from alternate routes, it takes time. 
2. CFADS usually grows over the life of the project. This is why project finance debt is often sculpted as opposed to repayments via a credit foncier profile. We will look at sculpted and credit foncier repayment profiles in the next blog tutorial. 

In the meantime let’s take a look at an example of CFADS. 

Example

Find the CFADS given the following information:

• Revenue = 50
• Expenses = (10)
• Capital Expenditure = (15)
• Tax = (5)
• Working Capital Movements = (2) 
• Depreciation = (10)

If you got a CFADS of 18, then you’d be correct. Don’t get fooled by the depreciation, which is a non-cash movement. 

Now let’s take a look at what the DSCR is. 

DSCR

DSCR stands for Debt Service Coverage Ratio and is defined in the project finance loan documentation. In most cases it is equal to the:

CFADS divided by Debt Service 

The DSCR can be taken over a forward or backward looking period, say 6 months, or simply during one individual period.  More often than not the DSCR is calculated during the operations phase of the project only. 

Debt Service is usually defined as the aggregate of senior debt interest, repayments/principal and in some circumstances may also include other fees.

As you probably already guessed the DSCR is a measure of the amount cash flows can cover debt. The higher the DSCR, the more cash flow is available for debt financiers as a safety net.  

There are usually two benchmarks DSCR’s, defined in the project loan documentation, which are compared to the calculated DSCR. The first is the lock-up DSCR and the second is the default DSCR. 

When the calculated DSCR is below the lock-up DSCR all cash is locked up and may not be distributed to equity, until such time as the calculated DSCR is above the lock-up DSCR again.

If the calculated DSCR goes below the default DSCR, then the company would be in default and there may be many mechanisms to deal with such default, such as the lenders stepping in to perform the company’s duties, selling the project etc. The default provision is done on a project by project basis. 

Let’s take a look at an example for calculating the DSCR.  

Example

If we have the following CFADS, Interest and Principal payments what is the DSCR for each period individually and also using a look-back of two periods.  

Figure 1: CFADS, Interest and Principal

Firstly let’s work out the Debt Service:

Figure 2: Calculating Debt Service

Now let’s look at finding the DSCR for each individual period and also the DSCR for a two period look-back.

Figure 3: Individual Period DSCR

Figure 4: Two Period Look-Back DSCR

You should notice something here. There isn’t sufficient cash in period 3 despite the look-back DSCR ratio being above one. Be careful of this and make sure your financial model has a check for cash flow shortfalls. 

Hopefully this blog clears up the fact that CFADS and DSCR are not words from foreign languages. 

If you like this blog tutorial, take a look at our Intermediate financial modelling courses.

As always we’d love your feedback, so that we can continue to provide content that is relevant to you. Also check out Video Financial Modelling's website, which has plenty more free resources and great training courses.  

 

 

 

 

 

 

 

 

Calling all Excel Logical Operators

Forget telephone operators, who almost always put you on hold, today we’re talking about Excel logical operators (also called Excel logical functions).
A logical operator can simply be defined as a function that returns certain values if a logical test is true or false.
We will look at three of the key Excel logical operators; IF, AND and OR.

Note: You can follow the examples by downloading the accompanying Excel spreadshset and YouTube video.

IF
The format of the IF statement is as follows:

= IF (Logical Test, Value if logical test is true, Value if logical test is false)

The IF statement checks if a logical test is true or false and returns a certain value based on the outcome. Let’s look at an example.
Example: Below is a table with people’s dates of birth. We want to find out which of the people are over 21. Let’s assume that today’s date is 31 December 2011.
Normal 0 false false false EN-GB X-NONE X-NONE

Person	Date of Birth
Bob	31 Aug 1990
John	30 Sep 1995
Bob	31 Dec 1992
Jane	30 Jun 1984
Sally	31 Dec 1991

See the formulas for the calculation in Figure 1 below.

Figure 1: Calculation for IF Function Example
If you found that Bob (the first Bob that is) and John are the only people over 21 then you’d be correct.
AND
The format of the AND statement is as follows:

= AND(Logical Test 1, Logical Test 2...)

The AND statement returns TRUE if all the logical tests are true and FALSE if any one of the logical tests is false. Let’s look at an example.
Example: We want to find whether the date 31 May 2011 is in between 30 June 2011 and 31 December 2011.
We can see the calculation for the above in Figure 2 below.

Figure 2: Calculation for AND Function Example
If you got FALSE you’d be correct.
This may seem like an easy example but its implications are far reaching. Imagine if you had lots of dates and you wanted to find which dates are between two particular dates, a start date and an end date. I bet you could copy this formula across or down a row to find which dates meet the AND criteria.

Remember our Blog tutorial on Car Indicators – How they can help you model in Excel? We did a similar thing in that Blog tutorial.
OR
The format of the OR statement is as follows:

= OR(Logical Test 1, Logical Test 2...)

The OR statement returns TRUE if any of the logical tests are true and FALSE if all of the logical tests are false. Let’s look at an example.
Example: The below is a table with test results for two tests recently taken by students. We want to find the students that either scored over 75% in one test or had an average of more than 65%.

Person	Test 1 (%)	Test 2 (%)
Bob	76.00%	65.00%
John	53.00%	74.00%
Bob	67.00%	65.00%
Jane	45.00%	65.00%
Sally	65.00%	60.00%

See Figure 3 for the OR calculation.

Figure 3: Calculation for OR Function Example
You'll notice that both the Bob's met the criteria.
If you like this article, there are plenty more tips, tricks and worked examples in our Excel Functions training course.
Check out our Excel and Financial Modelling training courses.