Debt Service Coverage Ratio (DSCR) Sculpting

DSCR Sculpting 

Overview

In this extract from a Pivotal180 course, you will learn the concept of Debt Service Coverage Ratio (DSCR) sculpting.

It is the most common form of project finance debt repayment – so if you work in project finance this is a must see.

Find out how to calculate DSCR with our free  How to PDF on Calculating DSCR.

Video

Transcript 

Welcome to this demo where we will do the calculations for DSCR sculpting based on the concepts you learned in the last module.

We’re still in the term loan demo file in tab 3U. That’s the unsolved version of this exercise. Tab 3S is the solved version.

We’re going to calculate the principal and interest calculations for an eight year model.

To make it easier to understand, we’re going to use here a tenor of six years, term loan interest rate of 5% per annum and a DSCR target of 1.30.

Our CADS is simply 50 million across every year except for year three where it’s 45 million.

Now, we mentioned earlier also that you should build flags before you do any calculations based on a specific time frame. Our time frame here being within the tenor of the term loan and so we do these calculations in row 18 and 19. In row 18, we’re doing a calculation to determine the operations year and that’s quite simple because this is an annual model for eight years. So, all it is, is the previous year plus one. So in 2021, it’s just year one plus one. The only trick I’ve got here is in 2020 because in column F, is before my model starts.

So we’ve styled that as a blank format to make it really clear for the user not to touch it. And it’s got nothing in it, so it’s a value zero. So, that’s what creates a value of one in 2020. Directly below I have formatted flags and that’s pretty simple. I

t’s just if my current year is less than or equal to my tenor of six years, then make the value one otherwise zero. So we see one’s across all the way to year six followed by two zero’s in year seven and eight.

You will notice that it’s linking here to C19. That’s the input for tenor. It’s styled blue because it’s a link format. It’s linking up to the actual input here in C8. This may seem like overkill because on the screen I can clearly see six. So I could have linked this line here straight up to C8. But it’s a good practice to generally bring the numbers down to where you need them. And, more importantly, here in column F, I’m calculating the total of this row, which equals six, so I can easily see my input of six years here and my output of six years here,right beside each other. It’s really obvious to the user. And how did I calculate, or how did I format this cell to show six years? Well, I can have a look at the format of the cell just by pressing control 1.

That brings up the formatting tab. And up here you can see I’ve created a custom format. It’s got zero with a space and then it’s got in quotation marks, YR[S] for years with quotation marks ending it. That’s how I’ve created the format for “years”. So let’s go ahead now and look at the calculations for our principal. We’re going to complete all of the green lines here, the lines that are highlighted in green. Just, they are there so you know which lines to do if you’d like to do this yourself, go for it. I highly recommend it. If not, stick with me and we’ll complete the calculations.

So, I’m going to do these calculations, but I only care about them within the tenor of the loan. It doesn’t matter if I calculate my repayments in years seven and eight when I’m not going to have repayments then. So, I want to limit all my calculations purely to this tenor through here. So, how would I do this? I want to start by calculating my CADS which have been put up here and I can limit that simply by multiplying by a flag.

So, I take my CADS and multiply by the flag. If I copy this across the row with Control R, you can see that it’s just multiplying my CADS by the one or the zero. So, by the time I get across to year seven, it’s multiplying CADS by zero and, of course, it yields zero.

So, I’ve now calculated my CADS within the tenor itself. Hopefully you’ll recall from the last module that our max debt service or principal plus interest just simply equals my CADS divided by my DSCR. So, you may think that, look I just now take this CADS and I’m going to divide it by 1.30. It’s not a bad assumption so let’s try it. I simply take my CADS divided by my DSCR and I want to put a dollar sign around the C here because I don’t want it to drag across as I copy right.

So, let’s copy this to the right. And it looks right, but be a little bit careful. Why? Well, could CADS reasonably be negative? Well, yes it could. Then you say, “Well, that doesn’t make any sense. Why would I have a project with zero CADS or negative CADS?” You may have for example major maintenance that causes this. And you say, “Well, okay. But, clearly I need cash to pay even my interest on my debt.” And you’re right. But maybe you would have actually set aside some cash to do that. But it’s not part of our cash available for debt service.

So, this looks right, but be careful. Could CADS reasonably be negative in a given period. It could be and you may think that’s odd, but it’s possible that you’ve actually set cash aside in a specific account to cover something that is not part of CADS. So, it could be negative.

So, let’s just play with this. What if I hard coded here, for a second a CADS that’s minus five. We can see my max debt service is now negative 3.85. That makes no sense. I can’t have a negative debt service. I’ve got to pay the vendors.

