How to Build a Debt Schedule in 3-Statement Models and LBO models: A Guide to Business Debt Schedules, Credit Models, and Financial Modeling

A debt schedule is a detailed analysis outlining a company’s total debt obligations over time, including debt principal, discretionary debt payments, interest payments, maturities, and covenants. It plays a crucial role in financial modeling, particularly in 3-statement models, LBO models, and credit models. A debt schedule helps forecast a company’s future cash flow available for debt repayment, assess its financial health, and plan repayment strategies. Whether you’re working with investment banking and private equity transactions or managing small business administration loans, a debt schedule provides a comprehensive view of a company’s debt structure. Using a business debt schedule is a crucial aspect of ensuring accurate financial projections and planning.

Applicability in the Field: Business Debt Schedules in a 3-statement model, LBO, and Other Financial Models

A debt schedule is commonly used in several types of financial models:

1. 3-Statement Models: Integrates the balance sheet (BS), income statement (IS or P&L), and cash flow statement (CFS) to provide a complete picture of a company’s financial performance. The debt schedule in 3-statement models captures net interest expenses, principal repayments, and new borrowings, all of which affect the company’s net income and cash generation.
2. LBO Models: Essential for modeling leveraged buyout models, as they often include complex financing structures using much debt to acquire the target asset. In LBO models, a detailed debt schedule tracks multiple tranches of debt, from senior secured loans to subordinated mezzanine debt in some cases, ensuring accurate projections of debt repayment, interest expenses, and deleveraging. Modeling each tranche of debt separately allows for a detailed understanding of repayment priorities and interest obligations.
3. Credit Models: Used by creditors and analysts to assess the risk and repayment capacity of borrowers. A credit model relies on an accurate debt schedule to evaluate the company’s debt balance, leverage and interest coverage ratios, and covenant compliance. Keeping a debt schedule updated helps in maintaining transparency for stakeholders and assists credit risk takers in taking lending decisions.
4. Merger Model: Helps evaluate how new debt will affect a combined entity’s financial statements, considering synergies and the impact on cash. It also ensures that cash and debt in similar structures are appropriately accounted for in post-merger financials.

Common Types of Debt Financing (RCF, T/L, Notes, Bonds, Leases)

Understanding the different debt instruments is essential for accurate modeling. Common instruments include:

1. Revolving Credit Facility (RCF): A flexible line of credit that companies can draw from and repay as needed within the tenor of the facility (typical tenors are 1-, 3-, and 5 years). The RCF is often used to manage short-term liquidity needs, e.g., backstopping commercial paper programmes or peak seasonal working capital needs, hence its outstanding balance can fluctuate significantly over time.
2. Term Loans (T/L): Loans with a fixed annual amortization or bullet repayment schedule and typically fixed interest rates. These are mostly used for capital expenditures or long-term investments, e.g., acquisition financing or restructuring programs. In some cases, they can be used for dividend recapitalizations, a tool which gained popularity in the private equity industry with the purpose to extract value when a sale of a portfolio company is currently not possible or not profitable.
3. Notes and Bonds: Longer-term debt instruments with fixed or variable interest rates. Bonds can be issued in various forms, including senior, subordinated, and convertible. Bonds usually do not have amortization schedules and the face value becomes due at maturity.
4. Leases: Obligations for rented assets, often treated similarly to debt in similar ranges in financial models. Capital leases, in particular, are recorded on the BS and impact both interest cost and debt principal.
5. Other Debt Securities: Mezzanine financing, convertible debt, and PIK interest loans. Mezzanine debt is typically subordinated and carries higher interest rates, while convertible debt offers the option to convert into equity. PIK interest loans allow interest payments to be deferred by adding them to the principal. Our Expert LBO Package includes detailed modeling of PIK securities.

Seniority of Debt Tranches

Debt instruments vary in their claim on a company’s assets. Senior secured loans are backed by collateral and have the highest repayment priority in case of default, offering lower risk but also lower profitability to lenders. Senior unsecured loans are not collateralized but still rank above other obligations in the capital structure. Subordinated debt sits lower in the capital structure, meaning it is repaid only after senior debts have been satisfied, carrying higher risk and thus offering higher returns. Understanding debt seniority is crucial when structuring tranches of debt in LBO models and merger models.

