Objectives

Section Learning Objectives

5.1

Step-by-Step Construction

📝 Worked Example

Structuring the Debt Schedule

Scenario: You are tasked from scratch to construct the debt schedule for a new Leveraged Buyout (LBO) model.

Question: What is the correct chronological order of the 6 key steps to construct an error-free schedule that avoids circularity?
1
Set up inputs
Consolidate all terms: Debt amounts, interest rates, amort %, maturities, minimum cash balance.
2
Link Beginning Balance
Link beginning balance from the prior period's ending balance. (If Year 1, link from the transaction close / balance sheet).
3
Calculate Interest Expense
Calculate interest using `= Beginning Balance × Rate`. This establishes the baseline expense before any cash flows.
4
Calculate Mandatory Amortization
Calculate regular principal repayments based on the initial balance: `= Original Principal × Amort %`.
5
Calculate Optional Prepayment (Cash Sweep)
Apply cash flow sweep logic using `MIN/MAX` functions to pay down debt facilities in priority order.
6
Calculate Ending Balance
Sum it up: `= Beginning Balance - Mandatory Amortization - Optional Prepayment (or + Revolver Draw)`.
5.2

Quality Checks

📝 Worked Example

Linking to the 3-Statements

Scenario: You've just finished the calculation block for a ₹200 Cr Term Loan facility for Larsen & Toubro. Now, you need to plug these values into the core financial statements.

Question: Where exactly do the Interest Expense, Ending Balance, and Principal Repayments go on the 3-statement model?
  • Interest Expense → Must link as a negative to the Income Statement (below EBIT, above EBT).
  • Ending Debt Balance → Must link to the Liabilities section of the Balance Sheet.
  • Principal Repayments (Amort & Sweep) → Must link as cash outflows into the Cash Flow from Financing (CFF) section of the Cash Flow Statement.
  • Internal Check → The Ending Balance check (`Ending = Beginning - Payments + Draws`) should show exactly ₹0 difference across all forecasted years.
5.3

Common Errors

📝 Worked Example

Spot the Errors

Scenario: A junior analyst hands over a debt schedule. They set up the Mandatory Amortization line to calculate as `= Beginning Balance * 5%`. They also hardcoded the revolver draw to automatically take any missing cash down to ₹0 without considering a minimum limit.

Question: What are the two distinct modeling errors in this approach?
1
Error 1: Amortizing the Wrong Balance
The analyst set amort as `% of Beginning Balance`. As the balance drops, the amort payment will shrink.
Correction: Standard term loans amortize as a fixed `% of the ORIGINAL Principal`. (e.g., ₹500 Cr × 5% = ₹25 Cr static repayment every year until paid off).
2
Error 2: Ignoring Minimum Cash Thresholds
The analyst drew the revolver to take operating cash down to ₹0. Operational businesses need a buffer to pay immediate bills.
Correction: The model must target closing cash to a Minimum Cash Balance assumption (e.g. ₹50 Cr minimum). The Revolver should draw based on `= MAX(0, Min Cash - Available Cash)`.
3
Other Common Watchouts
• Sign errors (e.g. adding repayments instead of subtracting)
• Not capping revolver draws at maximum facility commitment (e.g. drawing ₹200 Cr on a ₹100 Cr max facility)

Key Takeaways

  • Build in order: Inputs → Interest → Amort → Balance
  • Always include quality checks
  • Use beginning balance method
← Back to Lecture Home