This lesson involves calculation of debt service accounts and cash flow sweeps in a case where the model is monthly but the debt payments are semi-annual. I think some of the videos are somewhat long and detailed. But if you look carefully at the sheets you can see how cash flow is accumulated in a debt service account and then released to various other accounts depending on the debt repayment period, covenants and cash flow sweeps. For this lesson I did not include separate exercises when I was making the videos.
Monthly/Semi-Annual Analysis and Cash Flow Waterfalls
| Subject | File with Subject | Video Link | ||
| Computing Repayment Months for Semi-annual debt | Monthly Debt | https://www.youtube.com/watch?v=Z-e1aQhXUUc | ||
| Constructing Monthly Time Line with Switches for Repayment | Monthly Debt | https://www.youtube.com/watch?v=JYzSdN99chg | ||
| Debt Service Account to Accumulate Cash Until Next Repayment | Monthly Debt | https://www.youtube.com/watch?v=sskJxtdbWBw | ||
| Waterfall – Default on Debt Payment in Monthly Model | Monthly Cash Flow Waterfall | https://www.youtube.com/watch?v=W4oAuEmrcZs | ||
| Debt Service Reserve Account in Monthly Model | Monthly Cash Flow Waterfall | https://www.youtube.com/watch?v=6FfRT3A9DkI | ||
| Monthly Debt Analysis with Waterfall – Lock-up Covenant | Monthly Cash Flow Waterfall | https://www.youtube.com/watch?v=pwn7Y0-A4mQ | ||
| Monthly Debt Analysis with Waterfall – Cash Sweep Account | Monthly Cash Flow Waterfall | https://www.youtube.com/watch?v=1aSoJuG5s5I | ||
| Roll-up to Annual for Monthly Cash Flow Waterfall | Quarterly and Annual Roll-up | https://www.youtube.com/watch?v=xPCCuzgmC1Y | ||
| Roll-up to Quarterly for Monthly Cash Flow Waterfall | Quarterly and Annual Roll-up | https://www.youtube.com/watch?v=5PVn_gjQNOw |
Files associated with Lesson Set 5: Monthly/Semi-Annual Analysis and Cash Flow Waterfalls
Quarterly and Annual Roll-up.xlsm
Monthly Debt.xlsm
Monthly Cash Flow Waterfall.xlsm