Modeling Structured Finance Cash Flows with Microsoft Excel

60 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL ■Default. Depending on company and legal definitions, a delinquen ...

Delinquency, Default, and Loss Analysis 61 FIGURE 4.1 A timeline of the events that take place when an asset defaults. The first ...

62 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL Model Builder 4.1: Building Historical Delinquency Curves 1.Ideal ...

Delinquency, Default, and Loss Analysis 63 FIGURE 4.2 A standard set of delinquency curves organized by periods out. FIGURE 4.3 ...

64 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL needs to be sized in the modeling. Also, notice that there is a s ...

Delinquency, Default, and Loss Analysis 65 FIGURE 4.4 Raw loss data is best presented on a static loss basis. FIGURE 4.5 The raw ...

66 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL loss performance during periods of rapid portfolio growth. When a ...

Delinquency, Default, and Loss Analysis 67 FIGURE 4.7 The difference between organizing data using a periods-out versus a dates ...

68 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL 2.Similar to the delinquency analysis, copy the origination dates ...

Delinquency, Default, and Loss Analysis 69 Analyzing Historical Loss Curves Vintage loss curves created using a static methodolo ...

70 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL 3.A useful way to observe the timing is to make a cumulative timi ...

Delinquency, Default, and Loss Analysis 71 provided monthly, there could be one vintage that has reached maturity or ‘‘termed ou ...

72 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL 3.In the next row down, label cell B65Loss % Taken. This row is a ...

Delinquency, Default, and Loss Analysis 73 FIGURE 4.10 The additional rows are used to project expected loss. 7.The last step is ...

74 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL FIGURE 4.11 The new adjusted weighted average curve is less than ...

Delinquency, Default, and Loss Analysis 75 therefore have percentages of loss taken out. However, it may seem unusual when using ...

76 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL The effects of seasoning are accounted for in a model by calculat ...

Delinquency, Default, and Loss Analysis 77 from most of the other items in the model, insert a new sheet after the Cash Flow she ...

78 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL time. In this case, cells A8 and B8 will be 13 and 24 respectivel ...

Delinquency, Default, and Loss Analysis 79 FIGURE 4.15 The timing curve is represented on a monthly basis on the Vectors sheet. ...