🎯

Lecture Learning Objectives

📚

Course Sections

1

Understanding Debt Structure

Types of debt, tranches, and key terminology

  • Types of debt instruments
  • Revolving credit facilities (RCF)
  • Term loans and bonds
  • Debt seniority and tranches
  • Key debt terms and covenants
⏱ 30 min Start →
2

Interest Calculations

Interest expense computation methods

  • Fixed vs. floating interest rates
  • LIBOR/SOFR-based pricing
  • Interest calculation formulas
  • Actual/360 vs. 30/360 conventions
  • Excel implementation
⏱ 35 min Start →
3

Repayment Schedules

Amortization and prepayment mechanics

  • Amortization schedule basics
  • Mandatory principal payments
  • Optional prepayments (cash sweep)
  • Cash available for debt service
  • Debt capacity and covenants
⏱ 40 min Start →
4

Circular References

Understanding and resolving circularities

  • The circular reference problem
  • Interest → Cash → Debt → Interest loop
  • Excel iteration settings
  • Copy-paste macro solution
  • Circular reference breaker formula
⏱ 35 min Start →
5

Building the Schedule

Complete debt schedule construction

  • Step-by-step schedule construction
  • Multiple debt tranches
  • Linking to three statements
  • Quality checks and validation
  • Common errors and debugging
⏱ 40 min Start →

📥 Download Practice Files

Download these files to follow along with the hands-on exercises

📊
lecture-07-debt-schedule-data.csv
Sample debt data for 3 companies
⬇
📋
lecture-07-debt-schedule-template.csv
Blank template for practice
⬇
📖
Practice Exercises Guide
Step-by-step exercises with solutions
⬇
📋

Key Formulas Quick Reference

Interest Expense

Interest = Beginning Balance × Rate × (Days/365)
Annual Interest = Average Balance × Rate
Excel: =Beg_Balance*Rate

Debt Roll-Forward

End Balance = Beg + Drawings - Repayments
Revolver = MAX(0, Required - Available)
Paydown = MIN(Available, Balance)

Cash Sweep

Cash Available = Cash Flow - Min Cash
Prepayment = MIN(Available, Balance)
Priority = Highest cost debt first

Circular Breaker

Avg Balance = (Beg + End) / 2
Simple: Use Beg Balance for interest
Iteration: Enable in Excel options
✓

Knowledge Check