We will now proceed to work out how much we can use of our debt service money, the maximum allowed that we've already calculated, how much gets used for interest and therefore the remainder we can then use to repay the principle of the debt. The maximum allowed debt service that's on line 60. We've already worked that out so we can just relink it back up on line 28. How much interest will we pay? Probably easiest to work this out starting in year four because that's the first year in which we will actually be paying interest. How much interest will we be paying? Well, it's 10%. Let's fix that. Put some dollar signs on it of 2,795. So that's the amount before any interest. Multiply it by that debt repayment period flag, so it only kicks in in the periods from year four onwards. Answer 279. Let's copy that left and right.

So how much principal repayment can we afford to make where we're allowed to spend? 666.7 279 of that is interest. The rest 387 is a principal repayment. If I link that back up, I need to make it a negative link that back up into the debt base calculation. And you can see here that as I make the payments, it reduces the balance, which then changes the balance of the next year. That then updates the interest, gives me a new principal repayment number. Use that number. It reduces the balance again, and this keeps happening each year, but now here's the problem. Year nine, we're paying too much. The debt balance turns negative and year 10, while we wouldn't normally need to make any payment in year 10, if we've already repaid all of our debt, what we need to do here is have on our line that says actual principal repayments, we will calculate it's the minimum of the amount that we've just worked out. Maximum debt service minus interest or the balance of the debt, how much is actually owed at each period in time, which is line 24. So how much cash do I have available? How much debt do I owe? Which one is less? Let's link that revised set of numbers into the debt-based calculation. So instead of linking to line 30, we're going to link it now to line 31 instead. That makes more sense. It's the same repayments all the way up until the end of year eight. But in year nine it says, although we have 922 we could spend, we don't need to, we only owe 771, so we'll pay 771 and then it reaches zero and therefore We don't need to pay anything in year 10. That works nicely. We repay our debt within the ninth year, but it's not optimal because we've got 922 we could use for debt principle repayments, we only end up using 771, and in year 10 we've got 800 available and we use none of it. What this says is if we want to keep within our 1.5 times ratio and we want everything repaid by the end of year 10, we could afford to have more debt. If I change that to say 70%, you can see I'm using more of my year nine cashflow. In fact, all of it now, and I'm using some of my year 10 cashflow, but not all of it. I could use more if I went to 75%. What happens while I use all of my year nine cashflow and I use some, but not all the majority, but not all of my year 10 cashflow. If I push it to 80% debt funding, now what happens? I need all of my year nine cashflow and all of my year 10 cashflow and it's still not enough because I've got a balance left over at the end of year 10, but cash flows are not sufficiently high that I can make all the repayments and get to zero by the end of year 10.

So we know that the optimal answer is somewhere in the middle between 75 and 80% to get the exact number we need to do a goal seek.