DSCR Sculpting Webinar – Video

Pivotal180 Webinar  –  Recorded Video

Missed it live – feel free to watch the recorded video of our webinar

DSCR sculpting – structuring debt repayments for project finance transactions.

Whether you work in renewable energy, infrastructure, or mining project finance, the debt service coverage ratio (DSCR) is a must-know. Debt is structured often from the DSCR, through a process called DSCR sculpting.

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

Transcript 

Welcome everyone to today’s webinar for Pivotal180, called DSCR sculpting. Thank you for joining. My name is Haydn Palliser and I’ll be leading the webinar today. Before we kick to talk about the items we’re going to cover, just a little bit about us ourselves. This is me down in the bottom left, Hayden, Palliser and my colleague Dan Gross, on the right hand side. We primarily advise, teach and we coach clients through complex transactions, so predominantly within the renewable energy, infrastructure, and project finance world and we do this mostly through training courses, so we provide online training courses. We also provide public sessions that are open to anybody. Anybody can come to, one of our courses and learn how to do project finance with people from other organizations and that’s always good from a networking standpoint. We also do highly tailored courses at corporate offices so we can come to your organization and tailor a course specifically to you based on your sector or the particular challenges you’re trying to address.

We also teach on campus as well. So we can do sort of one or two day courses for students who were getting into the market as well. I think what’s unique about our offering is we teach not only modeling, but we also teach how to review and understand commercial concepts and documents. We believe that to be a good modeler you also need to be able to read the core transaction documents as well and understand that the commercial terms and drivers of investors. And the other part that’s unique about us is we have the online training. So if you attend one of our public sessions, you also get access to the online content as well.

I’ll be running the session today. So, let’s jump into the agenda.

So in terms of what we’re going to cover, the first I’m going to talk about as some of the important terminology around debt service coverage ratios. We’re going to talk about different repayment types. And primarily the ones that we’re going to focus on are annuity, linear, and DSCR sculpted repayments. And we’re going to compare and contrast these different approaches. And as part of that, we’re going to determine why DSCR sculpting makes sense within project finance. And of course we’ll then go into the concepts behind DSCR sculpting by rearranging the DSCR formula. And we’ll talk about the concepts behind it before we jump into an Excel model and do the calculations together. And this question I’m sure will come up from you. Can I get access to the Excel spreadsheet? Absolutely. Of course. As long as you’ve registered for the webinar, you will be able to download the Excel spreadsheet as well.

So before we get into the debt sculpting part, let’s just take a quick step back and remind ourselves of the benefits of debt on the transaction. So let’s assume we have a project with a $100 outflow in year one, or capital cost, and at the end of year one we’re going to receive a $109 sale price. So if we did this on an unlevered basis without debt, we would have zero debt and would fund it entirely from equity. And so our profit on exit is just going to be $9 that’s the 109 minus 100 and because we have no debt, we don’t have any sort of interest expense. So our distribution to equity is going to be $9 so the profit less interest. So what’s our return on equity without debt? Well, it’s simple. First it’s our distribution to equity.

The benefit equity to receive the $9 divided by the amount of money equity put in, which is 100. So our return on equity is 9% Let’s contrast that with if we lever a project and let’s say we have $60 of debt and $40 of equity, so it’s leverage of 60% for those outside of the U S gearing of 60% and let’s presume we have an interest rate of 5%. Our profit from the project itself is still $9 we’re still making $9 on the hundred dollars of total investment, but in this case we’re going to have to pay interest to the Lenders and with debt of 60 and our interest rate of 5% we have interest expense of $3 so our distribution to equity is six. So we’re sending some money to debt and keeping a little bit less than we had before for equity. But our return on equity is changed and that we’ve now got a $6 profit just to equity. But the amount we invested is only 40 so we’re return on equity has gone up to 15% so the important takeaway here is that well debt will financially increase our returns in project finance. But this is a rather simple example and its just a one year set of cash flows and project finance has 10, 20, 30, 50 or even 99 years of cash flows.

