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?