Using Excel: Your goal is to have $5,000 in five years from now, and your mother help to get started and give you $500 now. From your income you plan to make five additional deposits, one at the end of each year. The first deposit from your money is made at the end of the first year. If you deposit the money in a bank that pays 7% interest, how much must your annual deposit be.
ANSWER:
Below is the excel sheet.
year | 0 | 1 | 2 | 3 | 4 | 5 |
initial deposit from mother | 500 | |||||
annual deposit | 747.51 | 747.51 | 747.51 | 747.51 | 747.51 | |
cash flows | 500 | 747.51 | 747.51 | 747.51 | 747.51 | 747.51 |
future value | $701.28 | $979.83 | $915.73 | $855.82 | $799.84 | $747.51 |
total future value | $5,000.01 |
in excel we will solve via trial and error as the total future value has to be $5,000 at the end of 5 years as given in the question.
future value is calculated in excel using the fv function.
=-fv(rate,nper,pmt,pv,type)
for year 0 future value
=-fv(7%,5,,500) = $701.28
similarly for year 1
=-fv(7%,4,,747.51) = $979.83
the value in the annual deposit will be changed manually so the total future value comes up to $5,000.
The correct answer is $747.51
Get Answers For Free
Most questions answered within 1 hours.