How to Build a Debt Schedule

Institutional Definition¶

A debt schedule is built by rolling each debt tranche forward from an opening balance through drawdowns and repayments to a closing balance, calculating interest expense on a consistent basis, and connecting the results to all three financial statements. This guide sets out the build step by step, for the generic corporate case. For the DSCR-driven repayment profiling used in project finance, see Debt Sculpting Mechanics, which follows a materially different repayment logic.

Step 1: List the Debt Tranches¶

Begin by listing each facility separately — term loans, bonds, and any revolving credit facility — each with its own opening balance, interest rate (fixed or a margin over a floating benchmark), amortization terms, and seniority. Aggregating all debt into a single blended balance at the outset makes it impossible to later distinguish tranche-specific interest rates or repayment obligations, and should be avoided even for a business with a simple capital structure.

Step 2: Roll the Opening Balance Forward¶

Opening Balance (Tranche N)
+ Drawdowns
- Mandatory (Scheduled) Repayment
- Optional (Cash Sweep) Repayment
= Closing Balance (Tranche N)

Each tranche rolls forward on this structure independently. The opening balance in any period must equal the prior period's closing balance — a broken link here is one of the most basic and most consequential debt schedule errors, since it disconnects the entire roll-forward from its own history.

Step 3: Distinguish Mandatory From Optional Repayment¶

Mandatory (scheduled) repayment follows the fixed amortization profile specified in the loan agreement — level principal, level payment (annuity-style), or bullet-with-partial-amortization structures are all common — and is due in full regardless of the borrower's actual cash position in the period.

Optional (voluntary) repayment, frequently structured as a cash sweep, applies surplus cash — beyond mandatory obligations, operating needs, and any reserve requirements — to accelerate debt paydown ahead of the mandatory schedule. Whether a cash sweep is mandatory under the loan agreement's terms (a common feature of leveraged finance structures) or genuinely discretionary should be stated explicitly, since it changes how the modelled repayment behaves under different cash flow scenarios.

Keep these two repayment types on separate rows in the schedule. Combining them into a single "repayment" line makes it impossible to later assess how much deleveraging is contractually required versus how much depends on cash generation performing as projected.

Step 4: Model the Revolver as a Balancing Plug¶

Where the capital structure includes a revolving credit facility, it is standard practice to designate it as the model's balancing mechanic:

If (Cash Available Before Revolver < Minimum Cash Requirement):
    Revolver Draw = Minimum Cash Requirement - Cash Available Before Revolver
Else:
    Revolver Repayment = MIN(Cash Available Before Revolver - Minimum Cash Requirement, Revolver Outstanding Balance)

This connects the debt schedule directly to the balance sheet's overall funding position: a period showing a cash shortfall draws the revolver to cover it; a period showing a surplus repays the revolver first, before any other discretionary use of cash such as a further voluntary term-loan prepayment or a dividend. This mechanic should be presented as a clearly labelled, disclosed feature of the financing structure — not left implicit for a reviewer to have to infer from the formulas alone.

Step 5: Calculate Interest Expense and Resolve the Circularity¶

Interest can be calculated on the opening balance, the closing balance, or the average of the two:

Interest Expense (Opening Balance Method) = Opening Balance × Interest Rate
Interest Expense (Average Balance Method) = AVERAGE(Opening Balance, Closing Balance) × Interest Rate

The average-balance method is generally more precise, since it better approximates interest accruing on debt drawn or repaid partway through the period, but it introduces a genuine circular reference: closing balance depends on the period's cash flow, which depends on interest expense, which (under this method) depends on the closing balance itself.

Two standard resolution techniques, covered in full on Circularity in Debt Models:

1. Calculate interest on the opening balance only. This removes the circularity entirely, at the cost of a small approximation that slightly overstates interest in a period with a large repayment or drawdown.
2. Enable iterative calculation with a documented circuit breaker. Excel's iterative calculation setting allows the circular formula to resolve through repeated recalculation, but should always be paired with an explicit toggle cell that can force the circular term to zero, so the model can be checked for stability and does not silently break if iteration is disabled by a different user opening the file.

Whichever method is chosen, disclose it explicitly in the model's documentation, since the choice materially affects the interest figure calculated in periods with significant balance movement.

Step 6: Connect the Debt Schedule to the Three Statements¶

• Closing balance (summed across all tranches) feeds the balance sheet's debt line.
• Interest expense (summed across all tranches) feeds the income statement's interest line.
• Drawdowns and repayments feed the cash flow statement's financing section.

See Statement Linking Mechanics for how this connection fits into the broader three-statement build, and confirm the balance sheet still balances once all three links are in place.

Common Errors¶

Continue Reading¶

Prerequisites¶

• Financial Statements in Financial Modelling — the parent pillar
• Debt Schedule

Related Technical Guides¶

• Circularity in Debt Models
• Debt Sculpting Mechanics in Project Finance Models
• Statement Linking Mechanics

Related Glossary¶

• Circular Reference