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 ?
Add a comment
|
1 Answer
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.
answered Sep 17, 2016 at 10:38
-
Doesn't Excel want the $100000 to be entered as -100000? Sep 17, 2016 at 16:22
-
Highlighting the
TRANSPOSE(...)^0*100000and pressing F9 resolves the array correctly and finds the answer, but it isn't automatic. Sep 17, 2016 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. Sep 17, 2016 at 17:25