There is a newer post that replaces the amortization schedule to calculate historic PSA with a UDF:
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
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:
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.