Lesson Objective: To construct the dynamic debt schedule that tracks debt issuances, repayments, and interest expenses, and to implement the globally accepted “circularity switch” required to handle the interdependent relationship between interest expense, net income, and cash flow.

In-Depth Notes:

1. The Components of the Debt Schedule:
A corporate balance sheet typically contains multiple layers of debt with different seniorities and interest rates. The supporting debt schedule must disaggregate these layers:

• Revolving Credit Facility (Revolver): This is the “plug” facility. It is drawn down when the company has a cash deficit and repaid when excess cash exists. It usually has the highest interest rate (SOFR or EURIBOR + spread).

• Senior Term Loans (TLA, TLB): Bullet or amortizing loans with fixed repayment schedules. These have fixed interest rates (or floating with a floor).

• Bonds (Senior Notes, Subordinated Notes): Typically fixed-rate instruments with interest paid semi-annually (US standard) or annually (European standard). They have a fixed maturity date and are not repaid early unless a “make-whole” call provision is triggered.

• Finance Leases (IFRS 16 / ASC 842): The debt schedule must include lease liabilities, separating the interest portion (which goes to the P&L) from the principal portion (which goes to the financing section of the CFS).

2. The Circularity Problem (The “Chicken or Egg” Dilemma):
This is the most complex mechanical challenge in integrated financial modeling.

• The Issue: Interest expense is calculated based on the average (or opening) debt balance. The cash flow statement determines how much debt is repaid or drawn. However, the amount of debt repaid/drawn depends on the company’s cash flow, which is impacted by the interest expense. Therefore, Interest Expense depends on Debt Balance, and Debt Balance depends on Cash Flow, which depends on Interest Expense. This creates a circular reference.

• The Algebraic Solution (Preferred for Institutional Models): The modeler breaks the circularity by calculating interest on the opening debt balance for the current period (rather than the average of opening and closing). This creates a one-period lag and eliminates the circular reference. This is the standard approach under US GAAP for quarterly forecasts and is accepted by global auditing firms.

• The Iterative Solution (The “Circularity Switch”): If a model requires interest calculated on the average debt balance for precision (common in project finance), a “circularity switch” macro is employed.

  • The model sets a toggle switch (Cell = 0 or 1).

  • When set to 1, the model executes a VBA macro that repeatedly recalculates the sheet until the difference between the calculated interest and the input interest is less than 0.001.

  • This is only acceptable if the model’s iteration settings are limited to 100 iterations and the maximum change is set to 0.001 to prevent the model from entering an infinite loop.

3. The Cash Flow Waterfall (Debt Drawdowns and Repayments):
The debt schedule must determine the mandatory and discretionary repayments.

• Mandatory Amortization: Scheduled principal repayments (e.g., 5% of the original term loan amount per year). These are fixed and must be met.

• Discretionary Repayments (Cash Sweep): After mandatory amortization and required cash reserves, any “excess cash” is used to pay down the Revolver or other debt tranches with the highest interest rates (a practice known as “cash sweeping”).

• The Minimum Cash Balance: The model must include a “Minimum Cash” assumption (e.g., $50 million) to maintain liquidity. The cash sweep calculation is: Excess Cash = Ending Cash - Minimum Cash. Only this excess is used for voluntary repayments. If ending cash is below the minimum, the Revolver is automatically drawn to restore liquidity.

4. Interest Calculation Protocols:
The model must calculate interest expense and interest income accurately using the day-count conventions established in Module 2.

• Interest on Revolver: Calculated based on the average drawn balance during the period. If the Revolver had a zero balance for most of the year but was drawn in Q4, the average balance must be used.

• Cash Interest vs. PIK Interest: In distressed or high-yield models (common in European restructuring), the model must distinguish between cash interest (paid in cash) and Payment-in-Kind (PIK) interest (which is added to the principal balance of the debt). The debt schedule must track PIK interest separately, as it increases the debt balance without increasing cash outflows.