Let's now work our way through a simple example of a debt service cover ratio and see what the best way to model this might be. Go to the sheet that's called workout and you'll see here a number of years, one through to six and cashflow available for debt service. This would come from elsewhere in the workbook, maybe from the cashflow sheet. We also have information on the amount of interest that needs to be paid year by year and the principle repayments that are needed on a particular piece of debt. Nothing for the first three years, then 250, 450 and 450 in years, four, five and six.

If we add up to get a total debt service, the interest and the principal repayments, that's our debt service cost.

Our debt service cover ratio on line 18 should just simply be the amount of the cashflow divided by the debt service. And we do that for each year.

And you can see here that there are some very big negative numbers, minus 10, minus 3, minus 1, and then it increases and becomes 1.1, 1.2, 1.7.

The question we need to ask ourselves is these negative numbers here, are they actually meaningful? What is likely happening during the period when these numbers are negative? Will our cash flow is negative in years one, two, and three? If this is the beginning of the project, that's likely to be the construction period. So it's natural and perfectly normal that the cashflow would be negative. We haven't reached our operational stage yet. We have no revenue or we're doing is spending money building or buying an asset that will raise revenue for us in the future.

So if we were to calculate our debt service coverage ratio as we've done on line 18, and then we would say, what is the minimum? Well, it would come up with this number here of minus 10.

No bank is likely to lend on a debt service coverage ratio with a minimum of minus 10. But is that really meaningful? No, it's not because it's the construction period. We would expect that this would be a negative amount of cashflow and therefore a negative ratio. What we need to do is distinguish the construction period from the rest of the model, and what we'll do is set up a little flag that says if the year which is in line eight is less than or equal to three, let's say that's the end of our construction period. And normally we would have a separate assumption for that, then give me a 1 otherwise give me a zero. So this is one of our binary flags that says it's a 1 during the construction period, but it's a zero afterwards. Now what we need to do is say, well, if we are in a construction period, let's not do the ratio because it's not meaningful. So we'll say if that equals 1, otherwise it's of the construction period. If that is the case, then give me a piece of text like n/a or blank or dash, anything that's not a number, don't use zero. And we'll show you why in a moment. If it's not during the construction period, then do the cash flow divided by the debt service cost. So the normal ratio, so that says n/a in the first period, it'll say n/a in the second and the third, but it will give us a ratio in periods four, five and six because now we're in the operating cash flow point of the project. Now we can do an intelligent minimum equals min of everything on that line. Include the n/as, that's fine. Excel will ignore anything that is an alphabetic or symbol character. It will only pick up numbers if you're asking it to do a min or a max and it says the minimum here is 1.1.

Our desired target maybe this is what the covenant has been set on. This is the minimum that must be reached in terms of the loan agreement.

So actually, although we are showing positive numbers here, these two in years four and five are actually still too low.

And we want to highlight that by doing a formula in here that says are we in breach? So if the resulting ratio, and we can do it with the periods that are n/a, if it is less than 1.25 because that's the covenant amount, let's fix that, lock that and put some dollars on it, then give me a 1 otherwise give me a zero. So it'll be one whenever we are in breach And there we are 1 and 1. That number's okay in year six, but year four and five are too low. And we want to show that in our model to show that we have a problem here. This would not get past the bank and we would need to do something different either in terms of loan agreements negotiation, or the terms and conditions or we'd have to do something different to be able to change our cash flows. It's also useful to do a sum of them so that we see how many times over the life of a loan do we have a breach of this condition that we need a minimum 1.25 times target.

I've set this up on this road to have conditional formatting if it is anything other than a zero, it colors it red so that it's nice and easy to see visually.