In this workout, we're going to calculate the debt service coverage ratio and the loan life coverage ratio. We have a forecast of unlevered free cash flow for years 4 through 11.

We also have a required headroom, which is what the bank requires us to have. And in this case it is at 20% of the free cash flow. And here we have a little calculation for our debt and interest forecast.

So our year 4 beginning debt balance is equal to 400, and our ending balance is the sum of the beginning balance and the repayment.

We can copy this all the way to the right.

Now for the interest expense. For simplicity, we're gonna assume that we are gonna pay the interest based on the beginning balance. So we're gonna say minus 5% of the beginning balance of our debt, which is 400, and that gives us a 20 interest expense and we can copy that to the right as well.

Now if we go down here, we can actually compute our total debt service and that will be equal to our repayment plus our interest expense, which in year 4 is 50.

And then our debt service covers ratio will simply be the ratio of our unlevered free cash flow and our total debt service of 50.

So in year 4, our debt service covers ratio is two times.

We can take both of these lines and copy them all the way to the right. And you can see how the debt service covers ratio is a single year metric of the ratio free cash flow and the total debt service. Now we can calculate our loan live coverage ratio.

And what's different about the loan life coverage ratio is that here we are incorporating all of the future cash flows in a single number or a single metric. So to begin, we take the present value of all of the free cash flows at a discount rate of 5%. So for this, I'm gonna use the NPV formula in Excel at a rate of 5%. And for the values, I'll simply select all of the unlevered free cash flow forecast.

And that gives us a present value of 818.2.

Now we're take the loan amount initially at 400, and the loan life coverage ratio will be the ratio of the present value of the cash flows, and the loan amount. That will give us a loan life coverage ratio of 2 times.

Now we can actually compute a loan life coverage ratio for every year in the forecast. And what we'll do here is we'll take the remaining cash flows in the timeline. So for example, this 818.2 incorporates all of the cash flows starting in year 4 through year 11. But if I wanted to compute the loan life coverage ratio for the next year, or for year 4, I would only incorporate the cash flows from year 5 and onward. In this case, all I would need to do is take my NPV formula and I'll lock in the cell reference to K8. So that's gonna fix the year 11 cash flow.

When I copy this to the right, my D8 cell reference will change, but my K8 cell reference will remain fixed.

And as you can see, we get 759.1, which is the remaining free cash flows post year 4. I can also copy this to the right. And now we have our loan life coverage ratio in year four, which incorporates the cash flows post year 4. I can do this for every single year. So let me copy over a few more columns. Let me go all the way up here so you can see how we are only selecting the cash flows starting in year 7 to compute our loan life coverage ratio for year 6.

We can of course take this and copy it all the way to the right up to year 10 to get a view, a dynamic view of our loan life coverage ratio. And this is how you calculate both the debt service coverage ratio and the loan life coverage ratio.