Google Sheets – How to Create a Compound Interest Table for Indefinite Calculations

David, try this:

In A1 enter 15.00

In A2 enter 1.0125

In B1 (with the rest of Column B empty) enter this formula:

=ArrayFormula(ROUND(A1*A2^SEQUENCE(900,1,0),2))

HOW IT WORKS

The ROUND wrap is obviously just truncating results to two decimal places. You could remove it and just format the entire Column B as currency.

ArrayFormula(...) tells Sheets to process a range (real or virtual) of data.

If you hadn't applied the interest yet, the formula would be the following:

=A1*A2^0

That's because anything to the zero power is 1. So this would just return 15.00.

If you wanted the amount after one application of 1.0125, this would be the formula:

=A1*A2^1

And so on.

So the only part that changes is the "power" to which the interest is raised.

We can accomplish an unlimited sequence with SEQUENCE.

What the SEQUENCE call says (in plain English) is this: "Form a virtual array that is 900 rows tall and 1 column wide, starting at 0 (and moving up by 1 each time by default)."

The ArrayFormula wrapping acts on this sequence and provides a result for each step.

Of course, if you wanted to "hard-wire" your numbers into the formula instead of referring to external cells A1 and A2, you'd just pop them in directly:

=ArrayFormula(ROUND(15*1.0125^(SEQUENCE(900,1,0)),2))

Just by the way, there is a fourth possible argument to SEQUENCE, which is the step. If you leave it out, the default is "count by 1 each time." But if you wanted only every, say, 5th step, you'd divide 900 by 5 to get 180 rows and you'd add a fourth argument of 5, like this:

=SEQUENCE(180,1,0,5)

... or in context of the full formula at hand ...

=ArrayFormula(ROUND(A1*A2^SEQUENCE(180,1,0,5),2))