The concept is equity investors would achieve higher returns with high leverage and part of our job as a project finance analyst is to seek to maximize our debt to increase our returns. And in the context of project finance, leverage depends on a few things. First and foremost, it depends on the cashflow that our project is going to generate. Called cash available for debt service often called CADS. And so it’s the ability of our project to repay our debt through the cashflow itself. The next thing is debt service. The total amount of money I’m going to pay to our lenders and the other really critical part of leverage and project finance, at least within most sectors in most countries, is something called the debt service coverage ratio. Often abbreviated as DSCR. So before we jump into debt sculpting, we need to fully understand each of these items themselves.

So starting with CADS, now there’s a concept of, in the US at least, that the project vehicle SPV does not pay the federal tax. The ultimate shareholders pay that. And that’s called a pass-through entity. And we talk about this in a fair amount of detail on our courses, around the concepts of project finance. So what is CADS for a pass-through entity where we’re not paying any federal tax on that level? Well it’s simple and that it’s the project revenues. The revenue from the project we add to that any investment income. Investment income is our interest on our savings accounts. For example, the cash we’re holding at the project company and we’ll always hold some cash there to pay for our costs that might occur in the next month, the next day. We take off from that, any operating costs that we have to pay to run our project, we’ve got to make sure we can run our project before we think about paying debt or equity.

We will also take off any required deposits into reserve accounts. And an example of reserve accounts, for maintenance, capital costs for example. We may have a very big maintenance capital cost in say year five and in that particular year we don’t have enough cash to pay the maintenance costs. So we start to set aside money earlier and because we require that maintenance to run our project, we need to pay that before we pay anything to debt or equity. And of course in that context we also need to pay our capital costs. Anything that was required to operate our project. If we’re in a country, we have taxable entities, i.e., the SPV of the project has to pay tax. It’s exactly the same thing with one exception and that we also paid tax at that level as well. So it’s just really all about operating cashflow from the project itself.

Now the one question that I have when we get to this is why do we call it cash available for debt service and not cash available for the firm or free cash flow to firm as people from a corporate finance background often think about. Because this is all of the operating cash flow out of our project and it’s for equity and debt. So why cash available for debt service. And the answer to that is that debt is going to be paid first. So we work out our CADS, debt gets paid, and equity just gets everything else that’s left over. That’s why we call it CADS or cash available for debt service.

So that’s CADS. Debt service, on the other hand, is relatively straight forward in that we are going to pay some sort of interest expense to the lenders for borrowing money and we may also have some fees on that as well. We need to pay off the loan, that’s principal amortization and we pay that loan off within the term or the tenor of the loan itself. So debt service is then defined as our interest expense and fees if applicable, plus the principal amortization. So using a simple example of this, let’s say we have a five year loan and we want to work out our total debt service. Let’s presume our loan is $100 million and if we assume say a 5% interest rate with five year straight line principal amortization,

our interest in year one is just 5% times $100 so interest is 5 million. If we’ve got five year straight line principal amortization it’s $100 as our balance divided by five years, we’re going to pay $20 million off every year.

And so a total debt service is $25 million for the year. And as a quick plug from an accounts or best practice standpoint, you can see we have a debt opening balance, we have minus principal amortization as we pay it off and a debt closing balance of $80 million. That’s the balance at the end of the period and our opening balance will just equal our closing balance at the end of the last period. So we can now repeat the same calculation. My interest is just 5% times the $80 which is $4, my principal repayment is still the same, it’s $20 it’s the same every year. So I have $24 million of debt service and my closing balance is $60. So that’s my debt service itself.

If I move across to the debt service coverage ratio, this is the most commonly used ratio in project finance and it’s simply defined as CADS, that’s our cash available for debt service divided by debt service. And so here we have a set of cash flows in year one to four of $60 million of CADS and we have 5 million in year five. And we have our debt service going down as we just calculated from year one to four and then in year five, I’ve made that equal one. And you’ll see the reason for that shortly. So given that our DSCR is just CADS divided by debt service, I take CADS and divide by debt service.

