Can formula summarize monthly mortgage amortization table into annual summary?

1) Normally for a mortgage that is paid monthly, I could create an amortization table in excel that had columns set up something like this:

A = beginning balance (= prior month ending balance, column E from row above)
B = monthly mortgage payment
C = principal portion
D = interest portion
E = ending balance (=beginning balance - principal = ending balance)

If this was a 30 year loan, there would be 30x12 = 360 rows in my amortization table. I can easily use formula to create this table.

But what if I want to see this same information shown on an annualized basis - i.e., 30 rows of data instead of 360? (so that I can quickly evaluate what might happen if I made an extra principal payment, or to quickly see what balance would be at the end of year 10 etc.)

I can manually come up with the above by first creating a 360-row table ("detailed table"), and then creating a second 30-row table ("summary table") that uses data taken from the detailed table.
(i.e., on the summary table: column A would show beginning balance, columns B, C, and D would show sum of first 12 months from the "detailed table", and then column E would show ending balance. etc., so each row on the summary table, summarized one complete year (12 rows) from the detailed table).)

But is there a formula that would accomplish the same, without my needing to follow this two-step process?

(Obviously, my numbers would be off if I tried to do a simple 30-row table, because compound interest portion of the total payments made each year, is based on 12 monthly payments -- i.e., less interest than if paid in one lump sum).

I can't see how this can be done. However, one of my professors insists that there is a formula to do so - but won't reveal what the formula is. The professor has a lot of software knowledge, but not much business knowledge, and I wonder if she is mistaken? Or does a formula really exist to do this?

Again: Of course, I know how to use formulas to calculate amount of monthly mortgage payments on, say, a 30 year loan. And also how to create an amortization table (which would have 360 rows of data) showing how the loan is gradually paid off, and the interest vs. principal portion of each monthly payment. (This is the standard amortization table that would be provided by a bank).

If I wanted subtotals for each year, put into a 30-row summary table instead, I could do this manually by calculating the sum of months 1-12 from the detailed schedule and putting it in the first line of my summary, then taking the sum of months 13-24 from the detailed schedule and putting it into the second line of my summary, and so on..... But is there a formula to do this?

Hi amosnliz,

My understanding is there isn't one formula, it would take 2 for what you are describing.

The first formula calculates the payment per period based on original loan value, term and interest rate.

The second formula adjusts the payment term and last payment amount based on how much additional principal was paid during the loan term. The 2nd formula would hold the payment amount (calculated with formula 1), interest rate and payment frequency constant and would be used to adjust the outstanding loan amount during the term. Paying additional principal during the term of a loan would only reduce the overall term length, payoff amount and last payment amount.

Basically, since the first formula calculates the payment per period and the second formula uses the payment per period as a constant to determine other values, there isn't one formula to do both at once. It takes 2.

I have attached a workbook with a macro in it that comes up with an amortization schedule based on inputting purchase amount, interest rate, term and downpayment. Once those are filled in, the macro is event driven and it will produce an amortization schedule. Once the schedule is made, additional principal can be input into Column G and the table will be updated automatically.

Hope that provides some direction,

Dan

Thank you so much for taking the time to reply and for your input!

Originally Posted by amosnliz

Oh, incidentally, our class was never taught CUMINT or CUMPRINC functions, so that wouldn't have been the solution. The teacher did talk about "nesting" formulas/ functions, but I still couldn't see how this would resolve this assignment.

You're correct: "nesting functions" has nothing to do with it.

What about FV: did you cover that in class?

But if you don't need this for class, why limit yourself to what was covered in class?

Again, thank you so much for taking the time to respond. I know how to use various functions in order to calculate monthly mortgage payment and set up an amortization table. But if mortgage payments were paid monthly, my formulas would calculate monthly payment amount, and amortization table would have one row for each monthly payment.
If teacher told us to calculate monthly mortgage payments -- and the display the results in an annual amortization table - that wouldn't make sense (unless you took a second step of adding every 12 months of monthly payment info - principal/interest portion) to use to create the amortization table.

I would love to find someone willing to carefully read though actual homework assignment, and then tell me if what teacher is saying seems to make sense (the final outcome being required) - or if the only real sense would be if she had asked for monthly amort. schedule, not annual.

Thanks again.