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

I have made adjustments to the MBS mega formula that calculates the price of a mortgage-backed security. It now allows for default rates (CDR) and loss severity.

In the past I have made other adjustments and I have included a review of these adjustments in the spreadsheet linked below.  I’m not sure who will use this new formula, since I have published a number of spreadsheets that allow for Default Rate (CDR) and Loss Severity when calculating the price of Mortgage-Backed Securities. They include both VBA functions and amortization schedules.

A math formula allows for the calculation of an MBS price in one Excel cell, however the formula is much larger than the original mega formula:

Given:

Download “TotalMega

 

 

18 thoughts on “MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity

  1. David

    The Financial Standards Accounting Board is requiring major changes to lease accounting on the part of the lessee in late 2018 and 2019. The changes are reflected in the FASB ASU #842. Leases under 12 months are not subject to the new accounting rules. The vast majority leases are the operating type.

    Would you please design an Excel spreadsheet to record the amortization of a group of leases under the new FASB accounting standard?

    Thank you,

    David

    Reply
    1. Don PistulkaDon Pistulka Post author

      David,
      I am not a CPA, nor am I familiar with lessee accounting. If you would send me an example from your outside CPA firm on how the new accounting would look, possibly I could put something together for you.

      Send to blog@pistulka.com
      Don

      Reply
  2. Les

    Don, I’ve noticed that none of your MBS valuation models consider the GFee charged by the agency. Shouldn’t that be considered?

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

    1. Don PistulkaDon Pistulka Post author

      Les
      The guarantee fee is part of the servicing fee. For example, the actual servicing might be 25 basis points and the guarantee fee another 25 basis points. You would enter under servicing fee 50 basis points.

      Don

      Reply
      1. Les

        Thanks Don, I see that the math works out when the Gfee is added to the servicing. I’m not sure if there might be some slight difference in the timing when the agency funds the pool for defaults, but its probably not intended to be that exact,

        Reply
          1. Les

            Thanks, I was referring to the agency (Fannie Mae, Freddie Mac) funding the security pool for defaults, which is different than the default recovery that you’ve modeled. There is no un-recovered principal (since the agency takes care of that), just a delay in receiving the payment for the default.

          2. Don PistulkaDon Pistulka Post author

            Les,
            Send my an example of a “security pool for defaults” and I will try and include the pool inside an amortization schedule.

            Don

  4. Pingback: Mortgage Pool Price Given Yield, Amortization vs. Mega-Formula – Excel@CFO

  5. Pingback: Breaking Down the Mortgage Mega-Formula – Excel@CFO

  6. Pingback: Conforming the “Mega” MBS Formula to Street Conventions – Excel@CFO

  7. Pingback: Mortgage Backed Securities (MBS) “MegaFormula” – Excel@CFO

    1. Don PistulkaDon Pistulka Post author

      Tristan,
      I don’t have any models for estimating loss severity. At the credit union, the head of mortgage lending used an average of 10%. That was based on California laws and his experience. That would include fixing up a repossessed home, realtor sales fees, etc. It would also depend on expectation about the housing market. An experienced mortgage lender would have a good idea of what the average loss severity would be for his/her market.

      Don

      Reply
  8. kumar pimpale

    Hi Don,

    I am a newbie in a corporate Job and am learning the ropes in mortgages. I want to calculate IRR for a pool of loans, would you able to help me?

    Reply
    1. Don PistulkaDon Pistulka Post author

      Kumar,
      Can you tell me how many loans are in the pool and are the interest rates and maturities close to the same? Maybe you could send me a sample of the loans in the pool. It would be easier for me to give you the best method if I can see the loans.

      Reply

Leave a Reply

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

WordPress spam blocked by CleanTalk.