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

One thought on “Mortgage Loan Pool – Default Recovery

  1. David Borsos

    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 *

WordPress spam blocked by CleanTalk.