complex amortization

[cfspahn24]

Problem: 5 loan amounts, each different, with varying interest rates,
payments, and loan periods. The loan periods are epressed in decimals of a
year, not full years. I need to determine the total amortization if, as the
highest interest loan is paid off, the payment for that loan is applied to
the next highest interest rate loan, continuing until the final loan is paid
off. This boggles my mind!

 

[Fred Smith]

It won't be too hard to set up a spreadsheet for a specific situation. Setting
up a generalized sheet would be more difficult.

First, ignore the calculation on the loan period. If you're doing an
amortization table, you calculate the number of periods.

An amortization table has four columns: Opening balance, Payment, Interest,
Closing Balance.

I would put your five loans in columns, eg A-C, D-G, H-K, etc. in order of
highest to lowest rates. Copy down rows until the first loan is paid off. Then
change the payment of the second loan in the next period. Continue until all
loans are paid off.

It will be a little tedious, but will get you the answer you are looking for.