48 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL
formulas for the main types of prepayment rates (as described above), and standard
prepayment curves. Also, the model should be developed in a way that it is easy
to select which curve to use, the option to use different curves for specific groups
of assets, and a method of automating scenarios so curves can be selected and run
without much adjustment. All of this willbe demonstrated and worked through in
the Model Builder exercises.
The Effect of Prepayments on Structured Transactions
Prior to the step-by-step exercises in the Model Builder sections a moment should
be taken to understand the effects that prepayments have in a structured finance
transaction. Prepayments accelerate the repayment of principal for the assets and,
in pass-through structures, the principalof the notes or certificates. At first thought
an investor might think prepayments are good since they will have their principal
returned to them faster.
This is true if the assets are consistentlyperforming poorly and there is risk that
there will not be enough cash flow to repay the investor. In such a case, one would
want their money as fast as possible. However, when assets are performing as they
are expected, there should be more asset interest thanliability cost— also known
asexcess spread. This excess spread provides credit protection over the life of a
transaction. Obligors who tend to prepay the fastest are the better credits with high
interest rates that, when, eliminated, reduce excess spread. If those higher interest
obligors prepay quickly, the transaction terminates faster and overall there will be less
excess spread in the deal than if the assets prepaid at a slower rate. This is extremely
important because if a comparison weremade between two transactions — A and
B — where everything was the same including default rate but the only difference
was transaction A prepaid faster than transaction B, then transaction A would have
less excess spread.
Another consideration is that the prepayment rate needs to be modeled accurately
to give a correct estimation of the weighted average life of the transaction and the
total return provided to investors. Institutional investors often purchase asset-backed
securities with certain tenors and returns in mind. Prepayments can drastically affect
the weighted average life of a transaction and can reduce the overall interest paid,
thereby reducing the total return of investment.
MODEL BUILDER 3.1: HISTORICAL PREPAYMENT ANALYSIS AND
CREATING A PROJECTED PREPAYMENT CURVE
1.The first step in any prepayment analysis is to review the data that exists. Open
MB3-1 RawData.xlsfrom the Ch03 folder on the CD-ROM. There is only
one sheet in this workbook and it contains very pertinent and organized data for
