Modeling Structured Finance Cash Flows with Microsoft Excel

80 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL FIGURE 4.16 100 percent SDA displayed as a line graph. a level .6 ...

Delinquency, Default, and Loss Analysis 81 possible, blank cells were included in the ranges as part of the data validation list ...

82 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL 13.Two additional concepts on the Cash Flow sheet can be finished ...

CHAPTER 5 Recoveries A lthough a defaulted asset generates a loss,there is often an opportunity to recover cash. Repossessing th ...

84 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL and was classified as a default in August 2005. The balance of th ...

Recoveries 85 costs, and lag times. This historical data analysis differs from those in Chapters 3 and 4 because it is more simp ...

86 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL FIGURE 5.3 The recovery analysis is shown completed. 5.Liquidatio ...

Recoveries 87 Model Builder 5.2: Integrating Recoveries into Project Model Builder 1.Start at the Inputs sheet in cell J17 and l ...

88 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL Scheduled Principal, Prepaid Principal, Interest, and Recoveries ...

CHAPTER 6 Liabilities and the Cash Flow Waterfall W ith asset generation complete, this book now turns to the liability side of ...

90 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL FIGURE 6.1 Cash moves through each period from left to right and ...

Liabilities and the Cash Flow Waterfall 91 Each individual liability needs to be set up in a way that shows exactly what is need ...

92 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL 2.Two liability input sections need to be created for this Model ...

Liabilities and the Cash Flow Waterfall 93 multiplied against the assets’ current balance. Remember that the fees typically quot ...

94 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL 10.Finally, to keep the cash ‘‘moving,’’ a column needs to be use ...

Liabilities and the Cash Flow Waterfall 95 susceptible to market fluctuations and can change from period to period. If the asset ...

96 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL 2.The Liability Interest Type for a given debt tranche can be one ...

Liabilities and the Cash Flow Waterfall 97 cell AR7 as follows: =IF(LiabIntType1="Fixed",LiabFxdRate1 This part of the formula c ...

98 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL 8.In cell AT4 enter the labelNote Interest Paid. This ‘‘paying’’ ...

Liabilities and the Cash Flow Waterfall 99 FIGURE 6.5 The nearly complete senior interest section of the Cash Flow sheet. 12.As ...