Debt Schedule

A debt schedule is the backbone of any financial model that includes leverage. It tracks all outstanding debt obligations, models interest expense, and ensures covenant compliance. Without a properly structured debt schedule, your balance sheet, income statement, and cash flow statement won’t reconcile.

What Is a Debt Schedule

A debt schedule is a detailed model of all debt outstanding, including principal balances, interest rates, maturity dates, and repayment terms. It serves two critical functions:

• Balance Sheet Link: Debt balances flow to the balance sheet under current and long-term liabilities.
• Income Statement Link: Interest expense flows to the income statement, affecting EBIT and net income.

The debt schedule ties directly to your three-statement model, making it essential for LBO models, DCF valuations, and any analysis involving leverage. Without it, you can’t calculate leverage ratios, WACC, or project cash flows accurately.

Types of Debt Instruments

Different debt types have different characteristics. Here’s a breakdown of common debt instruments in financial models:

Building the Debt Roll-Forward

The foundation of your debt schedule is the debt roll-forward—tracking how each tranche’s balance changes over time. The formula is straightforward:

For each debt tranche and each period, you’ll populate:

• Beginning Balance: Prior period’s ending balance (or initial amount at close for Year 1)
• New Draws: Additional borrowing during the period (e.g., from revolver or delayed funding)
• Repayments: Principal paid down, including mandatory amortization and optional prepayments
• Ending Balance: The balance sheet figure for that period

Keep this simple and transparent. Each row should clearly show inputs vs. calculated outputs, and formulas should reference your cash flow statement for repayment sources.

Modeling Interest Expense

Interest expense is where a debt schedule gets tricky. You must account for the fact that interest depends on average or ending balances, and the interest you accrue affects cash available for repayment.

Fixed vs. Floating Rate

Fixed Rate: A constant interest rate for the debt’s life. Easier to model but locks in cost.

Floating Rate: A base rate (e.g., SOFR) plus a spread. Your interest expense varies with market conditions. In models, you typically assume a flat base rate or apply a forward curve.

The cost of debt for each tranche is defined at close and should be locked in your model unless modeling refinancing scenarios.

Interest Calculation

Most models use the average balance method:

• Average Balance = (Beginning Balance + Ending Balance) ÷ 2
• Interest = Average Balance × Annual Rate ÷ (# of days / 365)

This avoids a circular reference and is standard in practice.

Mandatory vs. Optional Repayment

Not all debt is paid down the same way. Your model must distinguish between mandatory and optional repayments.

Mandatory Repayment

Most term loans include a mandatory amortization schedule. For example, a 7-year TLA might amortize 5% annually, then the remainder at maturity. This is contractual and non-negotiable—you must build it into your model.

Track amortization schedules explicitly. Many models create a separate schedule for each debt tranche showing scheduled amortization by year.

Optional Repayment (Cash Sweep)

When a business generates excess free cash flow, debt agreements often require a portion to be used for prepayment—a “cash sweep.” Your model should include:

• Available Cash for Debt Repay: FCF less minimum cash balance and capex requirements
• Sweep Rate: The percentage of available cash that must go to debt (e.g., 50% in Year 1, 75% thereafter)
• Prepayment Priority: Usually highest-rate debt first (TLB before TLA, Subordinated before TLB)

This adds flexibility and reflects real credit agreement terms.

Revolver Modeling

A revolver is a plug in financial models—it adjusts to keep cash at or above a minimum threshold. It acts like a credit line for the business.

How It Works

The revolver balance is calculated as:

If projected cash exceeds the minimum, the revolver is repaid. If cash falls short, the company draws on the revolver to maintain liquidity. The revolver commitment (available capacity) is also tracked separately.

Key Inputs

• Minimum Cash Target: Often 10–25% of annual revenue or a fixed dollar amount
• Revolver Commitment: The maximum available; rarely fully drawn unless in distress
• Revolver Rate: Often prime + spread; may include an undrawn fee on unused capacity

Covenant Analysis

Debt agreements include covenants—financial tests that must be met to avoid default. Your debt schedule should include a covenant checklist.

Common Covenant Types

• Leverage Ratio: Total Debt / EBITDA, typically 3.0–5.0x depending on credit quality.
  Leverage = Total Debt ÷ EBITDA
• Interest Coverage Ratio: EBITDA / Interest Expense, typically 2.5–4.0x.
  Interest Coverage = EBITDA ÷ Interest Expense
• Debt-to-Equity: Total Debt ÷ Total Equity; primarily for balance sheet health.
• Minimum Cash: Enforced through revolver mechanics; maintains operational flexibility.
• Capex Threshold: Restrictions on major capital investments without lender approval.

What-If Testing

Use sensitivity analysis to model covenant compliance across scenarios (base, downside, upside). Identify which covenants are tightest and at what point the business risks a covenant breach. This informs debt structure decisions during deal underwriting or refinancing.

Integration with the Three-Statement Model

Your debt schedule doesn’t stand alone. It integrates tightly with the three-statement model:

• Balance Sheet: Debt balances (current + non-current) feed directly. Accrued interest may flow to current liabilities.
• Income Statement: Interest expense reduces EBIT; the debt schedule produces this figure.
• Cash Flow Statement: Debt draws and repayments appear in the financing section. They reduce or increase cash.

Ensure your formulas cross-reference properly. If debt balances on the BS don’t reconcile with your debt schedule, something is broken. Similarly, interest expense on the IS must match the debt schedule calculation exactly.

For valuations, the debt schedule feeds into WACC calculation (debt levels and cost of debt are inputs). It also underpins LBO analysis where debt paydown and returns to sponsors depend entirely on the debt schedule accuracy.

Key Takeaways

Frequently Asked Questions