**Note: I have updated this post and spreadsheet with another way to create your own CPR vectors:**

Previously, I showed a way to vary the CPR prepayment rates http://pistulka.com/Other/?p=1300 on an amortization schedule. That method did not include ramps. This spreadsheet shows how to ramp CPRs up, down, and flat. In this manner you can copy various models or make your own. It is a little tricky, so I included examples on a separate sheet. Although I have posts that have the PSA prepayment model built in with formulas, I will use the PSA model to show how you might construct that model below:

First the inputs:

As always, only the yellow cells are input cells. You need to include the remaining payments and the age of the loan in months. The chart represents the CPRs used in the amortization schedule, starting at month (in this case) 11. The Prepayment Speed is the multiplier for increasing or decreasing the speed of prepayments. Entering 200 will double the models speed of prepayments.

Then you construct the prepayment model in the table below, in the yellow cells. The table represent the starting CPR from when the loan was new. Ramp 1 always starts at month one. PSA starts with a CPR of .2 in the first month and ramps up at .2 each month until the 30th month. Then from month 31 on to month 360, it is flat at a CPR of 6. The 6 CPR is not entered, because after 30 months of ramping up at .2, it will be at 6 CPR at month 30. Ramp 2 CPR is entered as 0, to represent a flat ramp. Remember that the ending month will be the last month of the original maturity of the loan you are analyzing,

The chart shows the 10 month old loan, starting on the 11th month, will have a CPR of 2.2. It will continue the ramp of plus .2 CPR until it peeks at a CPR of 6 in month 30. It remains a 6 CPR until month 360.

Now lets take a look at another model to construct, MHP (Prepayment Rate for Manufactured-Housing). In this case, the CPR starts a 3.7 in the first month and increases by .1 CPR each month through month 24. It then flat lines at 6 through month 360:

This time the starting CPR (3.7) is different from the ramp rate of plus .1 CPR each month until month 24, when it reaches 6 CPR. We are still assuming a 360 month loan that is 10 months old. This time the chart shows that starting in month 11, the current CPR is 4.7. The CPR continues up the ramp to 6 CPR in month 24 and then flat lines.

Taking the above model, lets assume that instead of flat lining at a 6 CPR in month 24, we want to assume the CPR starts dropping in month 25 at a rate of .1 CPR per month until month 60 and then flat lines.

In each of the examples above, the monthly CPR (SMM) is derived from the column “Current CPR”. You will want to keep an eye on the average life.

Hi Don,

Just discovered your site. GR8 stuff. Really some informative worksheets and info.

Have you built a worksheet for auto loans that have prepayment, defaults & recoveries? I’m trying to build a worksheet that I can calculate ROA with these variables.

Thanks,

Joe T

Joe,

The only spreadsheet I have specifically for autos is in the link below. The spreadsheet is intended to convert CPR, SMM, and ABS (sometimes referred to as APS).

http://www.pistulka.com/Excel_Shared/CPR-SMM-ABS.xlsx

Don

Don,

Thanks for the worksheet. I guess I should be a bit more specific. I inherited a partial worksheet that is doing what I stated in my first comment. My dilemma is the default curve calculations. I cannot for the life of me figure out how the monthly default factor is calculated.

Is there a way of sending you the worksheet to discuss further?

Thanks,

Joe T

Hi Joe,

Yes, send me your workbook.

blog@pistulka.com

Hey Don, do you have a good method of doing prepayment analysis as the months continue to roll forward? We just installed a new database and I’m trying to recreate a set up I’ve seen from an ALM vendor we have.

The securities make sense because they don’t grow but I’m stuck on how to apply this to a live loan portfolio.

Any guidance helps!

John

John,

I am sending you an amortization schedule.

I’m not sure what you mean by “rolling forward”, but it depends on which prepayment method you are using.

The attached has an option for CPR or PSA prepayment models.

For CPR, all you need for a fixed rate loan going forward is the current balance. All the other inputs stay the same and it does not matter how old the loan is. CPR is a constant rate.

For PSA however, you need original payments and the age of the loan. That is because PSA ramps up .2% CPR each month until it gets to 6%, then stays a 6% CPR.

Look at the attached to see if that helps.