A few weeks ago, I wrote a post called “Valuing a Mortgage Portfolio With Cash Flow Analysis” along with a spreadsheet using VBA to calculate the cash flows of a loan portfolio. Ideally, if an amortization schedule for each loan could be created and the cash flow matched, this would be a proof that the VBA cash flows are correct. The example portfolio that I sent out with the original post however, had 800 loans. So I built another spreadsheet that takes the same portfolio information and creates 800 amortization schedules (or as many as your system can handle) and the cash flow is exactly the same as the VBA cash flows. The file was so large however, that it was not practical to allow downloading it.
Instead, for this post, I used the first 20 loans in the previous post and compared the two spreadsheets. If you have already downloaded the VBA spreadsheet, do it again. I tweaked a penny rounding problem. If you want to copy/paste all 800 loans, go ahead.
If you want to use this spreadsheet to create your own amortization schedules, do the following:
After the new loan data are entered, clear any loans that have already been created by clicking . Then enter your loans and create your amortization schedules by clicking the button. If you do add all 800 loans and then create 800 amortization schedules, it will take awhile to create them all. That is why the VBA spreadsheet is preferable, in that it takes only a few second to calculate 800 loans.
However, there are a couple of advantages in using this spreadsheet. First, all of the loan data are linked to the individual amortization schedules. Therefore, if you change any input data you don’t have to run the amortizations again. The amortization schedule is instantly recalculated. Also, each amortization sheet is named with its loan number. Next, if you take a look at the loan numbers (under the column header “Codes”), each is hyperlinked to that amortization schedule. You can click the loan number and go directly to that amortization. Each amortization schedule also has a button that returns you to the data input sheet.
This is the amortization input sheet:
Next is the VBA version, with the same 20 loans. Note that there is one balloon and two interest only loans, just to test those assumption also.
The results are exactly the same, an IRR of 3.4082% and each cash flow (I cut it off at the 20th cash flow) is also exactly the same.