CPR/PSA Prepayment Speed Calculator Excel Template
CPR/PSA Prepayment Speed Calculator Excel Template
Interactive Excel Financial Model
Download, customize, and integrate into your own analysis
What's Included
- Interactive financial model with live Excel formulas
- All formulas visible and fully editable
- Professional formatting with color-coded inputs & outputs
- Formula reference sheet with variable definitions
- Step-by-step instructions sheet
- Compatible with Microsoft Excel 2016 and later
Download Template
Price range: $0.00 through $20.00
Calculate CPR, SMM, and PSA prepayment speeds in Excel. Includes both direct CPR and PSA-based conversion paths with live, editable formulas.
Online Calculator vs Excel Template
| Feature | Online | Excel |
|---|---|---|
| Instant use — no download | ✓ | — |
| Works offline | — | ✓ |
| Customize formulas & layout | — | ✓ |
| Save & share with colleagues | — | ✓ |
| Integrate into your own models | — | ✓ |
| Print-ready formatting | — | ✓ |
About This Template
Analyze mortgage prepayment speeds with this professional CPR/PSA Calculator Excel template. Whether you're pricing mortgage-backed securities, modeling MBS cash flows, or understanding prepayment risk, this template provides instant conversions between the three key prepayment metrics: CPR, SMM, and PSA.
What You Can Calculate
This Excel template gives you two calculation paths:
- From CPR Input: Enter a Conditional Prepayment Rate and get the corresponding SMM (Single Monthly Mortality) plus the PSA equivalent at your specified loan age.
- From PSA Input: Enter a PSA speed (e.g., 150% PSA) and loan age to calculate the implied CPR and SMM.
- Prepayment Estimates: Both paths calculate approximate monthly prepayment amounts based on your pool balance.
Required Inputs
- CPR Rate: Your annualized prepayment rate (0-100%)
- PSA Speed: Multiple of the standard PSA benchmark (100% = standard)
- Loan Age: Payment month number (1-360)
- Pool Balance: Beginning-of-month balance for prepayment calculation
Methodology
All calculations follow industry-standard formulas:
- CPR to SMM: SMM = 1 - (1 - CPR)^(1/12)
- Standard PSA CPR: min(0.2% x month, 6%)
- PSA Adjusted CPR: Standard CPR x PSA Speed / 100
Assumptions & Limitations
- Uses the standard PSA benchmark designed for 30-year fixed-rate mortgages
- PSA ramp: 0.2% CPR increase per month until 6% plateau at month 30
- Monthly prepayment is approximate (Balance x SMM)
- PSA is a benchmark, not a forecast - actual speeds vary with market conditions
Frequently Asked Questions
CPR is a direct prepayment rate - enter it if you already know the annualized prepayment rate you want to analyze. PSA Speed is a multiple of the standard PSA benchmark (100% = standard). Enter PSA Speed when you want to calculate what CPR that benchmark implies for a given loan age.
One calculates prepayment from your direct CPR input; the other uses the PSA-implied CPR. Use whichever matches your analysis. If you entered a CPR directly, use Monthly Prepay (CPR). If you're working with PSA assumptions, use Monthly Prepay (PSA).
This Excel template focuses on CPR and PSA inputs. To convert SMM to CPR, use the formula CPR = 1 - (1 - SMM)^12 in a separate cell. For example, SMM of 0.5% (0.005) converts to CPR of approximately 5.83%.
A fully interactive financial model with live Excel formulas, an Instructions sheet with usage guide, and a Formula Reference sheet with variable definitions and model assumptions. All formulas are visible and editable.
Microsoft Excel 2016 or later. The template uses standard Excel formulas only — no macros, VBA, or add-ins required.
Yes. All cells are fully editable. You can modify any formula, add your own calculations, change formatting, or integrate the model into your existing spreadsheets.
The online calculator runs in your browser for quick calculations. This Excel template gives you a portable, offline financial model you can customize, save, share with colleagues, and integrate into your own analysis.
This template is provided for educational and personal use. You may use it in your own professional analysis and presentations. Redistribution or resale of the template itself is not permitted.
You can re-download the latest version from your account or by requesting a new download link. Free downloads are limited to 5 per email address per month.
