Cherry-Picking a Loan Portfolio, Revisited

Readers might remember my original post and spreadsheet http://pistulka.com/Other/?p=1473 in August 2015 called Cherry-Picking a Loan Portfolio.  That spreadsheet relied upon going through a loan portfolio and placing an X next to the loans you were interested in purchasing. It was one way I used to purchase participation loans from other credit unions. They would show their portfolio of loans, and we would offer to by a portion of them.

The problem with the process of placing a mark next to good loans was you had to look at each loan separately. If you were looking through thousands of loans, it could get tedious. I used the old worksheet, before sliders could be used on tables in Excel. This new Cherry-Slicer uses slicers to break down the loans into categories. In this manner you can save a lot of time by eliminating loans you are not interested in.. The Excel workbook “CherrySlicer” can be downloaded at the link.

The sample loan portfolio used in the workbook has 1504 loans. My sample does not have all the information you might want to see before picking a subset that you would like to bid on, but this is only a prototype that you can use to make your own model.

First I took the loan data and created a table called “Portfolio”. Then I added two helper columns, one to reduce the individual loan balances to even thousand dollars, and  a column that extracted the year from the maturity date of the loan:

Then I created the four slicers below. In order to include loans that fit the buyer’s needs for length of maturity, size of loans, the interest rate on the loans and servicing.

The chosen loans might look like below. Only the loans that match the buyers requirements are colored. Loans that are eliminated are in white.

All along, as loans are eliminated from the list, a description of the loans chosen appears, along with average weighted data:


The pricing formula is automatically given the average weighted data (blue cells) and the yellow cells are user expectations. For this example, the chosen loans, at a 95% participation rate ($120,680,793), would be worth $117,293,283 at a yield to the buyer of 3.5%.

Download: “CherrySlicer”

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.

6 Comments

  1. Hi Don, First of all I want to thank you for sharing your knowledge. Your website is really great and its really fun to read all of your posts.

    Thanks for replying back to me. I ended up reading about each term, CDR loss severity, ect.. I have been quantifying those risks & others on a per loan basis. Having a model (your cherry slicer) will save hours on the front end of analyzing pools of loans that I am looking at right now.

    I sent you and email a few days ago & also I never received your email (left put in my gmail this time). I would like to send you an email to ask few other questions.

    best
    Dennis

  2. Can I get the password
    I was wanting to add in LTV and FICO scores

    thanks
    Dennis

    thank you so much for creating this site its great!

    1. This is great, I wish I would have found you a few years ago. I just sent you an email.

      What does the “days delay” mean and is the CPR calculated into the pricing formula Vs the loss severity (and how is that calculated into the formula? Do you have the password so I can look under the hood?
      thanks
      Dennis

      1. Hi Dennis,

        I sent you an email with an attachment that has most of what you are looking for.

        Days Delay refers to the grace period on most mortgages. Typically, there is a 14 day grace period. Although the lender expects the mortgage payment at a certain date each month, the lender normally gives the borrower until the 15 of the month to make the payment. This obviously affects the total return on the mortgage. Mortgage backed securities will also pass on the payments with a delay period. Mortgages pay in arrears, so the normal Days Delay would be 30 days (default) if all payments came in on time. A grace period until the 15th of each month would have a 44 day delay (30 + 14).

        The loss severity is a component of the defaults (CDR)expressed as a percentage of the defaults. If a loan defaults, the servicer will take back the home, make any repairs needed to resell the home, including real estate fees. The loss severity is a best guess, based on the lenders experience and market resale conditions.

        As far as the VBA on Cherry-Picking a Loan Portfolio, the VBA is only to reset the slicers, and it is not protected.

Leave a Reply

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