I have an updated version of this post. Click here http://pistulka.com/Other/?p=3009:
Have you ever wanted to cherry-pick a portfolio of fixed rate mortgages or throw out the mortgages you don’t want from a pool? That is what this spreadsheet called “Cherry“ is for. A picture of the data needed for the calculations is shown below:
Here is a list of the more important advantages of this spreadsheet:
1. It uses a table. You can add as many columns and rows as needed and the table will expand to include the new data. Also, if you want to change the names of the titles, all formulas that use that name will change to the new name. Also, you can sort the loans based on your preference (i.e. highest rates or shortest maturity) to make it easier to select loans.
2. Click on the box called “Activate the Selection X”. To pick a loan you click on any cell in the “Check” column (column B). An X will appear in that cell and that row changes to a light orange color. Clicking the X cell again will remove the X. Simultaneously, the pertinent cumulative data is calculated including the sum of the checked loans, the average weighted data, and the term. The same data for the remaining unchecked loans is also calculated.
3. You enter the settlement date for the sale of the checked loans, a CPR, the yield that you need, and the percentage participation of the loans. With this information a clean price (not including accrued interest, if any) is calculated and the your cost. As you pick more or less loans, the price and total cost changes. The price is the mortgage price formula I introduced in the post Mortgage Backed Securities (MBS) “MegaFormula”.
4. The Default Days Delay in cell J2 is the number of days between payments. When you consider the grace period for the loans, you might want to add a few days to more accurately reflect the average time the payment is actually received. Any extra days will automatically reduce the price.
One warning: Before you sort or filter the table, uncheck the “Activate Selection X” box.