How to Build a Debt Schedule in Excel: Revolver, Term Loan, Bonds

A debt schedule is the spreadsheet engine that tracks every dollar you borrow, what it costs, when you pay it back, and how those cash flows move through the business. The core building blocks are a revolving credit facility that plugs liquidity, an amortizing term loan, and bonds with coupons and call rules. Build it right, and you know your cash needs before the month starts; build it sloppy, and you will be explaining surprises.

Think of the schedule as a modeling module that mirrors legal terms where they matter. It is not a credit agreement, and it will not replicate every defined term or tax nuance, but it must reflect the borrower’s legal reality wherever it is material and tie out to the financial statements cleanly.

What a Debt Schedule Must Deliver

To be decision useful, the module has to show the full picture and reconcile to the statements. Aim for auditability, lender confidence, and zero reconciliation headaches.

Balances by instrument: Beginning and ending balances, with carry values if issuance costs or OID apply.
Interest and fees: Cash and non-cash by period with clear drivers and day count conventions.
Principal movements: Mandatory and voluntary activity with ordering that matches documentation.
Tie-outs: Balance sheet for gross debt and carrying value, income statement for interest expense, and cash flow statement for financing flows.

Choose the Right Time Basis and Inputs

Pick the time basis first. Monthly columns are standard because revolvers and risk free rate accruals demand that granularity. Annual columns are too coarse for working capital swings that drive daily drawdowns and repayments.

Group assumptions by instrument so inputs stay traceable:

Revolver: Commitment and maturity, base rate and margin, undrawn fees, LC usage and fees, minimum cash policy and sweep rules, and borrowing base if asset based.
Term loan: Initial funded amount and delayed draws, base rate and floor, amortization, excess cash flow sweeps, OID and upfront fees, and prepayment economics.
Bonds: Face amount, coupon or base plus margin, pay frequency, call schedule or make whole, sinking fund, OID and issuance costs.
Cross instrument: Hedges and settlement approach, transaction fees and costs, day count, opening balances and accrued interest, and refinancing toggles.

Set the operational context where relevant. For example, link to your working capital drivers so revolver usage reflects operating cash needs rather than arbitrary plugs.

SOFR Rate Conventions and Practical Shortcuts

USD loans now reference SOFR. Term SOFR sets in advance while compounded SOFR accrues in arrears. Lookbacks, observation shifts, and day counts in the document determine timing and cash interest. Mistakes here compound over time.

Approximation choice: If the deal uses compounded SOFR but you model monthly and sensitivity is low, you can approximate with Term SOFR or the SOFR Index and flag it.
Apply floors correctly: Floors sit on the base rate, not the all-in rate. A 0.50% floor plus 3.75% margin yields 4.25%.
Margin grids: Step downs move on reset dates based on leverage using the agreement’s EBITDA and net debt definitions.

If your case requires a deeper dive on floors and step downs, see a focused primer on SOFR floors.

Workbook Architecture That Scales

Keep a repeatable structure so the model is easy to audit under time pressure.

Assumptions: All static terms and toggles without formulas.
Rates: Base curves, margins, floors, and hedges as time series.
Debt schedule: Uniform columns per instrument, including BoP, amortization, draws, prepay, EoP, interest, and fees.
Cash bridge: Unlevered cash, minimum cash, revolver draw or repay, other financing, and ending cash.
Covenants: Leverage, fixed charge, and interest coverage with testing dates and any springing tests.
Outputs: Tie out checks and a dashboard.

Set flags for accrual and pay timing. Loans may accrue monthly and pay quarterly, while bonds pay semiannually. Use a month index to map coupon dates and avoid brittle date math.

Revolver Mechanics: Model the Liquidity Plug

The revolver supports day-to-day liquidity. Model availability and cash flow interaction step by step so the plug behaves in stress.

Availability and fees

Committed availability: Commitment minus LC usage.
Borrowing base: If ABL, calculate advance rates times eligible AR and inventory minus reserves and ineligibles.
Net availability: The lower of committed availability and borrowing base, then subtract the drawn balance.
Cash interest: Average drawn times period rate using Actual/360.
Commitment fee: Period fee on the undrawn, adjusted for LC treatment.
LC fees: Risk based fee on usage plus fronting; treat as cash outflows.

Draw or repay logic

