0

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 ?

1

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? – JoeTaxpayer 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. – JoeTaxpayer Sep 17 '16 at 17:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .