Video Tutorial: The Debt Schedule in 3-Statement Models, LBO Models, and Credit Models

Debt Schedule Definition: A Debt Schedule uses a company’s cash flow projections to estimate how much Debt principal the company can repay and how its interest expense changes as a result; it may also project additional Debt and Equity issuances and stock repurchases.

A Debt Schedule could appear in various types of financial models: 3-statement models that project a company’s financial performance, merger models, leveraged buyout (LBO) models, and credit-focused models that compare financing alternatives for a company, such as 50% Debt / 50% Equity vs. 100% Debt.

Excel Files, Resources, and Links:

• Debt Schedule in a Refinancing Model for Netflix (Simplified) (XL)
• Debt Schedule – Presentation Slides (PDF)
• 3-Statement Model for Otis (Include a simple Debt Schedule)
• LBO Model for Twitter (Includes a more complex Debt Schedule)

Table of Contents:

• 0:00: Introduction
• 1:53: Summary of Debt Schedules by Model Type
• 5:37: Debt Schedules by Complexity and Features
• 7:22: Debt Schedules in 3-Statement Models
• 9:46: Debt Schedules in LBO Models
• 15:07: Debt Schedules in Credit or Refinancing Models
• 31:40: Recap and Summary

Almost every Debt Schedule uses the following mechanics:

Minimum/Targeted Cash: The company must maintain a certain amount of Cash to fund its operations and pay for day-to-day expenses.

Cash Exceeds the Minimum/Targeted Cash: In this case, the company can repay Debt or repurchase Stock.

Cash Falls Below the Minimum/Targeted Cash: In this case, the company must issue additional Debt (or Equity) to continue operating.

Types of Debt Schedules and Their Complexities

In most 3-statement models, you keep the Debt Schedule simple by combining the Debt issuances into a single line and using a weighted-average interest rate.

Most companies aim to keep their Cash and Debt in similar ranges over time, and these models reflect that.

In an LBO model, you normally need at least 2 “tranches” of Debt – Term Loans that can be repaid early and Senior Notes that can’t – and possibly a Revolver for additional borrowing needs.

LBO models typically assume that the company repays significant Debt, so you set up the assumptions to maximize that repayment.

Finally, in a credit or refinancing model, a company has upcoming Debt maturities or needs capital to fund other projects, so you forecast its financials and consider different options.

For example, should the company raise 100% Debt? 100% Equity? A mix of both? Which type(s) of Debt?

You still need its minimum or targeted Cash, but you also need its targeted credit stats and ratios and different operational scenarios, such as Base, Downside, and Extreme Downside Cases.

Here’s a summary of Debt Schedules by features and complexity:

A Simple Debt Schedule in a 3-Statement Model

In a timed modeling test or a case study with a 3-statement model, you should keep the treatment of Debt and Equity very simple.

The normal goal here is to keep the company’s Cash and Debt levels in similar ranges, either as percentages of revenue or in nominal dollars/euros/other currencies.

For a good example, look at our 3-statement model for Otis (elevator manufacturer).

To determine the Debt issuances/repayments and stock repurchases, we do the following:

1) “Excess Cash Flow” = Beginning Cash + everything on the Cash Flow Statement except for the Debt and Equity line items – Minimum Cash.

2) Positive Excess Cash Flow: The company spends 85% on stock repurchases and 15% on Debt repayments, per guidance from management in the investor presentation.

3) Negative Excess Cash Flow (i.e., a Cash Flow Deficit): In this case, the company issues enough Debt to offset this deficit. It repays no Debt and repurchases no Stock.

You can see the “Change in Debt” formula and output below:

The Interest Expense on the Income Statement is based on a simple interest rate (2.5% rising to 3.5% over time) applied to the company’s Debt balance in the year.

A Debt Schedule in an LBO Model for Twitter

A Debt Schedule in a leveraged buyout differs in several ways:

1) Purpose – The goal is not to maintain Cash and Debt in similar ranges but to repay as much Debt as possible with the company’s cash flows.

2) Debt Tranches – Depending on the time and model complexity, you’ll usually have to model at least 2 tranches of Debt, and sometimes up to 3, 4, or even more.

3) Mandatory Debt Principal Repayments – These repayments, common for Term Loans, must be factored into the company’s “Cash Flow Available for Debt Repayment” (CFADR) or “Cash Flow Surplus / Deficit” (or whatever it is called).

4) Additional Features – Revolvers (for additional temporary borrowing) are common in LBOs, as are additional features such as interest-rate floors, Paid-in-Kind or PIK Interest, cash flow sweeps for optional repayments, original issue discount (OID), and commitment fees.

Here’s a summary of our Debt Schedule for Twitter:

The steps to set up a Debt Schedule in a leveraged buyout are as follows:

Step 1: Calculate the Mandatory Repayments on each tranche of Debt, also known as the “Amortization” of the Debt principal.

For Twitter, these are simple percentages of the initial balances of Term Loans, Senior Secured Notes, and Margin Loans.

The MIN function handles the case where the remaining balance is less than the fixed annual repayment:

Step 2: In each period, calculate the Cash Flow Available for Debt Repayment (CFADR), also known as the Cash Flow Surplus / Shortfall or Excess / Deficit Cash Flow, among other names.

This is similar to the calculation in the 3-statement model above: Beginning Cash + Free Cash Flow – Amortization – Minimum Cash:

We use Free Cash Flow (FCF) rather than “everything on the CFS but the Debt/Equity lines” because in an LBO model, the Cash Flow Statement is typically simplified down to the core components of FCF (Cash Flow from Operations – Capital Expenditures).