So, I want to make sure that debt service cannot be negative. How would I do that? First, let’s fix this. How would I force max debt service to be positive? Well, it’s actually pretty simple. If I take the max of that calculation and zero, it’s going to force a positive result.

So, let’s put this back to minus five and see what it gives us. Here it’s saying take the max of a negative number and zero and clearly the max of a negative number and zero is positive, positive zero. So, here it’s resolved the negative number that I had earlier.

So, that’s the only trick there with max debt service. You may also note that there is a dash line that goes across this whole row. That’s called a line calc format. And what that’s really telling us is that you’ve just done a calculation based on the row directly above. Quite often you would have a separation between these rows.

So, we use a line calc style to make it obvious to the user what we have done. Now we’re going to scroll down just a little bit so we can see the remaining calculations. So now we have calculated our maximum debt service we’re going to calculate our interest and our scheduled principal and then we’re going to use that principal to pay down our loan.

So, you may see this $200 million. My debt size, how did I get that? Well, I just made that up for now. We’re going to come back to debt sizing shortly. What I do have is a $200 million loan draw down in year one.

So, if I’m in year one, pick 200 million, otherwise zero. You’ll also notice that it’s in a controller count because we’re recording a volume over time. And it says plus because we are adding to the balance. Loan draw down B-O-P, BOP is Beginning of Period.

So, this draw down is a very first second of 2020. So, what is my interest calculation here? Interest is just the interest rate times the balance on my loan. Now we can see I have my interest rate of 5% down here as a link format linking back up to the inputs. And you’ll also notice that interest has minus interest here because we’re going to work out, we’ve worked out our max debt service of principal plus interest. Our scheduled principal is just going to be our max debt service less our interest. And we don’t like doing one number minus another number in Excel, we tend to like addition, so I’m going to use minus interest.

So, the formula in column G is equal to the negative of my interest rate. With a dollar sign in front of the C times the opening balance. But I have this 200 in this row which is a beginning, a period cash flow.

So really that is a part of my opening balance. The sum of these two values. So, of course, 5% times 200 million is 10 million in the first year. And I copy that across to the right. And because I haven’t yet started paying down my loan, my interest is flat every period and, of course, I haven’t got repayments in my loan so it still thinks there is a loan outstanding past the end of the tenor. So my scheduled principal is really going to be my max debt service minus my interest.

So here it’s going to be 28.46. Could I just take the sum of the two numbers above and copy across? Well, I could but remember CADS could be negative or it could be very low in a given period. Let’s say for example it were zero. If my CADS were zero, my max debt service was then zero, but my interest was minus 10, my scheduled principal just calculates zero plus negative 10 and it gives me a negative 10 result of principal. I.e. the banks are paying me back the money, again, this does not make any sense and I need to stop that going negative.

So, what would I do? If I want to stop something going negative, I use a max formula.

So again, I can go the max of this and zero. So, you see that not only did it fix an issue here if I make it zero, I get a zero principal which is much better than negative but it’s also affects out here, the fact that I have interest and no CADS to pay it. This will resolve itself a little bit later, but it’s useful to see.

So I’ve now calculated my scheduled principal of 166.92 and I want to link that down into my loan account so I’m actually starting to pay off principal. And if the loan has a positive balance of 200 million in this account, will the cash flows need to be shown as negative or positive? Well, the clue is off to the left here. We have gotten minus scheduled principal payments and I’m sure you’ve guessed that anyway so let’s just try that. Let’s say I take the negative scheduled principal and I copy across to the right. This looks broadly right, but let’s be careful you may start to see where I’m going with this as we need to always consider our inputs changing in a model.

So, for example if a number is, could the number be positive, could it be zero, or could it be negative and that includes our inputs for the model and most of the time through this model, the inputs are actually being CADS. Inputs the section. So, what if, for example, I made this debt size 100? Well, you can see here now, I’ve got 100 being drawn down and more payments are negtative going through here, but uh-oh, we have a problem here. In 2022, our loan balance from that point on is going negative. Clearly that makes no sense.

So, how do I deal with this? So, in year three we have 33.05 million available to pay the principal, but we only have a balance of 31.4 million. We can overpay our loan. The best way of doing this is using something called a MIN formula and we’re going to take the minimum of the current balance of 31.4 and the scheduled principal of 33.05. So, if I took the minimum of these two numbers it would put here, negative 31.4 which would pay off the loan. If this balance were zero because I paid off my loan, and that was 38.54, it’s going to pick zero, so it’s going to stop paying off my principal. If I go back into year two, I have a $66.54 million balance but a 35.13 million principal repayment. It takes the minimum of the two so it would take the principal in this case.

