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.

PET

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!

 

Download workbook “Formula” from:

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

Downloads Written in Excel 2013

8 thoughts on “Mortgage Backed Securities (MBS) “MegaFormula”

  1. joe

    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?

    Reply
      1. Walter hall

        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

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

  3. Pingback: Cherry-Picking a Loan Portfolio | Excel@CFO

Leave a Reply

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

WordPress spam blocked by CleanTalk.