Google-sheets – How to programmatically drag new rows – running total

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

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

Best Answer

={"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/

Related Topic