Question

Kevin Hall is 30 years and wants to retire when he is 65. So far he has saved (1) $6,720 in an IRA account in which his money is earning 8.3 percent annually and (2) $5,810 in a money market account in which he is earning 5.25 percent annually. Kevin wants to have $1 million when he retires. Starting next year, he plans to invest the same amount of money every year until he retires in a mutual fund in which he expects to earn 9.38 percent annually. How much will Kevin have to invest every year to achieve his savings goal?

Answer 1

Current Amount in IRA = $6720, Annual return on IRA = 8.3%, Years to retirement = Retirement age - current age = 65 - 30 = 35

Accumulated value of amount in IRA at age of 65 = Current amount in IRA (1+Annual return)^{year to retirement} = 6720(1+8.3%)³⁵ = 6720( x 16.29280958 = 109487.680377

Current Amount in Money market account = $5810, Annual return on IRA = 5.25%, Years to retirement = Retirement age - current age = 65 - 30 = 35

Accumulated value of amount in money market account at age of 65 = Current amount in money market account (1+Annual return)^{year to retirement} = 5810(1+5.25%)³⁵ = 5810 x 5.9947856 = 34829.704336

Total amount needed at retirement = $1 million = $1000000

Amount that needs to be accumulated at age of 65 years by mutual fund = Total amount needed at retirement - Accumulated value of amount in IRA at age of 65 - Accumulated value of amount in money market account at age of 65 = 1000000 - 109487.680377 - 34829.704336 = 855682.61528

Rate of interest in mutual fund account = 9.38%, No of years to retirement = no of payments in mutual account = 35

Constant amount invested annually starting next year in a mutual fund forms an ordinary annuity. We can find this constant amount using PMT function in excel

Formula to be used in excel: =PMT(rate,nper,pv,-fv)

In the formula pv=0 because there is no initial balance in mutual fund account.

64 65 Amount that needs to accumulated at age of 65 years by mutual fund (fv) I 66 rate of return (rate) 67 Initial balance in mutual fund(pv) 68 No of years to retirement (nper) 69 Amount of save each year(pmt) 70 71 8 55682.615280] 9.38%! 0! 351 =PMT(H66, H68, H67,-H65) PMT(rate, nper, pv, [fv], [type])

Using PMT function in excel, Amount to invest each year in mutual find = $3638.6780

Amount that Kevin needs to invest each year = $3638.6780