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) that price given yield, using Bloomberg’s MBS calculator, was slightly different than the mega formula. After looking at an actual cash flow from an amortization schedule, it became apparent that the difference was in the assumption of how to treat the first odd payment. When you enter any number of days delayed, other than 30, you create an odd (fractional) payment.

The difference in how the fractional payment is handled is not new. When I was trading bonds, the U.S. Treasury had a different price formula when auctioning bonds and notes with odd first payments, than the “street” convention formula. The question is, should the fractional payment period be included in the compound present value formula, or should it be handled separately with no compounding.

The original mega formula handles the fractional period with no compounding, while the Bloomberg calculator obviously assumes compounding.  An adjustment to the last part of the original mega formula will allow it to match the street convention:

The original mega formula vs. the street convention formula (see red portion)

_p =(100*(((1+_c/12)^(_n*12)*((1-(1-_j/100)^(1/12))+_c/12-_s/12)*(1-((1-(1-(1-_j/100)^(1/12)))/(1+_y/12))^(_n*12))/(_y/12+(1-(1-_j/100)^(1/12)))+(_s/12-(1-(1-_j/100)^(1/12))*(1+_c/12))*(1-(((1-(1-(1-_j/100)^(1/12)))*(1+_c/12))/(1+_y/12))^((_n*12)))/(_y/12+(1-(1-_j/100)^(1/12))+(1-(1-_j/100)^(1/12))*_c/12-_c/12))/((1+_c/12)^(_n*12)-1))* (1+_y/12)/(1+_d/30*_y/12) )
_p =(100*(((1+_c/12)^(_n*12)*((1-(1-_j/100)^(1/12))+_c/12-_s/12)*(1-((1-(1-(1-_j/100)^(1/12)))/(1+_y/12))^(_n*12))/(_y/12+(1-(1-_j/100)^(1/12)))+(_s/12-(1-(1-_j/100)^(1/12))*(1+_c/12))*(1-(((1-(1-(1-_j/100)^(1/12)))*(1+_c/12))/(1+_y/12))^((_n*12)))/(_y/12+(1-(1-_j/100)^(1/12))+(1-(1-_j/100)^(1/12))*_c/12-_c/12))/((1+_c/12)^(_n*12)-1))* (1+_y/12)/(1+_y/12)^(_d/30) )

The difference is not a matter of the right vs wrong way to handle fractional periods. The mathematical purist might argue for no compounding, but you can’t fight the street calculation if you are going to trade MBS.

I have made changes to two workbooks:   Breakdown and Am_vs_Mega



One thought on “Conforming the “Mega” MBS Formula to Street Conventions

  1. Pingback: Mortgage Pool Price and Average Life One Cell Formulas – Excel@CFO

Leave a Reply

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

WordPress spam blocked by CleanTalk.