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“
Hi Don,
Very helpful post. Is there any way to see the VBA for this? Trying to build it into my skillset.
Thanks
James,
It is on the way.
Don
Hi Don,
Could you please share the VBA password?
Thank you
Check your email
Don
Hi Don,
Very useful post. Is there anyway I can have the VBA password for it?? Regards, Sanjeev
I will email it to you
Don
Hi Don,
Can you please share the VBA password ? I want to check the pricing algo you are using.
Thanks
Sam
Done
Hi Don.
Thanks for sharing your insights. Can i get de VBA Password please. It will be very useful for us.
Best regards.
Diego,
It is on the way.
Hi Don,
Thanks for the great post!
Would you mind sharing the VBA pass?
Cheers!
It is on the way.
Don
Hi Don,
can you please share the VBA password?
Thank you very much!
Already sent
Don
Hi Don – could you please share the VBA for this?
Thank you very much!
Check your email
Don
Don, and chance I could get the password?
Justin,
It is on the way.
Don, any chance I could get the password to edit?
Hi Don. Thank you for the post, it has been very helpful for me. Could I have the password for the vba please?
Maitena,
Check your e-mail.
Don
Hi Don,
I just found your website and subscribed; very interesting material and analysis.
For this spreadsheet, could you possibly send me the password to the VBA?
Thank you!
Alex,
I am e-mailing it to you.
Don
Hi Don,
Just came across your website, really enjoying the quality posts
Could I have the password for the vba?
Thank you
Check your email also.
Great content Don,
Could I have the password for the vba code? Thanks!
Great content Don,
Could I have the password for the vba code? Thanks!
Check your email.
Hi Dan!
Amazing to find your resources. I am working with a non-profit to analyze their CECL on their loan fund portfolio to CDFIs. I’d love to ask you a few questions about if this model can be tweaked for loans using different interest methods. Would appreciate any advice.
Best,
Ranjani
Ranjani,
I have been retired for several years, so I am not familiar with CECL accounting methods. I am sure ajustments could be made to the VBA. If you are comfortable with VBA, I will send you the password.
Don
Can you share the vba password?
It is on the way
Hi,
This is very helpful!
Can I please ask for the VBA password?
Thanks very much.
Hai,
Check your email.
Don
Hi Don,
This is great and very helpful – highly appreciated! Please would you share the VBA password ? – I will like to understand how the cash-flow is computed.
Many thanks
Ihedi
Check your e-mail
Hello Don – not sure how I haven’t discovered your website before. Would you share the password? You put intex out of work 🙂
Sharper,
Please check your email.
Don
Hi,
Great work! Can you share the password for the VBA?
Thank you,
John
John,
I’ll email it to you.
Hi Don,
I wanted to pick your brain on smth. I have a portfolio of residential and commercial loans and I want to see if I have to liquidate this portfolio within the next 30 months, how I would go about that.
For arm loans, I amortized the portfolio in excel using given margins and projected index values from bloomberg (some use 3 mo libor, some 1 month libor and several other indices) Then, I looked at the weighted average age of the portfolio for different asset categories, e.g. jumbo arm loans, helocs, gse conforming loans, etc. The age was over 30 months for each category, so I used 6% cpr and calculated monthly single month mortality based on that. I projected out principal interest and prepayments for 30 months. Then, for each asset category level, I aggregated P&I and prepayments and principal balances. After that, I calculated each, principal, interest and prepayment as a percentage of the beginning principal balance, for each asset category. Then, I came up with a liquidation schedule and decreased the principal balances of asset buckets based on this schedule. At that time, I used the percentages I had calculated previously, to roughly calculate updated p&I and prepayments. Since they were calculated as a % of principal balance, all the numbers were automatically updated when I change the liquidation amounts.
My first question is around what I have done so far: Does it make sense the way I did this in terms of using the projected indices from bloomberg, assumptions for prepayments and using the percentages to calculate post liquidation P&I and prepayment?
Second one is about default rates and loss severities. What kind of assumptions would you recommend using or would it be simpler to ignore defaults considering this liquidation exercise is only projected for 30 months?
I am looking forward to your insights on this and thank you very much for sharing your knowledge and experience with us.
Ian
Ian,
I will email you.
Could you send me the password for VBA?
Check your email
Hello Don,
I wanted to request the same.
And so you shall have it
Hello, can you please share the vba password? Many thanks
It is on the way.
Hey Don,
I downloaded the vba_many_loans file in excel but wasn’t prompted for a password like the others comments reference. Am I not looking at the same file?
Thanks for your help.
Hi Don,
Thanks for sharing! Could you please share the VBA password to be able to see/understand the calculations more in detail?
Thanks!
Check your e-mail
Don
Sean,
You only need the password if you what to see the VBA
Don
Like everyone else. Can i please get the password.
I sent it.
Don
Hey could I get the password? Thanks!
Ben,
It’s on the way.
Don
Hello Don, Thank you for the sheet. May you please share the VBA content?
Michelle
Michelle,
Password is on the way.
Don
Hi Don,
Would it be possible to share the password for this file with me? Many thanks
Ben,
It’s on the way.
Don
Hi Don Thanks for the awesome post and blog. This is helping a lot. Will you please share the VBA content just so I understand what is exactly behind the calculations please? Or the formulas behind it, whatever you prefer. I appreciate it.
best Regards,
Joao Freire
João,
Check your email.
Don
Hey Don,
Great model, thank you so much for building it.
Could you please share the vba password with me?
Kind Regards
Josh
Josh,
Check your email.
Don
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!
sent to you
Dear Google god has led to your web site . Can you please share password .
Thanks in advance ,
Muhammad,
Check your email.
Don
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
Sent by e-mail
Hi Don
interesting model – can i get the password for the VBA, be appreciated
thanks
r
Regi,
Check your e-mail
Don
Would you mind sharing the password to the VBA?
It is on the way.
Don
Hi Don,
This spreadsheet is awesome.
is it possible if you could share the VBA? I really appreciate it.
Many Thanks
It is on the way.
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!
It is in the mail.
Don
This is great. Can you please share the password for the VBA code?
Hi Don,
Interesting post. Could I get a look at the VBA?
Thanks
Hi Don,
Interesting post. Could I get a look at the VBA code? Thanks, D
Hi Don,
Fantastic website! Helped me a lot to understand cash flow. Can you share the VB macro password please.
Cheers!
Amitabh
Amitabh,
Check your email
Don
Don, Thank you for your excellent post. May I also get the password for the VBA code?
It is in the mail
Hi Don – could you please share the VBA for this?
Thank you!
Ashton George
I just sent you the password.
Don
Please may I have the vba code and password for the model.
Thank you
Kofi,
It is on the way.
Don
Hi Don,
Great model – would love to see the vba, thanks for sharing!
James
Check your email.
Don
Brilliant post Don.
Would you be so kind to share the VBA code with me? Thanks!
Nacho,
I will email it to you.
Don
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
PS,
I will email it to you.
Don, very interested post. Would you please share me the VBA code? I appreciate it.
I will email it to you.
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?
I will send it to you.
Hi, can I please have the password too?
Thanks!
I will send it now.
Don
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!
Amstel,
It is on the way.
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!
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
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/
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
Hi Don
Really appreciate your great job. would you pls send your password for VAB?
thanks
Nan
Nan,
Check your mail
Hi Don Would you please share the password? Thanks a lot!
Josh,
I will send it to the e-mail address you gave.
Hi Don Thanks for the great job Will you please share the VBA content? I appreciate it.
John,
Check your email
Hi Don, great site. Could I get the password for the VBA code?
Thanks is advance!
Jeff,
I sent it by email
Don
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.
Trav,
I will send you an email.
Hi Don,
I am interested in the password for the VBA.
Thank you,
Kelly
Hi Kelly,
I am sending it by email.
Don
Hello there, I am interested in learning how to do this using VBA. Can you email me the password? Thank you in advance.
Connie,
It is on its way. Let me know if you do not receive it.
Don
Hi Don,
Looks great! Would it be possible to be provided the password as well?
Thanks!
Eugene,
Check your emails
Don
Hello, I am interested in learning how to do this using VBA. Can you email me the password? Thank you in advance.
Justin,
The password is on the way. Check your email.
Don, I apologize if you have received multiple emails from me but I didn’t see my comment so I wanted to follow up.
I was hoping to get a look at the VBA code behind the calculation. Would you email it to me ?
Hi Don! Can you send me the vba password too, please?
Thank you!
Kate,
Check your email
Don
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
Thanks Brian
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
Brian,
It is on the way
Hi
Thank you for an interesting post and Website.
I would really appreciate if you would let me see the vba code.
Christer,
Check your email.
Hi Don,
Looks great! Could you please share the VBA code for this? Very interested…thank you very much!
Alex,
Thanks for the comment. I am sending the password by e-mail now.
Hi Don,
Looks great, thank you. Could you share the VBA code as well? Will appreciate this.
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?
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?
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
[…] Valuing a Mortgage Portfolio With Cash Flow Analysis September 2, 2016 […]
Hi Don Thanks for the great post, very informative. Will you please share the VBA content? I appreciate it.
It is on the way
Is there any way to see the vba for this?
Check your email
Hi Don – great post, this is very helpful. Is there any way to see the vba for this? Would greatly appreciate it.
Hi Drew,
I will send you the password by e-mail.
Don