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

Comments

  1. joe says:

    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. Don Pistulka Don Pistulka says:

      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

  2. Tom Si says:

    I checked that it produces the same result as http://pistulka.com/Other/?p=127 does. So no need to bother to understand it. It just a sum formula for the payment series.

    1. Don Pistulka Don Pistulka says:

      Tom, take a look at “Breaking Down the Mortgage Mega-Formula” http://pistulka.com/Other/?p=1632 for more information on the mega formula.

      1. Walter hall says:

        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

        1. Don Pistulka Don Pistulka says:

          Duncan,
          I will send you the password
          Don

Leave a Reply

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

*
*

WordPress spam blocked by CleanTalk.