It’s late November 2021. You are an aspiring personal financial consultant assisting a client in planning for retirement. Eileen Dover has saved $680,000 toward her retirement. She plans to invest 15% of her salary each month (assume the first deposit is at the end of January 2022) until she retires at the end of May, 2033.

Eileen’s 2021 annual (calendar year) salary will be $94,000, and she expects her salary to increase by 1.5% annually until retirement.

Upon retirement, Eileen plans to make monthly withdrawals from her retirement account for 27 years. The first withdrawal will be at the end of June, 2033. She believes that her withdrawals will have to increase by 3% annually to keep pace with inflation. At her death, she hopes to leave $2,000,000 for her children and grandchildren.

She believes her investments (including the amount already saved) will earn about 7% annually until retirement. At retirement, she’ll transfer the money to a more conservative fund which is expected to earn only 5% thereafter.

Eileen has these questions:

1. How much will I have at retirement?
2. For a comfortable retirement, I figure I’ll need 75% of the monthly salary I’ll be earning in my final year of work. Is this reasonable given the numbers you’ve developed? If not, what are my options?

Required: You must construct an Excel spreadsheet to solve this problem and write her a letter (in good “Business Communications class” form) in response to her questions. You may not work with others, but you may ask your supervisor (me) any questions needed to clarify Eileen’s situation, requirements, or expectations.