Module IV· Cash SweepIntermediate
Question
How do you model the cash sweep technically in Excel?
Answer
Mechanics
Standard formula with a MIN function for the bound logic:
```
Cash Sweep = MIN(
Excess Cash Available,
Outstanding Debt Balance,
Sweep % × Excess Cash
)
```
Deep diveShow more details
Example
- Excess Cash: $30m
- Outstanding TLB: $80m
- Sweep %: 75%
- Sweep = MIN(30, 80, 22.5) = $22.5m
Consequence
Three bounds at once:
- You can't sweep more than the available excess cash
- You can't sweep more than the outstanding debt
- You can't sweep more than the contractual sweep percentage allows
Common pitfalls
- Forgetting that the sweep comes after mandatory repayments
- Not trapping negative excess cash (MIN could go negative) — fix: MAX(0, ...)
- Cascade across several tranches: senior first in full, then junior
Pitch tip
In a modeling test bankers say 'Build me the cash sweep waterfall' — a structured answer: 'Excess cash → senior debt first in full, then mezzanine, then shareholder loan. Each tranche gets its own MIN bound.' Cleanly built with helper cells, not one mega-formula.