We are now going to produce a sculpted debt repayment profile in Excel. What you'll see is some assumptions. We've got a minimum debt service cover ratio for this project, 1.5 times debt financing of 65% and an interest rate of 10%. And here are the cash flows available for debt service on line 12.

The first thing we're going to do is sort out when we are going to have a repayment period. You can see that for the first three years, the cash flow is negative. That is likely to be because it's a construction period, so repayments will commence only once the construction period is over and the operating period begins. So we'll create a normal flag here that says if the year is bigger than year three, then it's a one, otherwise it's a zero, which means there won't be any debt repayment periods in the first three years, but thereafter it will be. Now let's look at what the maximum debt service allowable would be during the periods when there's no debt repayments possible anyway, because the cashflow's negative i.e. years one, two, and three, there's not going to be any debt service allowed anyway. When we're beyond that period, then all we need to do is take the cashflow and divide it by the minimum debt service cover ratio of 1.5. We do that by saying equals take the cash flow divide by the minimum debt service cover ratio, let's lock that, put the dollars on it, and we'll just multiply it by the flag. So we'll automatically be zero in the first three years, but after that we can see there are some non-negative numbers here that says if we have 1000 available in cashflow for debt service, the maximum debt service we can have if we wish to stick to this ratio of 1.5, must be 666.7.

Let's check that and just make sure that the resulting debt service cover ratio is okay. So we'll say cashflow divided by debt service and immediately it gives me a divide by zero error because in this period there is no debt service. We should try and clear that by using some sort of if error message.

So I've said, if it gives you an error, just call it zero, otherwise work it out. And yes, that gives us a 1.5 times ratio from the fourth period onwards. Now, let's do a debt hawk screw or base calculation. We'll start with zero. It's a brand new project, so it starts with no debt. What are the drawdowns? Well, essentially we are trying to fund with debt, these negative cash flows, not all of them, 65% of them. So my formula here is if the cashflow up there is less than zero, it's negative, so it needs to be funded, then give me that cashflow times 65%, lock that and then multiply it by minus one because otherwise you'll end up with a negative number. And if it's not less than zero, if it's positive cash, well then we don't need to draw down any debt at all. I'll just put a line at the bottom of that sell and then we'll just add them up. The interest during construction, well, for as long as it's not a debt repayment period, we'll assume that the interest gets added to the debt so equals. If that equals zero, then I want the amount of the debt so far multiplied by the 10% interest rate, and I'll fix that 10% assumption and otherwise zero. Because once we're beyond the interest during construction period, once we're in the operating period, we won't be capitalizing the interest and adding it onto the debt. We will then be paying it in cash again, I'll put a bottom border on that. Let's add them up. So 65 plus 650 so far, 715. I'll skip over the repayments line, we'll come back to that and we'll do a sum of the 715 and the blank line for repayments and I'll put a border on that. So 715 by the end of the first year, which becomes the start of the second year. In the second year we add another 1300 worth of debt. That's 2000 times 65% total now is 2,015 and we'll have 201.5 of interest that we add on. Let's keep doing this. We'll copy that through to the end. And you can see we stop drawing down debt after year three and we stop adding interest during construction from the end of year three.