But the question for me here is what does this really mean? And the easiest way to think about this is it’s just a measure of the health of the cashflows or CADS relative to debt service. So a DSCR of 1.0x means we have just enough money to repay our lenders. So if my CADS were $25 million and my debt service was $25 million, my DSCR is 1.0x

Or a 2.4x DSCR means the first 1.0x goes to the lenders, and the remaining 1.4x goes to equity. So if my DSCR had been 1.0x debt gets repaid, but equity gets nothing. And banks will set in a transaction a lowest permissible DSCR value, which they will monitor throughout the project. They will be interested to know what the minimum was. You must always maintain a minimum DSCR of their specification.

So given that we often have to calculate a minimum and an average DSCR. So a minimum is relatively simple. We just take the minimum of all of all of our DSCRs and in this case we’re getting 2.4x which is in the first period, which is actually quite common because our debt balance is the highest in year one, which means that interest is also going to be higher, particularly with a straight line amortization profile. And although the next one is not specified in a facility agreement or loan agreement, what we notice about the DSCR here is it is just measuring the health of the cash flows relative to debt service in each individual period. It’s not really telling me the overall loan, how healthy all the cashflows are relative to all of the debt service. And one way of dealing with that is through the loan life coverage ratio LLCR, which is quite commonly in loan agreements but very rarely in the U.S and renewable energy.

Each country is a little bit different. But one thing we almost always model is what’s called the average DSCR. The first way of doing it is you could take the average of all of the numbers on screen from 2.4x through to 5.0x so my average of those is 3.05x which is just taking each of these DSCRs, adding them together and dividing by five. We can see a value of 3.05x it’s applied an equal weighting to each of these DSCRs. And in the last period I’ve got a DSCR of five so my average is 3.05x, which doesn’t really look like the total average because it’s taking the equal weighting each of them. So a more preferred approach is by taking the total CADS and dividing by our total debt service over that period and that gives us a DSCR 2.58x, which looks a lot more reasonable and more realistic in terms of our average DSCR. The only trick here is we have to not, sum our CADS from years beyond the tenor of the debt.

So that’s the DSCRs and now we understand CADS is really our operations or operational cashflow from the project, we know our debt service is what we pay our lenders, which is primarily interest and principal, and our DSCR is CADS divided by debt service. And the critical component is lenders will set a minimum DSCR. So knowing that we can move into the repayment types. What are the different options for borrowers when we negotiate a project finance loan? One, and by far the least common, at least that I’ve seen in the market is mortgage style. Also often called an annuity. And we’ll go through each of these and they’re in the slides. The next is called linear principal amortization. It’s used more frequently than mortgage style, particularly by developmental organizations. They tend to use this a fair bit more. Then we’ll go into the DSCR sculpted approach and some other ones that we won’t cover in this webinar due to time constraints but are covered on our courses, such as the bullet and balloon repayment, and also a variation on above, something called a mini-perm, which is really, really common in the markets right now.

So starting with the mortgage style repayment, this represents a set of total cash flows to the lender over a period of time. And the red is the interest and gray is the principal. And the insight of this is that debt service or principal and interest is exactly the same in every single period and to force that and you can see that our interest is going to decline over time, which makes sense because our loan balance is going down and interest is just the interest times the opening balance and because of that to our principal is going to increase at a time such that our total repayments are the same.

Now the other thing you’ll see about the amortization profile is it’s kind of back-ended because we’re paying much more interest in the front than we are on the back end. We’re paying much more principal off of the back end of the loan. So our amortization profile is a curve coming down over time and accelerating towards the end of the loan. And the easiest way to calculate our mortgage style repayments is with the PPMT formula. But again, this is less common. The other one. The next one is the straight line and a straight line just means we’re paying the same amount of principal off it every year. So in our example earlier we had $20 million for five years, and again I’ll, interest is decreasing over time as our loan is repaid, but our principal is the same in every period. Our total debt service is therefore reducing over time, the mortgage style had a flat repayment profile.

