Business Debt Schedules (Financial Modeling)

A debt schedule tracks each loan’s opening balance, new borrowing, required and optional repayments, interest, and closing balance over time. Build one schedule for each facility, use its contract terms, and link the results to the balance sheet, income statement, and cash flow statement. Then test repayment rules, cash needs, and key assumptions.

A debt schedule turns loan terms into forecast figures that a financial model can use. It also makes errors easier to spot: a balance may fail to roll forward, interest may use the wrong base, or a cash sweep may repay more than the business owes.

I use a simple rule when building one: let the loan documents and the model’s stated accounting basis guide the assumptions. There is no spreadsheet layout that replaces those sources. The steps below show how to set up, link, and check a schedule.

Debt schedule purpose, inputs, and structure

A debt schedule shows how each borrowing changes over a forecast period. It brings contract terms into a clear set of calculations, so you can see debt balances, interest costs, and repayment needs. Before you build it, set the model’s purpose, time frame, reporting basis, and source dates.

Start with the latest loan agreements, amendments, and any case instructions. Record when each source was issued, because an older document may not show current terms. For each facility, capture the opening balance, available borrowing, interest rate, maturity, repayment rules, and any fees or covenants that affect the model.

Keep facilities separate when their rates, repayment order, or maturity dates differ. A revolving credit line and a term loan should not be combined into one balance just to make the model shorter. A blended rate can hide real costs and make the repayment forecast inaccurate.

Preparation checklist

• Confirm the forecast periods, such as months, quarters, or years.
• Confirm whether reporting follows U.S. GAAP, IFRS, or the basis specified by the assignment.
• List each debt instrument and its source documents.
• Note assumptions that are not stated in the documents.
• Mark any missing or uncertain terms for review rather than guessing.

Debt roll-forward, repayments, and interest

A roll-forward explains how a balance moves from one period to the next. For each loan, show beginning debt, new draws, required principal payments, optional repayments, and ending debt. Use the same structure for each period, then carry the ending balance into the next period as its opening balance.

How do you calculate the debt balance?

The core formula is:

Ending Debt = Beginning Debt + Draws − Mandatory Repayments − Optional Repayments

“Mandatory repayment” means principal the borrower must pay under the loan terms. An “optional repayment” is an extra payment, such as a cash sweep, when allowed. In the next period, beginning debt must equal the prior period’s ending debt.

For example, assume a term loan begins the year at $1,000,000. The company draws $200,000, pays $100,000 of required principal, and makes a $50,000 optional payment. Ending debt is $1,050,000. These figures are illustrative, not a market benchmark.

Keep repayment amounts within the limits in the loan terms. A model should not show an optional payment that drives the loan below zero. If the balance is fully repaid, cap the payment at the balance due and flag any excess cash separately.

How should a debt schedule calculate interest?

Interest expense is the cost of borrowing for a period. Its calculation depends on the interest rate, the balance used, timing, day-count method, and payment terms. State these assumptions in the model. Do not assume that one method applies to every loan.

A basic calculation is:

Interest Expense = Interest-Bearing Balance × Annual Interest Rate × Period Fraction

For an 8% annual rate on a $1,000,000 balance over a full year, simple interest would be $80,000. A partial year needs a period fraction. For example, a quarter may use 0.25 if the model assumes four equal quarters, but the loan’s day-count terms may require a different fraction.

Common balance choices include beginning balance, average balance, or daily balances. Beginning balance is simple, but it may not reflect borrowing and payments during the period. An average-balance method can better reflect mid-period changes, but it is still an estimate unless the model uses more detailed timing. Explain which method you use and why.

Also identify whether the rate is fixed or has components, such as a reference rate plus a spread. Record rate reset timing, payment dates, and how the model treats accrued interest and fees. Follow the loan documents and the stated accounting basis for fee treatment; do not add fees to interest by default.

Linking debt to the three financial statements

The debt schedule is useful only if its outputs flow to the right places in the model. The balance sheet reports debt balances, the income statement reports interest expense, and the cash flow statement reports cash borrowing and repayment. The schedule should support a clear reconciliation among all three.

Link ending debt by instrument to the relevant balance sheet line. Link interest expense to the income statement. Link draws and principal repayments to financing cash flows. Keep interest cash payments distinct from principal payments when the model requires that distinction.

Check these links each period:

• Balance sheet debt matches the debt schedule’s ending balance.
• Cash flow borrowing and principal repayment match the schedule’s draws and repayments.
• Income statement interest expense matches the schedule’s interest calculation.
• Cash and debt changes agree with the model’s cash flow logic.

Accounting treatment can vary with the reporting basis and transaction details. The schedule calculates forecast amounts; it does not decide how every item must be presented in financial statements. Document any treatment required by the assignment, accounting policy, or qualified reviewer.