Step 3: If the company does not have enough cash flow to make its Mandatory Repayments while maintaining the Minimum Cash, it must draw on its Revolver.

We handle this part with a simple MIN function in this model:

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.

For example, we assume a 50% cash flow sweep for the Term Loans here, which means that Twitter uses 50% of its Cash Flow Surplus to repay the Term Loan balance in each period:

Once again, the MIN function ensures that it does not repay too much of the Term Loans, which would turn the balance negative.

Step 5: Once you have the Mandatory and Optional Repayments, you can project the Interest Expense on Debt and the Interest Income on Cash.

We took the current interest rates and their forecasts from the deal filings and news sources:

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:

This Debt Schedule tells us how well the company can service its Debt, especially in different operational scenarios.

For example, Twitter does quite well in the Base Case here, but it struggles in the Downside Case, as its credit stats and ratios change to dangerous levels:

A Debt Schedule in a Credit/Refinancing Model for Netflix

This Debt Schedule is similar to the one in the LBO model, but the purpose is quite different.

The goal is to tell the company: “Based on your targeted/minimum Cash and targeted credit stats and ratios, you should use X% Debt and X% Equity to pay for your upcoming maturities or other funding needs.”

You might even go a step further and recommend specific types of Debt, such as Term Loans vs. Senior or Subordinated Notes.

The challenge in this Debt Schedule is creating justifiable assumptions based on the filings and outside research without overly complicating it.

The set of steps demonstrated in our simplified Netflix refinancing model is as follows:

Step 1: Build the cash flow projections or full 3-statement model, with support for multiple scenarios.

Multiple scenarios are important because credit analysis is all about the downside case. If things go poorly, can the company still service its Debt? What are the chances of default?

For Netflix, we used a combination of past trends, historical data, and its planned strategy to create several scenarios and plot them in simplified “cash flow projections”:

Step 2: Forecast the interest rates for each tranche of Debt.

Netflix has mostly Senior Note issuances with fixed rates, so this part is easy.

But when each issuance matures and needs to be replaced with a new one, the rate will almost certainly go up because rates were rising at the time of this case study.

We used overall market expectations from different data sources to estimate these higher rates:

NOTE: One oversight/problem here is that we should track the rates for the New Senior Notes separately for each separate issuance, as the company could potentially issue 5 new sets of Senior Notes in this model.

So, the rates might be 5.0% for the Year Issuance, 6.5% for the Year 2 Issuance, and so on, and they would stay the same each year for that issuance.

In practice, this is a small problem that we can ignore in a simplified model like this one because no matter what happens, the weighted-average interest rate will increase over time, and relatively little new Debt gets issued.

Step 3: Project the mandatory repayments and maturities to estimate each year’s cash outflows.

Netflix’s Senior Notes do not have fixed principal repayments, but they do mature in future periods. We plot the balances via the lines below:

Step 4: Calculate the CFADR or “Cash Flow Surplus / (Deficit)” the usual way, i.e., Beginning Cash + Free Cash Flow – Debt Maturities/Repayments – Minimum Cash.

This part is almost identical to the same schedule in the 3-statement and LBO models.

The main difference is that there is no fixed annual amortization, only the maturities of the Senior Notes:

We used “Free Cash Flow” in the formula above, but, as with the 3-statement model, this part is closer to “Everything on the CFS except for the Debt and Equity line items.”

Step 5: Draw on or repay the Revolver, factoring in constraints like the maximum size and the commitment fees.

We use a formula like the one above for Twitter, but this Revolver has a maximum draw of $500 million.

So, we compare the ($500 million – Beginning Revolver) to the absolute value of the Cash Flow Surplus or Deficit to determine how much the company can draw:

Step 6: If the company has a Cash Flow Deficit, issue Debt or Equity; if it has a Cash Flow Surplus, repay Debt or repurchase Stock.

These formulas are similar to the ones in the simplified 3-statement model:

Step 7: Calculate the Interest Expense and Interest Income and link everything on the financial statements or cash-flow projections.

The Interest numbers are based on SUMPRODUCT formulas with the Debt balances and the rates:

And the links are straightforward since we do not have multiple financial statements:

Step 8: Evaluate the credit stats and ratios in different scenarios until you find a mix of Debt and Equity that works.

The main constraints are that Netflix wants to stay at or below 5x Debt / EBITDA and at or above 3x EBITDA / Interest.

In the Base and Downside Cases, 100% Debt satisfies these constraints.

But in the Extreme Downside Case, the results are much worse:

If the company uses 50% Debt and 50% Equity instead, it complies with the maximum Debt / EBITDA in this scenario:

Our Recommendations to Netflix

Based on this analysis, we would tell the company to shift to a higher percentage of Equity funding if its business performance declines by more than expected.

Also, we might tell the company to issue additional Debt or Equity now rather than waiting for the maturities so it can build up a “cash buffer” and avoid paying even higher rates in the future.

The Debt Schedule: Just How Important Is it?

You should now understand how to build a Debt Schedule into a 3-statement model, LBO model, and credit/refinancing model.

But how important is it in investment banking interviews or private equity interviews?

You’re unlikely to get detailed technical questions about it because the Debt Schedule is more of a “mechanical topic” than a conceptual one.

If interviewers want to check your skills, they’ll give you an LBO modeling test with a Debt Schedule rather than asking you verbal questions about it.

Timed LBO modeling tests are extremely common in private equity interviews but far less 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.

So, if you are interviewing for internships or entry-level roles, it’s good to be familiar with the setup of a Debt Schedule, but you don’t need to know all the formulas.

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: