LBO Case Study - Unsecured Notes
- 02:38
Model the debt repayment of a leveraged buyout deal using Excel. How to use the min function to calculate the cash flow available for debt servicing and how to apply different interest rates to different tranches of debt.
Glossary
Debt Repayment Debt Servicing LBOTranscript
So once we've done the senior tranche, we can then move on and kind of waterfall the repayment to the subordinated tranche. And the subordinated tranche we've just called unsecured notes here, and this is kind of like the very high yield side of the structure, and we're gonna anchor that first. So I'm gonna go up to the sources and uses of funds and pick up the initial amount of the unsecured notes. Then I'm gonna do a beginning balance, as we did before. And now we can do another min function. This time we're going to do the minimum of row 52 and then minimum of row 54 because we only want to start repaying the date if you have cash available. And you can see we only get cash available by 2029. So this actually is only gonna kick in once we have completed the repayment of the senior tranche. And that just reflects reality of how debt's structured. So I'm gonna do the minimum of the beginning balance and the cash available times minus 1. And that means in the first year there's no repayment. In fact, there's gonna be no repayment until 2028. Now some people when you copy this across say, well, why do we even care? Because we are assuming that the private equity house is going to be selling the business by year four. So this means why do we even bother modeling the unsecured note repayment? Well, the reason is, in order for the deal to get financed, you still need to pass it by the Bank Commitments committee. And if you remember some of the criteria we had of 50% of total debt paid off within seven years and all senior debt paid off within seven years, you need to have this cashflow forecast beyond year four to make sure that your debt structuring actually meets those standards. So you can see now a big slug of this tranche gets paid off in 2029, and the remainder gets paid off in 2030. Now, of course, this is gonna change because we haven't put interest into the income statement, and that will reduce the cashflow and extend the repayment terms. So I'm gonna calculate the interest expense on the unsecured notes, and we have a set assumption for that, which is 7.25% absolute reference, and then multiply by the average of the ending balance, and then times by minus 1 to make it negative. And then I can copy that across, and you can see, obviously when we repay this debt, the interest amount goes to 0. So we've got our debt servicing there.