Worked example and scenario tests

A worked example makes repayment logic visible. The figures below are invented for teaching, not data from a company. The term loan and credit line remain separate because they have different repayment rules. Testing them under different cash conditions can reveal a funding gap or an incorrect repayment assumption.

For each row, apply the roll-forward formula. Do not merge the two balances or apply one rate to both unless the underlying terms support that treatment. For interest, calculate each facility using its own rate and chosen balance convention.

Build at least a base case and a downside case. The base case uses the central assumptions in the model. A downside case might use lower cash generation or a higher rate, but its inputs should be tied to the model’s purpose rather than chosen as universal standards.

Check how each case affects:

• Ending debt and available borrowing capacity.
• Cash interest expense and principal due.
• Maturities within and beyond the forecast period.
• Covenant measures specified in the loan documents.
• Cash available for optional repayment after required uses.

A covenant is a condition the borrower must meet under its agreement. Use the agreement’s exact definition and test timing. Do not assume that a common ratio or threshold applies to every loan. If the terms are unclear, flag the issue for review.

Spreadsheet controls and common errors

A model control is a formula or review step that helps reveal an error. Controls do not prove that the assumptions are correct, but they can show when the schedule fails to balance or breaks a stated rule. Use both automated checks and a review of source documents.

Add checks for negative ending debt, mismatched opening and prior closing balances, and differences between the schedule and financial statements. Add alerts for covenant breaches when the model has enough information to calculate them. State what each check tests so another user can understand it.

Circularity can arise when interest depends on an average debt balance, while optional repayments depend on cash after interest. The repayment changes the balance, which changes interest, which changes cash available for repayment. Define the repayment order and interest method before enabling spreadsheet iteration. If iteration is used, document the setting and independently verify the outputs. A formula that calculates is not automatically a correct formula.

Review sequence

1. Compare each input with the applicable loan document or assignment instruction.
2. Confirm formulas are consistent across periods.
3. Trace debt balances to the balance sheet and cash flows.
4. Review interest rates, timing, and day-count assumptions.
5. Test downside and refinancing cases, and explain any exceptions.

Applying the schedule in finance work

A debt schedule is a practical modeling skill for work involving company forecasts, lending analysis, transaction models, and financial planning. It helps an analyst explain how borrowing needs change over time, what cash interest may cost, and when principal repayments come due. The schedule alone does not determine whether a business can borrow or repay.

In finance roles, a reviewer may ask where an assumption came from, how a repayment was calculated, or what happens if a rate changes. Clear source notes, separate facilities, and visible checks make those questions easier to answer. Practice with a model that links debt to all three statements, then explain the logic without relying on spreadsheet labels alone.

A useful next step is to rebuild a small schedule from a sample set of loan terms. Add one required repayment, one optional payment, and a rate change. Then check that the statements update as expected.

Frequently asked questions

These answers cover common modeling choices for debt schedules. Contract terms and assignment instructions take priority over general examples. Where a loan document does not give enough detail, note the gap and seek clarification instead of treating an assumption as a confirmed fact.

What is a debt schedule in a financial model?
It forecasts each debt facility’s opening balance, draws, repayments, interest, and closing balance by period. It links borrowing activity to the model’s financial statements.

What is the debt roll-forward formula?
Ending debt equals beginning debt plus draws, minus required principal repayments and optional repayments. The next period’s opening balance equals the prior period’s closing balance.

Should each loan have a separate schedule?
Use separate schedules when facilities have different rates, maturity dates, repayment rules, or priorities. Combining them can conceal differences that affect interest and cash needs.

Should interest use beginning or average debt?
Use the convention required by the loan terms or modeling instructions. State the method. Beginning-balance interest is simpler; average or daily balances may better reflect changes during a period.

How do I model a cash sweep?
Define the cash available for repayment, the repayment order, and any contractual limits. Cap the optional payment at the outstanding eligible balance, and check for circular references.

How do I avoid negative debt balances?
Limit each repayment to the amount of debt eligible for repayment. Add a check that flags any negative ending balance rather than allowing it to pass unnoticed.

How does debt link to the three statements?
Ending balances link to the balance sheet, interest expense to the income statement, and draws and principal payments to financing cash flows. Reconcile the links by period.

What should I do if loan terms are missing?
Do not invent a term. Mark the assumption as unresolved, identify the source needed, and use a clearly labeled placeholder only if the assignment permits it.

How do I test a debt schedule?
Check roll-forwards, statement links, interest calculations, and payment limits. Then run relevant base and downside cases and review covenant and maturity exposure against the agreement.

(This article was written by one of our staff writers, James Holloway. Visit our Meet the Team page.)