This has a decreasing repayment profile. And what’s kind of nice about straight line is it’s very easy to calculate from an amortization standpoint. First of all, the loan balance declines on a linear basis. So if my loan size, were $100 million at the beginning, my loan balance, in year five of a 10 year period would be half of the loan balance. So it’s just the five divided by 10. Or if I have a $100 loan with a 10 year repayment period, I pay $100 divided by 10, or $10 in each period. So it’s an easy one to calculate.

Now the problem with both linear and mortgage style, and here’s my linear, my total debt service is going down over time and with my annuity it were flat across the whole period. It’s great. But my cashflows in project finance do not mirror, that sort of amortization profile. And there’s a couple of reasons why. One example is in renewable energy projects. Unfortunately for planning purposes, the wind doesn’t blow the same every day or every quarter. That’s the same for the sun shining and some infrastructure projects you might see a more constant cashflow profile. So the wind could in quarter one be very different to quarter two and quarter three and quarter four they could all be very different. And in the next year you follow the same pattern again. Quarter five is just like quarter one and quarter six is just like quarter two.

And so because our wind is blowing differently, our CADS will be varying as well. So our CADS is moving. Another example could be maintenance. So maybe every third year we have to actually pay some maintenance. In year one and two we have flat CADS but year three goes down and the same thing in year six. So our CADS is reducing in every third year. Another extremely common one, you’ll see this across all of project finance, including across infrastructure is inflation and this may result in cash flows increasing over time. So you might see your cashflow slowly going up. Again, it doesn’t look anything like our amortization profiles we’ve already discussed and you can see that the cash flow is increasing over time. So let’s take those and say we have a cashflow profile like this. So the green representing CADS and in year one we have CADS here increasing slowly in year two for inflation.

And year three we’ve got some sort of maintenance. Year four and five we keep stepping up, we have maintenance again, in year six and in year nine. So there’s a total cashflow profile. So let’s overlay an annuity or mortgage style repayment on this. And bear in mind what’s really important here is the lenders will set a minimum DSCR requirement. So we remember this is our total debt service and our DSCR is a relationship between CADS and debt service. And this gray here is total debt service and with a mortgage style repayment, our interests will be declining and our principal will be increasing over time. So I would paying much more principal in year 12 than in year one where there’s a high interest component. So where here is our binding constraint for our, DSCR? It’s here in year three so we’re bound by a minimum DSCR in year three. If we look at say year five we’ve got a much, much higher DSCR and that gets even higher in year 12 and 13 so it’s more slack for debt coverage outside of that one minimum period. What’s important to recognize about this as well is that if you think about this gray line being principal and interest and the principal is increasing over time, that total principal or sum of that principal is going to be our debt size. So this doesn’t seem very efficient.

If I go now across and replace this gray line with straight line principal amortization, it’s even worse. My total debt service is going down over time. I’m still bound by this constraint in year three of 1.3x my DSCR is even higher and yeah, 10 11 and 12 so in this case, and probably going to get even less debt than I got on mortgage style repayment, this is very inefficient as a repayment profile. So what we most commonly do in project finance is we use what’s called DSCR sculpting and this is what it broadly looks like. We are going to maintain a 1.3x times DSCR every single period. So you can see in periods of higher cashflow, we maintain a 1.3x, when we have lower cash cashflow we maintain that 1.3x as well. So there’s just no slack. And every year we have a 1.3 DSCR. And again, recognizing that the gray is total principal and interest and a big chunk of that is principal.

The total volume of principal here is going to be much higher than with the annuity or the linear repayments, which is why the DSCRs sculpting approach is most commonly adopted. And the banks care about the minimum DSCR as long as your maintaining that you’re ok. So how do we do this? Remember that the DSCR is just our cash available for debt service or CADS divided by principal and interest. And so we’re going to back solve this equation to work out our principal. So starting first with our principal and interest, that’s our total debt service. You’re rearranging this formula it’s just our CADS and we take DSCR out at the other side of the equation. It’s just CADS divided by DSCR. Now to work out principal, we bring down just principal. We keep CADS divided by DSCR and we move interest to the other side of the equation. So our principal, it can be calculated by taking CADS divided by DSCR minus interest. So let’s look at this conceptually first with an example before we jump into Excel.

