Modeling Structured Finance Cash Flows with Microsoft Excel

100 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL Principal In addition to interest, banks and investors expect to ...

Liabilities and the Cash Flow Waterfall 101 the senior debt is 95 percent. In cell C24 enter 95.00 percent. Name cell C24 LiabAd ...

102 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL 4.The same should be done for the Sub Loan. Enter these labels i ...

Liabilities and the Cash Flow Waterfall 103 spread. That extra money will trickle down along the waterfall and eventually be ava ...

104 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL BV4:Unpaid BW4:Cash Remaining A similar debt principal calculati ...

Liabilities and the Cash Flow Waterfall 105 Now if the principal allocation is set to ‘‘Sequential’’ and the senior tranche is p ...

106 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL There are endless possibilities for the amount and timing of the ...

CHAPTER 7 Advanced Liability Structures Triggers, Interest Rate Swaps, and Reserve Accounts L oss protection is the single most ...

108 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL breached quickly, the liability structure switches, and the inve ...

Advanced Liability Structures 109 FIGURE 7.1 The Capture trigger should be entered in the Structural Inputs section. Model Build ...

110 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL cash is typically released out of the transaction. Any cash rele ...

Advanced Liability Structures 111 FIGURE 7.2 The triggers on the Cash Flow sheet should start taking form. 8.Still on the Cash F ...

112 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL FIGURE 7.3 The completed trigger section of the Cash Flow sheet ...

Advanced Liability Structures 113 FIGURE 7.4 Excess amounts that are used for principal acceleration are calculated at the end o ...

114 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL the amounts are calculated off of a notional amortization schedu ...

Advanced Liability Structures 115 3.Column AE is where the Notional Swap Schedule is stored. This is a base case amortization of ...

116 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL FIGURE 7.6 Make sure the Vector’s sheet is updated so Cash Flow ...

Advanced Liability Structures 117 FIGURE 7.7 The Swap section on the Cash Flow sheet is complete. Without this change the swap c ...

118 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL money can only be accessed for certain obligations; otherwise th ...

Advanced Liability Structures 119 BJ4:Reimbursements BK4:Reserve Account Ending Balance BL4:Cash Remaining 3.The reserve account ...