Home » Excel Spreadsheet » Mortgage Pool Pricing Tool

In April I publish an amortization schedule and I did a poor job explaining what it could be used for. Amortization Model – All Variable was the post and AllVary is the spreadsheet. I added some more options to the spreadsheet, like:

1. A Yield/Market Value/Price table.

all_5

 

2. Interest Only

3. Amortize out to 40 years.

If you are looking to purchase a pool of mortgages, or just want a simple amortization schedule, this one will do it all. A simple amortization input would look like this:

all_0

This will give you a $10,000,000, 6.0% loan schedule, amortize over 30 years. The data in the “Start Here” area is the loan amount with no servicing, no balloon payment, an no interest only. If the rate on the loan would change (for some reason), there is room for five changes throughout the life of the loan, as long as the “Term (Months)” column in the green area, adds up to the months the loan will be amortized over. In the above case, the rate will remain at 6.0%.

There are three other boxes that say “Term (Months)” in three different colored areas. Row 10 sums all three of these columns and they must all add up to the same months, so in this case we just add 360 to each column with no other data.

Now lets take a look at a $10,000,000 pool of mortgages that we expect to have prepayments, and defaults. You design the CPR, depending upon the experience of other pools from this seller or other factors. Likewise, the default and severity schedules can also be estimated throughout the remaining life of the pool. In all cases, the sum of months will equal the same as the green term sum (in this case 360 months).

all_3

Given this structure, you now go over to cell T6 and enter the yield you are willing to accept for this pool, remembering that you have already risk- adjusted the pool with  the default and severity numbers. A 6.0% yield would mean you would pay $9,845,199 for this pool. The Internal Rate of Return in T9 is the IRR after losses. It is the rate of return if you paid par (price of 100) for the pool.

all_4The price at other yield levels shows up in the data table:

all_5

This is not an Excel data table, so you can change the yields in the yellow cells  and a new price will be calculated. I hope this helps. Remember the other options of servicing, balloon payment, and interest only loans are also possible with this spreadsheet “AllVary “.

 

 

 

4 thoughts on “Mortgage Pool Pricing Tool

  1. stuart levin says:

    We are in the process of developing a pricing tool for auto loans based on the various assumptions, WAC, average life, servicing, amortization of paid premium over average life, severity and CPR. Do you have a spread sheet which could help in our development. Thank you kindly for your consideration.

    1. Don Pistulka Don Pistulka says:

      Stuart,

      How far along are you. Can you provide a sample of what you have so far? Are you pooling the loans or pricing individually?

      Use the blog@pistulka.com e-mail address to reply.

      Don

  2. Verónica Ancieta says:

    Great Spreadsheet, just one question, why you add the ammount corresponding to “defaults”. I understand that the default ammount represent the money you don’t receive because the credit risk of the mortgage pool….

    1. Don Pistulka Don Pistulka says:

      Hi Veronica,
      The default amount is not the “amount of money you don’t receive”. When the property defaults and is repossessed it is sold. The difference between the amount of the loan balance and the sale proceeds is the amount lost. That amount is the “Loss Severity” (entered as a percent of the defaults). Therefore, when you are looking for the amount you might want to pay for a pool of loans, you can estimate the credit loss using both the default rate and the loss severity.

Leave a Reply

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

*
*