And the top line here with CADS, I have CADS going across in year one of $45, year two of $30 etc across the row, and you can see it’s kind of lumpy CADS, it’s changing in every period quite drastically. Let’s say I also had a tenor of four years and I’m going to use a DSCR of 2.0x It’s quite a high DSCR but it’s easy for you to see when I divide by two as it’s a nice round number. So my total debt, service in year one here it’s going to be $45 divided by my DSCR of 2.0x which is $22.5. It’s the same calculation in year two where I take $30 and I divide by 2.0x to get $15. And it’s the same across the whole row. And my total debt service based on these cash flows generated by the project is $72.55. Let’s presume now that we also have a debt balance of $80 million, we start with a loan of $80 million and an interest rate of 5%. Well we need to calculate is our principal amortization here and we have our total debt service, which is principal and interest.

We want to take off the interest like our algebra we just looked at. So with a 5% interest rate, 5% times $80, my interest is $4m, which leaves me the $18.5 to pay off my loan. So I can pay off my loan with that amount, reducing my balance, now down to $61.5.And in the next period my interest is just the 5% times $61.5 which is $3.08 leaving my principal to $11.93, which again I pay off my loan and that continues through the loan. Now what’s really important to know is that unlike the linear and Mortgage style repayments, where I just took a loan size and forced a particular repayment profile in, DSCR sculpting is taking the cash on getting for the project and working out how much I can repay based on that. It’s a very important concept in project finance. So based on these cash flows I’m working out my principal, and interest in order to achieve a DSCR of 2.0x. And to check that we should calculate the DSCRs.

So, in year one, we divide CADS by debt service and of course $45 divided by $22.5 is 2.0x because we worked out our $22.5 by dividing by 2.0x It’s the same in year two and all the way through to the end of year four. So the DSCR is 2.0x in every single period, we’ve sculpted our debt fully. One thing you probably noticed when you look at this is CADS here is not high enough to actually support the loan size of $80 million. We’ve still got $18.23 million left at the end of the tenor. So that’s a problem. All we’ve done here is we’ve matched our repayment profile to our cash flows themselves as opposed to with a mortgage style or linear where we’ve just taken a loan size, of say $80m and forced a repayment profile and which will give us different DSCR. We haven’t sized our loan and that’s another item which we can’t cover fully today, but that is covered in all of our project finance courses. It’s actually really quiet straight forward. But you can see we’re not fully paying off a loan, we’re just sculpting out debt to the CADS itself. So this is the concept. And in order to have a look at this a little bit more is we’re going to jump into an Excel example.

You can see on screen here and this is the one you’d be able to download. I’ve got my term loans and discount rates spreadsheet and I’m looking at the DSCR sculpted and I’ve got down here and unsolved tab and I have a solved tab. If you want to afterwards have a play yourself at doing this, this is a great way to approach it. I’ve clearly separated my inputs from my calculations in a best practice approach. If I opened this up, we can see the main inputs themselves and I have a model start date of end of December, 2019 and I have a model that’s going across for eight years, to the end of 2027. We’re gonna have a loan tenor of four years and an all-in interest rate for my term loan of 5%. And I’m going to use a 2.0x DSCR just to see the math. And finally I have CADS and these CADS here match what we saw on the screen, a short while ago and beyond that we have CADS of 20 million every single year.

I’ve got nice units labeled down here as well from best practice standpoint. And of course I’ve styled my input cells as assumption style. If you look at our styles, webinar and example videos, you can follow how that’s done. I’ll open up my calculations here. I’ll just go through the, the parts that are already completed before we do the calculations. At the very top here in row 18 I’ve got my operating year. So this just takes the previous value and adds one to it going up to, year 8. And then I have my tenor of four years and as a best practice standpoint, I’ve brought down my inputs from above down to the line where I’m actually going to calculate the values on and that input is four years. Four is the value in the cell with years being formatted, again that is covered in our courses and our free videos online. So in order to calculate my tenor, I have built a flag and it’s really important to me to build a flag when there’s any timing related item, and there is a good video on this one as well called best practice flags and conditional formatting showing you how to do it. But really the formula here is if my current year is less than four years, then give me a one otherwise a zero. What that means is if I’m in the tenor, give me a one.

