This spreadsheet was originally compiled to help make the decision to either sell mortgages that were originated, or keep them. It can also be used to help with the decision to purchase a pool of whole loans, or a securitized mortgage pools. Two different concepts of return are referred to in this post. The two are Holding Period Return or HPR (aka Horizon Analysis), and Internal Rate of Return or IRR.
HPR on a pool of mortgages is calculated with the following formula:
Internal Rate of Return is the interest rate that makes the present value of the total cash flows equal to the initial investment. Excel makes it easy for us to calculate IRR with the built in function IRR(). IRR uses an iteration process that tries different rates of return until it finds a rate that satisfies this equation (as a shortcut I use the Excel NPV function):
One of the major differences between the two is that HPR lets the user forecast what rate cash flows will be reinvested at in the future, while IRR assumes that all cash flows will be reinvested at the IRR rate. The spreadsheet calculates both, and expresses the rates in terms of monthly, semiannual, and annual returns. More on that latter.
The input cells are in yellow (as are all my spreadsheets). After entering the starting principal balance, we enter the gross interest rate. Next is servicing. Servicing is from the standpoint of the owners or purchasers of the pool. If this were a purchase of a pool of whole loans or securitized mortgages, we would enter the servicing rate. That rate would be subtracted from the gross rate is get the net interest rate. In this example, we are assuming that we originated the loans and are now deciding if we want to hold them, or sell them to FNMA. If we keep them (as in our example) the servicing rate is not subtracted from the gross, because we will be receiving the gross rate. If this were a purchase analysis, the servicing fee would be entered. Next is the WAM or weighted average maturity (# Payments).
Now we have some decisions about the future to make concerning this pool of mortgages. First, how fast will it prepay principal? The drop down box to the left lets you choose either PSA or CPR. Click the link for more information on PSA & CPR. Next down is the default assumptions, either CDR or SDA. See these links for descriptions of CDR or SDA. To the right of each is your assumption rates. Next is the loss severity. That is the percent of the defaults that is assumed to be a loss. The last cell is the price paid for the pool. In this case we originated the pool, so the dollar price is par ($100.00).
Now we have to input the time we are going to keep the pool, or even if the pool isn’t sold, the value is marked-to-market. In this example we have 36 months. We are going to assume that all cash flows will be reinvested at 5%, and the market bid for the remaining term of the pool will be 6.5% (250 basis points higher than now). The market value in three years is determined by taking the present value of the remaining cash flow, discounted at 6.5%.
Prepayments, defaults, and loss severity are not used in the market value calculation of remaining cash flows, because it is assumed that a market bid for this pool will be risk adjusted and adjusted for any prepayments.
Now we take a look at the numbers needed to calculate HPR over the 36 months:
And now the monthly compounded HPR and the IRR (red box).
Due to the assumed 5% reinvestment rate, the HPR says we lost 2.23% annually on a monthly compound basis over the 36 months, while the IRR shows an annual loss of 3.34% on the same monthly compound basis. In this case, we would sell all the loans we originate.
All spreadsheets are created in Excel 2013