How to Build a Debt Schedule in Excel

A debt schedule tracks each tranche of debt from beginning balance to ending balance, layering in mandatory amortization, optional repayments, and interest. The optional repayment is usually a cash sweep that pays down debt with excess cash, and because interest depends on the balance while the balance depends on cash after interest, the schedule is circular. You resolve it with iterative calculation.

What a debt schedule is and why it matters

A debt schedule is the engine that connects a model's debt balances, interest expense, and cash flow. For each tranche of debt it computes a beginning balance, the repayments during the period, and an ending balance, and it calculates the interest that flows to the income statement.

The schedule matters because debt is rarely static in a forecast. Mandatory amortization reduces the balance on a fixed timetable, while a cash sweep uses surplus cash to pay down debt early. Both change the interest expense, which changes net income, which changes the cash available to sweep. That feedback is the defining feature of a real debt schedule.

Build the schedule step by step

This layout shows one tranche across a single period. The ending balance becomes next period's beginning balance, and interest is computed on the average of beginning and ending balances.

1. Set the beginning balance equal to the prior period ending balance.
2. Subtract mandatory repayment, a scheduled amortization amount or percentage of the original principal.
3. Subtract the optional repayment, which is the cash sweep limited to available cash and the remaining balance.
4. Compute the ending balance: beginning - mandatory - optional.
5. Calculate interest on the average balance: =rate * (beginning + ending) / 2.
6. Feed interest to the income statement and the repayments to the cash flow statement's financing section.

The cash sweep is capped by both available cash and the remaining balance, so a tranche never overpays.

The formulas and the circularity

The cash sweep creates a circular reference. Interest depends on the average balance, the ending balance depends on the optional repayment, the optional repayment depends on cash available, and cash available depends on interest. Excel resolves this when iterative calculation is enabled under File, Options, Formulas.

To keep the model auditable, add a circularity breaker switch. When the switch is off, hard set interest or the sweep to zero so you can find errors without a runaway loop; when on, the live formulas run.

• Beginning balance: =prior_period_ending_balance.
• Mandatory repayment: =MIN(scheduled_amount, beginning_balance).
• Cash sweep: =MIN(cash_available_for_sweep, beginning_balance - mandatory).
• Ending balance: =beginning - mandatory - optional.
• Interest on average balance: =interest_rate * (beginning + ending) / 2.
• Circularity switch: =IF(switch=1, live_formula, 0) to break the loop while auditing.

Pitfalls and what reviewers check

The classic pitfall is an uncontrolled circular reference that fills the model with zeros or #REF! after a stray calculation. Always pair the cash sweep with a circularity switch and keep iterative calculation settings documented near the schedule.

Reviewers confirm that the sweep cannot pay more than the outstanding balance, that mandatory and optional repayments both flow to the cash flow statement, and that interest is computed consistently, either on the beginning balance or the average balance, but not mixed across tranches.

Check the direction of the links too. Repayments are a use of cash in financing, draws are a source, and interest expense reduces pre-tax income. Mapping which cells feed the loop is the fastest way to find a broken link.