I’m going to calculate my CADS, and my scheduled principal. Were going to do that calculation together and we have below a loan itself. We have a control account where we have an opening balance, which always equals our closing balance of last period.

We have a draw down on our loan and it’s, if we are in year one, I’m going to draw down $80 million and this $80 million is an assumption sell style. While it’s not best practice itself, in order to not scroll around in this demonstration, I brought it down for your benefit.

If I am in year one I am drawing down my debt, I’m drawing down my debt at the beginning of the period, it’s added to my loan account and I’m repaying my principal in each period after that. So my closing balance is just equal to my opening balance plus any movements in the account. And down below we’re going to calculate the DSCR together.

With that, the first thing we are going to calculate, just like the example you saw on the screen earlier, is our CADS within the tenor of the loan. And our CADS is up in row 14, so I’m going to take our CADS from row 14, but I only want to see for the purposes of debt sculpting, I don’t care about CADS that’s outside of my tenor. I’m only sculpting debt within my tenor. So I can multiply this by my tenor flag, which is just a one or a zero. So from year one to four it’s going to multiply by one. And then once I get across to get five, it’s going to multiply by zero. I’ll copy this across the row. You can see it’s $45 times one, $30 times one etc. And so my CADS within the tenor is $145.10, much lower than my total CADS. So what’s my total debt service, the principal plus interest? Remember I rearranged my DSCR formula to say principal plus interest equals my CADS divided by my DSCR. So fundamentally I could just take the $45 divided by my DSCR.

So that’s broadly the equation. The problem is if I copy this formula across to the right, using my control R as a shortcut, and press F2, you can see, well I’ve dragged this reference one cell to the right. I didn’t want that. I wanted to lock the reference to column C. And I can do that if I press F2 like I have now, I go across pressing left arrow and, press the dollar sign, I can lock the reference to column C. If you copy this across the row, you may think that’s the right formula. And broadly it is, the challenge is occasionally when you run downside sensitivities and scenarios, you CADS could become negative. And we know that debt service can’t become negative. You know the lenders are not going to give us money back because we’ve got negative cashflow. So in order to fix that, we want to force this value to be positive.

So I’m going to hard code in here, say -10 as an example. I want to avoid this, I don’t want to take -10 divided by two. So the way I could deal with that is I can force this value to be positive. And the most common approach for that, is i’ll add a max function at the front of the calculation. So I’m taking the max of my cash divided by DSCR and zero. So if the calculated CADS divided by DSCR were negative, the max of a negative number and zero is zero and forces my ballet to be positive. So even with my hard code, a value of 10 here, I copy this across, it would now show I’ve got no debt service. That’s better than a negative value which you clearly couldn’t have. So it’s always worthwhile thinking about could the input I’m using, in this case CADS, be negative or positive or really, really big or really, really small.

We have an interest rate of 5% here and that’s an all-in just right and our loan balance in year one is going to be the drawdown amount because that’s the beginning of a period. So again, this is broadly going to be the sum here, which is our loan balance at the beginning of the period and any drawdowns that we make at the beginning of the period times our interest rate. Again as I copy this formula across to the right, I would not want this to move but I will want this section to move. So I’ll put a dollar sign in front of the C. I’m not quite done. First of all, remember that I’m going to take my max debt service and I’m going to take off my interest to work out my scheduled principal. And we generally don’t like doing formulas such as this; equals that minus that. Now if you have for example, many, many lines above this, if I had 10 lines and I was going row five minus row six plus row seven minus row eight. It’s hard to check. So generally we like to add numbers together instead and force things to be negative in order to do so. And so one other change we’ll make to this is I’m going to make this value negative such, that when I come down to this line, I’m going to be adding the two lines above. That’s going to be much clearer and I’ve got this minus in the front, my interest.

