Question

Your best friend Steve just celebrated his 30th birthday and wants to start saving for his anticipated retirement. Steve plans to retire in 30 years and believes that he will have 25 good years of retirement (he has looked at the life expectancy tables and is playing the odds here) and believes that if he can withdraw $120,000 at the end of each year, he can enjoy his retirement. In this problem, assume that the full 25 years of retirement payments are made, and the account has a zero balance at the end. Assume that a reasonable rate of interest for Steve for all scenarios presented below is 7% per year. This is an annual rate, review each individual question for more specifics on compounding periods per year. Because Steve is planning ahead, the first withdrawal will not take place until one year after he retires. he wants to make equal annual deposits into his account for his retirement fund.

A. If he starts making these deposits in one year and makes his last deposit on the day he retires, what amount must he deposit annually to be able to make the desired withdrawals at retirement?

A1) First: Amount Steve needs to have saved as of his retirement:

A2) The amount Steve must save each year (beginning at the end of the first year) to fund his retirement is:

A3) If Steve decides to make monthly deposits for 35 years to reach his same retirement goal, how much must Steve start depositing one month from today?

Answer 1

A1]

Amount Steve needs to have saved as of his retirement is calculated using PV function in Excel :

rate = 7% (annual rate of interest earned)

nper = 25 (25 yearly withdrawals during retirement)

pmt = 120,000 (yearly withdrawal amount desired)

PV is calculated to be $1,398,430

A2]

Amount Steve must save each year is calculated using PMT function in Excel :

rate = 7% (annual interest rate)

nper = 30 (30 annual deposits until retirement)

pv =0 (current value of retirement account is zero)

fv = 1,398,430 (required value of retirement fund at the end of 30 years from now)

PMT is calculated to be $14,804

A3]

If Steve makes monthly deposits for 35 years to reach his same retirement goal, the monthly deposit is calculated using PMT function in Excel :

rate = 7% / 12 (converting annual rate into monthly rate)

nper = 35 * 12 (35 years with 12 monthly deposits each year)

pv =0 (current value of retirement account is zero)

fv = 1,398,430 (required value of retirement fund at the end of 30 years from now)

PMT is calculated to be $776.45