Goal seek is just Excel calculating in an iterative way to set a number to a given value by altering one of the numbers that influences the results you're trying to change. In this case, we want to set this closing balance at the end of year 10 to be zero by changing the proportion of debt financing. Give me the number there that will set this number here to be zero. That's on your data menu, under what if analysis goal seek. Set cell M26. Yes, that's the right one, that's where I am. I want to set it to zero by changing the debt financing percentage. Hit okay and you can see here the optimum amount is 76.5% debt financing, because at 76.5% it means the cash cashflow that is allowed to be used for debt service. We use it to pay interest and we also use all of the remainder to repay principle. By using all of the available cashflow within the 1.5 limit it gets us to exactly zero by the end of year 10. That's our optimum amount of debt for this project.

Let's check. Are we actually achieving our 1.5 times ratio by doing this? If I go to line 33, the cashflow during the repayment period, well that's cashflow times by the repayment flag. So that's not cash in the repayment period, nor are those, but these ones from year four to 10 are the actual debt service. Well, that's the interest plus the actual principle repayments. And what is our resulting DSCR? It is the cashflow divided by the actual debt service. Careful, that gives me a divide by zero error in the first year. Because I've got no debt service. I can fix that by having an if error. And I'll just say if it's an error, call it zero and you can see the result is 1.5 times exactly. So we are at the limit. 1.5 times is the minimum that the bank will allow, but this project can stick within that limit, have 76% debt funding and get everything repaid by the end of the 10th year. So we've used the debt service cover ratio to sculpt a repayment. This is our repayment profile. It is the cashflow divide by one and a half minus the interest, and you can see it rises and falls depending on what the cash flow Is that's coming in that year. So as the cashflow falls, then the repayments will fall. As the cashflow rises, the repayments rise as well. So it's putting exactly the same level of pressure on the project in each year. It's not demanding a repayment in a bad year when cashflow is low, that is unaffordable. It is keeping it sculpted within the 1.5 times limit.