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”

3 thoughts on “Build your own CPR model

  1. Brian Taylor

    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

    Reply
  2. Pingback: Amortization – Variable Terms, Rates, & Payments | Excel@CFO

Leave a Reply

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

WordPress spam blocked by CleanTalk.