Question

Your client is 32 years old. She wants to begin saving for retirement, with the first payment to come one year from now. She can save $14,000 per year, and you advise her to invest it in the stock market, which you expect to provide an average return of 10% in the future.

1. If she follows your advice, how much money will she have at 65? Do not round intermediate calculations. Round your answer to the nearest cent.

   $  

2. How much will she have at 70? Do not round intermediate calculations. Round your answer to the nearest cent.

   $  

3. She expects to live for 20 years if she retires at 65 and for 15 years if she retires at 70. If her investments continue to earn the same rate, how much will she be able to withdraw at the end of each year after retirement at each retirement age? Do not round intermediate calculations. Round your answers to the nearest cent.

   Annual withdrawals if she retires at 65: $

   Annual withdrawals if she retires at 70: $

Answer 1

There are two key problems in this question:

Part a & b wherein, the amount accumalated needs to be figured out.

Part c & d wherein, the payout each year needs to be figured out.

Part a: Amount accumalated till 65th year of life

This can be calculated using the future value formula in excel as shown below:

Particulars		Comments
Yearly Payment	14000	PMT
Rate of Return	10%	Rate
Years to Retirement (65-32)	33	Nper
Present Value	0	No investment assumed
Future Value	$3,111,521.62

The formula to be used for future value calculation is: =FV(10%,33,-14000,0,0)

Hence, the amount accumalated at the end of 65th Year is $ 3,111,521.62

Part b: Amount accumalated till 70th year of life

This can be calculated using the future value formula in excel as shown below:

Particulars		Comments
Yearly Payment	14000	PMT
Rate of Return	10%	Rate
Years to Retirement (70-32)	38	Nper
Present Value	0	No investment assumed
Future Value	$ 5,096,608.08

The formula to be used for future value calculation is: =FV(10%,38,-14000,0,0)

Hence, the amount accumalated at the end of 65th Year is $ 5,096,608.08

Part c: Withdrawal at the end of each year for next 20 years

We can calculate the yearly withdrawal using the PMT formula as given below:

Particulars		Comments
Rate of Return	10%	Rate
No. of years	20	Nper
Present Value	3111521.62	Accumulated investment at the end of 65th year
Future Value	0.00	Ending value of investment
Yearly Withdrawal	$ 365,478.16

The PMT formula to be used is: = PMT(10%,20,-3111521.62,0,0)

Hence, the yearly withdrawal possible for the next 20 years is $ 365,478.16

Part d: Withdrawal at the end of each year for next 15 years

We can calculate the yearly withdrawal using the PMT formula as given below:

Particulars		Comments
Rate of Return	10%	Rate
No. of years	15	Nper
Present Value	5,096,608.08	Accumulated investment at the end of 70th Year
Future Value	0.00	Ending value of investment
Yearly Withdrawal	$ 670,070.31

The PMT formula to be used is: = PMT(10%,15,-5096608.08,0,0)

Hence, the yearly withdrawal possible for the next 15 years is $ 670,070.31