Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page.

Robin's Retirement

[Note: This problem is an application of the principles learned in Chapter 3 that goes beyond the examples provided in the textbook. But please be assured that all of the principles that must be used for this problem are covered well in Chapter 3 of the textbook and the study guide for chapter 3. If you have any difficulties in understanding the details of the problem please ask the instructor for clarifications.]

Robin is 25 years old today, and attends Small Peak College. She currently has a seasoned whole life insurance policy on her life which has a current cash value of $20,000. She is confident she can earn 4% annual interest for the foreseeable future in this policy, and all calculations should be based on this 4% annual rate of interest. She would like to know how much money she will need to set aside each month starting today until one month before her 75th birthday so that she can have a monthly income of $4,000 per month starting on her 75th birthday and continuing until the month before she dies on her 100th birthday. Said another way, she wants to start saving now, at the rate of 4% annual interest, by investing a monthly amount each month until one month before her 75th birthday. Then on her 75th birthday she will start withdrawing $4,000 per month until her money is all gone when she dies penniless on her 100th birthday.

Please work this problem in 5 steps as follows. Each part is worth 2 points:

1. How much cash value will she need to have in the policy so that she will be able to withdraw the $4,000 per month starting on her 75th birthday until the day she dies at age 100? __________________

2. How much will Robin need to add to her insurance policy each month starting on her 25th birthday so that she will have the amount you calculated in #1 above available on her 75th birthday? __________

3. What is the total of the dollar payments into the insurance policy during the 50 year period? _____________

4. What is the total of the dollar income that Robin will withdraw from the insurance policy in her 25 years of retirement? ___________

5. If you have done the above calculations correctly, your number for question #3 above is considerably less than your number for question #4 above. Provide a short explanation of how Robin can save such small amounts during her working years and then withdraw such relatively large amounts during her retirement years.