There is a newer post that replaces the amortization schedule to calculate historic PSA with a UDF:

http://pistulka.com/Other/?p=2964

Back in December of 2014 I wrote a post and Excel spreadsheet called Reverse Engineering Constant Prepayment Rate (CPR). I called it that because instead of giving a CPR to a model, we could do the reverse and calculate the historic CPR given either the prepayment cash flows or given just the ending balances. I added a couple of things to the sheet called “Given Ending Balance Only“. Initially the sheet showed how to calculate the CPR from issue through a given month. I added another set of ending balances so the monthly CPR’s could be calculated.

Both of these calculations use Excel built in functions. From issue though a given month uses CUMPRINC (cumulative principal) and the monthly calculation uses PPMT (principal payment for a given month). The reason these functions are used is that we need to know what a normal (no CPR) amortization looks like. We use the normal scheduled ending balances to compare with actual ending balances to determine what CPR it would take to get both equal. You can see when you open the spreadsheet what the formulas look like, but here are examples for both calculations:

**From Issue Through a Given Month**

**=(1-(OFFSET($C$16,$D$10-1,0)/($D$6+CUMPRINC($D$8/12,$D$7,$D$6,1,$D$10,0)))^(12/$D$10))*100**

**Monthly**

**=IFERROR((1-((1-(PPMT($K$8/12,1,$K$7-K15,J15)+(J15-J16))/(J15+PPMT($K$8/12,1,$K$7-K15,J15)))^12))*100,0)**

You will remember from previous posts that the linear calculation for equating CPR and PSA (with its ramp) for the purpose of forecasting prepayments is:

**=CPR*100/6*MAX(1,30/Loan Age)**

Although this formula works fine for forecasting, it has limited value when it comes to historic calculations. For example, the default data on the spreadsheet shows a new 30 year pool with a WAC of 4.35%.

The CPR calculation shows that from issue through the 36th month, the CPR has been 17.27. If we were to use the CPR to PSA linear equation, the PSA would be 287.86.

PSA uses the historic CPR to calculate historic PSA. What is needed is a PSA rate that will get the historic balance of a the CPR equal to the balance of the PSA calculation, given the same inputs. To do this I used an amortization schedule on sheet “Amortize”. When the green button is clicked, VBA switches to the amortization sheet, uses the Goal Seek Excel function to run the amortization schedule a sufficient number of times until the PSA ending balance matches the historic ending balance at month 36. If values with higher accuracy are required, set the Maximum Change to more decimal places (File, Formulas, Maximum Change). The PSA requires a loan age, unless it is 30 months old or more.

The chart below shows the historic ending balances and the PSA ending balances at 468.60. Both meet at the 36th month.

Download http://pistulka.com/Excel_Shared/CalCPR.xlsm

** **

M HeinieDo you have an amortization schedule that allows one to recalculate the IRR at each rate change? Accounting guidance requires that the IRR be applied from the date of change forward while leaving previous periods unchanged.

Don PistulkaPost authorI am not sure what you are asking for, but if you can sketch out your requirements on a blank Excel sheet, I will try and help. Send the Excel spreadsheet to blog@pistulka.com.

Don

Carlos MaldonadoHi Don,

Thank you for posting these models. Quick question- could you leverage any of your existing models to calculate the prepayment speed based on a Ginnie CMBS’s factor, WAC, and outstanding principal balance?THank you,

Carlos

Don PistulkaPost authorCarlos,

I am not familiar with Ginnie CMBS’s factor. Please give me an example or point me to a source I can go to.

Thanks,

Don

Pingback: Change to “Calculate Historic CPR and PSA” | Excel@CFO

KevinI’ve been poking around your website, really great stuff! Very helpful. I have a question on your methodology for calculating historical CPR. Have you tried to tie this methodology out with SIFMA’s guidelines for calculating historical CPR (https://www.sifma.org/wp-content/uploads/2017/08/chsf.pdf)? Using the example in your attached spreadsheet, the 12mo CPR that you are calculating is 5% whereas if I use SIFMA’s methodology for the same period (essentially 1-(actual bal/sched bal) for this example I come to 12mo CPR of 6.02 using the numbers in your spreadsheet. Interested to hear your thoughts, perhaps my calculation is off.

Don PistulkaPost authorKevin,

I am sending you an email with the formula from the SIFMA and the numbers I used in my calculation, which both result in a CPR of 5. I have also attached an amortization schedule with a 5 CPR, which equates to the same CPR value as shown in my example sheet.

Thank you for your comment.

Don

AndrewHow would you go about finding the historic CPR for a portfolio of mortgages? I am trying to model prepayments on a loan level for thousands of loans.

Pingback: Reverse Engineering Constant Prepayment Rate (CPR) | Excel@CFO

Don PistulkaPost authorUp to date prepayment speeds on various coupons and maturity mortgage backed securities are available through brokers/dealers, but you need to have some sort of business relationship with them. As a portfolio manager I dealt with many brokers, so I could get the data from them. Bloomberg had some agency prepayment speeds also. If you do not have a relationship with a broker, call some (Merrill Lynch, Goldman Sachs, etc.) and ask to speak to someone in the MBS area, and see if they can help you. One way or another, it will cost you something.

Agencies that issue MBS securities like Fannie Mae might be of help. If you have thousands of loans, I am assuming you have a relationship with Fannie Mae.