Question

You are completing a retirement plan and you expect to work for an additional 25 years until retirement. You plan for a retirement horizon of 20 years. During retirement, you would like to withdraw an annual income equal that affords purchasing power equal to $150,000 today (so your withdrawals must adjust for inflation). Assume you will make 20 annual withdrawals during retirement with the first occurring one year following your retirement date. You would like to also have $1,000,000 in your account as a bequest (or to fund additional expenses should you live longer than expected) at the end of the 20-year retirement horizon. You currently have $150,000 in a savings account. You will make additional monthly deposits to this account as described below in order to meet your retirement goals. Assume a constant 7% interest rate per year (EAR) and a constant 2% inflation rate per year (also treat this as an EAR).

Answer the following in your uploaded response. Label the separate parts clearly and show your work.

(7 points) Determine the value at retirement date of your retirement goals including the desired annual withdrawals and the bequest amount.

(6 points) Suppose you will deposit $3,500 per month during your working life (first deposit one month from now). Given your current savings and these deposits, will you meet your retirement goals? Determine the excess or shortfall in your account, relative to your goal, as of your retirement date.

(6 points) Now suppose that you make a deposit of $3,500 one month from now, but then increase subsequent deposits at the rate of inflation. Determine the updated excess or shortfall in your account as of your retirement date.

(6 points) Finally, suppose you instead decide to make deposits of $4,000 per month (first deposit in one month) for a certain number of months (call this "N"). Thereafter you will cut down your work hours and reduce your remaining deposits by one-half (to $2,000/month) until full retirement 25 years from now. Determine, to the closest integer, the number of months N you will need to contribute $4,000 so that you exactly meet your retirement goal.

Answer 1

1st withdrawal (to be made after 26 years) = $150000*1.02^26 and all subsequent withdrawals will increase by 2% p.a.

value at retirement date of your retirement goals  

= present value of 20 payments + present value of bequest amount

= (150000*1.02^26/1.07+150000*1.02^27/1.07^2+....+150000*1.02^45/1.07^20 )+ 1000000/1.07^20

=150000*1.02^26/1.07 * (1-(1.02/1.07)^20)/(1-1.02/1.07) + 1000000/1.07^20

= $3350910.52

Let a deposit of $3,500 per month is made for 25 years or 300 months (first deposit one month from now)

Monthly interest rate = 1.07^(1/12)-1 = 0.0056541

So, Future value of savings of $150000 + Future value of deposits

= 150000*1.0056541^300 + 3500/0.0056541*(1.0056541^300-1)

= $ 3,554,761.48

As the Future value of savings and deposits is more than what is required, the deposits of $3500 is sufficient to meet the retirement goals

Excess amount in account, relative to goal, as of retirement date = $3554761.48- $3350910.52 = $203850.96

If first deposit is $3500 and all subsequent deposits are higher by inflation rate

Monthly inflation rate = 1.02^(1/12)-1 = 0.0016516

Future value of savings of $150000 + Future value of deposits

= 150000*1.0056541^300 + (3500*1.0056541^299+3500*1.0016516*1.0056541^298+....+3500*1.0016516^299)

= 814114.90+ 3500*1.0056541^299*(1-(1.0016516/1.0056541)^300)/(1-1.0016516/1.0056541)

= 814114.90 + 3311350.67

=$4125465.56

Excess amount in account, relative to goal, as of retirement date = $4125465.56- $3350910.52 = $774555.05

Suppose $4000 is contributed for N months and then $2000 for (300-N) months

Future value of savings of $150000 + Future value of deposits = 3350910.52

150000*1.0056541^300 +(4000*1.0056541^299+.....+ upto N months) + (2000*1.0056541^(299-N)+.... upto 300-N months)  = 3350910.52

=>814114.90+ (4000*1.0056541^299+.....+ upto 300 months)- (2000*1.0056541^(299-N)+.... upto 300-N months) = 3350910.52

=> 814114.90+ 4000/0.0056541*(1.0056541^300-1) - 2000/0.0056541*(1.0056541^(300-N)-1) = 3350910.52

=> 2000/0.0056541*(1.0056541^(300-N)-1) = 595371.90

1.0056541^(300-N) =2.6831597

=> 300-N = ln(2.6831597)/ln(1.0056541) = 175.05

N = 124.94

So , One needs to contribute $4000 for 125 months and then $2000 for remaining 175 months to achieve the retirement goals