# Excel formula for compound interest?

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! :)

• Does person B's investment compound annually too? It says "every with with" currently. – Hart CO Sep 7 '19 at 14:39
• Can you tell us what part you are having trouble with? – Craig W Sep 7 '19 at 16:14

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.

• Nice way to illustrate. Now I need to ask a question on superuser.se on how this can be done and embedded in answer :) – Dheer Sep 8 '19 at 7:55

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.  