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 (3 pts): A2) The amount Steve must save each year (beginning at the end of the first year) to fund his retirement is (3 pts): 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 (3 pts)? B. If Steve decides he isn’t earning enough money yet and wants to wait several years before starting his investment deposits. Assume that instead of starting immediately (that is, the end of year 1), Steve waits for 7 years (first deposit at the end of year 7) leaving 7 fewer years to grow his retirement nest egg, what amount must he deposit annually to be able to make the desired withdrawals at retirement (4 pts)? C. Suppose your friend has just inherited a large sum of money. Rather than making equal annual payments, he has decided to make one lump sum deposit today to cover his retirement needs. What amount does he have to deposit today? (3 pts) D. We are now back to Steve staring his retirement investments at the end of the first year (30 years to retirement). Suppose Steve's employer will contribute $2,500 to the account each year as part of the company's profit sharing plan. In addition, assume that Steve has a trust fund that will pay out $35,000 to him when he is 50 (20 years from now). What amount must he deposit annually under these assumptions to be able to make the desired withdrawals at retirement? Hint: This is your answer in part D4, D1-3 help you build to the final answer. To find the amount of the annual deposit now, it is easier to break down the components of the problem. Doing so for each of the following to find your friend's annual deposit, we get: D1) Value of employer's contribution at retirement (1 pt): D2) Value of trust fund at retirement (1 pt): D3) Remaining amount that Steve needs at retirement (1 pt): D4) (Final answer) Amount to save each year under these assumptions (1 pt):

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 = 5% (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 $21,048

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 = 5% / 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 $1,231

B]

If Steve waits seven years, annual deposit required to meet retirement goal is calculated using PMT function in Excel :

rate = 5% (annual interest rate)

nper = 23 (23 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 $33,754