There’s one other thing I can do here to tidy up this formula. I’m only going to be paying interest within the tenor of my loan. So if I’m only paying interest within the tenor of my loan, I need to limit the interest to within the tenor. And the simplest way of doing that is I’ve created this very beautiful flag here, in row 19 that I can multiply by. And so if I multiply by when I’m in the tenor itself and I multiply by one, I get the interest outside of that, I’ll get a value of zero.

So my formula here is just the sum of my opening balance itself, times the interest rate, times the flag. And I as I copy this across, you can see my interest is not declining, which is very odd. And the reason for that is my loan balance is not declining because we haven’t yet put in our repayments themselves. If I had not multiplied by the flag across here, I would have also been seeing 4 million. Now, although once we size and structure our debt that’s no issue, it’s always best to multiply by flags immediately, to avoid potential errors. So my scheduled principal is just the sum of these two numbers. But again, you’ve got to be a little bit careful. I took the sum of these numbers above. What could go wrong?

Now what could go wrong in some cases if I didn’t have such a high coverage ratio than I do here, is that my interest could be bigger than my total max debt service or max debt service being just my CADS divided by my DSCR. So for argument’s sake say I had a really big loan of $800 million and my CADS was only $45 million in year one, which meant I had $22.5m of debt service. My interest is $40m, my scheduled principal can’t be negative. Banks are not going to give me money back. So again, I need to force this one to be positive, and the way you force numbers to be positive is generally to use the, max function and zero.

And in this case with a $800m loan, I’m not paying any principal off. I don’t even have enough money to pay my interest. So that’s an extreme result. Now part of best practice modeling is constantly changing and thinking about how things will move. And the first one I did was make CADS negative. Second thing I’ve done here is make debt $800m. I could just as equally make it really, really small to make sure it works. So I’ve got an $80 million loan and debt service of $72.5m, which means I’ve got $56.55m available for principal. So we need to link down our scheduled principal into our loan account. And again, broadly this is the trick with this. It’s just a negative of our scheduled principal. If I copy this across to the right, one thing you’ve noticed, let me undo that, sorry. Right now I’ve got, or I had $56.55 million of scheduled principal.

If I linked this down here, and I’m going to make this negative because this is a repayment on my loan account I’ll make it negative of my scheduled principal. Remember $56.55. If I link this across here. Now what’s happened is my loan is started to be repaid every period, but instead of $56.55m, I’ve got $61.77m. Why have I got more principal available now? Well, it’s because I’m paying down my loan. And so my interest itself has reduced from four every single period to reducing based on the balance of my loan. So because my scheduled principal is just my total debt, it’s less my interest. If my interest gets smaller, then my principal gets bigger. And so you might think, we’ve got the right answer again, but we don’t. So what could go wrong here? Now the answer to this can be quite difficult, but I’m going to make my loan size small.

I’ll make the loan size $20m, make it $25m. With my loan size of $25, I’ve still got the same CADS because my project still generates the same. My total max debt service is the same. My interest is now much lower because my loan balance is lower, which leaves me quite a large amount of scheduled principal. And so in year one, I drawdown $25m but I have $21.25m left to pay principal. So I’ve got $3.75m left. I come across to this period, and I’ve only got $3.75m left on my loan balance, but I’m paying off $14.81m! Again, that’s just silly. It’s crazy. It’s nonsensical. I don’t want that. What I really want to do here is I only want to pay off $3.75m. So let me just change this number to $40m and look at some alternatives. In column H, I’ve got $14.03m available for scheduled debt service, but I’ve got a $19.5m balance.

How much do I want to pay off? Well I want to pay off $14.73m. That’s the max amount of cash I’ve got. I want to use all of that to repay my loan. Once I come to column I, I’ve got $27.23m of cash available but only $5.48m of loans. So I only want to pay $5.48m I’m not going to overpay my loan, that’s a little bit too friendly to the banks. So what do I really want to take here? One way of thinking about it is if my scheduled principal is less than my opening loan balance, I want to use the lesser of those, which is this scheduled principal. In this period, if my scheduled principal is greater than my loan balance, well I want to just use my loan balance? So again, the lesser of the two, so I’m picking the smaller of this value, these two values here and the smaller of these two values here. So like we used the max before, if I want to pick the smaller of two values, I can use what’s called the MIN function. I’ll just type this formula out.

