Reference no: EM13478863

Cathy and John from last week need more assistance.  As a reminder, here are their assets:

- John 401K - $60,000

- Cathy Trading account - $100,000

- John Roth IRA - $15,000

- John IRA - $195,000

- CD - $25,000

- Cathy 401k at old job - $17,000

- Checking and Savings total $6,000

Cathy and John have about $700 every month left over after paying for expenses.   Their joint annual income is $112,000.  They are both 48 years old.  Monthly living expenses during retirement are $7,000.  Assume inflation is 4% and the nominal rate of return is 8%.  His social security kicks in at age 66 and he will draw $2,600 per month.  She will begin at age 66 too and will draw based on his benefit. 

You will need to input the information in the attached spreadsheet.  You will also have to add all formulas including using the "goal seek" function.  Do NOT turn in a report that looks like Worksheet 14.1 shown on page 477.  If you do this to answer the questions, you will receive no credit.  There is not an example in the book so follow the instructions carefully.Make sure you send me both a spreadsheet and a word document with your answers.

- Column Headers:                                                                                                            

o Age                                                                                                                                       

o Annual Spending Needs which begin at retirement                                         

o Annual Contributions to investments from now until retirement

o Income derived from other sources                                                                       

o Net spending from investments (adjusted for inflows and outflows)

o Net Rate Of Return                                                                                                                        

o Ending Adjusted Balance on Investments

- Reminders:

o Begin the plan at their current age and run through age 90

o Reduce the rate of return directly by inflation   

o Make sure you present everything in TODAY's DOLLARS, not future dollars

1. Cathy and John would like to retire when they are 65.  Using their retirement assets ONLY, what is the annual savings required to fully fund retirement if the last dollar is spent at their age 90?

2. John is sort of dreaming of leaving the work force early.  What amount of annual savings is required to fully fund retirement at age 62 for both if the last dollar is spent at age 90?

3. Based on the information you have been given:

a. Can they retire at age 62?

b. Why or why not?