1

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.

Help?

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

  • There you go... sheet 2 – Costa Jun 28 at 17:31
  • Updated link... – Costa Jun 28 at 17:36
  • I updated the link. – Costa Jun 28 at 17:49
  • How about now? ? – Costa Jun 28 at 17:58
1

={"Remaining Principal"; 
 ARRAYFORMULA(QUERY(IF(LEN(E7:E), MMULT(TRANSPOSE((ROW(E8:E)<=
 TRANSPOSE(ROW(E8:E)))*{B4; -1*INDIRECT("E"&ROW(E8)&":E"&ROWS(E:E)-1)}), 
 SIGN(E8:E)^0), ), "offset 1", -1))}

0

demo spreadsheet of running total


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

  • Hmm... doesn't seem to work on the demo sheet. Perhaps I miscommunicated? – Costa Jun 28 at 19:20
  • I adjusted the sheet I shared and dragged down to show the output I need. – Costa Jun 28 at 19:21
  • 1
    it does exactly that. see: docs.google.com/spreadsheets/d/… – user0 Jun 28 at 19:25
  • 1
    You rock! Was looking at the wrong sheet. – Costa Jun 28 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 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.