Breaking Down the Mortgage Mega-Formula

Additional update to formula:

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

This post will probably not interest the average reader, but I have been so fascinated by this 40 some year old formula that I stuck away and have recently rediscovered. The formula fits in one Excel cell, calculates the present value (price) of a fixed rate pool of mortgages at a rate that differs from the interest rate (WAC). That by itself would be cool, but this formula handles servicing, CPR, and days delayed in payments also.

The breakdown takes place on six sheets:

1. Inputs Plus Original Formula: This sheet is the only sheet that will accept the mortgage pool information needed to calculate the formula. It also shows the original formula and the price derived from the input cells, which are yellow. A linked picture of the input data is placed on all five of the other sheets.


Breakdown22. Step #1 Reduce Formula: As the name of the sheet implies, I have reduce the length of the formula by approximately 50%. I did this by renaming the input names that have to be converted to a monthly basis. A picture of these new names for monthly data are placed on each of the other sheets.



Step #2 Start at the Beginning: This sheet starts with the above formula, with the CPR, Servicing, and Days Delayed, stripped out. The results are two simple ways to explain calculating the price of a pool of mortgages, as long as there are no prepays, no servicing, and no payment delays.



Step #3 Add CPR (SMM): The reason I used SMM for the name of the monthly CPR is that this is the commonly used name, standing for (Single Monthly Mortality). I want to point out that the addition of SMM is the most complicated part of the formula. After breaking down the formula into steps that are similar to the simple formula, I found that the two most complicated parts of this new formula were actually derivatives of graduated annuity formulas. If you have not read my post on Graduated Annuities, you can follow this link.  


The above part of the formula is equal to the net rate of a graduated annuity: Net Rate = (1+Y)/(1-SMM)-1. Then using the net rate we calculate the present value of an ordinary annuity and dividing the present value by 1-SMM. =PV(Net_Rate,p,-1,0,0)/(1-SMM).


The other complicated formula, above, also was a derivative of the graduated annuity formulas, but not as straightforward. Normally graduated annuities use an interest rate and a grow rate. In this case, I had three rates to consider, the interest rate (WAC), the Yield, and the CPR. After playing with the formulas I found the ones that worked. =((1+Y)/(1-SMM)/(1+IR)-1) for the net rate and =PV(Net_Rate,p,-1,0,0) for the present value, divided by (1-SMM)/(1+IR).

The use of graduated annuities makes since, when you consider that an amortizing loan with a CPR, is an annuity that grows at a negative rate.

Below is the diagram of adding SMM:


Step #4 Add Servicing: Servicing only shows up in two places in the formula. The diagram below shows the places servicing was added in red:7

Breakdown7Step # 5 Add Days Delayed: This was the last step and the easiest. The formula is at the end of the mega-formula and takes the present value (price) of all the cash flows that preceded it, which were all even months, and calculates the present value of the partial month, but without compounding.


Download “Breakdown


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. Hey Don – Is it possible to use this same approach to calculate the MSR value? I saw your other post using a user defined formula for MSR value, curious if it can be done with a mega formula.

    1. I am sure it can be done, but my VBA formula is simpler. I found the formula in the early 70’s and held on to it. I do not know the person that wrote it. It was probably someone at Salomon Bros. In those days, few people had access to computers.

  2. Hi Don,

    is it possible to rearrange your incredible mega mortgage formula, so that it calculates the ending balance (for example as a % of the original amount) for any given payment period?

    For example based on your cashflow schedule on the “original proof” worksheet in the totalmega.xls, if i input payment # 10 into the formula, it would give me $906617,47 / $ 1000000 = 0,90661747% ?

    Thanks in advance for your reaction (and for the good work!).
    I work in the finance department of a small mortgage & savings bank in the Netherlands.

    Paul van de Berg

Leave a Reply

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