Loan Repayment Types

Different repayment methods affect the debt schedule and a company’s cash flow:

1. Amortizing: Gradual loan repayment over time, often with fixed annual amortization. This reduces the debt principal incrementally and lowers interest as the outstanding balance decreases (Think of your standard 30-year mortgage or 72-month car loan).
2. Bullet Repayment: Entire debt principal repaid at maturity, common in bonds and certain term loans. This structure results in lower periodic monthly debt payments but requires careful planning to ensure sufficient funds at maturity.

Mandatory Debt refers to legally binding repayments often dictated by loan covenants, where failure to meet these obligations can trigger penalties or loan defaults. In contrast, Optional Repayments allow companies to make early repayments to reduce interest costs and leverage ratios. Companies may choose to repay debt or repurchase stock depending on market conditions and capital structure strategies. This flexibility becomes especially valuable when business performance declines, providing room to manage financial obligations proactively.

Covenants

As a current or aspiring finance professional, it is important to be aware that debt agreements often include covenants that restrict certain actions, such as taking on additional debt, acquisitions or divestitures, or making large capital expenditures. Covenants can be financial (e.g., maintaining certain debt-to-equity ratios) or operational (e.g., restrictions on asset sales). Violating covenants can trigger penalties, increased interest rates, or even loan acceleration.

Debt Schedule and Calculation of Interest Expenses

The debt schedule is essential for projecting the impact of new debt, assessing debt over time, and ensuring that cash available for debt covers all legally binding obligations. It tracks interest expense and interest income, manages debt tranches, and helps with proper debt repayment planning. The debt schedule lays out how these payments could affect a company’s financial standing in the future.

Structure of a Simple Debt Schedule in Excel

A simple debt schedule could include the following components:

1. RCF: Tracks draws and repayments on the revolving facility. It shows the opening balance, any new borrowings, repayments, and the closing debt balance. Note that if there is an outstanding balance under the revolver, this needs to be repaid first.
2. Mandatory / Nominal Repayment or Issuances and Non-Mandatory Repayments: For most other types of debt, the structure will look very similar. Often referred to as the “BASE” method, each tranche of debt will have four lines in excel. Starting with the beginning balance (B), followed by the new borrowings which are added to the balance (A) and the repayments which are subtracted from the balance (S), yielding the ending value (E) of each period. Hence, the “A” and “S” lines capture both mandatory debt repayments and any additional debt issued during the period, as well as discretionary repayments when excess cash is available and the company decides to lower their leverage and save on interest payments.
3. Automatic debt paydown formulas: Advanced financial modeling requires proficiency with automatic debt paydown formulas and their underlying logic. This constitutes the basis for a separate article, but is best understood through hands-on practice. 
4. Interest expense: Calculates interest based on outstanding debt principal for each tranche using a simple interest rate assumption, or ideally a detailed interest rate if legal agreements are available (I.e., Interest floor, base rate, spread) or you have access to a Bloomberg Terminal at work. This is crucial for accurately forecasting interest cost on the income statement. Note that interest is calculated separately for each tranche of debt a company holds, given the type of debt and seniority affects the interest rate lenders will charge.
5. Interest income: Most companies hold cash reserves, earning interest income from these deposits or short-term investments (Think of it like your savings account). This interest income can offset some of the company’s interest expenses, thereby reducing the net cost of debt. The interest income is calculated based on the company’s cash balance and prevailing interest rates, and it is important to include this in the debt schedule to provide a complete picture of net financing costs.
6. Totals: Finally, it is important to create a table from the different debt sections of the schedule, including:

• Total Issuances (Repayments): Total new borrowings minus repayments in a given accounting period.
• Net Interest: Total interest obligations, reflecting both interest expense and any interest income from cash and short-term investments. Using the average debt and cash balance to calculate interest expenses and income helps smooth out intra-year fluctuations for more accurate forecasting.

Example – Debt Schedule for a U.S. Retail Corporate in Excel

Note: This debt schedule is part of our Expert Credit Modeling Package, where you learn to build similar schedules from a blank Excel sheet.

