This Excel spreadsheet could help small to medium size banks, credit unions, and mortgage investors, to evaluate their mortgage portfolio. It could also be used for commercial and consumer loans as long as the user understands that the model uses a 30/360 mortgage day count.
The difference between valuing a portfolio of mortgages and mortgage backed securities (MBS) is that MBS contain a pool of mortgages with the same or close to the same interest rates and maturities. That way the average weighted coupon and maturity can be entered into an internal rate of return calculator (see Market Price given yield or Market Yield given price) with a high degree of assurance. In the case of a portfolio however, some of the loans can have rates and maturities spread over a wide range. A simple average weighted two factor rate
doesn’t take into consideration that some of the shortest maturities might have the highest rates, or the lower balances might have lower rates, or some combination that reduces the reliability of the average rate. This can be overcome somewhat with the use of a three factor average weighted rate, using the additional factor of term:
This increases the accuracy of the rate somewhat, however I personally prefer having the internal rate of return of the actual cash flows of the entire portfolio. In addition, the three factor formula does not consider different Constant Prepayments (CPR), defaults (CDR), loss severity, balloon payments, and periods of interest only.
The inputs to the spreadsheet are in yellow cells and look like this:
I included 800 loans using the randbetween() function for rate (APR), term, and amount. Theoretically, over a million loans could be entered using Excel 2007 – 2016, but the VBA subroutine will get slower the more loans you use. The 800 loans, with terms as long as 360 months, takes about 10 seconds to calculate the total cash flow for all loans, using my computer. That includes having other programs running. A portfolio of 800 shorter term loans (less than 84 months) took about two to three seconds. Your results will vary.
Remember that neither a balloon or interest only month can be longer or equal to the term. If you enter a longer or equal month for either one, conditional formatting will change the row to a dark red fill.
To the right of the input data are the cash flow results, with the internal rate of return. In this example 3.3826%. This translates to a dollar price of $100. The green cells with cash flow will be filled until the last payment of the longest maturity. In this case, 360 rows.
After changes are made to the yellow input cells, click the “Clear” button to clear the cash flows and then “Run” button to calculate the cash flow.
The 3.3826% internal rate of return in this example, is based on the total current balance of the portfolio and, as I mentioned before, would be expressed as a price of par ($100). Even though you might have entered risk factors (defaults and loss severity) for each loan, market rates may have change. You can enter the days delayed (default is 30) and the current market yield, to get a market price. This would be particularly helpful if you were looking to purchase or sell this portfolio.