Question

Making an investment (FV Function): long term

You told Jane about your calculation result and Jane is very impressed by your ability of using the FV function. She says that she would like to save more. Now she wants to know if she is able to deposit $3,000.00 in a mutual fund account each year as a lump sum, and let it grow for 30 years, how much money would she receive at the end of 30 years.

Before she makes a decision about how much she would deposit for each year, she wants to do a comparison about how the initial deposit amount affect the end result. She figures out that she would be able to make the deposit between $2000 and $5000 each year. She will make the deposit at the beginning of each year.

She asks you to calculate this for her. Since the return (rate) of the mutual fund varies, sometimes it could be negative, we will just assume that the rate varies from 3.5% to 8%. Therefore, in your calculation, you need to include the following annual interest/return rate: 3.5%, 4%, 4.5%, 5%, 5.5%, 6%, 6.5%, 7%, 7.5%, 8%.

For the principal/deposit for each year, you will need to include: $2,000, $3,000, $4,000, $5000.

Use a table to calculate how much Jane would receive at the end of 30 years for different deposit amount and rates.

You must use the FV function.

You must use proper cell reference. You will set up the formula one Excel cell in such a way that you can drag and fill out the rest of the table.

Answer 1

Amount Jane would receive at the end of 30 years is calculated using FV function as below :

rate = annual rate of interest

nper = number of years = 30

pmt = -yearly deposit (This is entered with a negative sign because it is a cash outflow)

pv = 0 (beginning balance in account is zero)

type = 1 (Each deposit is made at the beginning of the year, therefore it is an annuity due)