Home » Uncategorized » 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

2 thoughts on “Change to “Calculate Historic CPR and PSA”

    1. Don Pistulka Don Pistulka says:

      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 *