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: