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.

Download http://pistulka.com/Excel_Shared/many_amortized_loans.xlsm

Hi – thank you for the great work and sharing.

Could you email me the password for both VBA please?

Thank you,

Mo

It is on the way Mo.

Don

Could you send me the password too? Thanks!

NP,

It is on the way.

Don

Could you please share the password ?

Thank you!

Thanks Don – would you share the pw?

The password is the same as the one I sent you.

Really helpful, thank you very much for sharing. May I please have the vba password? Tyvm in advance

Check your email

Don

Hi Don, can I please get the VBA password? Many thanks

In the mail

Hello, can I get the password for the VBA?

Thank you,

Anna

Hello, can I p-lease get the VBA password? many thanks

this is awesome, would you be able to send the VBA passcode? Would love to check out your code.

Michael,

Check your email

could you also send me the password for the vba? thank you!

Tony,

It’s on the way

Don

this is really impressive! please could you send me the VBA password from the original VBA version? Thanks! Ross

Ross,

Check your email

Nice one!

Thank You,

Don