DebtSchedule
Models a debt tranche paying down through mandatory amortization plus an optional excess-cash-flow sweep, the structure of a typical term loan in an LBO. Each period charges interest on the beginning balance, repays the mandatory amount, then sweeps a percentage of that period's excess cash flow (wired in as a series), capped so the balance never goes below zero. The output is a table with Period, BeginBalance, Interest, MandatoryRepayment, CashSweep, TotalRepayment, and EndBalance; once the debt is repaid, remaining rows show zeros.
Inputs
| Input | Accepts | Required |
|---|---|---|
Principal | Value (a single number) | Yes |
InterestRate | Value (a single number) | Yes |
Periods | Value (a single number) | Yes |
MandatoryAmort | Value (a single number) | Yes |
ExcessCashFlow | Series (a list of numbers) | Yes |
Outputs
| Output | Produces |
|---|---|
Result | Table |
Settings
Typed in on the node or set by the assistant, not wired.
| Setting | Default | Notes |
|---|---|---|
Principal | 500000 | Opening debt balance. Default 500000. |
InterestRate | 0.06 | Rate per period as a decimal, applied directly to the beginning balance; for annual periods use the annual rate. Default 0.06. |
Periods | 5 | Number of periods to model. Default 5. |
MandatoryAmort | 0 | Fixed repayment per period in dollars (not a percentage). Default 0. |
SweepPercent | 0.5 | Share of each period's positive excess cash flow swept to repay debt: 0.5 sweeps half. Default 0.5. |
Example
A $50M term loan at 7% over 5 years with $5M of mandatory amortization and a 50% sweep. Project excess cash flow with ForecastSeries (say $8M growing 10%) and wire it into ExcessCashFlow. Period 1: $3.5M of interest, $5M mandatory, and a $4M sweep (half of $8M), leaving a $41M ending balance. The schedule lands as a table; chart EndBalance to show the deleveraging path.
Tips
Interest is informational: it is not added to the balance, so the schedule assumes interest is paid currently out of cash flow. Sweep only happens in periods where the excess cash flow value is positive; feed a series with zeros or negatives to model down years.