Home » Excel Spreadsheet » Build your own CPR model

Last November I posted an amortization spreadsheet that allowed for variable interest rates on mortgages. I used that spreadsheet and added a CPR variable. That way you can create your own CPR model, rather than using one CPR for the full term or using the PSA model.

Vectors1

The default numbers above show the user wants interest rates on this loan to be 6% for the first 36 months, 2% for the next 36 months, 6% for the next 36 months, 6.5% for the next 36 months, and 4% for the remaining 116 months. If you did not want to change rates, just enter the term (260 months in this case) and a single rate. The next set of numbers are the CPR rates. This model shows no prepayment for the first year, a CPR of 1 for the next year, 3 for the next 60 months, and a CPR of 6 for the remainder. Remember to use the same total months in each set of tables or it will confuse the amortization schedule.

Vectors2

The chart will show the both the CPR and Rate changes. If you use CPR’s the payment will reprice each month. Enjoy

Download “CPRModel”

5 thoughts on “Build your own CPR model

  1. cheri says:

    HI I have a little question about the cdr default calculation. it seems that sifma documentation is using the cdr calculated monthly default rate * the beginning balance of the month without deleting this month principal and prepayment. https://www.sifma.org/wp-content/uploads/2017/08/chsf.pdf
    see the link. I am wondering why your model and a lot of other models are doing thing differently

    1. Don Pistulka Don Pistulka says:

      Cheri,
      The same basic question came in within half and hour, but responding to a different post. I assume they are both from the same person.
      The answer lies in the sequence of principal reduction. Notice that prepayment reduces principal, after the payment principal is deducted from the starting principal balance. Likewise, principal defaults are reduced after the payment and the prepayment.
      Therefore, normal P&I came in before any prepayments, and defaults take place. The prepayments and defaults are used establish the starting balance of the next payment. I hope that helps.
      Don
      P.S. Also, remember that these amortization schedules are from the standpoint of the investor. Even though defaults may be indicated in a given month, the investor is advanced the total P&I from the servicer. From the investor standpoint , defaults can be thought of as just a prepayment, due to the guaranteed P&I.

  2. Brian Taylor says:

    Just a note to say I really enjoyed using this spreadsheet and this and many other of your spreadsheets, they have been really helpful for my learning towards the Financial Maths & Modelling Exam I’m studying for (part of AMCT Treasury qualification in UK). Best website I’ve seen for along time, many thanks, kind regards Brian Taylor, Manchester, UK

    1. Don Pistulka Don Pistulka says:

      Thank You for the kind comments.

Leave a Reply

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

*
*