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.
- Using a credit foncier repayment profile (don’t worry we’ll talk you through the concept in a second)
- Using a target DSCR of 1.30x
- 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.
No comments:
Post a Comment