Calculating Historical CPR

There is a newer post that replaces the amortization schedule to calculate historic PSA with a UDF:   Back in December of 2014 I wrote a post and Excel spreadsheet called Reverse Engineering Constant Prepayment Rate (CPR). I called it that because instead of giving a CPR to a model, we could do the reverse and calculate the […]

Certificate of Deposit Ladder Optimizer

There are all kinds of calculators you can find to “optimize” your rate of return by laddering certificates. Of course the term optimize is a misnomer because you don’t know what the reinvestment rate will be on the shorter term CDs.  There are some advantages to laddering such as, if you need the funds earlier than expected, you can […]

Conforming the “Mega” MBS Formula to Street Conventions

See updated formula at: MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity This adjustment to the mega formula for mortgage-backed securities only affects those that use an odd first payment, by entering an amount other than 30, in the “Payment Days Delay” cell. It was pointed out by Win Smith (The Well-Tempered Spreadsheet) […]