Cash before financing: Beginning cash plus CFO minus CFI excluding financing minus the minimum cash target.
Draw: The positive shortfall, limited by net availability.
Repay: The positive excess cash, limited by the prior balance.
Ending cash: Equals the minimum cash target if capacity allows; otherwise flag a shortfall.

Interest circularity appears when interest affects cash and cash sets the draw. Three workable options are using beginning balances, averaging BoP and EoP with iteration, or solving algebraically if interest on the plug is the only loop.

Term Loan Mechanics: Predictable but Detailed

Term loans accrue interest and amortize on a fixed schedule, with optional prepayments and ECF sweeps if included in your case.

Rate stack: Term or compounded SOFR, margin, and a base rate floor with step downs tied to leverage.
Amortization: For example, 1% per year, paid quarterly.
Prepayments: Mandatory from asset sales or ECF; voluntary at period end as toggled.
OID and costs: Record at issuance and amortize under the effective interest method to produce non-cash interest.

Use this flow: BoP principal to scheduled amortization to voluntary and mandatory prepay to EoP principal. Accrue interest on the average principal and pay on schedule. Delayed draw tranches carry undrawn fees until funded.

Bond Mechanics: Fewer Levers, Stronger Rules

Bonds are simpler to model but have tight payment and call rules you cannot ignore.

Fixed rate bonds: Coupon on face, semiannual pay, Actual/Actual day count.
Floating notes: Base plus margin, reset on pay dates.
Call protection: Non-call periods and step premiums or make whole at a spread to Treasuries.
Accounting: OID and issuance costs amortize via EIR; unamortized costs flow to P&L on extinguishment.

Map coupon months with flags. Accrue monthly and pay on coupon dates. On a refinance, pay the call price on outstanding face and release unamortized costs per policy.

Model Fees, OID, and Issuance Costs Without Errors

Separate economics from accounting so yield and presentation stay correct.

OID: Lowers proceeds and raises yield; accretes non-cash interest over time.
Lender fees: Typically deferred and amortized via EIR under US GAAP and IFRS.
Commitment and ticking: Period costs that you classify within operating or financing cash flows per policy.
Presentation: Net issuance costs against the liability and amortize; allocate shared costs across instruments by proceeds.

For a quick illustration, a 7 year TLB at Term SOFR plus 3.75% with a 0.50% floor and 2% OID produces an effective interest rate above the cash coupon, so reported interest expense exceeds cash interest as carrying value accretes toward par.

Accounting, Reporting, and Tax Interactions

Your model should support correct classification, disclosures, and tax effects.

US GAAP and IFRS: Apply modification or extinguishment guidance and recompute EIR when terms change.
Accruals: Accrued interest sits in current liabilities until paid; capitalize interest only for qualifying assets.
Disclosures: Rate risk, maturity ladder, covenant status, fair value, and unamortized OID and costs by instrument.
Tax: Model IRC 163(j) interest limits, any cross border withholding, and taxable effects of exchanges or repurchases.

Documentation Map You Can Trace

Trace every modeling rule to a document source so reviews are fast and defensible.

Credit agreement: Definitions, interest mechanics, amortization, waterfalls, triggers, grids, and covenants.
Fee letter: Economics like OID and ticking fees.
Indenture: Coupon terms, call schedules, and trustee rules.
Intercreditor: Priority among revolver, term loan, and bonds, including ABL split collateral.
ISDA: Hedge settlements and collateral requirements.

Payment Priority and Flow of Funds

Within each period, follow the waterfall that lenders expect and your auditors can replicate.

Order of operations: Accrue interest and fees, compute unlevered cash, repay the revolver to minimum cash, pay term loan amortization, pay loan interest and bond coupons, apply mandatory prepayments, then draw the revolver if cash is short.
Instrument roles: Revolvers support daily liquidity, term loans amortize structurally, and bonds usually sit last for voluntary prepays due to call premiums.

Refinancings and Changes to Plan For

Build toggles so you can run scenarios quickly and document outcomes clearly.

Repricings: Margin changes without maturity changes; if a modification, recompute EIR prospectively.
Exchanges: Debt for debt with call or make whole economics, gain or loss on extinguishment, and a new instrument with fresh OID and fees.
Add-ons: Combine if fungible or track as separate tranches for clarity.

For background on call protection and prepayment costs, review this overview of call protection and OID.

ABL Borrowing Base Specifics

If your revolver is asset based, the borrowing base drives real availability. Model it explicitly and stress the inputs.

