Mortgage Loan Pool – Default Recovery

For those that follow my blog, you know that I have used amortization schedules extensively in my analysis. This post and spreadsheet takes a closer look at the default and loss severity assumptions. In previous spreadsheets, the defaults and loss severity have taken place in the same month. In other words, there has been no delay in recovering the net portion of the default principal. When using this type of analysis as a pricing tool for a pool of mortgages, a delay in recovery of the defaults can make a significant change in the price you might what to pay for a pool of whole loans, or when analyzing a pool in your own portfolio.

As you might recall, pricing a pool of mortgages using an amortization schedule,  requires taking the present value of the various cash flows produced by the pool. Let’s assume that we are interested in purchasing a pool of whole loans with servicing retained by the seller. The inputs cells (yellow) might look like this:

 If you are not familiar with the inputs, take a look at some of my past posts. The new entry in the red circle is an estimate of how long it will take for the defaulted properties to be repossessed, fixed up, and sold. Depending on which state the property is located, it could take three to six months, just to take the property back.

Comparing the present value of the cash flows at a discount rate of 4.75%, first without considering the delay:

And then assuming a six month delay:

The delay in this case would reduce the value of the pool by approximately $66K.

The method I used on the spreadsheet was to calculate the net return after losses in a separate column, delayed by the number of months entered. Then use this column to calculate the present value of defaults, less losses. I used the formula below to capture the cash flows, in this case four months prior:


I used the index function to find the proper row (row one will always be the 19th row). So we subtract the losses from the default for each month ($H$19:$H$378-$I$19:$I$378), starting with the current row (row()), minus the number of months delayed, minus 18. If the current month is <= the months delayed, the recovery for that month is zero.

The first few rows look like this:Download Spreadsheet “DelayRecovery.xlsx

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 – this is very helpful. I analyze non performing pools that are either already in 1000 days delinquent and or already in foreclosure stage. Do you have a model that would run these thorough liquidation? I came up with average days in foreclosure for each state (judicial vs non judicial) so I can plug in an assumtion. To be more accurate, i can add the estimation of the property value at the time of liquidation to reflect net proceeds of each property. I just don’t know how to tie it all together. Any advice ? Thanks!!

  2. Loving your “Ratio_Charts.xlsm.” I added a second line graph to make it a 4-year chart. Any chance at getting the password so I can tinker with the macros so they don’t reset it to the 3-year mode when the macro is run?

    Love your work! I discovered your page 2 days ago and read through like 5 pages of prior posts. I thought I was pretty sharp on finances until I read some of your more “mathy” stuff!

    David Borsos
    Controller, Granite Credit Union

Leave a Reply

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