So I can use the minimum function. Where it’s taking the minimum of my scheduled principal in that period, the maximum amount I could repay based on the cash flows, and my current loan balance outstanding. It just takes the minimum of those two at every point in time. So with a $40 million loan balance, I pay everything off, but well before my tenor. With an $80 million loan balance, I’ve got $18.23m leftover just like in our example. And we’ll go through the debt sizing itself in our courses. Again, just like our example, we want to calculate our DSCR to prove we’ve got the right number here and that number is relatively easy to calculate. We can just take our CADS and divide it by our debt service, and copy that across the row. And we expect here to get 2.0x and every single period. But we get this divide by zero error when we come across to column K. We don’t want that. So that makes sense though, we can’t divide by zero, it’s not an Excel problem, we just can’t divide by zero. It doesn’t mathematically make sense. So here to fix this problem, some people use IFERROR, which I don’t tend to like because you don’t know which error is being identified. I tend to use something a bit simpler. I can say if G34 equals zero, then give me a zero result, otherwise calculate my DSCR. So in the case when I go across here,

it’s saying if this value is zero, there just give me a zero result. I don’t care about my DSCR. I clearly don’t have debt service, so it’s irrelevant, otherwise take CADS divided by DSCR. I’ve now calculated all my DSCRs and my average DSCR, although it’s going to give me the same result right now, if I took the average of these, I’ll get 2. If instead here I took the CADS within the tenor, and divided it by my total debt service, I’m getting 2.0x. I’m getting the DSCR that I expected. I’m getting the same result here. Unlike our example on screen. So just to step back before we jump back to PowerPoint, it’s a really simple concept generally just to rearrange this formula, you just have to be extremely careful that values can’t be negative and check your loan size if it’s really big or if it’s really small. Always be looking for those sort of things. Obviously build an account for debt. And what you can see here from best practice standpoint is I’m drawing down $80m I’m only paying $61.77m. The power of totals is, extraordinary. And I’m using a flag here. And what I love about flags is when I just look at this section, I can see my flag and all my calculations are just so neatly under that. So I’m pretty sure I’ve got my timing right on my model as well.

So relatively simple, but when you go into say, monthly calculations or full quarterly model in our course, we go through that as well, but this was the core concept itself. With that we’re running out of time. So I wanted to see if anyone has any questions in relation to that. You can also see or download the spreadsheet itself. So feel free to, to download that. Does anyone have any questions? Okay. So, the first question is ‘how do size debt? And that’s a great question. It’s a little bit too much, for this particular timeframe we’ve got because, there’s a few things that go into that. Cconceptually it is very simple, but, we will cover that in our courses itself.

No other questions?

So just a little bit about our upcoming courses as well for those who want to join. Our next course we’ve got in New York is renewable energy project finance modeling. And if you come to this public course, you also get all of the online stuff as well. And this course is three days, so it covers debt sculpting, building up all of the operational parts of renewable energy project finance transaction, going through the P values with generation. It goes through operating costs, debt size, we sculpts debt. We optimize our project to achieve a target PPA price. And after three days you get the online access. On the back of that, we have our tax equity and modeling course for one day.

Although we introduce tax equity in our renewable energy project finance course, the tax equity won’t go into a lot of detail because it’s a complicated topic. And then we have an infrastructure and project finance modeling course in New York on the 3rd of March. It’s similar in terms of it’s project finance modeling as renewable energy, but we focus on the different types of infrastructure. They have, core metrics that you use in infrastructure and we also focus a little bit on transport infrastructure itself as our case study. We’ll have courses, in Toronto without upcoming courses in Europe, and in Houston. These are the next ones coming out that you can always sign up to.

So I hope you enjoyed the webinar and thank you for joining us and we hope to see some stage on our courses. Thank you.