Debt modeling: build a borrowing and repayment schedule

Debt modeling turns a company's borrowing records into a schedule of opening debt, new draws, principal repayments, interest and closing debt. The schedule must explain both what the company owes and what cash it will pay, using the terms of each facility.

Start with the borrowing records

Use the latest lender statements, the debt ledger and the executed repayment terms. Keep one row per facility and month, with entity, currency, opening principal, draw date, principal payment date, annual rate, reset date, maturity and any fees. A facility identifier prevents two loans with the same lender from being combined accidentally.

Separate contractual inputs from planning assumptions. An executed repayment date is different from a proposed draw, and a current floating rate is different from the assumed rate after its next reset. Treasury owns the contract inputs; FP&A owns their use in the plan; the controller checks the recorded balance.

Payment structures differ. For example, the SBA describes most 7(a) term loans as repaid monthly through principal and interest, and notes that variable-rate payments can change when rates change. That supports checking the actual payment structure rather than assuming every borrowing has a constant payment.

For the balance definition before scheduling movements, see financial debt. Keep the resulting payment dates aligned with the company's cash flow forecast.

State the balance and interest rules

Closing principal = Opening principal + Draws − Principal repaid

Cash debt payment = Principal repaid + Cash interest paid

This simple schedule has no capitalized interest, foreign exchange movements, fees or noncash principal changes. Add those as separate, signed lines when they exist; never bury them in draws.

For interest, this example assumes each modeled month is a contractual 30-day period on a 360-day basis. Draws occur at the start of the period, principal is repaid at the end, and interest is paid in the same period:

Interest = (Opening principal + Start-of-period draws) × Annual rate × 30 / 360

The fraction equals one-twelfth. It is an explicit illustration, not a convention to apply to every contract. A midmonth draw needs its own time weighting, and accrued interest paid later needs a separate interest-payable roll-forward.

Work the monthly schedule

An illustrative US distributor starts January 2026 owing $1,200,000. It draws $300,000 at the start of February, repays $100,000 at each month end and has an assumed rate reset in March.

January interest is 1,200,000 × 6% / 12 = $6,000. February uses 1,100,000 + 300,000 = $1,400,000 for the full period, producing $7,000. March uses 1,300,000 × 9% / 12 = $9,750. All amounts are exact whole dollars under these assumptions.

The quarter ends with the same principal it started with, but it still requires $322,750 of payments. Gross draws and repayments remain visible: netting them would hide the payment calendar.

Reproduce it in Excel

Place the table's eight columns in A:H, January in row 2 and March in row 4. Enter the assumptions in B:E, then use:

F2 =(B2+C2)*E2/12
G2 =B2+C2-D2
H2 =D2+F2
B3 =G2

Fill F:H down and link each later opening balance to the preceding closing balance. Sum only the movement columns for the quarter; adding the three opening balances does not produce a useful debt balance.

For several facilities, keep a detail sheet named Detail with month in A and cash payment in H. If K2 contains the month value, aggregate matching rows with:

=SUMIFS(Detail!$H$2:$H$500,Detail!$A$2:$A$500,K2)

Microsoft documents SUMIFS as summing values that meet specified criteria. Use real, consistently stored dates or matching period labels in both places. Covirage is not affiliated with Microsoft.

Prove the schedule before using it

The quarter's balance identity is 1,200,000 + 300,000 − 300,000 = $1,200,000. The payment identity is 300,000 + 22,750 = $322,750. In the spreadsheet, both residuals must be zero:

=B2+SUM(C2:C4)-SUM(D2:D4)-G4
=SUM(H2:H4)-SUM(D2:D4)-SUM(F2:F4)

For actual periods, tie closing principal to lender statements and the debt ledger, then tie payments to the bank record. Explain accrued interest, fees and posting delays rather than forcing them into principal. The separate debt service formula defines the payment measure used here.

Stress one input at a time. If only March's assumed annual rate rises from 9% to 12%, interest becomes 1,300,000 × 12% / 12 = $13,000. That adds $3,250 to March and quarter payments, taking the quarter to $326,000; closing principal stays $1,200,000.

Where it goes wrong

• Wrong movement dates. Charging a full month's interest on an end-of-month draw overstates interest.
• Expense confused with payment. Accrued interest and cash interest can fall in different periods.
• Missing maturity. Continuing small installments beyond the contractual maturity hides a balloon payment.
• Unavailable draws assumed certain. A planning draw should retain its availability assumptions and approval status.
• Different debt bases. Principal, accounting carrying value and covenant debt may differ; reconcile them explicitly.

Check your own borrowing schedule in Covirage

Upload your supplied debt workbook and a sample of your own lender or ledger records: Covirage maps the columns, and its tools compute and check the balance movements, interest assumptions and payment totals. The external AI model explains the results with every figure traced to its rows; create a free Covirage account, which takes about a minute, then set up your business and bring your file.

See Covirage for finance teams for the supporting reporting context.