**This post has been updated in a new post called Calculating Historic CPR. The spreadsheets has also been updated “CalCPR.xlsm” After you review this post, please see the updated post.**

Unless you work for a firm that packages pools of mortgages, you will probably never need to calculate the historic CPR on an existing pool. Even so, there are some interesting Excel formulas that can be used with and without array formulas to calculate the historic CPR. In the “CalcCPR” workbook there are two sheets. the first sheet gives a formula to calculate the historic CPR, if you are given the prepayments:

{=-(FVSCHEDULE(1,(EBalance/(SBalance-Principal))-1)^(1/($F$7/12))-1)} |

The FVSCHEDULE function in Excel applies a series of interest rates to a principal balance to get a future value. In our case the principal is $1.We don’t use the actual starting balance because we are only concerned with the percentage change. When we get the future value of $1, we can then calculate the rate of return (which in this case equals the CPR). Normally it is not necessary to make the function FVSCHEDULE an array formula, but in our case we need to determine the schedule of rates by calculating one for each month on the amortization schedule. The SMM for each month is calculated by dividing the ending principal balance by the starting balance less the principal that is part of the normal payment. We are calculating the CPR from the first month up to and including the month you entered in cell F7.

The reason the CPR comes out exactly 5.000%, is that I have entered a default CPR prepayment cash flow that is equal to 5.00% for this pool structure example. The yellow cells for prepayments can be changed. If you needed a CPR for a period, for example, the 13th to the 24th, you need to make adjustments to the named ranges.

Before I go on, I have a disclaimer to make. You can’t enter the last month of the loan, you have to enter a month less than the last month. The reason for that is that I do not treat any part of the last principal payment as prepayment. The last month that prepayments appear is the month before the last month. My logic is that that principal is all due at the last month, so no part should be considered prepayment. Why am I mentioning this? If memory serves me, Bloomberg® carves out a portion of the last payment as prepayment.

The second sheet “Given Ending Balance Only” is a more likely scenario than being given the principal payments broken out for you. The amortization schedule is the same as the one on the first sheet, however I have hidden all the columns except the ending balance. What if the only information you had was the basic structure of the pool and a range of ending balances each month. This is the formula:

=1-(OFFSET($H$15,$I$7-1,0)/($I$3+CUMPRINC($I$5/12,$I$4,$I$3,1,$I$7,0)))^(12/$I$7)

This formula is not an array formula, and is made easy with the use of Excel’s function “CUMPRINC”. This function returns the total principal payments using the same basic structure of the loan, except there are no prepayments. In simple term, if we want the CPR through the 12th month, this formula finds the ending balance with CPR payments and divides it by the balance if there were no CPR payments, then annualizes the results.

Obviously, most pools don’t have the exact same coupons and maturities, as are assumed in this workbook. If you had multiple coupon rates with an average weighted rate of 3.00%, these formulas would need adjustments. It is only meant to show how the math works.

Download workbook “CalcCPR” from:

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

Downloads Written in Excel 2013

Do you have a sheet that calculates basic P&I payment for fixed rate mortgage, allowing for CPR inputs?

I’m not sure what you need. I will send some links.

Don,

In your Calculate CPR Given Prepayments sheet, why does the Payment amount change every month? Shouldn’t it be fixed?

Moritz,

The payment changes because each month that there are prepayments, loans are leaving the pool of mortgages and a new payment has to be calculated based upon the remaining loan balance.

Don

[…] Reverse Engineering Constant Prepayment Rate (CPR) December 1, 2014 […]