68 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL
2.Similar to the delinquency analysis, copy the origination dates and balances
to rows 37 and 38. Also copy the periods in column B so that it starts with
Origination Amt in row 38.
3.Starting in cell C39 enter the following formula:
=IF(C8="","",C8/C$38)
This is similar to the delinquency formula; but it bases all of the loss on original
balance instead of current balance. Copy this formula over the range C39:AA62.
4.So far monthly loss percentages have been created. This data is better analyzed
looking at cumulative percentages to see the ‘‘curve.’’ Copy the vintage dates
and origination amounts to row 65 and 66. Also copy the periods in column B
to start at row 66. The cumulative percentage is the current period’s loss plus
the previous period’s cumulative percentage. Before entering a formula, insert
a blank row in row 67. This should put a row of empty cells between the first
period and the origination amounts so the formula that will be entered does
not add the origination amounts to the cumulative loss percentage. With that
complete enter the following formula in C68:
=IF(C39="","",C39+C67)
Copy this formula over the range C68:AA91. The cumulative curves are often
summarized in a line graph that looks like Figure 4.8.
Before continuing further with Model Builder 4.2, it is important to understand
how to analyze the curves to use them for projections.
FIGURE 4.8 A graph of cumulative loss curves.
