FINAN 5654
View the step-by-step solution to:

# *Attach Excel Spreadsheets This framework should be applied in recognition of the risk/return tradeoff and realistic associated financial assumptions....

This framework should be applied in recognition of the risk/return trade‐off and realistic associated financial assumptions. The goal of the project is to expose you to financial planning for retirement in a way that reinforces key financial management concepts and analytical tools developed in our course.

Background Information

Finally, assume you wish to have \$2 million at the end of your expected life (age 90) as a safety margin in case you live longer than expected. If you don't live long enough to use this safety sum, you will leave the remaining balance to your heirs at death.

You project that your salary will grow at a rate 1% higher than the long‐term average rate of inflation and that your retirement income needs will grow at the long‐term inflation forecast. The higher growth rate in your salary reflects your expectation that you will receive raises and promotions on top of adjustments for inflation. The assumed investment rate for your working life is 5% above the long‐term inflation rate and declines to 3% above the inflation rate after you retire. Assume that the long‐term inflation forecast is 2.5% and that Social Security payments will be adjusted annually at the same rate of 2.5%.

Retirement Planning Questions

1. What percentage of your age 33 salary do you need to save to meet your expected needs?

Assume that you will save a constant percentage of your salary throughout your working life.

Find the solution to this problem by taking all cash flows to the present time, which is age 32.

Build a spreadsheet model to answer this question and solve for the needed amounts using a time value of money model that includes the present or future values of the relevant annuities or one‐time amounts. Solve first for a dollar amount of savings at age 33 and then convert this into a percentage of your salary received at age 33. Note that you will be saving the same percentage each year (ages 33 through 67 or for 35 years) and that your salary and savings will grow at a constant rate.

2. In conducting your analysis, upload a row in your spreadsheet model that tracks the amount in your retirement account balance. The account balance in each year should bring the prior year's balance forward one year at the assumed rate of return and then adjust for current year cash flows. In the excel spreadsheet make a line graph that portrays your account balance on the vertical axis and age on the horizontal axis. Looking at the graph, describe how the balance will change across time. Explain any kinks or changes in the slope of the line over your remaining life.

3. To assess progress over time toward achieving the described investment goal by age 67, indicate the needed assets‐to‐salary ratio at the ages of 40, 50, and 60.

4. What is the final balance in your retirement account? Explain why this is the case.

5. Explain how ignoring inflation would affect the financial outcomes.

6. Explain how taxes in general and the use of traditional tax‐sheltered investment accounts

(e.g., 401(k)) and Roth investment accounts (for general retirement investing apart from Roth

IRAs) would affect your financial outcomes. While you may feel free to quantify these effects, you are only required to explain the qualitative effects of them on your expected financial outcomes.

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents