Long before I wrote user defined functions for valuing servicing on a pool of mortgages, I developed amortization tables to understand the cash flows and proof my VBA. The amortization schedule provided below is not the typical amortization. It has a number of uses besides explaining the math of constant prepayment rates (CPR) and servicing. For example, if you are a financial institution that underwrites and packages mortgages to sell with servicing retained, your auditors might require you to take the present value of the servicing into income upon sale. If you are using an outside vender to value servicing, this amortization will help explain the process. Also, if you buy mortgage-backed securities, this amortization could be helpful explaining CPRs and cash flows.

Obviously, if you are producing 30-year mortgages, you can’t expect them to be outstanding for the full 30 years. That is where CPRs come in. The current prepayment rates are available from a number of sources, such as brokers, and are useful for valuing servicing.

First the math:

CPR is stated on and annual bases, so in order to apply it monthly, it is taken to the .083333 power. Where cpr = Constant Prepayment Rate and rp = Remaining Principal. The math is the same process used to convert APY to a monthly APR.

Servicing is more straight forward, where s = the servicing rate and rp = remaining principal.

Due to the CPR assumption of a certain amount of mortgages prepaying each month, the monthly payment must be recalculated each month. Here we us the Excel formula for payment each month, using rpay = remaining number of payments and rp= remaining principal.

The inputs for the amortization schedule are yellow cells only.

The only other input is the yellow cell asking for the discount or market rate used to present value all the cash flows on the schedule, including servicing. I defaulted to 4.5%, which is the original APR of the mortgage, to show that the sum of the present values equals the original principal value.

Please download the workbook called “CPR_Servicing“, play with it and let me know what you think.

http://www.pistulka.com/Excel_Shared/

Looking for some ideas on calculators for loan value of portfolio to maintain the runoff rate-

Hi Gabrielle,

I’m not clear as to what you mean. Send me and email at blog@pistulka.com with more details.

Don

Dear Don,

I have a general question about your CPR defintion.. In your computations, for every choosen CPR Rate, the Time to maturity of the bond stays constant at 331.

I found other definitions where CPR does have an effect on the time to maturity, since prepayments make the loan prepay faster f.e. after 200 months.

I don’t really understand why in your case the CPR does not have an effect on the time to maturity of the bond?

Can you give me more Details about that, maybe any literature?

Thanks,

KS

Konstantin,

CPR stands for Constant Prepayment Rate. It remains “constant” throughout the life of the pool of mortgages. If you have access to Bloomberg or other means of calculating using a CPR, you can see a complete amortization including the CPR prepayments. These prepayments will last for the full 30 years.

An alternative would be to use PSA (see my post “PSA vs CPR” ) which changes the rate of CPR prepayments, but that also runs for the life of the pool.

Also see my post where the CPR rate is variable throughout the life of the pool ,and you can change the CPR multiple times.

CPR does not change until the last payment is due, but CPR does have an affect on the life of the pool, in that the average life and the duration are directly affected by the size of the CPR.

Thank you for the comment.

Don

PSA vs CPR

Variable

Dear Don

I am wondering why I16=$D$7 has to be tested twice in cell C16?

Best

Tom

Tom,

It is part of an error statement. This error statement makes sure that cells that result in an error, most likely because the loan matured before 360 days and there are only zeros in the row, will not leave error statements, but will leave a zero. Otherwise the schedule would produce potentially hundreds of error statements and look terrible. There are times when I use a copy of an old amortization schedule from previous versions of Excel and build upon it. In this case I could have used a newer error statement that started in Excel 2007 and it would have been a shorter formula, however this does the job.

Don