Estimating the retirement income of two different investor groups based on how they save for retirement. Use excel. For each of the investors do the following under their respective group instructions and use the 3 objective prompts listed above them to organize the results to compare the two groups:

1. Construct the cash flow diagram. Assume the investor can earn 8% (compounded annually) on his or her investments, which is a fairly reasonable assumption for a stock index fund. 2. Calculate the annual payments the investor can expect after reaching age 70 (A21-91)). 3. Calculate the how much the annual payment would be in today's dollars (PWA), assuming an inflation rate of 3%. Group 1 (Investors Sarah and Perry): Sarah graduates from college at age 22 and finds a good job. She diligently begins paying off her school loans. It takes her until the end of 8 years to pay off her loans. She then begins saving for retirement. She saves $1000 at EOY 31. Then increases this amount by the amount of her annual raise (5%) each year until she retires at age 70 (EOY70). Sarah also receives a single profit sharing payment from her company of $10,000 which she invests at EOY 50. Calculate the uniform amount she can withdraw each year from age 71 to age 90 to finance her retirement At age 22, upon completing his education at UWP, Perry gets a job for $70,000/yr. He was very frugal during college and has no student loans. He continues to live in his cheap rental house in Platteville and invests ten percent of his gross income into a retirement plan (assume he does not get any raises). He does this for 8 years (EOY23-EOY30). Perry then gets married and buys a house. He doesn't save anything for awhile. At end of year 35, Perry is only able to save $200. He adds an additional $100 to his savings the next year and continues adding an addition $100 each year until he retires at EOY 70. Calculate the uniform amount he can withdraw each year from age 71 to age 90 to finance his retirement Group 2 (Investors John and Jenny): John graduates from UWP at age 22 and is unable to find a job in the down economy. Five years later, he is finally employed, but it takes another 15 years to pay back the loans he ran up after college. At age 42 (EOY42), John begins planning for retirement. For 5 years (EOY42-EOY46), he sets aside $2000 each year. Then he is laid off. At age 50 he secures an excellent job. He begins saving $5,000 at age 51 (EOY51). He increases this investment by $100 each of the following years. He retires at age 70 (EOY70). Calculate the uniform amount he can withdraw each year from age 71 to age 90 to finance his retirement Jenny graduates from UWP at age 22. That same year her rich great aunt passes away leaving her with a $90,000 inheritance. Jenny considers buying a new car and putting a down payment on a house. Instead she invests the money in a retirement account (EOY 22). The next year Jenny also starts investing in her company's retirement plan. She invests $1000 the year after she gains the inheritance (EOY23) and increases this amount by $200 each year for the next 15 years (EOY24-EOY38). At this point she decides to quit her job and join the Peace Corps. She doesn't save any more money for retirement. At age 71 (EOY) she begins withdrawing from her retirement account. Calculate the uniform amount she can withdraw each year from age 71 to age 90 to finance her retirement