Debt and cash Updated November 2026

Debt schedule

A debt schedule tracks each tranche year by year: closing balance = opening balance + new borrowing - repayments, and interest expense = interest rate x opening balance (or average balance). The closing balance links to the balance sheet, interest to the income statement, and repayments to financing cash flow.

Syntax

Closing = Opening + Draws - Repayments;  Interest = Rate x Opening

Part	What it is
`Opening`	Prior period closing balance, linked
`Repayments`	Mandatory amortization plus any optional cash sweep
`Rate`	Interest rate on the tranche, fixed or base rate plus margin

Examples

Opening 1,000, repayment 100, rate 5%
Interest = 1000 * 5%, Closing = 1000 - 100

Result: Interest 50, closing 900

Year 2: opening 900, repayment 100
Interest = 900 * 5%, Closing = 900 - 100

Result: Interest 45, closing 800

Mandatory amortization of 10% of the original 1,000, capped at the balance
=MIN(opening, 1000 * 10%)

Result: 100 while the balance is above 100

Common mistakes

Repaying more than the balance: wrap repayments in MIN(opening balance, scheduled amount).
Interest on the closing balance, which creates a circular reference with the cash sweep; use opening balance or enable iteration.
Forgetting to link repayments to the cash flow statement, so cash is overstated and the balance sheet does not balance.

Practice Debt schedule

These drills check your answer on live data, free, no account needed.

Interest on the opening debt balanceA term loan is repaid in equal amounts. Fill the opening balance for later years (row 5), the closing balance (row 7) and interest expense (row 8), charging the rate in B2 on the opening balance of each year.
Cash and revolver, fully linkedNow link the cash too. Each year starts with last year's closing cash, adds the cash flow, then draws on or repays the revolver to keep the minimum in B2. Fill rows 7 to 12 (the first opening cash and opening revolver are set).
Debt service coverage testLenders compare cash available for debt service with interest plus principal. Fill the coverage ratio (row 8) and a covenant test (row 9) that shows "Pass" when the ratio is at least the minimum in B2 and "Breach" otherwise.

Questions

What is a cash sweep?

An optional repayment equal to a percentage of excess cash after mandatory debt service. It is modeled as MIN(available cash x sweep %, remaining balance).

How do I order tranches in a debt schedule?

By seniority: revolver first, then term loan A, term loan B, then subordinated notes. Cash sweeps repay the most senior prepayable tranche first.

Interest on the opening balanceInterest = rate x opening balance avoids a circular reference; rate x average balance is more accurate but circular. Both formulas, an example, when to use.
RevolverA revolver draws when cash falls below the minimum and repays from excess cash. The MIN and MAX formula, a two-year worked example and avoiding circularity.
Amortization tableA loan amortization table shows opening balance, payment, interest, principal and closing balance per period. How to build it with PMT, exact rows, mistakes.
DSCRDSCR = cash flow available for debt service / (interest + principal). How lenders compute it, exact examples, typical covenant levels and sizing debt from it.