Cherry-Picking a Loan Portfolio, Revisited

Readers might remember my original post and spreadsheet 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%.