Formula: Eligible AR times advance plus eligible inventory times advance minus reserves, with ineligibles such as aged AR, concentrations, uninsured foreign AR, and WIP inventory.
Seasonality: Step changes in eligibility can produce availability cliffs.
LC interaction: LC usage reduces availability dollar for dollar.

Covenant Testing Without Surprises

Implement tests exactly as defined so lender reporting is plug and play.

Leverage: Total and first lien using document definitions and permitted cash netting.
Coverage: Interest or fixed charge coverage including fees when included by definition.
Springing tests: Maintenance tests triggered by revolver utilization; map the usage trigger and test logic.

Excel Build Sequence That Works

Build once, then reuse. A clean sequence reduces rework and improves accuracy.

Time axis and flags: Monthly columns, quarter end and coupon flags.
Rates: Base curves, floors, and margin grids with lookups.
Assumptions: Terms, fees, OID, and costs with no hardcoding in calcs.
Revolver: Availability, draw or repay logic, interest and fees, and circularity handling.
Term loan: Amortization, interest accrual and pay, OID amortization, and sweeps.
Bonds: Coupon mapping, accrual and pay, call schedule, and costs amortization.
Cash bridge: Start from unlevered cash, apply debt service and revolver mechanics, derive ending cash.
Tie-outs: Balance sheet carrying values, income statement interest, and cash flow financing lines.
Covenants: Calculate and flag with definitions visible.
QA checks: No negative undrawn, no negative balances, minimum cash logic works, OID does not accrete above original, financing flows reconcile.

If you are building the full three statement model around it, see how to build a three-statement model and avoid three-statement models pitfalls. A hands-on three-statement Excel model can help you test the full integration.

Common Pitfalls and Quick Tests

Small errors produce big headaches. Use these checks as part of your QA routine.

Timing errors: Do not use EoP balances for interest without adjustments, and map accrual month versus pay month correctly.
Day counts: Loans often use Actual/360; bonds often use Actual/Actual.
Rate floors: Apply to the base rate, not the all-in rate.
Availability math: Include LC usage in commitment fees and availability.
Minimum cash: Do not overpay the revolver and miss the policy.
OID vs costs: Track separately and accrue under EIR.
Iteration risk: Do not depend on iteration without a convergence test.

Run quick tests: hold balance and rate fixed and hand calculate accrual; cut the revolver or ABL base mid year to confirm no negative undrawn; and confirm cash interest plus OID or fee amortization equals the effective interest implied by carrying value and cash flows.

Edge Cases and Extensions

Advanced cases are easy if your structure is clean. Add toggles and flags to extend capability without breaking the core.

PIK toggles: When on, add PIK interest to principal and reduce cash interest.
Delayed draws: Track undrawn commitments and ticking fees until funded.
Multi currency: Separate curves and day counts, translate with FX, and respect sub limits.
Cash traps: Covenant holidays or springing sweeps via flags.
Mod accounting: Recompute EIR when terms change under ASC 470 50 or IFRS 9.

If you want a broader context on where debt scheduling sits in the modeling ecosystem, see this overview of debt scheduling in financial modeling.

Testing, Validation, and Decision Ready Outputs

Finish with outputs that inform financing decisions and lender conversations.

Liquidity headroom: Ending cash plus undrawn availability minus blocked amounts, tracked monthly.
Interest burden: Next 12 months cash interest by instrument and total, including fees.
Rate sensitivity: Plus or minus 100 bps on cash interest and liquidity, and margin step downs by the next reset date.
Refinancing map: Next 24 month maturities and call costs by month.
ECF exposure: Estimated ECF sweep based on your forecast and definition sensitivities.

Governance and Retention

Treat the schedule like the control system it is. Keep terms visible, assumptions isolated, and formulas short enough to audit. Archive versions and inputs, log Q&A and user access, hash archives, set retention, and ensure vendor deletion with a destruction certificate. Remember that legal holds override deletion.

A final rule of thumb: model the borrower’s documents, not your preferences. Note any assumption not in the paper and why it is reasonable. If your sheet can be read by a first year at 1 a.m. and produces the same answer every time, you have built a useful tool.

Conclusion

A robust debt schedule turns complex lending terms into clear cash planning and covenant visibility. Build on a monthly time base, respect rate conventions, and enforce clean mechanics on revolvers, term loans, and bonds. Then validate with tight tie outs and guardrails. Done well, it protects liquidity, reduces surprises, and keeps your team focused on compounding value rather than chasing modeling ghosts.