Level 3: Debt and cash · Exercise 32 of 60 · Pro

Term loan with a cash sweep

A financial modeling exercise from Modeldrill. Solve it with a live spreadsheet grid in your browser, with Fill right and Fill down, and it is checked the moment you submit.

The task

A term loan has a fixed scheduled repayment (B3) plus a cash sweep: the share in B2 of any cash left after the scheduled repayment goes to repay the loan early. Fill the opening balance for later years (row 6), the scheduled repayment (row 8: the smaller of B3 and the opening balance), cash after the scheduled repayment (row 9), the sweep (row 10: the share of the positive cash left, but never more than what remains of the loan) and the closing balance (row 11).

What this exercise teaches

A sweep sends surplus cash to the lender before anything else, which is how leveraged loans deleverage faster than the schedule. Without the second MIN the 2030 sweep would push the balance below zero and interest in later years would turn into income.

A hint if you get stuck

Two caps: MAX(0, cash left) stops a shortfall from producing a negative sweep, and MIN(..., opening less scheduled) stops the sweep from repaying more than is outstanding. The closing balance in 2030 should land at exactly zero.

Functions used: MAX, MIN