Valuing a Mortgage Portfolio With Cash Flow Analysis

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

=SUMPRODUCT(Rate ,Balance)/SUM(Balance)

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:

=SUMPRODUCT(Rate,Balance,Term)/SUMPRODUCT(Balance,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.

Download “vba_many_loans

 

 

82 thoughts on “Valuing a Mortgage Portfolio With Cash Flow Analysis

  1. A_K

    Hi, great model! I am curious if you could provide the password to me so I can check out the underlying code. I am trying to improve my VBA skills. Thanks so much!

    Reply
      1. Muhammad Kashif Zafar

        Dear Google god has led to your web site . Can you please share password .
        Thanks in advance ,

        Reply
  2. Michael

    Hi Don,
    Could you also share the password with me? I’d appreciate that. Once again, thank you for sharing your knowledge.
    Kind Regards,
    Michael

    Reply
  3. regi athwal

    Hi Don

    interesting model – can i get the password for the VBA, be appreciated

    thanks

    r

    Reply
  4. Quang Le

    Hi Don,
    This spreadsheet is awesome.
    is it possible if you could share the VBA? I really appreciate it.

    Many Thanks

    Reply
  5. T Marbury

    Hi Don, I’d love to try and use this spreadsheet to value my company’s commercial loan portfolio. Could you email me the password? Thanks for what you do!

    Reply
  6. Amitabh Roy

    Hi Don,

    Fantastic website! Helped me a lot to understand cash flow. Can you share the VB macro password please.
    Cheers!

    Amitabh

    Reply
  7. PS

    Hi Don,
    Looks like a pretty good excel model, thanks for sharing. Would you mind sharing the password to the VBA as well?
    Thank you

    Reply
  8. Prince

    Great work! All your files are very helpful. Can you please send me the password? Do you have have same password for all your files?

    Reply
    1. Amstel

      Hi Don,

      Nice model! Could you share with me the password for the VBA file? I am trying to improve my VBA skills.

      Many thanks!

      Reply
  9. Nilza

    This is awesome! However, I do need to value portfolios that have weekly payments. Do you have a model that can help me do that? I also have a pool of loans that require daily payments (5 days/week), but accrues interest on sat and sun as well.

    Thanks!

    Reply
    1. Don PistulkaDon Pistulka Post author

      Nilza,
      I am not familiar with this type of loan. Does it pay down principal with each payment? What is the base year, 360 or 365 or actual days? How many weeks are assumed in each year? When you say “daily payments” you mean weekly payments, correct? Are these short-term loans or longer-term loans? Can they prepay? Is there a penalty for prepayments? Are these flat loans and if not, is there a name for these types of loans that I can Google to find out more about them?

      Don

      Reply
      1. Nilza

        Yes, it pays down principal with each daily payment. A 360 days loan has 249 payments (a payment each day except sat and sun), and 360 days of interest accrued. Most of the loans are 360 days loans, some are 540 days and 270 days. They are technically payday loans. They accept pre-payments. This link might help understanding it better https://www.merchantmaverick.com/reviews/ondeck-review/

        Reply
        1. Don PistulkaDon Pistulka Post author

          Nilza,
          The site was not that much help. On a spreadsheet, please take a 36-month loan at an interest rate of 5%. Calculate the factor rate and construct a simple amortization schedule (P&I) for each day until maturity and send it to me. As you can see in my workbook, an amortization schedule is required on each loan in order to get the total cash flows of the portfolio.
          Don

          Reply
  10. nan yang

    Hi Don

    Really appreciate your great job. would you pls send your password for VAB?

    thanks
    Nan

    Reply
  11. Trav

    Hi Don – Thanks for the great posts. It helps me a lot in learning the concept and VBA code. Can you share the VBA password as well? Thanks again.

    Reply
  12. Connie

    Hello there, I am interested in learning how to do this using VBA. Can you email me the password? Thank you in advance.

    Reply
  13. Justin

    Hello, I am interested in learning how to do this using VBA. Can you email me the password? Thank you in advance.

    Reply
  14. Brian Taylor

    Hi Don please could I have the vba password too as would be a great help for my AMCT treasury studies here in U.K. Great website! Many thanks, kind regards Brian

    Reply
  15. Brian Taylor

    Hi Don please could I have the vba password too as would be a huge help for AMCT treasury studies here in U.K. Great website by the way! Many thanks, kind regards Brian

    Reply
  16. Christer Carlstrom

    Hi
    Thank you for an interesting post and Website.
    I would really appreciate if you would let me see the vba code.

    Reply
  17. Alex

    Hi Don,

    Looks great! Could you please share the VBA code for this? Very interested…thank you very much!

    Reply
  18. Aleks

    Hi Don,
    Looks great, thank you. Could you share the VBA code as well? Will appreciate this.

    Reply
  19. Andrew

    Hello Don,

    Great post, can I please see your VBA code as well? Also, how would you model the cash flows if your pool was a mixture of fixed and floating loans?

    Reply
  20. Andrew

    Hello Don,

    Great post, can I also see the VBA code used in the spreadsheet? How would you model the cash flows if your pool was a mixture of fixed and floating loans?

    Reply
    1. Don PistulkaDon Pistulka Post author

      I will e-mail you the password. You will see how I modeled the cash flows of mixed loans when you see the VBA. The reason I only give the password out to those that asked for it, is that I want to get an idea of how many people are actually interested in it.

      Don

      Reply
  21. Pingback: Testing my VBA Mortgage Portfolio Cash Flows – Excel@CFO

  22. John

    Hi Don Thanks for the great post, very informative. Will you please share the VBA content? I appreciate it.

    Reply
  23. Drew

    Hi Don – great post, this is very helpful. Is there any way to see the vba for this? Would greatly appreciate it.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

WordPress spam blocked by CleanTalk.