Question

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

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

   $  

2. How much will she have at 70? 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? Round your answers to the nearest cent.

   Annual withdrawals if she retires at 65: $

   Annual withdrawals if she retires at 70: $

Answer 1

Ans:- (a)In this part we need to find the Future value.we can find this easily by the FV function of excel. Annual savings is given $15000 i.e PMT, Rate is given 11%.

Number of years she is going to save will be 65 - 23 = 42 i.e nper is 42.

FV function of excel is given by FV(rate,nper,pm,pv)

=FV(11%,42,-15000,0)

=$107,84,668.45

(b) In this part also we need to find the future value. Rate is 11%, nper will be 70-23 = 47, pmt is 15000 then at 70 she will have

= FV(11%,47,-15000,0)

= $182,66,210.54.

(c) In this part, we need to find the PMT i.e annual amount she will get for 20 years if she retires at 65 with the total savings of $107,84,668.45. we can use the PMT function of excel to calculate the required amount.

PMT function of excel is given by PMT(rate,nper,PV,FV).

Rate is 11%,nper will 20,PV is 107,84,668.45

=PMT(11%,20,-10784668.45,0)

=$13,54,291.61.

(d) In this part, we need to calculate the annual amount she will get if she retires at age 70.we do the same as the above .nper will be  15. rate will the same.PV will be $18666210.54

=PMT(11%,15,-18266210.54,0)

=$25,40,194.94.

Note: Please give thumbs up if this answer helps you thank you.