Question

*PLEASE SHOW SOLUTIONS USING MICROSOFT EXCEL FORMULAS ONLY, thank you!*

Sophie is 30 years old. However, she is already planning for retirement. She plans on retiring in 35 years when she will be 65 years old. Sophie believes she will live until she is 95.

In order to live comfortably, she thinks she will need to withdraw $15,000 every month during retirement. These monthly withdrawals will be made at the beginning of each month during retirement. Being a lover of animals, Sophie has pledged to donate $500 each quarter to “World Wide Fund for Nature” during her retirement. The first donation will occur one quarter after she retires. In addition, she would like to establish a scholarship at Ryerson. The first payment from the scholarship would be $50,000. Thereafter scholarship payments will be made every year. The first scholarship payment would be made 5 years after she retires. In order to keep pace with inflation, Sophie would like the amount of scholarship payments to increase by 2% each year. She wants the payments to continue after her death, which will last forever. During retirement, Sophie expects to earn 5% per year compounded semi-annually.

She currently has $10,000 in her investment account that earns 7% interest per year compounded monthly. Sophie currently contributes $100 every week to her RRSP. These contributions are made at the end of each week until she retires at 65. Sophie expects to earn 8% per year compounded annually on her RRSP contributions prior to retirement.

a) How much money does she need when she retires at the age of 65?

b) How much money will she have when she retires?

c) In order to finance any shortfall, Sophie will make monthly contributions into a new retirement account. This new retirement account will earn 9% per year compounded semi-annually. The contributions will be made at the end of each month until she retires at 65. How much must she contribute each month to the new retirement account?

Answer 1