Amortization Consolidation

If you have looked at very many of my posts and spreadsheets, it is obvious that a good deal of them involve loan amortization schedules. So does this one. The first sheet in the workbook “consolidate” is called “Amortization Loan”. That is because it contains a standard monthly amortizing loan schedule. There are times when you might want to consolidate the monthly schedule into totals by year, quarter, or semiannually. That is what the second sheet “Consolidated” does,

The sheet is protected so that only one yellow cell can be changed and that cell is a drop down box with the choices of Annual, Quarterly, Semiannually, or Monthly. The protection is not password protected, so you can unprotect the sheet at any time. I used both the Index and Sumproduct functions to create the consolidations.

A 30-year mortgage with a 10 year balloon, subtotaled annually would look like this :


The same schedule consolidated semiannually would look like this:




Download “consolidate

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

Leave a Reply

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