I have this project due for finances, however, I'm unsure as to what steps to take with it after I gather the information needed. My project is about making a retirement plan for a client who knows little to nothing about finance. They are planning to retire at the age of 67 and I have to make a spreadsheet of ages 62, 67, and 70 regarding how much they should be saving during those years. I got how much the social security would give an individual monthly at those ages, and the interest rate for each of them but I am not sure how I will translate this into a spreadsheet, and further more a memo or technical appendix.

My question is; how will I proceed if I have the information needed?

Here are the instructions for each;

The scenario

Your team is hired as a financial advisor firm. Your client, a single person, needs help with their retirement planning. The client is an educated person but their knowledge of finance is limited (hence the need for financial advice).

First, you will need to do some research, then perform some analysis, then communicate your work. There will be three outputs resulting from your work, plus a presentation.

1. All of your analysis will be in a single spreadsheet file, organized as you see fit. Include screenshots, directions etc to make sure that your spreadsheet is easy to follow.
2. You need to write a memo addressed to your client.

• The memo will include text (paragraphs), graphs, and tables organized in an efficient, professional way. Your memo will probably be a few pages, including graphs and tables. Please think very carefully about what goes in your memo. Consider your clients knowledge of finance.
• Make sure that the memo is complete. They will probably not want to see the details of your spreadsheet work, so everything you want to show (summary tables, graphs, etc to support your analysis) should be in the memo.

3. You also need to write a technical appendix that a finance student can use to replicate your work. The details of the technical appendix are below.

Retirement at the age of 67

In Vanguard calculator, enter your clients age, 67 for retirement age, your assumed salary from the Social Security calculator, 0 for annual savings and 0 for already saved. Then decide on how much the client will need upon retirement (default is 85% of current income, but you can change this as you see fit.) Then, add the Social Security figure from above.

• Start with an expected return of 7%. You will get a figure for what youll need and what you may have. Make sure your numbers are such that What youll need is more than What you may have. In other words, the client is facing a deficit.
• We assume that the client will live roughly 13 years (150 months) after retirement. Given an APR of 7%, what will be the present value of these 150 monthly deficits on the day your client retires?
• Lets say you advise your client to invest $X every month starting next month until their retirement day so that they will accumulate enough to make up for the expected deficit. What will X be?
• Repeat your calculations after changing the expected return to 5%. Comment on the impact of lower investment rate of return.

Retirement at the age of 62

Using the Social Security income for this age and 62 for the retirement age, repeat the Vanguard calculator. Use 7% APR first and assume that the client will live to 80 years again. Then, use 5% and repeat your analysis.

Retirement at the age of 70

Using the Social Security income for this age and 70 for the retirement age, repeat the Vanguard calculator. Use 7% APR first and assume that the client will live to 80 years again. Then, use 5% and repeat your analysis.