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.