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.
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:
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).
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.
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 “.