0

I want to set up a compound interest table. I want to start with $15 and multiply by 1.0125 indefinitely. I want to be able to see the answer for each calculation (for example, I want to see what the answer is after 39 calculation, after 74 calculations, 22 calcualtions, etc..)

  • Do you know how to do it for two cells? – RonJohn Nov 19 at 14:16
  • I'll use it for finance, DJClayworth. I do not RonJohn – David Kaufmann Nov 19 at 14:19
  • 3
    Put 15 in a cell (say A1). Put the formula =A1 * 1.0125 in the cell below (A2). Copy the formula cell down as much as you like. If desired, add a number column so you know which period you're looking at. This is a basic spreadsheet question, not finance. – D Stanley Nov 19 at 14:23
  • Welcome to Web Applications. Please add a brief description of your search/research efforts as is suggested in How to Ask. You might find helpful to start looking at the Google Sheets help menu. – Rubén Nov 19 at 15:54
0

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))
| improve this answer | |
1

For one calculation

15*1.0125 = 15.1875

After 39 calculations

15*1.0125^39 = 24.35
| improve this answer | |
  • That does not satisfy the requirement "to see the answer for each calculation". – RonJohn Nov 19 at 14:44
  • Thank you this is helpful, Chris Degnen. Let me focus the question somewhat: how would I be able to see the answer for each calculation up to 900 calcuations? – David Kaufmann Nov 19 at 15:15
1
  1. Enter 15 in cell A1.

  2. Enter =A1*1.0125 in cell A2.

  3. Select cell A2, hover your mouse cursor over the bottom right of the cell until a crosshair appears.

  4. Drag the crosshair downwards (e.g. to cell A20) and release.


Alternatively, if you need hundreds or thousands of rows:

  1. Enter formula =A1*1.0125 in cell A2.

  2. Select cell A2, and use Ctrlc to copy it.

  3. Select the entire column A by clicking on "A" in the column header, then Ctrlv to paste the formula into all rows of column A.

  4. Change the contents of cell A1 to 15.

| improve this answer | |

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.