I have the first few rows of my mortgage payments set up, and I want to know how to programmatically print the rest of the rows, as opposed to dragging the little corner box to output the rows.

This is important so that I can adjust the payment period, and automatically allow the total number of rows to change.

I think I have to use Array Formulas, but not sure how to print the next row the way dragging it or copy-pasting it would.

Also, I don't know how to give that array formula and ending condition.


enter image description here

The printing of new rows should only stop if the remaining principal gets to or below 0.

Sheet: https://drive.google.com/open?id=17Vl2M1d6gwaqHcuO_9c7SpQ-icQxUayqaLYxnur-CuU


={"Remaining Principal"; 
 TRANSPOSE(ROW(E8:E)))*{B4; -1*INDIRECT("E"&ROW(E8)&":E"&ROWS(E:E)-1)}), 
 SIGN(E8:E)^0), ), "offset 1", -1))}


demo spreadsheet of running total

alternative: https://wamoyo.github.io/amortization/

  • Hmm... doesn't seem to work on the demo sheet. Perhaps I miscommunicated? Jun 28 '19 at 19:20
  • I adjusted the sheet I shared and dragged down to show the output I need. Jun 28 '19 at 19:21
  • 1
    it does exactly that. see: docs.google.com/spreadsheets/d/…
    – user0
    Jun 28 '19 at 19:25
  • 1
    You rock! Was looking at the wrong sheet. Jun 28 '19 at 19:37
  • 1
    the issue here is that array formula is dependant on column E but column E is dependant on column F and this creates a loop
    – user0
    Jun 28 '19 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.