In: Finance
Suppose you have set a specific goal for the balance in your savings account 12 years from today. You have been making year-end deposits of $2,000 per year for the past 8 years, and this annual deposit (if continued for 12 more years) would have been sufficient to reach your goal under the 10 percent average annual interest rate (annual compounding) that you have been receiving. However you believe that the average annual interest rate on your deposits will be only 9 percent per year compounded annually over the remaining 12 years of the investment period. If your goal remains unchanged, you must adjust your annual deposit to reflect this new interest rate. Assuming that your next deposit will be made one year from today, what must be the amount of each of the remaining 12 deposits in order to reach your original goal?
The goal 12 years from today is calculated using FV function in Excel :
rate = 10%
nper = 20 (20 annual deposits --> 8 already made and 12 more to be made)
pmt = -2000 (annual deposit. This is entered with a negative sign because it is cash outflow)
FV is calculated to be $114,550
The value of the account now is calculated using FV function in Excel :
rate = 10%
nper = 8 (8 annual deposits already made)
pmt = -2000 (annual deposit. This is entered with a negative sign because it is cash outflow)
FV is calculated to be $22,871.78
The amount of deposit (adjusted) to reach goal is calculated using PMT function in Excel :
rate = 9%
nper = 12 (12 annual deposits already made)
pv = -22871.78 (value of account now. This is entered with a negative sign because it is like a deposit into the account now)
fv = 114550 (required value of account 12 years from today)
PMT is calculated to be $2,493.42
The amount of deposit (adjusted) to reach goal is $2,493.42