Question

You are considering a savings plan that calls for a deposit of $12,000 at the beginning of each of the next five years. If the plan offers an interest rate of 8 percent, how much will you accumulate at the end of year 5?

Do this calculation by completing the following template using Excel. Your spreadsheet will do the FV calculation twice - once using the FV function (in Cell B4) and once using a simple table that shows the accumulation at the beginning of each year that would be similar to what you would show a client that does not understand future value accumulation very well.

	A	B	C	D
1	Annual payment	12,000
2	Interest rate	8%
3	Number of years	5
4	Total value		<--Use FV function
5
6	Year	Accumulation at beginning of year	Payment at end of year	Annual interest
7	1	0	12,000	0
8	2	12,000	12,000
9	3
10	4
11	5
12	6

Answer 1

first part we use FV function which takes 5 parameters

=FV(rate, nper,pmt,(pv),(fv) )

by putting values =FV(8%,5,12000,0,0)

future value if payment made in the end =70399.21

   future value if payment made in the beginning =76031. 15

year accumulation at beg. payment at end interest

1 0 12000 0

2 12000 12000 163.64

3 12000 12000 1996.80

4 12000   12000 3116.54

5 12000 12000 4325.87

for calculating interest we use IPMT function

for accumulation at beginning we use PMT function