**See updated formula at:**

__MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity__

__http://pistulka.com/Other/?p=2384__

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:

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

Downloads Written in Excel 2013

Pingback: Mortgage Pool Price Given Yield, Amortization vs. Mega-Formula | Excel@CFO

Richard VestHello 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?

-Richard

Don PistulkaPost authorRichard,

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?

Richard VestSure Don. What’s the best way to attached the spreadsheet? Do you have a email address I could send it to you as an attachment?

Don PistulkaPost authorUse blog@pistulka.com

ErikI 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.

Don PistulkaPost authorErik,

Try it again. I fixed the link.

Don