Change to “Calculate Historic CPR and PSA”

In the previous spreadsheet used to calculate historic CPR and PSA, I used a separate sheet in the workbook to run an iteration on an amortization schedule in order to calculate PSA. An iteration is only necessary if the loan pools are 29 months old or less. I have replaced the amortization sheet with a user defined function (VBA). This should prove easier to use when calculating PSA on numerous pools. I left the old spreadsheet and post up, and directed the old post to this one. Read the old post first for more details.

Download CalCPR2.xlsm

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.


    1. Hi Andrew,

      I’ll have to give it some thought. Off the top of my head, the math is the same as the prepayment rate (CPR) for CDR, but because prepayments come before defaults, you would have to know the historic prepayment rate first. Then there is loss severity.
      There is a delay in recouping the remainder of a default. It has been a long
      time (7 years) since I have been retired, and I don’t remember if loss
      severity is even published separately for MBS.

Leave a Reply

Your email address will not be published. Required fields are marked *