Debt Schedule in Excel: Build a Credit Analysis Model (2026)

A single mislinked cell in a debt schedule once caused a $50 million valuation error in a mid-market M&A deal — the interest expense flowed to the income statement at the wrong rate for three projection years before anyone caught it. That kind of mistake is shockingly common. Among all supporting schedules in financial models, the debt schedule is the most formula-dense and the most error-prone. Yet it is also the schedule that lenders, credit committees, and PE deal teams scrutinize first.

This guide walks you through building a complete debt schedule in Excel from scratch, including multi-tranche structures, amortization mechanics, cash sweep logic, and the coverage ratios that drive real credit decisions.

What Is a Debt Schedule in Excel?

A debt schedule is a supporting worksheet in a financial model that tracks every outstanding debt instrument — its beginning balance, new borrowings, scheduled repayments, optional prepayments, and ending balance — across each projection period. It calculates interest expense for the income statement, closing debt balances for the balance sheet, and principal cash flows for the cash flow statement.

In credit analysis, the debt schedule is the single most important output. It answers the question every lender asks: Can the borrower generate enough cash to service this debt, and what happens if the business underperforms?

Why Credit Analysts Care About Debt Schedules

• Debt capacity sizing — determines how much a company can borrow based on projected cash flows
• Covenant compliance — tests whether leverage and coverage ratios stay within agreed limits
• Downside protection — stress-tests the schedule under recession or operational failure scenarios
• Refinancing risk — identifies when large maturities come due relative to available liquidity

Key Components of a Debt Schedule

Every debt schedule follows the BASE framework — four rows per tranche that capture the full lifecycle of a debt instrument:

Row	Label	Formula Logic
B	Beginning Balance	= Prior period Ending Balance
A	Additions (Drawdowns)	= New borrowings in the period
S	Subtractions (Repayments)	= Scheduled amortization + optional prepayments
E	Ending Balance	= B + A − S

Below the BASE rows, you add interest expense calculations and coverage ratio outputs. This structure scales cleanly — whether you model one tranche or ten, the pattern repeats.

Assumptions You Need Before Building

Before touching any formulas, define these inputs in a dedicated assumptions section:

• Facility size (total commitment)
• Drawn amount at close (may differ from commitment)
• Interest rate (fixed or floating spread + benchmark)
• Amortization profile (bullet, straight-line, sculpted, or percentage-of-original)
• Maturity date
• Mandatory amortization schedule (e.g., 1% per quarter)
• Cash sweep percentage (e.g., 50% of excess cash flow)
• Commitment fee on undrawn amounts (typically 25–50 bps)

💡 Pro Tip: Always separate your assumptions from your calculations. Place all debt terms in a clearly labeled "Assumptions" or "Inputs" section at the top of the schedule. This makes the model auditable and lets you run scenarios by changing inputs without touching formulas.

How Do You Build a Debt Schedule in Excel Step by Step?

Building a debt schedule in Excel requires setting up your assumptions, constructing the BASE rows, calculating interest, and linking outputs to the three financial statements. Here is the complete process broken into six steps.

Step 1: Set Up the Assumptions Table

Create a block with labeled inputs. Example layout for a $100M term loan:

B2: Facility Size           C2: 100,000,000
B3: Drawn at Close          C3: 100,000,000
B4: Annual Interest Rate    C4: 6.50%
B5: Term (Years)            C5: 7
B6: Annual Amort Rate       C6: 5.00%
B7: Cash Sweep %            C7: 50%
B8: Commitment Fee          C8: 0.375%

Step 2: Build the Time Header

Set up columns for each projection period. For annual models, Year 0 is the closing date and Years 1–7 (or whatever the maturity) are projection periods:

D1: Year 0    E1: Year 1    F1: Year 2    G1: Year 3  ...

Step 3: Construct the BASE Rows

For a term loan with mandatory amortization and optional cash sweep:

Beginning Balance (Row 10):

=IF(E1=1, DrawnAtClose, D13)

Where D13 is the prior period's ending balance.

Mandatory Amortization (Row 11):

=-MIN(FacilitySize * AnnualAmortRate, E10)

The MIN function prevents repayment from exceeding the remaining balance. Note the negative sign — repayments reduce the balance.

Cash Sweep Repayment (Row 12):

=-MIN(ExcessCashFlow * CashSweepPct, E10 + E11)

This applies only if there is excess cash flow after mandatory payments. Again, MIN ensures you do not repay more than what remains.

Ending Balance (Row 13):

