The purpose of the Debt Schedule is to:
Track the balances of each tranche of debt used in the LBO and the running cash balance which will ultimately feed into the Exit & Returns schedule
Calculate (net) cash interest expense which feeds into the Free Cash Flow schedule
Calculate financing fees amortization associated with each tranche of debt that feeds into the Tax schedule
This is a very important calculation schedule with high risk of error. The trickiest portion to model is in the optional paydown of debt, which we will cover here in detail.
Here is an example of the summary table Mosaic’s Debt tab rolls up to. It simply shows the ending balances of the various tranches of debt you have active, as well as the total cash interest expense running through the model. The diagram below shows the linkages between this schedule and the others throughout the Deal Model.
After laying out a summary schedule like the above (with no values yet), let’s start the actual calculation schedule by stubbing out a schedule like the below – you can repeat the “blocks” we’ve set up for First Lien / Second Lien for as many tranches of debt as you have in your deal:
The first two lines in the schedule above are direct links to the Operating Model and Free Cash Flow Schedules, respectively. Linking them through here makes it much cleaner (and easier to check / audit) than if you were to simply start using those original lines in the formulas to come.
For your first tranche of debt – set up the close period’s ending balance as the drawn amount of that tranche as set up in your sources and uses. The Beginning Balance for each period after the close is simply a link to the previous period’s Ending Balance. The Ending Balance is the sum of the rows between.
The next two lines are a bit more involved, but straightforward enough:
Mandatory Paydown. This is the “amortization” or required amount you need to pay each year. It’s typically stated as a percentage of the beginning balance. The formula is:
=-MIN(Drawn Debt at Close * Amortization Rate, Beginning Balance)
Optional Repayment. This is the optional debt paydown line and is one of the more complex formulas in a standard LBO. Still, no stress – below is a GIF of the formula so you can see how it hangs together, but essentially the Optional Repayment amount on any tranche of debt is:
the GREATER OF:
A – Zero; or
B – The Lesser of:
I – Cash Available for Debt Paydown, less any voluntary debt paydown that has already been paid on more senior tranches; and
II – The amount remaining on the tranche you’re testing
In Mosaic, we have an additional switch on the end of the formula that allows the investor to switch the tranche to not allow prepayment. You can skip this if it’s not relevant for your model.
An important note on interest calculation:
Mosaic calculates interest expense based on Beginning of Period Balances. A number of LBOs will teach you to cleverly compute the interest expense based on an average debt balance in the period – with the rationale being that debt pay down occurs throughout the period, and thus the average better reflects how much interest you’d be paying. We think this is overengineering and not helpful for two reasons:
It doesn’t reflect practical reality, erring on the aggressive side (i.e., overstating returns). I never had a PortCo CFO pay down his or her debt daily, which the average balance method implies. They typically wait at least until quarter end or even build up a bit of a cash buffer to operate the business and fund working capital without drawing on a revolver. By using the average balance method, you’re modeling in the best possible case of debt paydown for a given operating case, which is pretty aggressive.
It introduces circularity into your model. The average balance method combined with debt paydown based on a levered (i.e., interest-burdened) cash flow makes your model circular. If every time you save / F9 your model and need to leave your desk / get a coffee before it finishes refreshing your 40 data tables, that’s why.
Some of the world’s very largest and most successful private equity firms that we’ve interviewed do it the Beginning Balance method – we aren’t smart enough to have made it up – but the second we learned about it and understood the rationale behind it, we went all-in on coding it this way in Mosaic without any option to switch it.
Financing Fee Amortization
Financing fees, unlike transaction fees, are capitalized and amortized over the life of the related debt security for the purpose of computing taxable income and therefore cash taxes. This gives us a bit of a tax shield (which is positive to returns) but means we have a bit of set up work to do to track it appropriately.
Set up a schedule that looks like the below:
The Ending Balance for each tranche in the “At Close” period will be the total fee amount for that tranche. We simply divide the starting amount by the tranche’s maturity period to get each period’s amortization amount. We reduce the amount of remaining fee to be amortized accordingly, checking that we don’t amortize more than the remaining balance (i.e., it never goes negative). The GIF below shows the formula transparently:
Summary Cash Rows for FCF table
Add two rows at the bottom of the schedule that aggregate the Mandatory Paydown and the Optional Paydown across all of your tranches. These lines will drive the Free Cash Flow schedule lines we left blank previously. It’s much cleaner to roll them up here and have only one line referenced in the Free Cash Flow schedule – whoever is auditing your model will thank you.
