Pro forma debt schedule: build one that works
A pro forma debt schedule projects every debt instrument’s opening balance, draws, repayments, interest expense, and ending balance across your forecast periods, then feeds those outputs directly to the income statement, cash flow statement, and balance sheet. You build it in Microsoft Excel or Google Sheets. The one-line plan: set a central dates table, add one instrument block per tranche, flag input cells, build roll-forwards, wire the cash sweep, then link outputs to the three statements.
Required outputs for each period:
- Opening balance
- Draws (revolver or delayed-draw term loan)
- Mandatory amortization
- Optional repayments / cash sweep
- Cash interest
- Book interest (including OID amortization and PIK capitalisation)
- Ending balance
Time to build: a simple two-tranche model takes 1–2 hours; a mid-complexity deal with a revolver, term loan, and mezzanine takes roughly half a day; a full LBO-level stack with covenants, PIK, and hedges can take several days.
Table of Contents
- What does a pro forma debt schedule do inside a three-statement model?
- What debt instruments should you include, and how complex does each get?
- How do you build a debt schedule in Excel, step by step?
- How do you link the debt schedule to the three financial statements?
- How do you model credit-agreement rules and covenant tests?
- Where can you find a ready-to-use Excel template?
- Key takeaways
- The part most modellers skip
- Your lender-ready financials, built faster with LenderReady
- Useful sources and further reading
What does a pro forma debt schedule do inside a three-statement model?
The debt schedule is the model’s cash-and-covenant engine. Every dollar of interest flows from this tab to the income statement. Every ending balance feeds the balance sheet’s debt lines, split between current and long-term portions. Principal movements and cash interest paid wire directly into the financing section of the cash flow statement, reconciling to the net change in debt. Without a properly built schedule, your three-statement model is just three disconnected spreadsheets.
Who uses the outputs, and why:
- CFO and treasury: monitor liquidity headroom and revolver availability period by period
- Audit: reconcile book interest (EIR amortization, PIK accruals) to the income statement
- Lenders and agent banks: verify covenant compliance, mandatory prepayment triggers, and cash sweep mechanics against the credit agreement
- Tax: separate cash interest from non-cash accruals for deductibility analysis
A well-built debt schedule does more than track balances. It automates cash routing and covenant math so the model’s outputs match exactly what agent banks will test during their own reviews. Treat it as a compliance document first and a forecast tool second.
The schedule must output both cash and book interest because different stakeholders use different measures. Cash interest drives covenant tests like interest coverage and fixed charge coverage. Book interest, which includes OID amortization and PIK capitalisation, is what hits the income statement under IFRS or ASPE. Conflating the two is one of the most common errors in analyst-level models, and lenders will catch it.
One clarification worth making: the model’s debt schedule is not the same document as the legal amortization schedule attached to your credit agreement. The legal schedule shows contractual payment dates and amounts. Your model schedule implements those rules in formula logic, adds the cash sweep, and projects forward under your operating assumptions. Pro forma statements are assumption-driven planning tools, not guarantees of future performance, and your lender knows that.
What debt instruments should you include, and how complex does each get?
The answer depends on your capital stack. A straightforward operating company might carry only a revolver and a term loan. A leveraged buyout or growth-stage deal can layer in bridge facilities, senior notes, mezzanine, PIK toggles, seller notes, and asset-based lending. A robust debt schedule must reflect the full stack, with each instrument modelled at the level of detail its complexity demands.
Common instruments to consider:
- Revolving credit facility (revolver)
- Term loan A (amortizing) and Term loan B (bullet with nominal amortization)
- Delayed-draw term loan
- Bridge loan
- Senior secured notes
- Mezzanine / subordinated debt
- PIK notes or PIK toggle
- Seller note
- Finance leases
- Letters of credit (drawn vs. undrawn)
- Asset-based loan (ABL) with borrowing base
- Interest rate hedges (swap notional affects effective rate)
| Instrument | Key inputs required | Typical covenant exposure | Special modelling flag |
|---|---|---|---|
| Revolver | Commitment, drawn balance, base rate, margin, undrawn fee | Springing maintenance test | Plug / circularity breaker |
| Term loan A | Funded amount, amortization schedule, base rate, margin | Leverage, coverage | Mandatory amortization table |
| Term loan B | Funded amount, 1% nominal amortization, bullet at maturity | Incurrence-based | Bullet flag at maturity |
| Mezzanine / PIK | Funded amount, cash coupon, PIK rate, toggle election | Cash pay vs. PIK election | PIK capitalisation each period |
| Seller note | Principal, rate, balloon or amortizing | Subordination agreement | Subordination flag |
| ABL | Borrowing base formula, advance rates | Springing fixed charge | Borrowing base roll-forward |
PIK instruments, amortizing schedules with step-downs, OID, hedges, and FX-denominated tranches all require special treatment. For a Canadian borrower with USD-denominated debt, you will need an FX remeasurement row in each period. When complexity is low, model at summary level with a single rate and a flat amortization schedule. When the credit agreement has call premiums, step-up margins, or a borrowing base, model at instrument level with full input flags.
How do you build a debt schedule in Excel, step by step?
Step 1: Set up a central dates table
Start with a single row of period-end dates driven by one start date and one period-length input. Every instrument block references this row. A single timeline eliminates the most common source of circularity: mismatched period references across tabs. Add a binary flag row for each payment frequency (monthly, quarterly, semi-annual, annual) using a formula like =IF(MOD(MONTH(date),3)=0,1,0) for quarterly periods.
Step 2: Build one instrument block per tranche
Each block captures these inputs as named, flagged cells:
- Commitment and funded amount at close
- Base index (e.g., CORRA for Canadian-dollar facilities, SOFR for USD)
- Floor (e.g., 0.50%)
- Credit spread adjustment (CSA) if applicable
- Margin
- Amortization schedule (percentage of original principal per period)
- Payment frequency flag
- OID and issuance costs (amortized over the facility term using straight-line or EIR)
- Call premium schedule (year 1, year 2, etc.)
- PIK flag and PIK rate (if applicable)
- Hedge notional and fixed rate (if applicable)
Step 3: Build the rate calculator
Combine inputs into an effective periodic rate:
Effective rate = MAX(Base index + CSA, Floor) + Margin
For a quarterly model, divide by four. For a daily-accrual instrument, use an actual/365 or actual/360 day count depending on the credit agreement. Document the day-count convention in a comment cell. This is the kind of detail that separates an audit-ready model from one that produces unexplained rounding differences.
Step 4: Build the roll-forward
The roll-forward is the core of the schedule. Each period follows this sequence:
- Opening balance (= prior period ending balance)
- Plus: draws (revolver or delayed-draw)
- Less: mandatory amortization
- Less: optional repayment / cash sweep
- Plus: PIK capitalisation (if elected)
- Ending balance
Step 5: Calculate interest and handle circularity
Circularity arises because interest depends on the balance, which depends on the cash sweep, which depends on cash available after interest. Three practical approaches:
- Beginning balance only:
Cash interest = Opening balance × Periodic rate. No circularity, slight understatement in periods with large draws. - Average balance with iterative calculation:
Cash interest = ((Opening + Ending) / 2) × Periodic rate. Enable iterative calculations in Excel (File → Options → Formulas → Enable iterative calculation, max 100 iterations). Document this setting in the model spec. - Circularity breaker: add a toggle cell. When set to 1, the formula uses the beginning balance; when set to 0, it uses the average. This keeps the model stable when iterative calc is off.
For most analyst-level models, beginning-balance interest is the cleanest choice. Reserve average-balance for deals where the revolver swings significantly within a quarter.
Worked numeric example (quarterly, $10M term loan at 7.00% annual):
| Period | Opening ($M) | Amortization ($M) | Cash interest ($M) | Ending ($M) |
|---|---|---|---|---|
| Q1 | 7.00 | 1.75% | 1.75% | , |
| Q2 | , | 1.75% | , | , |
| Q3 | , | 1.75% | , | , |
| Q4 | , | 1.75% | , | , |
Amortization: 1% per quarter of $10M original principal. Interest: opening balance × 1.75% (7.00% ÷ 4).
Step 6: Wire the cash sweep
The cash sweep applies excess cash to debt repayment in the order specified by the credit agreement. The revolver acts as the only short-term plug: if cash before financing is short, the model draws the revolver; if excess cash exists, it repays the revolver first, then sweeps to term debt per the waterfall.
Key guardrails:
- Minimum cash balance (hard floor, never let cash go negative)
- Sweep percentage (often 50% of excess cash flow, stepping down after leverage targets are met)
- Waterfall order (revolver → TLA → TLB → mezz)
- A flag that prevents drawing and repaying the revolver in the same period
Pro Tip: Add a kill test row at the bottom of each instrument block: =IF(AND(draws>0, repayments>0), "ERROR: draw+repay same period", "OK"). This catches a common wiring mistake before it reaches a lender.
Step 7: Keep the sheet auditable
Use Excel’s LET function to define intermediate variables (effective rate, day count fraction, sweep amount) once and reference them cleanly. XLOOKUP works well for pulling amortization percentages from a schedule table. SUMIFS aggregates interest across instruments for the income statement link. Avoid volatile functions like INDIRECT or OFFSET in the main roll-forward rows. For automated reporting across multiple scenarios, named ranges and a central assumptions sheet make the model far easier to audit and hand off.
How do you link the debt schedule to the three financial statements?
The link points are specific and non-negotiable. Getting them wrong produces a balance sheet that does not balance, which is the fastest way to lose credibility with a lender or an auditor.
Income statement:
- Book interest expense (cash interest + OID amortization + PIK accrual) flows to the interest expense line
- Do not net against interest income unless your model explicitly separates the two
Cash flow statement (financing section):
- Cash interest paid (if using the indirect method, this may appear in operating activities under IFRS)
- Proceeds from new borrowings (draws)
- Repayment of principal (mandatory amortization + cash sweep)
- Debt issuance costs paid at close (financing outflow)
Balance sheet:
- Ending debt balances, split between current portion (next 12 months of mandatory amortization) and long-term
- Unamortized OID and issuance costs as a contra-liability (or asset, depending on your accounting standard)
- Accrued PIK interest as an addition to the principal balance
Reconciliation checklist for each period:
- Book interest on income statement = cash interest + OID amortization + PIK accrual
- Ending debt on balance sheet = sum of all instrument ending balances
- Current portion = next four quarters of mandatory amortization
- Net change in debt on cash flow = draws minus repayments (mandatory + sweep)
- Cash before financing feeds the sweep input correctly
Common mismatches and fixes:
- OID amortization omitted: book interest understated; fix by adding an EIR amortization row to each instrument block
- PIK not capitalised: ending balance understated and income statement overstated; fix by adding PIK to the roll-forward ending balance
- FX remeasurement missing: balance sheet debt does not match translated ending balance; add a remeasurement row using period-end FX rate
Control panel cells to expose:
- Net debt bridge (opening net debt → EBITDA → capex → interest → amortization → sweep → closing net debt)
- Liquidity bridge (opening cash → operating cash flow → capex → debt service → closing cash)
- Revolver availability (commitment minus drawn balance)
- Maturity ladder (bar or table showing principal due by year)
How do you model credit-agreement rules and covenant tests?
The model must codify the credit agreement’s exact definitions, not use convenient proxies. “EBITDA” in a credit agreement is rarely the same as EBITDA on your income statement. It typically includes pro forma add-backs, cost savings, and synergies up to a cap. Using unadjusted EBITDA will overstate covenant headroom and produce a model that diverges from what the agent bank calculates.
Credit-agreement items to map into the model:
- Base index and reset frequency (CORRA daily compounded, SOFR term, or fixed)
- Margin grid (if leverage-based, the margin steps down as leverage improves)
- Floor rate
- Amortization schedule (exact percentages and payment dates)
- Call premium schedule (non-call period, then step-down)
- Mandatory prepayment triggers (ECF sweep, asset sale proceeds, equity issuance proceeds)
- Sweep mechanics (ECF percentage, step-downs, permitted baskets)
- Fee amortization (commitment fee on undrawn revolver, agency fee)
- OID (amortized over the facility term)
- PIK elections (if the instrument has a toggle, model both cash-pay and PIK scenarios)
- Hedging rules (if the agreement requires a minimum hedge percentage, flag it)
Coding the covenant math:
- Net leverage:
Net debt / LTM EBITDA (credit-agreement definition). Net debt = total drawn debt minus unrestricted cash. - Interest coverage:
LTM EBITDA / LTM cash interest. Use cash interest, not book interest. - Fixed charge coverage (FCCR):
(LTM EBITDA minus capex minus taxes paid minus distributions) / (LTM cash interest + LTM mandatory amortization). - Springing maintenance test: only tested when the revolver is drawn above a threshold (commonly 35% of commitment). Add a flag:
=IF(revolver_drawn/commitment > 0.35, "TEST ACTIVE", "NOT TESTED").
Display headroom every period in a dedicated covenant dashboard row: Headroom = Covenant threshold minus actual ratio. Negative headroom is a breach. Stress-testing with a ±200 bps rate shock, a 20% EBITDA decline, and a delayed exit validates whether headroom survives plausible downside scenarios.
Pro Tip: Include a one-page term-sheet summary tab in the model file. Map each credit-agreement definition to the cell or formula that implements it. When an agent bank questions your covenant calculation, you can point to the exact row and show the source document reference. This single habit saves hours of back-and-forth during syndication or audit.
Where can you find a ready-to-use Excel template?
A well-structured template includes instrument blocks for a revolver, term loan, and mezzanine tranche; a central dates table; a rate builder; cash sweep logic; a covenant dashboard; and a set of audit-check rows. Both Microsoft Excel and Google Sheets versions are useful: Excel handles iterative calculations more reliably for average-balance interest, while Google Sheets is easier to share with remote teams for collaborative review.
What a complete template should contain:
- Central dates table with payment-frequency flags
- One instrument block per tranche (inputs clearly flagged in a distinct colour)
- Rate builder (base index + CSA + floor + margin → effective periodic rate)
- Roll-forward rows (opening → draws → amortization → sweep → PIK → ending)
- OID and issuance cost amortization rows
- Cash sweep waterfall with minimum cash floor
- Covenant calculation rows (leverage, coverage, FCCR, springing test flag)
- Control panel: net debt bridge, liquidity bridge, revolver availability, maturity ladder
- Audit-check rows (cash never negative, sources and uses tie, no draw+repay same period)
Worked example: revolver + term loan + mezzanine
Assume a Canadian company closes with a $5M revolver (undrawn at close), a $20M term loan at CORRA + 3.50% (floor 0.50%), and a $5M mezzanine at 12% cash pay. The term loan amortizes at 5% per year ($1M annually, paid quarterly at $250K). The mezzanine is interest-only with a bullet at year five.
| Period | TL opening ($M) | TL amortization ($M) | TL interest ($M) | Mezz interest ($M) | Total debt ($M) |
|---|---|---|---|---|---|
| Q1 | , | 1.75% | , | 0.150 | , |
| Q2 | , | 1.75% | , | 0.150 | , |
| Q3 | , | 1.75% | , | 0.150 | , |
| Q4 | , | 1.75% | , | 0.150 | , |
TL rate assumed at 4.00% effective annual (CORRA 0.50% floor + 3.50% margin). Mezzanine: $5M × 12% ÷ 4 = $0.150M per quarter.
Time estimates by complexity:
- Simple (revolver + one term loan, no covenants): 1–2 hours
- Mid-complexity (revolver + TLA + mezz, springing covenant): half a day
- Full LBO stack (multiple tranches, PIK, OID, hedges, ECF sweep, full covenant suite): 2–4 days
Canadian-specific notes: RBC guidance recommends assessing your current debt and building a structured budget before selecting a repayment strategy, and notes the trade-offs between avalanche and snowball approaches. For a business borrower, the equivalent is mapping your amortization schedule against your projected cash flows before committing to a repayment waterfall. Canadian lenders also expect clear disclosure of your repayment plan and amortization assumptions in any financing package. Currency and tax treatment for cross-border facilities should be confirmed with legal and tax counsel, as CORRA-based pricing and Canadian withholding tax rules differ from USD-facility conventions.
For complex covenant or ABL structures, specialist advisory help can help you map borrowing-base formulas and covenant definitions accurately before you build the model.
Key takeaways
A pro forma debt schedule is the model’s cash-and-covenant engine: build it instrument by instrument, wire the cash sweep correctly, and code the covenant math from the credit agreement, not from proxies.
| Point | Details |
|---|---|
| Output both interest types | Separate cash interest and book interest every period to satisfy lenders, auditors, and tax. |
| Revolver is the only plug | Use the revolver as the sole short-term balancing item; never let two instruments draw and repay in the same period. |
| Code covenant math precisely | Use the credit agreement’s EBITDA definition with pro forma add-backs, not unadjusted income statement EBITDA. |
| Stress-test every model | Run a ±200 bps rate shock, a 20% EBITDA decline, and a delayed exit to confirm headroom survives downside scenarios. |
| LenderReady accelerates the financials | LenderReady generates lender-ready financial models with DSCR checks and sensitivity analysis in minutes, reducing build time for entrepreneurs seeking funding. |
The part most modellers skip
There is a gap between what a debt schedule looks like and what it actually does. Most finance students build a tidy roll-forward table, link interest to the income statement, and call it done. Lenders and agent banks do not. They open the model, go straight to the covenant dashboard, and check whether the definitions match the credit agreement. If your EBITDA calculation does not include the add-backs defined in the agreement, your headroom numbers are wrong, and the lender will tell you so at the worst possible moment.
The other thing modellers consistently underestimate is the cash sweep. It sounds simple: take excess cash, pay down debt. In practice, the waterfall has baskets, step-downs, permitted payments, and a minimum cash floor that interacts with the revolver plug. Getting the order wrong means your model shows more debt repayment than the agreement allows, which overstates free cash flow and understates leverage in later periods.
My honest recommendation: before you build a single formula, read the mandatory prepayment section of the credit agreement twice. Map every defined term to a cell. Then build the sweep. The model will be slower to start and far more defensible when it matters.
One more thing on versioning: save a new file every time you change a covenant definition or a sweep mechanic. Name files with a date stamp and a brief change note. When a lender questions a number six months after close, you need to be able to show exactly which version of the model produced that figure and why. That audit trail is not optional.
Your lender-ready financials, built faster with LenderReady
Building a pro forma debt schedule from scratch takes real time, and getting the covenant math right takes even more. If you are an entrepreneur or small business owner preparing a financing package, LenderReady gives you a faster path to lender-ready financials without the blank-spreadsheet anxiety.
LenderReady’s AI-driven platform generates a complete business plan with rigorous financial modelling, including DSCR calculations, sensitivity analysis, and documented assumptions, in about 15 minutes through a conversational Q&A. If you are already operating, the Application File adds source-backed statement figures and deterministic readiness findings before you submit. It is the difference between handing a lender a polished, assumption-backed package and hoping a spreadsheet holds up under scrutiny.
Ready to see what your financials look like to a lender? Start your business plan on LenderReady today.
Useful sources and further reading
These are the primary references used throughout this guide. Each is worth bookmarking if you build debt schedules regularly.
-
Modeling a debt schedule (that actually works): Financial Modeling Education: Practitioner-focused walkthrough covering roll-forward mechanics, circularity handling, and cash sweep wiring. The most directly applicable technical reference in this guide.
-
Debt schedule in investment banking modeling: Investment Banking Analysts: Covers the full capital stack, instrument inputs, covenant math, and control panel design. Use this as a checklist when building a multi-tranche LBO model.
-
What are pro forma financial statements?: Investopedia: Clear conceptual background on pro forma analysis, the difference between GAAP and pro forma figures, and why assumptions must be disclosed. Useful for explaining the schedule’s limitations to non-finance stakeholders.
-
Pro forma financial statements: HBS Online: Accessible explanation of pro forma statements as planning tools, with a reminder to compare projections against historical GAAP figures to avoid misleading stakeholders.
-
How to pay off debt faster: RBC Royal Bank: Canadian lender perspective on structured repayment planning and budgeting. Relevant for Canadian borrowers preparing amortization disclosures for domestic bank presentations.
What to show a lender vs. what to keep in the model spec:
- Show the lender: the covenant dashboard, the maturity ladder, the net debt bridge, and the sources and uses at close. Keep the instrument-level input flags, the circularity breaker toggle, and the credit-agreement definition mapping in the model spec or a separate workbook tab labelled “Model Assumptions.” Lenders want to see clean outputs; auditors and agent banks want to see the full working.
See where your financing file stands
Fifteen questions, four minutes, no documents and no email. You get a readiness stage, the gaps a lender would raise, and the document list for your request.
Check my readiness, freeLenderReady is an educational service, not a lender, broker, or financial advisor. Lending criteria vary by institution and change over time; treat this as a starting point, not a guarantee.