LBO Case Study - Structure Test
- 03:43
Calculate the debt repayment schedule and the returns to equity holders in a leveraged buyout (LBO) model. Activate the circular reference and check if the debt structure meets the guidelines.
Glossary
circular references Debt LBOTranscript
Once we've done the debt structuring, we're now going to move to the returns to equity holders. But just before we do that, it's worth us just checking to see whether actually this structure does meet those guidelines we talked about at the very beginning. So here what we've got is we've just got a repayment schedule, just estimating how much of the debt's going to be repaid. It's like a base analysis. My beginning balance is equal to the ending balance of 0. That's obviously we don't repay anything. And then we're going to add what we repaid of the senior debt and because this is a repayment measure, I'm gonna make it positive. And then for the unsecured notes, I'm going to go and get the unsecured notes. Again, times minus 1 to make it positive. because this will just add up the total amount of debt that is repaid and then I can copy that across. But just before I do that, I'm going to go and pull in the total debt at acquisition. And that's going to be from the sources of funds. I'm going to take my senior debt and I'm just gonna add my unsecured notes there. And what I'm going to do, just to make this kind of simple, I'm gonna absolute reference these two numbers. You don't have to, but it kind of makes it simple. And then if I copy that across, and then we're going to calculate the percentage of total debt that's been paid down. So we'll just take our balance that's been repaid, divided by what was at the very beginning of the assets life. And you can see at the end of year one or 2024, we've repaid about 10% of the debt balance. Now we want to make sure that by year seven, that's year 3, 4, 5, 6, 7, that that 10.2 number is going to be above 50%. And you can see an actual fact we pay off a hundred percent. So that does mean that we've got more deck capacity. However, we are on slightly borrowed time because we haven't activated the iterative calculation and see interest flow through the model. So why don't I do that If I just press F5 and then go to my circ switch to double click it, and then I type this to 1, I get the error message alt FT. I'm gonna go and to the formulas and check ALT I that will activate the circular reference. Then I'm gonna go back to the LBO sheet and you can see actually these numbers have changed. So let's just do this calculation again. That's year 1, 2, 3, 4, 5, 6, 7. So 84% of debts paid off by year seven that meets that criteria. And do we pay off the senior tranche by year seven? That's gonna have a look. Senior tranche, that's year 1, 2, 3, 4, 5, 6, 7. Yes, we do. So Actually these that this structure currently meets the criteria of term B being paid off within seven years and at least 50% of total debt being paid off within seven years. Now I actually misspoke there saying term B, but the reality is that they used to, many years ago, B three tranches of senior debt term A, B, and C, and A was provided by banks. Term B was an institutional loan bullet payment, and term C was an institutional loan bullet payment. What's happened is the banks became so uncompetitive at this type of lending that the term a trache just disappeared and now term be tranch as the kind of powerhouse of these type of transactions.