Mortgage Pool Price and Average Life One Cell Formulas

See updated formula at:

MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity

A few posts back I showed the “megaformula” I used back in the day for calculating the price of a mortgage pool with prepayments (CPR). Rather than treating the one cell formula as just an interesting antique, I wanted to show how it might be useful. In order to make it useful, I needed a way to show what happens to the actual term of the mortgage as the CPR changes. I could have used VBA, but I am trying to avoid it. What I needed was an average life formula that would also fit in one cell.

Thanks to Win Smith (The Well-Tempered Spreadsheet) the math had already been done. I just needed to put it together in one cell.

First there is a Price Table with vertical yields and horizontal CPRs. As always, all yellow cells are inputs, so you can make changes.

The second one on the same sheet is an ALM (Asset Liability Management) interest rate shock, plus and minus 400 basis points. The chart shows the negative convexity of the pools value. As the market value increase tappers off when rates are falling, the average life drops quickly.

See what you can come up with one cell formulas

Download workbook “FormulasInOneCell” from:

Downloads Written in Excel 2013

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.


  1. Hello Don-

    I am trying to create an effective spreadsheet for hybrid arms. I have brought most cells in from Bloomberg and am working with Bloomberg API but they can’t figure out how to create book yields with varying cpr rates in the . Would it be possible to collaborate with you on this?


    1. Richard,
      I am not sure what you mean by varying CPR rates. PSA has varying CPR rates (see my publication “PSA vs. CPR), but it sounds like you what to crate your own vectors. If you are using an amortization schedule, you could create another column that would change the CPR speed.
      Can you send my an example what you have so far?

  2. I am having difficulty downloading this file, which I would find extremely useful. Is there an alternate download?

    Getting this message: The requested URL /Excel_Shared/FormulasInOneCell.xlsx was not found on this server.

    Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *