# How to calculate the compound interest for the following scenario

Mr A plans to invest 100000 every year for 15 years (he will withdraw money at the end of 15 years) and is expecting a return of about 12%.

I know the FV function of excel but how to use it ?

The future value is found by summing compounded deposits

``````fv = Σ(1 + r)^k for k = 1 to n
``````

∴ by induction the future value formula is

``````fv = (d (1 + r) (-1 + (1 + r)^n))/r
``````

Using the OP's figures

``````d = 100000
r = 0.12
n = 15

fv = (d (1 + r) (-1 + (1 + r)^n))/r = 4175328.04
``````

In Excel

``````=FV(rate, nper, pmt, [pv], [type])
``````

type - [optional] When payments are due. 0 = end of period, 1 = beginning of period. Default is 0.

Savings deposits are usually made at the beginning of the period.

``````=FV(0.12, 15, 100000, 0, 1)
``````

In the unlikely case that Mr A plans to invest at the end of each year, in which case he would withdraw money at the same time as making the last deposit, use

``````=FV(0.12, 15, 100000)
``````

Equivalent to

``````fv = Σ(1 + r)^(k - 1) for k = 1 to n

∴ fv = (d (-1 + (1 + r)^n))/r = 3727971.47
``````

The summation can also be written in Excel like so

``````=SERIESSUM(1+0.12,0,1,{100000,100000,100000,100000,100000,100000,100000,100000,
100000,100000,100000,100000,100000,100000,100000})
``````

It looks like the following should also work but it doesn't.

``````=SERIESSUM(1+0.12,0,1,TRANSPOSE(ROW(INDIRECT("1:15")))^0*100000)
``````

If anyone can suggest why that would be interesting.

• Doesn't Excel want the \$100000 to be entered as -100000? – JTP - Apologise to Monica Sep 17 '16 at 16:22
• Highlighting the `TRANSPOSE(...)^0*100000` and pressing F9 resolves the array correctly and finds the answer, but it isn't automatic. – Chris Degnen Sep 17 '16 at 16:57
• Thx, I fielded a spreadsheet question a couple weeks back and had to mention to the OP that for whatever reason Google spreadsheet versus Excel versus open office they don't all behave the same way and just to be aware of certain things needing to be entered as negative. – JTP - Apologise to Monica Sep 17 '16 at 17:25