Debt Schedule Model: Track Every Tranche and Interest

Key Takeaways

• A debt schedule model tracks each tranche separately, term loans, bonds, revolvers, with its own amortization schedule, interest rate, and roll-forward, feeding total interest to the income statement each period.
• Interest is computed on the average of beginning and ending balances, so a 25 million optional prepayment mid-year reduces interest expense for that period, not just the next one.
• Modeling all debt as a single bullet maturity is the most common shortcut error; it leaves a large unexplained repayment at maturity and understates cash drain in intervening years.
• Debt structure determines refinancing risk and free cash flow available to equity, making the schedule essential for any levered company analysis or LBO model.

What It Is

The schedule shows, for each period, the beginning balance of every debt instrument, mandatory and optional repayments, new issuances, the ending balance, and the interest expense on that tranche. Typical instruments include term loans (with mandatory amortization), senior notes or bonds (bullet maturity), convertibles, and a revolving credit facility.

Interest expense is usually computed on the average of beginning and ending balances so that mid-period draws or repayments get partial-period treatment. The total interest flows to the income statement, where it reduces pre-tax income. The cash portion of interest and principal flows to the cash flow statement.

The Intuition

Treating all debt as one bucket hides the risks that matter most. A company with 500 million outstanding could have it all due next year as a single bullet or spread across a term loan amortizing over seven years. The income statement interest line is identical, but the refinancing risk is wildly different. A debt schedule forces that detail into the model.

Separately, interest expense depends on the debt balance, and the debt balance depends on the cash flow, and the cash flow depends on interest expense. A proper schedule exposes this loop instead of hiding it inside one cell.

How It Works

Each tranche follows the same roll-forward.

Beginning balance
+ New issuance
- Mandatory amortization
- Optional prepayment
- Final maturity repayment
= Ending balance

Interest on that tranche uses the rate (fixed for bonds, floating for most term loans and revolvers) applied to the average balance.

Average balance  = (Beginning + Ending) / 2
Interest expense = Average balance x Interest rate

Floating rates are modeled as a benchmark (e.g. SOFR) plus a spread. For a revolver priced at SOFR plus 300 basis points, the rate for the period is the benchmark forecast plus 3 percent.

Mandatory amortization for term loans is either a fixed percentage per year (e.g. 1 percent annual) or a step schedule ending with a balloon payment at maturity. Optional prepayments are driven by a cash sweep rule or left to management discretion depending on the use case.

Total interest across all tranches hits the income statement as one line. The mandatory and discretionary principal repayments appear as cash outflows in the financing section of the cash flow statement.

Worked Example

A hypothetical company starts year 1 with two debt instruments.

Term Loan B:  500, amortizing at 1 percent per year, SOFR + 350
Senior Notes: 300, bullet due year 7, fixed 6.5 percent

Assumed SOFR: 4.5 percent, so TLB rate = 8.0 percent

Year 1 roll-forward and interest:

Term Loan B
  Beginning             500
  Mandatory amort       (5)     1 percent of starting
  Optional prepayment   (25)    from cash sweep
  Ending                470
  Average balance       485
  Interest expense       38.8   (485 x 8.0 percent)

Senior Notes
  Beginning             300
  No amort, no prepay     0
  Ending                300
  Interest expense       19.5   (300 x 6.5 percent)

Total interest          58.3   to income statement
Principal repayments    30     to cash flow financing

If revenue softens and the cash sweep prepayment falls to zero, total year 1 principal drops to 5, and next year's average TLB balance is higher, so interest expense rises. A one-number "debt" forecast cannot model this.

Common Mistakes

1. Interest on beginning balance only. Simpler but inaccurate when the company issues or repays debt mid-period. Using the average beginning-ending balance is the convention and handles partial-period financing activity correctly.

2. Wrong sign on repayments in the cash flow statement. Principal repayments are a cash outflow, so they sit with a negative sign in the financing section. Forgetting the sign inflates cash.

3. Missing the mandatory amortization schedule for term loans. Analysts sometimes plug term loans as bullets to save time. This leaves a huge unexplained repayment at maturity and understates cash drain in intervening years.

4. Double-counting issuance costs. Debt issuance fees are capitalized and amortized over the life of the debt. Modelers who expense them in year 1 and also amortize them separately end up deducting the same dollar twice.

5. Treating the revolver like a term loan. The revolver draws and repays automatically based on cash needs. Building it with a fixed amortization schedule defeats its purpose as the model's balancing plug. Revolver mechanics are covered in a separate article.

Frequently Asked Questions

Q: What is a debt schedule model in simple terms? A debt schedule model is a set of roll-forward tables that tracks the beginning balance, repayments, new issuances, and ending balance for each debt instrument a company has, then computes the interest expense that flows to the income statement.

Q: How does a debt schedule model affect investment decisions? It reveals the true cash cost of leverage, the pace of deleveraging, and when large maturities fall due. Two companies with identical income statement interest can have very different risk profiles depending on whether their debt is amortizing or a balloon.

Q: What is a real-world example of a debt schedule model in use? In an LBO, a 500 million Term Loan B amortizing at 1 percent per year plus a 75 percent cash sweep produces very different year-by-year interest and cash flow from a 500 million bullet bond, the schedule makes that difference explicit and measurable.

Q: How can investors use or avoid debt schedule errors? Investors should check whether a model treats each tranche separately and whether floating-rate debt reflects current benchmark rates. A model using a single blended rate on a single "debt" cell is hiding maturity risk and rate risk.

Q: How is a debt schedule model different from simply using total interest expense? A single interest expense line tells you the cost but not the structure. A debt schedule shows when principal falls due, which tranches are amortizing versus bullet, and how a change in cash flow affects which debt gets paid down first.

Sources

1. Wall Street Prep. "Debt Schedule | Formula + Calculator." https://www.wallstreetprep.com/knowledge/debt-schedule/
2. Wall Street Prep. "Revolver Debt | Formula + Calculation Example." https://www.wallstreetprep.com/knowledge/modeling-revolving-credit-line-excel-free-template/
3. Breaking Into Wall Street. "Debt Schedule: Video Tutorial and Excel Example." https://breakingintowallstreet.com/kb/leveraged-buyouts-and-lbo-models/debt-schedule/
4. Investment Banking Analysts. "How to Build a Debt Schedule in Excel: Revolver, Term Loan, Bonds." https://investmentbankinganalysts.com/how-to-build-a-debt-schedule-in-excel-revolver-term-loan-bonds/

Disclaimer

This article is educational content only and is not financial advice. Nothing here is a recommendation to buy, sell, or hold any security. Consult a licensed advisor before making investment decisions.