## Mortgage Backed Securities (MBS) “MegaFormula”

See updated formula at:

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

http://pistulka.com/Other/?p=2384

This post is just to show you 20-somethings what we had to go through to calculate the price of a mortgage backed security back in the day. I had the first desktop computer (before Apple and Radio Shack were available), the Commodore PET (Personal Electronic Transactor). I had 8K of memory (not 8 gigabytes, 8K) and tape storage. There were no spreadsheets available at that time, just  “Basic” (Beginner’s All-purpose Symbolic Instruction Code). It was a relatively easy shift for me to VBA when the time came, because VBA was built off Basic.

As you can imagine, conserving memory was very important. I am not a mathematician and I don’t remember were I found it, but this is the formula I used to calculate the price of an mortgage backed security (MBS) including a constant prepayment rate (CPR) and servicing fees:

 Price = (100*(((1+c/12)^(n*12)*((1-(1-j/100)^(1/12))+c/12-s/1200)*(1-((1-(1-(1-j/100)^(1/12)))/(1+y/12))^(n*12))/(y/12+(1-(1-j/100)^(1/12)))+(s/1200-(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))

I thought it was pretty slick. This formula is obviously written in one cell on an Excel spreadsheet, not math symbols as I originally found it. It looks different in a basic program. When I needed yield given price, I built an iteration that narrowed in on the answer after 25 passes and after over a minute or two of calculating.

The workbook “Formula” uses goal seek to solve for yield.

A few explanations.

1. The “mortgage rate yield” is the market yield, expressed on a monthly compound basis.

2. The CPR is enter as above, not as a percentage.

3. Mortgages are paid in arrears, so a regular loan’s payment is due 30 days after it is issued. If you want to include the 14 day grace period (until the 15th of the month), you would enter 44 (30+14). Each MBA issuer has it’s own number of delay days before you are paid. A small list is included on the spreadsheet.

4. Service fee is the amount of interest the issuer keeps for servicing the loans. You can also add in guarantee fees here. Although the default is entered as .25, it is actually 25 basis points or .25%. The formula adjusted the number to a percent.

Try it just for fun!

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

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. Hey Don,

I work in capital markets, focused on mortgage pipeline risk. I think this template is awesome, I was just wondering how you would incorporate a credit piece and the Gfee?

1. Hi Joe,
That formula was just to show it could be done in one cell. To incorporate other factors, I would use VBA. My price given yield UDF for a mortgage pool can be found at http://pistulka.com/Other/?p=2282

You can use this UDF and make adjustments for other factors.
God Bless,
Don

1. Don , I knew you were a genius…just not cut out as a bond salesman (with all the frustrations therein). I hope you are well. Best wishes. Wally Hall

2. […] Mortgage Backed Securities (MBS) “MegaFormula” November 17, 2014 […]

3. […] Mortgage Backed Securities (MBS) “MegaFormula” […]