IB Debt Schedule Template (LBO Modeling Guide)

An LBO debt schedule tracks each loan and note separately, then links borrowing, required repayments, optional paydown, and interest to cash flow and the balance sheet. Build it from the deal’s financing terms, preserve minimum cash, and set repayment priority. Then test the model for circularity, borrowing limits, cash shortfalls, and balance errors.

A common modeling mistake is to put all borrowing into one debt line and apply one interest rate. That can hide different repayment rules, pricing, and limits. It also makes it hard to tell whether the company can meet its obligations. A useful schedule does more than calculate debt: it shows how cash moves through the business and what could disrupt repayment.

LBO Debt Schedule Overview: Tranches, Terms, and Modeling Conventions

A debt schedule forecasts each financing facility from period to period. A tranche is one facility or layer of debt, such as a revolver or term loan. Before building the schedule, I list each facility’s opening balance, rate, required payments, limits, and priority so that the model reflects the deal terms rather than treating all debt alike.

Start with the financing terms and the transaction’s sources and uses. Sources show where the purchase funding comes from; uses show where it goes. The debt schedule’s starting balances should tie to the debt raised at closing, and the total sources must equal total uses.

For each tranche, include these fields:

• Opening balance
• New borrowing or issuance
• Required, or mandatory, repayment
• Optional repayment
• Closing balance
• Interest rate and interest expense
• Maturity, borrowing limit, and repayment priority, where relevant

Use a separate row for each movement. A basic roll-forward is:

Closing debt = Opening debt + New borrowing − Mandatory repayment − Optional repayment

The next period’s opening balance should equal the prior period’s closing balance. If it does not, the schedule does not roll forward correctly.

A floating rate changes with a benchmark rate plus a lender spread. A floor sets a minimum benchmark rate, if the loan terms include one. Model the stated terms: do not assume a floor, repayment rule, or maturity that the deal documents do not provide. Keep assumptions visible and label whether rates are fixed or floating.

Takeaway: Make one row set per facility, and make each balance movement traceable to a stated assumption or cash flow.

Debt Schedule Construction: Cash Waterfall, Repayment Rules, and Interest

A cash waterfall is the order in which cash is used. It typically reserves cash for operations and the required minimum balance before debt paydown. Exact order depends on the deal terms. Modeling that order explicitly helps prevent the schedule from using cash that is not available for optional repayment.

Build the schedule in this order:

1. Set cash available. Start from cash flow after operating needs and other modeled uses. Keep the minimum-cash requirement out of optional paydown.
2. Pay mandatory amortization. Apply required principal payments as specified in the debt terms.
3. Handle a cash shortfall. If cash would fall below the minimum, draw the revolver if it has available capacity and the terms permit a draw.
4. Apply optional repayment. Use eligible excess cash for optional paydown, following repayment priority and the cash-sweep percentage.
5. Calculate closing cash and debt. Reconcile the cash balance and each facility’s closing principal.

A cash sweep is the share of eligible excess cash used for optional debt repayment. If the sweep is 75%, for example, the model applies 75% of cash eligible under the deal’s rules; the rest is not automatically available for repayment. A 100% sweep is not a default assumption.

A revolver is a flexible facility that can be drawn and repaid, subject to its commitment and other terms. Draw only enough to cover a shortfall below minimum cash, up to unused availability. When excess cash is available, repay the revolver before junior-priority debt if the terms call for that order. Do not let a model draw beyond the commitment.

Interest also needs a clear convention. A floating-rate facility may use:

Interest rate = max(benchmark rate, applicable floor) + spread

For fixed-rate debt, use the stated fixed rate. Then calculate interest using the required timing convention. Beginning-balance interest uses debt at the start of the period. Average-balance interest uses the average of opening and closing debt:

Average debt = (Opening debt + Closing debt) ÷ 2

Average-balance interest can better reflect repayments during the period, but it can create a circular reference: interest affects cash available for paydown, and paydown affects average debt and interest. A beginning-balance method avoids that specific circular link, but it may not match the desired convention or the model’s assumptions. State the method and apply it consistently.

Takeaway: Protect minimum cash, follow payment priority, and make the interest timing assumption visible.

Model Integrity: Circularity, Audits, and Liquidity Stress Testing

Circularity occurs when a formula depends, directly or indirectly, on its own result. In an LBO model, average-balance interest may depend on closing debt, while closing debt depends on cash after interest. A controlled calculation or a documented non-circular method can address this, but the choice must be clear and the outputs must be checked.

One option is to enable iterative calculation in Excel and set the model’s iteration controls consistently. Another is to use a non-circular approximation, such as beginning-balance interest, or a separate calculation that estimates interest without linking it back to the paydown loop. Neither approach should be hidden. Document the method and compare it with a non-circular cross-check where possible.

Use this audit list:

• Debt roll-forward: Opening debt plus borrowing, less repayments, equals closing debt for every tranche.
• Cash balance: Ending cash meets the minimum-cash assumption, unless the model flags a shortfall.
• Revolver capacity: Drawn debt does not exceed the commitment; available capacity is not negative.
• Debt floor: Closing debt is not below zero.
• Interest link: Debt balances and interest expense flow to the intended cash flow and financial statements.
• Balance sheet: Assets equal liabilities plus equity in each forecast period.
• Sources and uses: Total sources equal total uses at closing.
• Formula integrity: Check copied formulas, signs, units, and links across periods.