So here let’s change this formula now to the negative of the minimum of G25 and the current balance of the loan and in this case, we have to take the sum of the opening balance and any draw downs in the period. I copy this across, now we can see it stops re-paying the loan when the loan has already been repaid. And I can set the loan size back to 200 million. Now, I’m sure you’ve all got the same questions each other here. I’ve got a $200 million dollar loan, but only 189 million of repayments. Now from a best practice standpoint these totals, they tell us a lot and hopefully that becomes obvious to you.

So what’s happening is, I end up with a remaining balance, a residual balance of 10.86 million in year six. And of course, I’m calculating interest based on this 10.86 million. Banks won’t like this. We clearly want to re-pay our total loan within the tenor because the banks are saying to us we will give you a loan, but you must pay it off within the tenor and you must maintain a DSCR of 1.3.

So, our job in debt sizing will be to change this debt balance such that we maintain a 1.3 DSCR in every period, including the last one and our loan is paid off in the last period, I.e. year six. Debt sizing will be covered shortly. Before we get there, let’s do a check. We want to check that we’ve actually done our sculpting correctly and the best way of doing that is to calculate an output. Our output DSCR in row 35. In DSCR, it’s simply CADS divided by debt service which I’ve already linked down here for you..

So I’m going to be dividing row 33 by 34, but again let’s be careful. We’re going to check what if these values go negative, what if they become zero? If CADS were negative, negative CADS divided by debt service, I’d get a negative DSCR. Fine, that can happen. Debt service can’t be negative we’ve already made sure that has to be positive or zero. So, it could be zero, in fact it will be zero once I get out to here and I’ve actually sized my debt. We know we can’t divide by zero because that gives us an error. So, we’re going to use an “if” statement here, we’re going to say equals if debt service equals zero then zero, otherwise our CADS divided by debt service. And I’ll copy that across the row. What’s great about it? I can see my DSCR is 1.3 in every period so I’ve sculpted my debt correctly, there’s no problem there. I just haven’t paid off my loan, which in itself is a rather large problem.

We can now also calculate our average DSCR and our average DSCR is simply our CADS in the loan tenor divided by debt service. Now, this is starred as a red cell because it’s a rather important output. And what might be surprising for this number is it’s not 1.30. That tells me something is probably not right. So, why is that? Well, debt service here continues out into year seven and eight because I haven’t yet sized my loan correctly.

So, it’s telling me that even though I think I I have a 1.30 DSCR every period, my average DSCR is 1.29. It’s telling me that I have an issue. So, hopefully you can see why we might calculate an average DSCR. So, this is how DSCR sculpting is done. We still have to do debt sizing and we will come to that shortly. But, before we do that, lets recap on the main parts of this because this has been quite a challenging exercise.

So, let’s review the main elements of the calculations. We started first by rearranging our DSCR formula to solve the repayments which was just the principal is the CADS divided by the DSCR less our interest. But it wasn’t quite that easy. We needed to employ a few tricks. Now, the first is that CADS only need to be considered during the loan tenor itself. We limited CADS to the loan tenor by multiplying CADS by the tenor flag. And CADS could possibly or potentially be negative but debt service itself can’t be negative, so to insure a positive debt service value, we made debt service equal to the max of our CADS divided by DSCR comma zero. And if interest calculated is greater than CADS we don’t have enough CADS for debt service. And so the formula of principal equals CADS divided by DSCR less our interest would be negative if interest is higher than CADS and negative principal does not make any sense. Lenders will not pay you the principal of the loan.

So, we again use the max function to force a positive value. Where the principal equals the max of debt service minus the interest comma zero. And we could also be at risk of overpaying our loan if the available cash for principal repayment is higher than the loan balance itself. And here we use the MIN (loan balance comma available cash for principal repayments) to avoid this. And given we have a loan balance to record over time, of course we build an account. And we also calculated an output DSCR to check if the DSCR was, in fact, the DSCR that we sculpted our debt to.

If we had a 30 year loan, a graph of the DSCR may be a much better way to view this output. Note that we haven’t yet sized our debt, and we will come to that in the next lesson. And once we have sized the debt for our project, our debt repayments calculated under this sculpting method will be fixed. This means that they will be hard-coded and recorded in the loan agreement.

Sculpting is only really to structure the loan. Once we are in operations, the repayments are fixed.