Debt Capacity Term Loan B workout. In the previous model, we looked at how much we could borrow in a term loan A, which involves the cash flows from years one through six. Term loan Bs tend to be longer than term loan As, so they're looking at the cash flows in years seven. They have a slightly higher interest rate because the cash flows, the further out we get the more risky they are, and they don't amortize. So we don't need to touch, for the most part, the cash flows from years one to six that will jeopardize the amortization of the term loan A. However, in determining how much we can borrow in a term loan B, we have to go out to year seven and look at that cash flow. And then we have to acknowledge the fact that we can't take the entire amount of year seven cash flow out in a loan. Because even though this loan is a bullet, and it does not amortize, it will be repaid in its entirety in year seven. There will be interest due each year, including year seven. So we need to account for the fact that in year seven we need cash flow to pay interest, and we need cash flow to repay the entire principle amount. So mathematically that would be equal to year seven cash flow divided by one plus the after-tax cost of debt. So what that's saying is that out of the 189.2, 181.5 of it can be principle, and the remainder is actually interest that I'm going to pay on that outstanding amount during the year. Now we can go ahead and build our amortization schedule for term loan B. The ending balance is simply going to be the loan amount. The beginning balance in year one will be the ending amount In year zero. The accrued interest will be the after tax interest rate anchored times the beginning amount. The interest paid will be the same amount negative to show that it's being paid out. As far as the debt repayment, there isn't any debt repayment until year seven. So what we can do is we can just say the opposite of my beginning balance times, and this just avoids having to write out a big if statement. I'm gonna just say my term in years equal to and I'm gonna click on the year that I'm in. Now this year one, it looks like a label, but it's actually been coded in Excel to be a number. My term in years is equal to the year that I'm in, which is year one.
It'll be true. If it's not, it'll be false. And Excel reads falses as, so I'm gonna get a zero here. Now this formula is copyable as long as I anchor my E16 and my interest rate. And now I can do the sum of these to show my ending balance. And I can copy this across all the way to year seven, and I should get a zero balance and I do. So what are the implications of this? Well, I still have to have a term loan A, but the term loan A is going to change now because I don't quite have as much cash flow as I did in the previous workout. I have that cash flow less the interest that has been paid on term loan B. So I'm actually gonna go ahead and calculate that by doing beginning free cash flow plus the interest paid cause it's a negative, it's showing as a subtraction. Now I can calculate my new term loan A based on the new cash flows after the term loan B interest has been paid, and that's gonna be the MPV at that interest rate of the first six years. Remember, we're still only doing six years, and I get a term loan, which is quite a bit less than, in the first version I think it was around 890 something. So now we have 823.8, and if this works, we should, even though we're borrowing less in term loan A, overall be able to increase the leverage of the transaction. So I'm gonna just fill out my Term Loan A Schedule, which is relatively easy, and that is the beginning balance times the interest which is anchored. And then the interest paid is the opposite of that. And then the debt repayment is simply my cash flows from term loan A plus the interest paid, and that tells me my amortization amount in year one. If I flip that to negative, it reduces the ending balance.
And now if I copy this across, I should get to zero by the end of year six, which I do. Now if I scroll down to my ratios I can see my total debt to EBITDA is going to be the total debt amount divided by the EBITDA. Again, we'll either use the LTM EBITDA or we'll use the EBITDA from projected year one. Since I don't have LTM EBITDA, I'm going to use my EBITDA from projected year one, and that's gonna be the EBIT plus the D plus the A.
And that gets me to total debt EBITDA of 3.7 times which is greater than the 3.2 times we had calculated under just the term loan A scenario.
Debt Capacity With Scenarios TLa and TLb Empty