=E10 + E11 + E12

⚠️ Warning: Cash sweep formulas create a circular reference because excess cash flow depends on interest expense, which depends on the debt balance, which depends on the cash sweep. Enable iterative calculations in Excel: File → Options → Formulas → check "Enable iterative calculation" with maximum iterations set to 100 and maximum change set to 0.001.

Step 4: Calculate Interest Expense

For a fixed-rate term loan with beginning-of-period convention:

=E10 * AnnualInterestRate

For mid-period convention (more common in practice):

=(E10 + E13) / 2 * AnnualInterestRate

ℹ️ Note: Using the average of beginning and ending balance for interest calculation is standard in most LBO and credit models. It better approximates interest accrual when amortization payments occur throughout the year. Some models use beginning balance only — confirm the convention with your deal team.

Step 5: Add the PMT/IPMT Alternative

If the debt amortizes like a standard mortgage (equal periodic payments), use Excel's built-in financial functions instead of manual calculations:

=PMT(AnnualRate/12, TermMonths, -Principal)

This returns the total periodic payment (principal + interest). To isolate components:

=IPMT(AnnualRate/12, CurrentPeriod, TermMonths, -Principal)

Returns just the interest portion for a specific period.

=PPMT(AnnualRate/12, CurrentPeriod, TermMonths, -Principal)

Returns just the principal portion for a specific period.

Function	Returns	Use Case
PMT	Total payment (P+I)	Fixed installment loans
IPMT	Interest portion only	Income statement interest expense
PPMT	Principal portion only	Cash flow statement / balance sheet
CUMIPMT	Cumulative interest over a range	Total interest cost analysis
CUMPRINC	Cumulative principal over a range	Total paydown analysis

💡 Pro Tip: For most corporate debt (term loans, revolvers, bonds), do NOT use PMT/IPMT. These functions assume fixed equal payments like a mortgage. Corporate debt typically has custom amortization schedules (e.g., 1% per quarter mandatory plus discretionary sweeps). Use the manual BASE approach instead. Reserve PMT/IPMT for consumer lending models or mortgage-backed securities.

Modeling Multi-Tranche Debt Structures

Real-world credit facilities rarely consist of a single loan. A typical leveraged buyout might include three or more tranches, each with different priority, pricing, and amortization:

Tranche	Example Size	Rate	Amortization	Maturity	Priority
Revolver	$50M commitment	SOFR + 300 bps	None (bullet)	5 years	Senior secured (1st lien)
Term Loan A	$150M	SOFR + 325 bps	5% annual mandatory	6 years	Senior secured (1st lien)
Term Loan B	$200M	SOFR + 450 bps	1% annual mandatory	7 years	Senior secured (1st lien)
Senior Notes	$100M	8.50% fixed	None (bullet)	8 years	Senior unsecured
Subordinated Notes	$75M	11.00% fixed	None (bullet)	10 years	Subordinated

Waterfall Repayment Logic

Mandatory and optional repayments follow a priority waterfall. Cash sweep proceeds typically pay down the most expensive debt first (or the most senior, depending on the credit agreement):

graph TD
    A[Excess Cash Flow] --> B{Cash Sweep Applied?}
    B -->|Yes| C[Pay Down Revolver First]
    C --> D[Pay Down Term Loan A]
    D --> E[Pay Down Term Loan B]
    E --> F[Remaining to Senior Notes]
    B -->|No| G[Retained by Company]

In Excel, model the waterfall with nested MIN and MAX functions:

Revolver Paydown:
=-MIN(CashSweepAmount, MAX(RevolverBalance, 0))

TLA Paydown:
=-MIN(CashSweepAmount - ABS(RevolverPaydown), MAX(TLABalance, 0))

TLB Paydown:
=-MIN(CashSweepAmount - ABS(RevolverPaydown) - ABS(TLAPaydown), MAX(TLBBalance, 0))

Each subsequent tranche receives only what remains after higher-priority tranches are fully repaid. The MAX(..., 0) guards prevent negative balances. In PE-sponsored leveraged buyouts, this debt repayment waterfall feeds directly into the private equity distribution waterfall — once debt tranches are paid down, the remaining proceeds flow through GP/LP tiers that allocate preferred returns and carried interest.

Revolver Draw and Repay Logic

A revolving credit facility is unique — the borrower draws and repays as needed. Model it as:

Revolver Draw = MAX(0, CashDeficit)
Revolver Repay = -MIN(CashSurplus, RevolverBalance)

