Structured Finance & Mortgage-Backed Securities
Agency MBS PSA Prepayment Ramp & Cashflow Formula
Calculate SIFMA PSA Seasoned Conditional Prepayment Rate (CPR), Monthly Single Monthly Mortality SMM = 1 - (1 - CPR)^(1/12), Unscheduled Principal on Post-Amortization Balance, and Total Principal.
Apply SMM to post-scheduled balance—and ramp 100% PSA linearly across months 1 to 30!
Newly originated residential mortgages prepay slowly at first before reaching full refinancing/relocation turnover. Under the SIFMA / Public Securities Association (PSA) benchmark, 100% PSA starts at 0.20% annualized CPR in Month 1 and adds +0.20% per month until reaching 6.00% CPR in Month 30+, scaled proportionally by (PSA_Speed / 100): CPR_t = (PSA_Speed / 100) * MIN(0.06, 0.002 * Loan_Age_Months)! Converting CPR_t to Single Monthly Mortality SMM = 1 - (1 - CPR_t)^(1/12) exposes a classic structured-finance interview trap: SMM ALWAYS multiplies (Beginning_Balance - Scheduled_Principal)—NEVER raw Beginning_Balance, because scheduled principal paid that month cannot also prepay!
Verify on Beginning_Pool_Balance = $10,100,000 (B2), Scheduled_Monthly_Principal = $100,000 (leaving $10,000,000 post-scheduled balance, B3), Loan_Age_Months = 15 (mid-ramp, B4), and Quoted_PSA_Speed = 100 (100% PSA, B5): Annualized CPR is (100/100) * MIN(0.06, 0.002 * 15) = 0.0300 (3.00% in E2); Monthly SMM is 1 - (1 - 0.03)^(1/12) = 0.002535 (0.2535% in E3); Unscheduled Prepayment is $10,000,000 * 0.002535 = $25,350.00 (E4); and Total Principal Cashflow is $100,000 + $25,350 = $125,350.00 (E5)!
| Agency MBS PSA Waterfall ($10.1M Pool, $100k Sched, Month 15, 100% PSA) | Formula | Verified Output |
|---|---|---|
| PSA Seasoned Annualized CPR | (PSA / 100) * MIN(0.06, 0.002 * Age) | 0.0300 (0.0600 @ Month 40) |
| Single Monthly Mortality SMM | ROUND(1 - (1 - CPR)^(1/12), 6) | 0.002535 (0.005143 @ 6.0% CPR) |
| Unscheduled Prepayment ($) | (Beg_Bal - Sched_Prin) * SMM | $25,350.00 ($51,430 @ 200% PSA) |
| Total Monthly Principal ($) | Scheduled_Prin + Prepayment | $125,350.00 |
SIFMA PSA Ramp Annualized CPR & SMM Formula
Guard CPR >= 1 ("Invalid PSA Speed"—speeds >= 1,667% PSA on seasoned pools imply >= 100% annualized CPR!) and validate Beginning_Balance > Scheduled_Principal >= 0!
=LET(bg_b, B2, sc_p, B3, ag_m, B4, ps_a, B5, IF(OR(bg_b <= 0, sc_p < 0, sc_p >= bg_b, ag_m < 1, ps_a < 0), "Invalid PSA Speed", LET(cp_r, (ps_a / 100) * MIN(0.06, 0.002 * ag_m), IF(cp_r >= 1, "Invalid PSA Speed", ROUND(1 - ((1 - cp_r) ^ (1 / 12)), 6)))))Seasoned Plateau (Age >= 30) vs 200% PSA Equivalence Check
Verify that Month 40 at 100% PSA (B4 = 40) and Month 15 at 200% PSA (B4 = 15, B5 = 200) both hit 6.00% CPR (SMM = 0.005143 -> $51,430.00 unscheduled prepayment)!
=IF(ISTEXT(E3), E3, ROUND((B2 - B3) * E3, 2))Try the example
Use the original values or change them to test your understanding.