Now, in this situation, this workout, we're going to do debt tranching. So this is similar to debt capacity analysis using cash flows. But instead of just simply using one tranche, we're going to use multiple tranches. So we've got our first tranche, which is the five-year amortizing loan and the bank's charging us a pre-tax interest rate of 6%. But in addition, the bank is structuring a second loan that's gonna be repaid one year later. Now, this loan is a bullet repayment. All that means is that the principal is repaid in one year at the end of the loan's life. Now, because this loan is repaid after the first loan, it's going to have a higher interest rate at 7%. And that reflects how the financial markets work. You take more risk, in other words, you wait for your money longer. You would expect a higher return as a lender. So the loan which is paid off in year six gets a higher interest rate. Don't think that you can repay that loan early. You wouldn't be able to. The reason that loan's more expensive is because it's paid off in year six and not before. And the reason the five-year loan is cheaper is that it's paid off earlier. So let's take a look at the information below. The first thing we're going to do is we're going to establish the maximum that we can borrow of the term B loan. And in order to do that, we're going to calculate the after-tax cost of debt. So I'm gonna take the 7% and I'm gonna multiply it by one minus the marginal tax rate of 30%. We use the marginal rate because we assume interest is fully tax deductible. Then what I'm going to do is I'm gonna move to the end of our forecast and I'm just going to freeze our titles here just so we can see the labels. So I'm gonna come to the far right and you can see in year six we have total cash of 552.9 to service debt.

That has got to do two things. By this point, our first loan will have been repaid. So it's all available to do two things. One, repay the principal of loan B and pay the interest on loan B as well. Now, in order to calculate that, I'm gonna take the 559 number and then I'm going to divide it by open parentheses, one plus the after tax interest rate of 4.9%. Now, this may seem a little strange, but what we're saying here is that some of that 552.9 has got to be used to pay off the term B interest. So if I now calculate the term B interest by taking the balance of the loan times the after tax interest rate, and I'm going going to absolutely reference that so when I copy it, it won't change, and I hit enter. If you look at those two numbers added together and if you just look at the bottom of the screen where it says sum right down at the very bottom, you can see if I just move my cursor down here, the sum of that is 552.9, which exactly equals the free cash flows. So the free cash flow, if I take the free cash flow number and I subtract the principal repayment of term B and the interest on term B in year six, you will see that the cash flow is reduced to zero. It's all been used up to repay the principal of term B and the interest on term B in year six. Now, we can't just pay the interest on term B in year six. We're gonna have to make that payment in every single year. So I'm gonna copy that back until year one, all the way back to year one because I'm gonna have to make that payment. Now, you can see that's zero, and that's simply because I didn't absolute reference that first cell. So I'm just going to go ahead and do that. Press F4 to nail it to the spreadsheet to put the dollar signs and then copy that all the way back because I just want that to be fixed every year 'cause we've got to make that interest payment on term B. Now, this means by introducing the term B tranche, that will restrict the cash flow available to pay interest and principal on term A. Although term A will be senior, it means we can afford less term A while introducing this term B. So now I'm gonna calculate the cash flows available for term loan A by taking the free cash flows and subtracting the interest that we've got to allocate to term B. Now, this is just a mathematical issue. You would actually have to pay the term loan a principal and interest first but mathematically, it means we can only have available 430 million for the term A interest and principal, assuming that we're going to have a term loan B as well. So I'm gonna copy that right all the way to year five because remember, this is a five-year loan for the term A tranche. And I'm gonna calculate the interest rate post-tax by taking the 6% is cheaper because this loan would be paid off first times one minus the 30% and that means the after-tax cost of this debt is cheaper 'cause it's paid off earlier. Now, the term A principal is going to be calculated using a net present value calculation, an NPV, and it's going to use the after-tax cost of debt. Comma, and then I'm gonna reference the year one cashflow available for the term loan A, year two, three, four, and five. This means that the total amount of debt of term A debt that we can support is 2,088.3.

We can now prove that this works by just putting it through a beginning balance, a base calculation and I'm gonna take the ending balance in the historical year of the 2,088.3 and then the beginning balance is going to equal the 2,088.3. The interest on the beginning balance is gonna equal 4.2% absolute referenced times the beginning balance, and then the cash payment is going to be just the cash available to service term A, which is the 430. And then I will sum that up and we get the ending amount and I'm gonna copy that cross to year five and we should see that by year five, we've completely paid off the term A tranche. So the principal repayment here is just going to be the difference between the interest amount and the total cash paid. So this is what the principal payment of the term A loan looks like. It's just the cash payment after deducting the interest. Now, if you look at the free cash flows, we can break down what's being used by the free cash flows here. So if I take the free cash flow forecast, the total free cash flow forecast of 456, part of it is used to be paying the interest on term A. And I'm gonna take that the 87.7. Part of it is being used to pay the interest on term B, which I've calculated above. Part of it is used to repay debt. So if I just sum that up, that will give me the cash flows available for debt repayment. And I'm then going to calculate the debt repayment for term loan A, which I've just calculated there. And I'm just gonna take off that minus sign. So that's how much we're paying off term A. And then we'll take the principal repayment of tranche B and now I'm gonna take that up from the tranche B section of the model, which is the principal there. So the net cash flow, if I take the sum of the cash flows available for debt repayment, the repayment of A, the repayment of B, I get zero. And we should see that every year for the six-year period. So if I copy this right all the way to year six, we should see that the free cash or the net cash flow sorry, is zero every single year. So you can see that the interest on term A is being paid, the interest on term B is being paid, the term A is being repaid. And then once that's fully repaid by year five, the repayment of tranche B kicks in. So this debt capacity is structured around the cash flows with the tranche A, the most senior loan being repaid first, followed by the next most senior, followed by the next most senior, et cetera, et cetera all the way down the capital structure.