Where CashDeficit is the amount by which operating cash flow falls short of required payments, and CashSurplus is the excess after all obligations. This creates the same circular reference warning as cash sweeps.

How Do You Calculate Credit Ratios in Excel?

Credit ratios (also called coverage ratios or debt metrics) measure a borrower's ability to service its debt. Lenders set minimum thresholds as loan covenants. Here are the four ratios every credit analyst models.

Debt Service Coverage Ratio (DSCR)

=CFADS / (InterestExpense + MandatoryPrincipal)

Where CFADS (Cash Flow Available for Debt Service) equals EBITDA minus taxes minus maintenance capex minus changes in working capital. A DSCR above 1.25x is the typical minimum for investment-grade borrowers. Project finance lenders often require 1.30x–1.50x. For commercial real estate acquisitions, lenders apply similar 1.20x–1.25x thresholds — a check built into every well-structured real estate pro forma model as a period-by-period test of projected NOI against debt service.

Interest Coverage Ratio (ICR)

=EBITDA / InterestExpense

Simpler than DSCR because it ignores principal repayment. An ICR above 2.0x is generally considered healthy. Below 1.5x signals distress.

Leverage Ratio (Total Debt / EBITDA)

=TotalDebtBalance / EBITDA

This is the single most-cited credit metric. Investment-grade companies typically operate below 3.0x. Leveraged buyouts commonly start at 5.0x–7.0x and delever over the hold period.

Fixed Charge Coverage Ratio (FCCR)

=(EBITDA - CapEx - Taxes) / (InterestExpense + MandatoryPrincipal)

More conservative than DSCR because it deducts capital expenditures and taxes before measuring coverage.

Metric	Formula	Healthy Threshold	Distress Signal
DSCR	CFADS / Debt Service	> 1.25x	< 1.00x
ICR	EBITDA / Interest	> 2.00x	< 1.50x
Leverage	Total Debt / EBITDA	< 4.00x	> 6.00x
FCCR	(EBITDA−CapEx−Tax) / Debt Service	> 1.10x	< 1.00x

Example: A company with $80M EBITDA, $25M interest expense, and $10M mandatory principal has a DSCR of $80M / ($25M + $10M) = 2.29x and an ICR of $80M / $25M = 3.20x. Both comfortably exceed typical covenant thresholds.

Linking the Debt Schedule to the Three Financial Statements

The debt schedule is not a standalone artifact — it feeds critical line items into the three-statement financial model. Getting these linkages wrong is the most common source of model errors.

graph LR
    DS[Debt Schedule] -->|Interest Expense| IS[Income Statement]
    DS -->|Closing Debt Balances| BS[Balance Sheet]
    DS -->|Principal Repayments| CF[Cash Flow Statement]
    IS -->|Net Income| BS
    IS -->|Net Income| CF
    CF -->|Ending Cash| BS
    BS -->|Opening Debt| DS

Income Statement

• Interest expense from the debt schedule flows into the income statement below operating income (EBIT)
• This reduces pre-tax income and therefore tax expense
• Net income then flows to retained earnings on the balance sheet and starts the cash flow statement

Balance Sheet

• Total debt (sum of all tranche ending balances) appears in current liabilities (portion due within 12 months) and long-term liabilities
• Split current vs. long-term based on next year's mandatory amortization from your debt schedule
• The balance sheet must balance: if debt goes down, either cash goes down (you used cash to repay) or equity goes up (retained earnings accumulated)

Cash Flow Statement

• Mandatory principal repayments and optional prepayments appear in the financing activities section
• New borrowings (revolver draws, new issuances) also appear in financing activities
• Interest expense is already captured in operating activities through net income (or added back and shown separately under some presentation formats)

⚠️ Warning: Always verify that total debt on the balance sheet equals the sum of ending balances in the debt schedule. Build a check row: =BSDebt - SUM(AllTrancheEndingBalances). If this is anything other than zero, you have a linkage error.

Common Debt Schedule Mistakes and How to Avoid Them

After reviewing hundreds of financial models, these are the errors that appear most frequently in debt schedules:

1. Unresolved Circular References Cash sweeps and revolver draws create circularity. If you forget to enable iterative calculations, Excel shows 0 or #REF! errors throughout the model. Always set iterative calculations to 100 iterations with 0.001 tolerance. Better yet, add a circular reference breaker toggle — a VBA-based circuit breaker is the most robust solution for complex multi-tranche models, as covered in our VBA for financial modeling guide.

