Question

John Wayne is 50 years old and plans to retire in 20 years. His new employer provides 401K retirement plan and he plans to accumulate $1,000,000 in his retirement account by age of 70. Use Excel to answer the following questions.

1. How much he has to contribute per year at return of 7% per year to reach his retirement goal.
2. Redo part (a) if john decides to increase his contribution 0.50% per year over 20 years.

c.Redo part (a) if the yearly return for the first 10 years is 7% and last 10 years is 9%.

Answer 1

Time Period of Deposit = 20 years

Future Value required = $1,000,000

a.

Calculating Annual Deposit,

Using TVM Calculation,

PMT = [Pv = 0, FV = 1,000,000, N = 20, I = 0.07]

PMT = $24,392.93

Annual Deposit = $24,392.93

b.

Future Value of Growing Annuity = P/(r - g)[(1 + r)ⁿ - (1 + g)ⁿ]

1,000,000 = P/(0.07 - 0.005)[(1.07)²⁰ - (1.005)²⁰]

P = $23,509.94

First Deposit = $23,509.94