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:


Download “TotalMega



Don Pistulka
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. Hi Don – can your mega formula be applicable to interest only loans? If so do you have an example to share? thank you!

  2. 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,


    1. 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

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

    1. 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.


      1. 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,

          1. 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. Les,
            Send my an example of a “security pool for defaults” and I will try and include the pool inside an amortization schedule.


  4. Don – do you have any models to actually calculate loss severity itself?

    1. 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.


  5. 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?

    1. 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.

      1. Hi Don,
        Would you care to share any reading on the types of methods used to calculate IRR on a pool of loans.
        Im setting up a leding fund in Peru to service mortgage backed business loans, tickets averaging $100k, we expect to get some 30 deals this first year. Maturities will be in the 2 -4 year range.
        I wonder what you would recommend not to do in the model.
        Thank you in advance.

Leave a Reply

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