In this workout, we are going to do a simple cash flow based debt capacity analysis. And you can see here we have got a little free cash flow forecast from year zero all the way to year five. So what we're going to do is we're going to calculate the maximum loan that this business will be able to support, assuming that we have a 6% pre-tax interest rate.

And this loan is going to be amortizing, which means that it's going to be repaid each year over year one, two, three, four, five. And the maximum amount of cash that it can use either to repay debt or pay the interest is the free cash flows. So the first thing we need to do is we need to take the interest rate of 6% and we need to recognize that we're going to have to pay tax and actually interest is tax deductible. So although we are going to pay interest to the bank at 6% actually we'll get a tax deduction which will reduce it. And the tax rate is 30%. We're using the marginal tax rate because typically interest will get a full deduction on our tax return. So the post-tax interest rate is going to be the 6% times one minus the 30% tax rate. So this means actually the interest is only gonna cost us 4.2% because of the tax deduction. Now, in order to calculate the debt capacity what we now need to do is we need to calculate the present value of those future cash flows. And there's a useful little formula in Excel to do this. We can use =npv, which stands for net present value. And it's asking us first what discount rate we want to use. Well, that's going to be the interest rate post-tax. So I'm going to take this 4.2% comma and then I'm going to select the cash flows from year one to year two, year three, year four, year five. And what Excel will do is present value all those future cash flows from year one to year five using the 4.2%. So I'm gonna close parentheses, hit enter, and then Excel has worked its magic. So the total present value of the five years free cash flows is 2202.6. That is the maximum loan that this company will be able to afford assuming it's using all the free cash flows to pay interest and repay the loan within a five year period. Let's test this out to see if it actually works. So I'm gonna start in the historical year with the ending balance which is the present value number, the 2202.6 And then the beginning balance of year one is going to equal the ending balance of that historical year. The first thing we're going to do is we're going to charge interest on the loan. So I'm gonna go up and take the 4.2% and I'm gonna absolute reference this 'cause I'm gonna copy it, right? And I'm in a multiply it by the beginning balance. So this means because we have this loan we are in the first year accruing 92.5 million of interest. Now we're gonna make a cash payment and we're gonna make a cash payment equal to all the free cash flows that we generate in that year. And in this case it's 456 million. Now some of that 456 million is gonna be used to pay the interest. The 92.5, the rest will go to repaying the loan. So if I just do a little sum then, you can see that the loan will drop because the rest of the 456 million that's not being used to pay the interest of 92.5 goes to start to repay the loan. And then next year the beginning balance will start off slightly lower and now the interest expense is going to fall because the loan balance is going to fall and notice that the free cash flow has risen. So the loan repayment is going to be even greater because we've only got interest of 77.2 yet we've got more free cash flow. This structure is very, very similar to a mortgage payment. And then if I get the ending amount, I sum it up, and you can see the ending balance has dropped further. So as we go forward, what we will see is that the loan, more and more of the loan will get repaid, and by year four you can see it's dropped to 570. And then lastly in year five like magic our ending balance is zero.

And the reason our ending balance is zero is because we used a net present value to calculate the amount of debt the business could support over the five year period. And you can see it exactly calculates the total amount of debt.