2. Interest Timing Convention Mismatch Using beginning-of-period balance for interest when the model assumes mid-period (or vice versa) will systematically over- or understate interest expense by the amount of that period's amortization times the rate.

3. Negative Debt Balances Without MIN guards, repayment formulas can drive a tranche balance below zero. Every repayment cell should include: =-MIN(RepaymentAmount, RemainingBalance).

4. Ignoring Commitment Fees Revolving credit facilities charge fees on the undrawn portion. The formula is:

=MAX(0, Commitment - DrawnBalance) * CommitmentFeeRate

This is a real cash cost that hits the income statement and is frequently omitted.

5. Wrong Current vs. Long-Term Debt Split Next year's mandatory amortization should be classified as current debt on the balance sheet. Forgetting this throws off working capital calculations and any ratios that depend on current liabilities.

Building Sensitivity Analysis Into Your Debt Schedule

A single-scenario debt schedule is useful, but lenders want to see how the model performs under stress. Use Excel Data Tables to sensitize key outputs:

=DSCR formula in the corner cell
Row input: Interest rate scenarios (5%, 6%, 7%, 8%)
Column input: EBITDA decline scenarios (0%, -10%, -20%, -30%)

This generates a matrix showing DSCR under sixteen combinations of rate and earnings stress. Conditional formatting with red/yellow/green highlighting makes covenant breaches immediately visible.

You can also use the sensitivity analysis techniques covered in our earlier guide to build one-way and two-way data tables around any output cell in the debt schedule. For capital structures that include publicly traded fixed-rate bonds, this sensitivity extends to market price: how much the bond's value changes when yields move — a measure captured by bond duration analysis in Excel through modified duration and convexity.

Frequently Asked Questions

What is the difference between a debt schedule and an amortization schedule?

An amortization schedule tracks a single loan's principal and interest payments over time, typically with equal periodic payments (like a mortgage). A debt schedule is broader — it models all of a company's debt instruments simultaneously, including revolvers, term loans, bonds, and subordinated notes, each with different repayment mechanics and priorities.

How do you handle floating-rate debt in a debt schedule?

Model the interest rate as a formula: =BenchmarkRate + Spread. Place the benchmark rate (e.g., SOFR) in your assumptions as a time series so it can change each period. This lets you stress-test rate scenarios. Many models also include an interest rate floor (e.g., 1.00% SOFR floor) using =MAX(Floor, BenchmarkRate) + Spread. For fixed-rate tranches in the same capital structure — senior notes or subordinated bonds — analysts on the investor side use bond duration in Excel to quantify how the market value of those positions changes with each rate move, the complementary measure to DSCR for anyone evaluating both sides of the credit.

What DSCR do lenders typically require?

Most commercial lenders require a minimum DSCR of 1.20x–1.25x for corporate loans. Project finance deals — where non-recourse lenders use debt sculpting to align repayments with project cash flow rather than uniform amortization — typically require 1.30x–1.50x, a mechanic covered in detail in our project finance model guide. Highly leveraged transactions may negotiate down to 1.10x, but this leaves minimal margin for underperformance.

Should I use annual or quarterly periods in a debt schedule?

Use quarterly periods when the credit agreement specifies quarterly amortization payments or covenant testing. Use annual periods for early-stage analysis, pitch decks, or when quarterly granularity does not materially change the result. Most production-quality credit models use quarterly periods to match real-world payment and testing schedules.

How does VeloraAI help with building debt schedules?

VeloraAI's formula generation can translate natural-language descriptions of debt mechanics into working Excel formulas — for example, describing a cash sweep waterfall in plain English and receiving the nested MIN/MAX formula structure. This is especially useful for complex multi-tranche models where formula errors are most costly.

Putting It All Together

A well-built debt schedule is the backbone of every credit analysis, LBO model, and leveraged finance deal. Start with the BASE framework, add interest calculations using the appropriate convention, layer in cash sweep logic for optional prepayments, and tie everything to coverage ratios that lenders actually monitor.

The formulas themselves are not complicated — MIN, MAX, IF, and basic arithmetic handle 90% of the logic. The difficulty lies in getting the linkages right between the debt schedule, the three financial statements, and the circular references that inevitably arise. Build check rows, stress-test your assumptions, and validate every balance against the balance sheet.

For analysts working with complex multi-tranche structures, tools like VeloraAI can accelerate the formula-writing process, but understanding the underlying mechanics is non-negotiable. The best credit analysts do not just run models — they interrogate them.