Debt Schedule
Executive Summary
Key Takeaways
- ✓ A debt schedule rolls each debt tranche forward each period, from an opening balance through drawdowns and repayments to a closing balance, and calculates the resulting interest expense.
- ✓ A revolving credit facility is frequently used as a balancing plug — drawn when the model shows a cash shortfall, repaid when it shows a surplus — connecting the debt schedule to the balance sheet's overall funding position.
- ✓ Calculating interest on the average of the opening and closing balance introduces a circular reference, since the closing balance itself depends on interest expense through available cash.
- ✓ This entry covers the generic corporate debt schedule; project finance models typically use DSCR-driven debt sculpting instead of a fixed corporate amortization schedule, described on Debt Sculpting Mechanics.
Definition¶
A debt schedule is the section of a financial model that tracks the periodic movement of a company's debt balances, rolling each tranche forward from an opening balance through drawdowns and repayments to a closing balance, and calculating the interest expense that balance generates. It is the specific mechanism connecting the balance sheet's debt balance to the income statement's interest expense and the cash flow statement's financing section.
This entry covers the generic corporate debt schedule — a company financed by one or more loan facilities with contractually defined amortization terms. Project finance debt is frequently structured differently, with the repayment profile itself derived from projected cash flows rather than fixed in advance; see Debt Sculpting Mechanics for that distinct, more constrained case.
Why It Matters¶
The debt schedule is one of the three supporting schedules — alongside working capital and capex/depreciation — most commonly responsible for breaking three-statement integration when it is not fully connected to all three statements. It is also the schedule most likely to introduce a genuine circular reference into a model, since interest expense and available cash are mutually dependent within the same period. Understanding both the standard roll-forward mechanics and the circularity they can create is essential to building — and auditing — a debt schedule that behaves correctly.
Technical Background¶
The Debt Roll-Forward¶
Opening Debt Balance
+ Drawdowns
- Scheduled (Mandatory) Repayment
- Optional (Voluntary/Cash Sweep) Repayment
= Closing Debt Balance
Interest Expense = Debt Balance × Interest Rate
Each tranche of debt is rolled forward separately, since different tranches typically carry different interest rates, repayment terms, and seniority. Scheduled repayment follows a fixed amortization profile set out in the loan agreement, due regardless of the borrower's cash position in the period. Optional repayment, often structured as a cash sweep, applies surplus cash beyond scheduled obligations to accelerate debt paydown, which reduces future interest expense but also reduces cash available for other uses.
The Revolver as a Balancing Plug¶
A revolving credit facility is frequently designated as the model's balancing mechanic: in a period where the rest of the model shows a cash shortfall, the revolver is drawn to cover it; in a period showing a surplus, the revolver is repaid first before any other use of cash. This connects the debt schedule directly to the balance sheet's overall funding position each period, functioning analogously to the cash-balancing mechanic described on the Balance Sheet glossary page, but through debt rather than a cash balance. As with any balancing mechanic, this is legitimate and standard when disclosed and intentional; it becomes a structural concern only when it is used to mask an error elsewhere rather than to reflect a genuine, disclosed financing structure.
Interest Expense and the Circularity Problem¶
Interest expense can be calculated on the opening balance, the closing balance, or the average of the two. Calculating on the average balance is generally considered more precise, since it better reflects interest accruing on debt that is drawn or repaid partway through the period — but it introduces a genuine circular reference: the closing balance depends on the period's cash flow, which depends on interest expense (a cash outflow), which depends on the average of the opening and closing balances, which depends on the closing balance itself.
Interest Expense → Cash Available → Repayment/Drawdown → Closing Balance → Average Balance → Interest Expense
This circularity is a structural consequence of accurately modelling the genuine financial simultaneity of interest and cash availability, not necessarily a modelling error — but it must be deliberately and visibly controlled, either by calculating interest on the opening balance only (removing the circularity at the cost of a small approximation) or by enabling iterative calculation with an explicit circuit breaker. See Circularity in Debt Models and Circular Reference for the full treatment of resolution techniques and how to distinguish a controlled, intentional circularity from an uncontrolled structural error.
Connection to Debt Service and Coverage Metrics¶
In leveraged and project contexts, the debt schedule's interest expense and scheduled repayment together make up debt service, the denominator of coverage ratios such as DSCR. Even in a purely corporate context without formal covenant testing, tracking total debt service separately from the debt balance itself is useful for assessing repayment capacity against projected cash flow.
Common Errors¶
| Error | Description | Risk |
|---|---|---|
| Interest expense not linked to the debt schedule | Interest hardcoded or estimated independently of the actual debt balance | Income statement disconnected from the model's actual financing structure |
| Uncontrolled circularity | Average-balance interest calculation introduces a circular reference with no circuit breaker or documented resolution | Model is calculation-unstable or produces unreliable results depending on iteration settings |
| Revolver balancing mechanic undisclosed | Revolver draws and repayments used to force the balance sheet to tie out without being clearly presented as the financing structure | Reviewer cannot distinguish a legitimate financing mechanic from a plug |
| Scheduled and optional repayment conflated | Cash sweep repayment not distinguished from mandatory amortization | Repayment capacity and covenant headroom cannot be properly assessed |
| Closing balance not linked to the balance sheet | Debt schedule's closing balance not the same figure appearing as debt on the balance sheet | Statement integration fails |
Best Practices¶
Roll each debt tranche forward separately with its own interest rate and repayment terms, rather than aggregating all debt into a single blended balance. Disclose the interest calculation basis (opening, closing, or average balance) explicitly, and if using an average-balance calculation, document and control the resulting circularity deliberately rather than allowing Excel's default iterative settings to resolve it silently. Present any revolver balancing mechanic as a clearly labelled, intentional feature of the financing structure, not as an unexplained plug.
Continue Reading¶
Related Pillars¶
Related Glossary¶
Related Technical Guides¶
- How to Build a Debt Schedule
- Debt Sculpting Mechanics in Project Finance Models — the project-finance-specific case, not covered here
- Circularity in Debt Models
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
What is a debt schedule?
The section of a financial model that tracks each debt tranche's periodic movement — opening balance, drawdowns, scheduled and optional repayments, interest expense, and closing balance — connecting the balance sheet's debt balance to the income statement's interest expense.
What is the difference between scheduled and optional repayment?
Scheduled (or mandatory) repayment is a fixed amortization amount specified in the loan agreement, due regardless of the borrower's cash position. Optional (or voluntary) repayment, often structured as a cash sweep, uses surplus cash to pay down debt faster than the mandatory schedule requires, typically at the borrower's discretion or as a covenant-driven mechanism.
What is a revolver used for in a debt schedule?
A revolving credit facility is commonly used as the model's balancing mechanic — drawn upon when the rest of the model shows a period cash shortfall, and repaid when it shows a surplus — connecting the debt schedule directly to the balance sheet's overall funding position in each period.
Why does calculating interest on the average debt balance create a circular reference?
Because the closing balance depends on the period's cash flow, which itself depends on interest expense (a cash outflow); if interest is calculated on the average of opening and closing balances, the formula depends on a figure (closing balance) that depends on itself through interest expense.
How is the interest circularity typically resolved?
Either by calculating interest on the opening balance only (which removes the circularity but is a slight approximation), or by using Excel's iterative calculation setting together with a circuit breaker to make the circularity intentional and controlled rather than an uncontrolled error, described on Circularity in Debt Models.
How is this different from debt sculpting in project finance?
A generic corporate debt schedule typically follows a fixed amortization schedule set out in the loan agreement. Project finance debt is frequently instead sculpted — the repayment profile is derived from projected cash flows to maintain a target DSCR — described on Debt Sculpting Mechanics, which is a distinct, more constrained mechanic than the generic case covered here.
What does the debt schedule connect to elsewhere in the model?
The closing debt balance feeds the balance sheet's debt line; interest expense feeds the income statement; and drawdowns and repayments feed the cash flow statement's financing section, described in full on Statement Linking Mechanics.
Related Articles
Financial Statements in Financial Modelling
The income statement, balance sheet, and cash flow statement are the three financial statements that together describe a company's or project's performance, financial position, and cash movements. In a financial model, these are not three independent outputs — they are dynamically linked, so that a single change in an assumption flows correctly through all three, and the balance sheet balances in every period as a direct consequence of that linkage rather than as a plug engineered to force it. This page is the hub for the Knowledge Centre's financial statements content: what each statement represents, how a three-statement model integrates them, where financial-statement mechanics anchor broader industry models, and how a structural audit tests statement integration for the errors that most commonly break it.
How to Build a Debt Schedule
Building a debt schedule correctly means rolling each debt tranche forward from an opening balance through drawdowns and repayments to a closing balance, calculating interest expense on a consistent and disclosed basis, and connecting the result to all three financial statements. This guide walks through the build step by step: listing the tranches, the roll-forward mechanics, distinguishing mandatory amortization from optional cash-sweep repayment, using a revolving facility as the model's balancing mechanic, and the interest circularity that average-balance calculations introduce, along with the two standard techniques for resolving it.
Debt Sculpting Mechanics in Project Finance Models
Debt sculpting is a technique used in project finance financial models to derive the periodic debt repayment schedule from the projected cash flows available for debt service, rather than from a fixed amortisation schedule. The repayment in each period is sized such that the debt service coverage ratio (DSCR) in that period equals a defined target, or such that a defined proportion of available cash flow is applied to debt service. Sculpting shapes the repayment profile to match the project's cash flow profile, front-loading repayment in high-cash-flow periods and reducing repayment in lower-cash-flow periods, which increases the project's ability to service debt throughout the loan life.
Debt Service
Debt service is the total periodic payment obligation on a loan facility, comprising interest payable in the period and scheduled principal repayment due in the period. In project finance, debt service is the denominator of the debt service coverage ratio (DSCR). The DSCR measures the ratio of cash available for debt service (CADS) to total debt service, and must exceed the minimum threshold specified in the loan agreement throughout the loan life. Debt service is applied at a defined step in the cash waterfall, after operating costs and before reserve contributions and equity distributions.
DSCR (Debt Service Coverage Ratio)
The Debt Service Coverage Ratio (DSCR) is the primary metric lenders use to assess a project's ability to service its debt from operating cash flow in a given period. It is calculated as cash available for debt service (CADS) divided by total debt service (interest plus scheduled principal) due in that period. A DSCR of 1.00x means the project generates exactly enough cash to cover its debt obligations for the period; lenders typically require a minimum DSCR above 1.00x, specified in the loan agreement, to provide a buffer against downside performance. DSCR is one of the most frequently independently recalculated figures in a project finance model audit, given its direct link to covenant compliance.
Circular Reference
A circular reference occurs when a formula in a financial model depends, directly or through a chain of intermediate cells, on its own value. Excel flags circular references by default and returns zero in the affected cells unless iterative calculation is enabled. In financial models, circular references arise most often in interest-on-debt calculations, cash sweep mechanics, and tax shield computations — some are structural errors, others reflect genuine simultaneous financial relationships. The distinction between the two, and how each is handled, is addressed in full on the dedicated technical guide linked below.
Circularity in Debt Models
Circularity in debt models arises from the interdependence of interest expense and cash availability in the same period. In a project finance model, interest is charged on the drawn debt balance; the interest payment reduces available cash; available cash determines the repayment amount; the repayment amount determines the closing debt balance; and the closing balance determines the next period's interest charge. When a model calculates interest on the average of opening and closing balances, or when a cash sweep mechanism uses the same period's interest cost in determining sweep amounts, a circular dependency is introduced. The two principal resolution techniques are: calculating interest on the opening balance rather than the average balance, and using a defined debt repayment algorithm that determines the repayment amount without reference to the closing interest charge.