See updated formula at:
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