Cash Available for Debt Service / Paying Off Debt

Calculating cash available for debt service is critical for managing mandatory repayments and ensuring sufficient liquidity. The debt schedule definition involves detailing these obligations alongside tools like revolver draws for liquidity, which provide flexible funding during shortfalls. Overdraft facilities and other liquidity crunch mitigants can also help maintain stability in the short-run. The formula typically factors in beginning cash, and all other cash flows except those related to debt issuances and repayments within the financing section of the CFS. The debt schedule lets companies assess if they can meet obligations, need to refinance, or even draw under their RCF. 

How to Build a Debt Schedule in Practice

Let’s consider a company planning to take on new debt for expansion through Capex. The business debt schedule will help:

1. Project interest expense and repayment obligations based on different tranches of debt.
2. Determine how much debt is sustainable given the company’s free cash flow and future prospects.
3. Analyze the impact of the expansion spending on the company’s cash generation and ability to meet debt obligations without compromising operations.
4. Decide whether to repay debt or repurchase stock based on available funds and market conditions.

By modeling the debt schedule, the company can make informed financial decisions, maintain optimal debt levels, and ensure compliance with covenants. This approach is essential for companies looking to grow your business while managing risk. In combination with a cash flow analysis, a debt schedule can help you see how much the company can withstand working capital stress and other external forces, e.g., geopolitical, FX, and commodity price impacts. It is a cornerstone of a quick business evaluation. 

Link to CFS / BS / P&L

For accurate modeling, the debt schedule must be linked properly to the balance sheet, income statement, and cash flow statement to ensure correct projections for valuation, covenant monitoring, and strategic planning:

• Cash Flow Statement: Debt repayments and new issuances are linked from the debt schedule to the cash flows from financing section. These flows directly influence the BS by linking to the company’s debt balances.
• Balance Sheet: The debt balances at the end of each period are updated based on information from the cash flow statement, ensuring accurate reporting of total outstanding debt and its effect on the company’s leverage ratios.
• Income Statement: Interest expense and interest income from the debt schedule flow into the income statement, affecting net income. This net income then rolls into the top line of the cash flow statement, ensuring a coherent model that accurately reflects financing costs and profitability.

Circular References

Linking interest payments to debt balances often creates circular references in Excel. This happens when debt is repaid in the debt schedule, reducing interest expense which links to the P&L, increasing net income, which flows into the cash flow statement and increases cash available for debt repayment in the same period. More cash means more debt can be repaid, further decreasing interest expenses that cycle back through the P&L.

Excel flags this as an error, but you can fix it by enabling iterative calculations in Excel settings. Set iterations to 100 for smooth calculations. We recommend to learn excel shortcuts to navigate and adjust your model efficiently when dealing with these issues.

Be cautious—a stray letter or symbol in the loop can cause #VALUE! errors across your model. This issue has fueled countless all-nighters for analysts and plenty of finance memes. Understanding how to quickly fix this issue can mark the difference between passing an LBO modeling test and breaking into wall street or falling short of it. We will explain how a circ ref error arises and how to solve it in a standalone blog. For immediate questions on this, please contact us directly.

Other Types of Debt

In addition to traditional debt instruments, companies may utilize mezzanine financing, convertible debt, and PIK interest loans. These forms of debt often carry higher risks to lenders and thus yield higher returns, and they require careful modeling to understand their impact on cash flow and financial health. For example, mezzanine debt is subordinated and typically includes equity warrants, while convertible debt can dilute shareholders if converted.

Conclusion

A well-structured debt schedule is vital for accurate financial modeling, whether you’re working on LBO models, credit models, or 3-statement models. Understanding how to build and link a debt schedule can help you master investment banking and private equity modeling tasks, as well as acing interview modeling tests.

Curious about how to put these principles into practice? Download our free case study on the Financial Modeling LLC homepage, featuring a fully integrated 3-statement model. In this case study, you’ll learn how to build a detailed debt schedule and seamlessly integrate it into a dynamic financial model. Explore firsthand how a company’s debt is integrated into a full-scale analysis and enhance your modeling skills!