Financial
Mortgage CPR to SMM formula: Single Monthly Mortality & pool prepayments
Convert annualized Constant Prepayment Rate (CPR) to monthly Single Monthly Mortality (SMM) and model unscheduled mortgage pool principal prepayments.
Why SMM is 1 - (1 - CPR)^(1/12) instead of CPR / 12!
CPR annualizes a monthly prepayment rate: if the same monthly SMM applied for twelve months, the surviving proportion would be (1-SMM)^12. Thus SMM = 1-(1-CPR)^(1/12). This conversion is not a prediction that actual monthly prepayments will remain constant.
Solving for Single Monthly Mortality gives SMM = 1 - (1 - CPR)^(1/12). Importantly, monthly unscheduled prepayment dollars apply to Beginning Pool Balance MINUS that month's Scheduled Principal payment: Prepay$ = (BegBal - SchedPrin) * SMM!
| Pool Prepayment Metric | Formula | 6% CPR ($500k Pool, $4k Sched Prin) |
|---|---|---|
| Single Monthly Mortality (SMM) | 1 - (1 - 0.06)^(1/12) | 0.005143 (0.5143% / month) |
| Unscheduled Prepayment ($) | ($500,000 - $4,000) * SMM | $2,550.93 |
| Inverse: SMM = 1.0% -> Annual CPR | 1 - (1 - 0.01)^12 | 11.36% CPR |
Convert annualized CPR to monthly SMM
A2 holds CPR as a decimal between 0 and 1. The displayed SMM is rounded to six decimals; use the unrounded expression when calculating dollars.
=IF(OR(NOT(ISNUMBER(A2)),A2<0,A2>1),"Invalid inputs",ROUND(1 - (1 - A2) ^ (1 / 12), 6))Calculate monthly unscheduled principal prepayment dollars
Apply unrounded SMM to beginning balance less scheduled principal. Inputs must be numeric; scheduled principal must be between zero and the beginning balance.
=IF(OR(COUNT(A2:C2)<>3,A2<0,A2>1,B2<0,C2<0,C2>B2),"Invalid inputs",ROUND((B2 - C2) * (1 - (1 - A2) ^ (1 / 12)), 2))Convert monthly SMM back to annualized CPR
D2 contains SMM as a decimal between 0 and 1.
=IF(OR(NOT(ISNUMBER(D2)),D2<0,D2>1),"Invalid inputs",ROUND(1 - (1 - D2) ^ 12, 4))Try the example
Use the original values or change them to test your understanding.