Use a base case, a downside case, and a liquidity stress case. Change operating cash flow, rates, or other relevant assumptions based on the model’s stated scenario design. Watch when cash reaches its minimum, when the revolver is drawn, whether capacity runs out, and how much debt remains. Avoid presenting one scenario as a prediction.

Illustrative units only. The table is not a claim about a real transaction. In the second case, a 4-unit draw would bring cash to the 10-unit minimum. In the third, the available 3-unit commitment would leave a 1-unit funding gap. The model should show that gap rather than silently allow extra borrowing.

Takeaway: Treat failed checks and liquidity gaps as visible results, not errors to conceal.

Debt Capacity and Career Application: Interpreting Leverage and Repayment Outcomes

Debt capacity is the amount of debt a business can support under the assumptions and terms being modeled. A debt schedule helps assess repayment ability by showing interest burden, required payments, optional paydown, and remaining principal. It does not, by itself, establish that a financing structure is suitable or likely.

To interpret the schedule, review both the debt path and the cash that supports it. Useful measures include:

• Leverage: Debt divided by a stated measure of earnings, often EBITDA in transaction analysis. Identify which debt and EBITDA figures you use.
• Interest burden: Interest expense relative to the earnings or cash-flow measure selected for analysis. State the measure and period.
• Mandatory repayment: Required principal due in each period under the modeled terms.
• Optional paydown: Cash used for repayment after required uses and minimum cash.
• Remaining debt: Closing principal by tranche and in total.

These measures need context. A falling debt balance may reflect strong cash generation, but it can also depend on a high cash-sweep assumption. A low interest burden in one period may not capture later floating-rate costs or a maturity payment. Check the operating forecast and financing terms alongside the ratios.

Illustrative repayment cases

These examples show how schedule logic changes with terms. They are not real transaction case studies or market benchmarks.

• Case A: Term-loan paydown. A company has a term loan with required amortization and a 75% sweep on eligible excess cash. The model first reserves minimum cash and pays mandatory amortization, then applies the stated sweep to remaining eligible cash. The remaining 25% is not treated as debt repayment.
• Case B: Revolver use. Forecast cash falls below minimum cash. The model draws only the amount needed, subject to available commitment and draw terms. In a later period with excess cash, it repays the revolver before other debt only if the stated priority requires that order.
• Case C: Floating-rate stress. The benchmark rate rises in a downside scenario. The model applies the benchmark, any stated floor, and the spread, then checks interest expense, cash available, and repayment capacity. It should not assume that higher rates leave paydown unchanged.

For students and early-career modelers, the practical skill is not just entering formulas. It is explaining why cash is used in a particular order, what happens when the forecast weakens, and which assumptions drive the result. I recommend labeling every key assumption and adding a short output summary: leverage, interest expense, minimum cash, revolver usage, and debt remaining by period.

Takeaway: Use the schedule to explain repayment capacity under stated assumptions, not to claim certainty.

Frequently Asked Questions

These answers address common build and audit questions for an LBO debt schedule. The right model choice depends on financing terms and stated conventions. When terms are not provided, mark the assumption clearly rather than treating a customary approach as a contractual requirement.

What should an LBO debt schedule include?

Include a separate roll-forward for each facility, with opening debt, borrowing, mandatory repayment, optional repayment, and closing debt. Add pricing, interest expense, maturity, limits, and priority as relevant. Link debt and interest to cash flow and the financial statements, then add checks for cash, debt, and borrowing limits.

Why should I model each debt tranche separately?

Facilities can have different rates, amortization, maturities, limits, and repayment priority. Combining them can hide a breached limit or apply the wrong payment rule. Separate schedules let you reconcile each balance and see which facility receives cash under the modeled waterfall.

How do I calculate interest expense?

Use the facility’s stated fixed rate or floating benchmark plus spread, applying any stated floor. Multiply the rate by the balance under the model’s chosen timing convention, such as beginning or average balance. Show the convention and period basis, and link the resulting expense to the appropriate statements.

Should interest use average debt or beginning debt?

Use the convention specified for the model or required by the analysis. Average debt reflects changes during a period but may create circularity when paydown depends on cash after interest. Beginning debt is simpler and avoids that loop, but it is a different assumption. Document and test the choice.

What is a cash sweep in an LBO model?

A cash sweep is the share of eligible excess cash directed to optional debt repayment. Calculate it only after operating needs, minimum cash, and required repayments, following the deal’s rules. For example, a 75% sweep applies 75% of eligible cash, not 75% of all reported cash.

When should the revolver be drawn?

Draw the revolver when forecast cash falls below the minimum-cash requirement, if the facility permits the draw. Limit borrowing to available commitment and reflect any other stated terms. If the revolver cannot cover the shortfall, flag the funding gap instead of allowing an unsupported balance.

How do I handle Excel circularity?

Either use controlled iterative calculation or apply a documented non-circular method. Check that the model converges and that the result is stable under the chosen settings. Compare key outputs with a non-circular cross-check where practical, and disclose the method so another user can review it.

What are the most important debt schedule checks?

Confirm every tranche roll-forward, minimum cash, nonnegative debt, revolver availability, and sources-and-uses balance. Also check that interest links correctly, the balance sheet balances, and formulas copy across periods. Run a downside case to see whether cash shortfalls or borrowing-limit breaches appear.

How should I test repayment capacity?

Review mandatory payments, interest expense, optional paydown, and closing debt across the forecast. Test a base case and relevant downside or liquidity scenarios, including changes to cash flow or rates. State the assumptions and track minimum cash and revolver usage; do not treat a modeled outcome as a guarantee.

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