Question

Please use excel

If you deposit $5,000 in a savings account that pays 8% annual interest compounded
monthly, and make no other deposits to the account, how much will you have
after 5 years?
		$7,449.23

In addition to the $5,000 as specified in the above problem, how much will you

have at the end of 5 years if you also deposit an additional $200 at the end of

each month for the entire 5 year period?

In addition to the $5,000 as specified in above problem #4, how much will you

have at the end of 5 years if you also deposit an additional $200 at the beginning

of each month for the entire 5 year period?

Answer 1

1]

Future value = present value * (1 + r)ⁿ,

where r = interest rate per period,

and n = number of periods.

Here, the r = monthly rate.

r = annual rate / 12 = 8% / 12  

n = total number of monthly periods

n = number of years * 12 = 5 * 12  

Future value = $7,449.23

2]

The accumulated value at the end of 5 years is calculated using FV function in Excel :

rate = 8%/12 (converting annual rate into monthly rate)

nper = 5 * 12 (5 years with 12 monthly deposits each year)

pmt = -200 (Monthly payment. This is entered with a negative sign because it is a cash outflow)

pv = 5000 (deposit at beginning of 5 years. This is entered with a negative sign because it is a cash outflow)

type = 0 (each monthly deposit is made at the beginning of the month)

FV is calculated to be $22,144.60

3]

The accumulated value at the end of 5 years is calculated using FV function in Excel :

rate = 8%/12 (converting annual rate into monthly rate)

nper = 5 * 12 (5 years with 12 monthly deposits each year)

pmt = -200 (Monthly payment. This is entered with a negative sign because it is a cash outflow)

pv = 5000 (deposit at beginning of 5 years. This is entered with a negative sign because it is a cash outflow)

type = 1 (each monthly deposit is made at the end of the month)

FV is calculated to be $22,242.57