Video Tutorial: The Debt Schedule in LBO Models and 3-Statement Models
In this tutorial, you’ll learn how to create a Debt Schedule in a leveraged buyout model or 3-statement operating model, and you’ll see a real example from a leveraged buyout by Bain Capital.
Debt Schedule Definition: In financial models, a Debt Schedule uses a company’s cash flow projections to estimate how much Debt principal the company can repay over time and how the interest expense changes as a result.
A Debt Schedule could appear in various financial models: 3-statement models that project an individual company’s financial performance, merger models, and leveraged buyout (LBO) models.
However, a Debt Schedule is most useful when the company has significant Debt across many “tranches,” or types of Debt, such as Term Loans, Senior Unsecured Notes, and Subordinated Notes.
If a company has only 1-2 tranches of Debt or a minimal Debt balance relative to its Market Cap and EBITDA, a full Debt Schedule is rarely worthwhile – you can use much simpler methods to project its interest expense in that case.
The Debt Schedule in an LBO Model
To illustrate these concepts, we’ll use an LBO model based on Bain Capital’s $1 billion leveraged buyout of NichiiGakkan, a nursing-home operator in Japan.
Debt Schedules are the most useful in LBO models because in all leveraged buyouts, the acquired company must take on significant Debt.
By contrast, a company does not necessarily raise significant Debt in a standard M&A deal or as a standalone entity.
The steps to set up a Debt Schedule are as follows:
Step 1: Calculate the Mandatory Repayments on each tranche of Debt, also known as the “Amortization” of the Debt principal.
Step 2: In each period, calculate the Cash Flow Available for Debt Repayment (CFADR), also known as the Cash Flow Surplus or Shortfall.
Step 3: If necessary, have the company draw on its Revolver if it does not have sufficient cash flow to make its Mandatory Repayments while maintaining the Minimum Cash required to operate its business.
Step 4: If the company has extra cash flow, i.e., it can easily make its Mandatory Repayments while maintaining its Minimum Cash, you may assume Optional Repayments, also known as the “Cash Flow Sweep,” for certain tranches of Debt.
Step 5: Once you have the Mandatory and Optional Repayments, you can project the Interest Expense on each tranche of Debt and the Interest Income on Cash.
Step 6: Finally, you can link the Debt Schedule to the financial statements. The Interest Expense and Interest Income appear on the company’s Income Statement, and the Debt Repayments and Additional Borrowings show up on its Cash Flow Statement.
The Debt Schedule tells you how well or poorly the company can service its Debt, especially in different operational scenarios.
For example, if there’s a downturn or recession, would the company still generate enough cash flow to pay for its Interest Expense and the Mandatory Repayments?
You focus on the pessimistic or worst-case outcomes in credit analysis because optimistic outcomes do not benefit the lenders.
Their returns are limited to the interest rates on the Debt, so it’s irrelevant if a company outperforms its financial projections by 50%.
But if the company underperforms by 50%, lenders will enter “crisis mode.”
Debt Schedule, Step 1 – Mandatory Repayments
The initial capital structure in this leveraged buyout looks like this:
The Revolver is undrawn, and only the Term Loans have Mandatory Repayments and Optional Repayments (the “Cash Flow Sweep” shown below refers to the Optional Repayments):
The “spreads” above refer to the interest rates on top of the 10-year Japanese government bond yield.
If the 10-year yield is 1.0%, and the spread is 3.5%, then the interest rate in the year is 4.5%.
The “floors” mean that if the 10-year yield is below a certain number, such as 1.0%, it is bumped up to 1.0% to calculate the interest rate.
Based on this information, we can set up the Amortization for the Term Loans:
The formula is:
= –MIN(I163, $H$166 * $H$46)
H46 is the 10% annual principal repayment, and H166 is the starting Term Loan balance.
We need to check this amount against the Beginning Balance in This Period (“BoP Term Loans”) in cell I163 because the company might have only a small balance left.
For example, if the fixed annual repayment is $10, but the remaining Debt balance is $2, the company should repay $2 rather than $10.
Mandatory Repayments do not exist for the Subordinated Notes or Mezzanine, so we ignore them for those tranches.
Debt Schedule, Step 2 – Cash Flow Available for Debt Repayment (CFADR)
Next, we can calculate the Cash Flow Available for Debt Repayment.
If this number is negative, the company will need to borrow extra by “drawing” on the Revolver to meet its Minimum Cash balance.
If this number is positive, the company may repay some of its Revolver and Term Loan balance optionally.
The usual formula for this item goes like this:
= Beginning Cash + Free Cash Flow – Mandatory Debt Repayments – Minimum Cash
You can see the setup in this Debt Schedule below:
The “Amortization” line represents the company’s required principal repayments on the Term Loans.
If something boosts the cash flow available, such as the company’s Beginning Cash or its Free Cash Flow, we add it, and if something reduces the cash flow available, we subtract it.
Debt Schedule, Step 3 – Revolver Draws and Repayments
The basic logic here is that if the company has a Cash Flow Shortfall, i.e., a negative CFADR, it should draw on the Revolver.
And if the company has a Cash Flow Surplus, i.e., a positive CFADR, the company should use it to repay any Revolver balance:
The Excel formula is:
=IF(I155>0, –MIN(I155, I157), –I155)
We need the MIN function to handle the case where the Cash Flow Surplus exceeds the Beginning Revolver.
For example, if the Beginning Revolver is $100, and the company’s Cash Flow Surplus is $150, it does not make sense to repay $150 of the Revolver.
Instead, we repay only the remaining $100, which is the minimum between $100 and $150.
Debt Schedule, Step 4 – Optional Repayments
Next, we build a formula that allows for Optional Repayments of the Term Loans when there’s a Cash Flow Surplus and some cash remains after repaying the Revolver.
The instructions in this case study state that 50% of the company’s “Excess Cash Flow” can be used for this purpose.
We set up the following Excel formula to handle this case:
In text, the formula is:
=IF(I155 + I158 > 0, –MIN((I155 + I158) * $I$46, SUM(I163:I164)), 0)
The first part, =IF(I155 + I158 > 0, checks to see if the Cash Flow Surplus minus the Revolver Repayments exceeds 0.
If it does not, we cannot repay anything optionally on the Term Loans, so we set the Optional Repayments to 0.
If it does, then we have to compare the cash flow available * 50% to the remaining Term Loan balance after the Mandatory Repayments and repay whichever one is smaller.
We could simplify the IF check in the beginning by only checking to see if the Cash Flow Surplus is positive, as the MIN(I155 + I158) part handles the comparison of Cash Flow Surplus minus the Revolver Repayments to the remaining Term Loan balance.
Debt Schedule, Step 5 – Interest Expense
With everything above set up, we can now calculate the Interest Expense on each tranche of Debt.
The formulas for the interest rates are straightforward:
We add each spread to the 10-year government bond yield or other benchmark rate, and if there’s a “floor,” we take the MAX between that floor and the benchmark rate.
Then, we calculate the Cash Interest Expense for the Revolver and Term Loans based on the Beginning Balances:
We use the Beginning Balances rather than the Average Balances to avoid circular references, which can make the model unstable.
The Average Balance creates a circular reference because it’s based on the Beginning and Ending Balances.
The Ending Balance depends on the Optional Repayment, but the Optional Repayment depends on the Interest Expense – since the Interest Expense reduces the company’s cash flow.
Therefore, the Interest Expense depends on the Optional Repayment, and the Optional Repayment depends on the Interest Expense, so a circular reference is created.
Some LBO models and Debt Schedules support circular references, but we choose to eliminate them by using the Beginning Balances.
In addition to the Cash Interest, the Subordinated Notes and Mezzanine have Paid-in-Kind (PIK) Interest, so the Interest accrues to the loan principal rather than being paid in cash:
The Cash Interest for both these tranches is straightforward and follows the setup above: multiply the Cash Interest Rate by the Beginning Balance in the period.
Debt Schedule, Step 6 – Links on the Financial Statements
With the Debt Schedule complete, we can now link it to the financial statements, starting with the Interest Expense and Interest Income on the Income Statement:
The Cash Flow Statement here is unorthodox because it starts with EBITDA rather than Net Income.
As a result, we need to link in or calculate several items: the Cash Interest Expense, the Cash Interest Income, the Cash Taxes, and the Debt Drawdowns / (Repayments):
Even though it’s not the traditional CFS setup, the Free Cash Flow calculation is the same as always.
EBITDA – CapEx +/- Change in Working Capital – Cash Interest Expense – Cash Taxes +/- Other Items + Cash Interest Income is simply an alternate way to calculate FCF.
You can see this if you use the traditional calculation (Net Income + D&A +/- Deferred Taxes + Non-Cash Interest +/- Other Items +/- Change in WC – CapEx) and cross out the items that are equivalent.
The PIK Interest reduces the company’s Taxes, but this non-cash interest is not deducted to calculate FCF. It reduces the company’s Cash Taxes, but nothing else.
As a result of these links and the lower cash flow, the company repays significantly less Debt and pays a higher Interest Expense over time.
The Debt Schedule in this base scenario looks significantly different from the previous iteration before the links:
The Debt Schedule: Just How Important Is it?
You should now understand how to build a Debt Schedule into an LBO model or standalone 3-statement model.
You’re unlikely to get detailed technical questions about it because the Debt Schedule is more of a “mechanical” topic than a “conceptual” one.
In other words, if they want to check your skills, they’ll give you a case study or modeling test with a Debt Schedule rather than asking you verbal questions about it.
This is extremely common in private equity interviews, as the Debt Schedule is always a part of LBO modeling tests, but it’s not common for internships and entry-level roles in investment banking.
Modeling tests in investment banking are usually given to more experienced candidates, such as those interviewing for lateral roles.
This tutorial is a small taste of the knowledge you’ll gain in our paid courses. Breaking Into Wall Street uses real-life modeling tests and interview case studies to prepare you for investment banking and private equity interviews – and a leg up once you win your offer and start working. Find out more about our advanced training by via the button below: