After a buyout, the company uses its cash to pay down debt. The debt schedule tracks each loan year by year:
closing balance = opening balance - mandatory amortization - optional repayment
- Opening balance is last year's closing balance.
- Mandatory amortization is the scheduled repayment, often a fixed percentage of the original loan each year.
- Optional repayment, the cash sweep, uses the cash left over after the scheduled payment to repay more. It can never be more than the cash available or the balance still owed, which is what
MIN is for.
- Interest is charged on the balance. Charging it on the opening balance keeps the model simple: interest depends only on last year, so there's no circular reference.
Interest on the average balance is more precise, but then interest depends on this year's repayment, which depends on cash flow, which depends on interest: a loop. Modeling tests usually accept the opening balance, and it's the safe choice under time pressure.
Repayments are shown as negatives, so the closing balance is a plain SUM.