Excel formula for compound interest?

Question

I would like to make an excel formula according to this situation:

Person A / Person B Person A invests 1000$ every year from 18 to 28, and then holds it until age 65. It compounds every year with 8%.

Person B invests 0$ every year from 18 to 28 but then starts to invest 1000$ every year until age 65. It compounds every with with 8%.

I want 47 rows (65-18) for Person A & B and finally a "total". I want to see it grow in every cell.

Anyone that could help me set it up and perhaps link it to a an upload service? I want to learn... :) Thanks! :)

Answer 1

One of the most powerful features of Excel and other spreadsheet programs is the ability to copy a formula or pattern down a column or across a row.

Here's a quick demo showing the scenario you describe for Person A:

For Person B you could just copy columns C and D and paste them over to the right then adjust where the contribution amounts go.

In Excel, you'll notice the cursor change when it is on the bottom right corner of a cell, that's where you click and drag to copy-down or across. To increment the year you don't even need a formula, you can just seed the pattern with some entries, select all of them and copy down. You'll also notice that when I type in the formulas the reference to the interest rate starts off as B1 but then becomes $B$1, 'F4' is the shortcut key that locks the reference, that means when I copy my formula down to subsequent rows it always references B1, and in this case if I copied it to other columns it would also reference B1. $B1 would lock the column reference but allow rows to adjust as it was copied, B$1 would lock row reference and allow columns to adjust as it is copied across (you can hit 'F4' multiple times to cycle through the reference lock variations.

For reference, here are the resulting formulas from the copy-down:

You can get much fancier, if you look for loan/mortgage/interest calculator templates in Excel you'll find some that accept a great number of variables and have more dynamic output. Similarly, Excel and most other spreadsheet programs offer a large number of built-in financial functions.

Answer 2

For person A rows 1 to 10 are given by the following formula for compounding deposits d over n years at interest rate r.

So with

deposits   d = 1000
interest   r = 0.08

at the end of year 1 (row 1) balance b is

n = 1
b = (d (1 + r) ((1 + r)^n - 1))/r = 1080

through to year 10 (row 10)

n = 10
b = (d (1 + r) ((1 + r)^n - 1))/r = 15645.49

Rows 11 to 47 are calculated by simple compounding, e.g.

n = 1
b (1 + r)^n = 16897.13

...

n = 37
b (1 + r)^n = 269816.22

For person B the formula is used. Rows 1 to 10 are zero. Rows 11 to 47 are

n = 1
b = (d (1 + r) ((1 + r)^n - 1))/r = 1080

...

n = 37
b = (d (1 + r) ((1 + r)^n - 1))/r = 219315.95

It should be simple enough to put these calculations into Excel. E.g.

And an expanded example showing formulas. Row height for rows 23 to 44 is reduced to fit the image. Note d set in the top-left by overtyping 'B2' for variable declaration.

And lastly, the calculated figures, matching the earlier calculations.

Addendum

For those who would prefer to use built-in Excel formulas the same results can be achieved using the future value (FV) function.