Suppose that you are 32 today. You plan to retire at age 67 and will need an amount as retirement income at 68 that has the same purchasing power as your final years salary. All living expenses between now and retirement will be covered by your salary for each year. All living expenses after retirement will be covered by your retirement allocation for each year. Your retirement allocation will come from two sources withdrawals from your retirement account and Social Security benefits. Assume that your first withdrawal from your retirement account for post-retirement living expenses occurs on your 68^th birthday. Assume also that you will start receiving Social Security benefits at age 68. If you were to begin receiving SS benefits today, you would receive $25,000. The amount that you will receive in the future depends on the adjustment made for inflation (more on this below).

You will provide for your retirement needs (above SS benefits) through three types of saving your own annual saving in the future, your employers annual contributions, and your current accumulated savings to date. Suppose that you are paid on an annual basis and that your salary for next year will be $105,000 (to be paid on your 33^rd birthday). Assume that your next deposit and employer contribution into your retirement account will occur one year from today (age 33) and that you will continue to deposit each year through age 67. In addition to your own saving, assume that your employer will also contribute an amount equal to 6.25% of your salary at each point in time. Assume that you have saved $79,000 to date. Finally, assume that you wish to have $2.0 million at the end of your expected life (age 90) as a safety margin in the event that you live longer than expected. If you dont live long enough to use this safety margin, 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 living expenses will need to 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 appropriate interest 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 estimate of inflation is 2.5% and that Social Security payments will be adjusted at the same rate of 2.5%.

Question:

Part A: What % of your age 33 salary do you need to save to meet your expected needs? Assume that you will save a constant % of your salary throughout your working life. Find the solution to this problem by taking all cash flows to the present (i.e. age 32). Build a spreadsheet model to answer this question and solve using a TVM model (including present or future values of the relevant annuities or one-time amounts). Solve first for a $ amount of savings at 33 and then convert this into a % of your salary received at 33. Note that you will be saving the same % each year (age 33 through 67 or for 35 years) and that your salary and savings will grow at a constant rate. Please use in excel.

Part B: Create a row in your spreadsheet model that tracks the amount in your retirement account balance. The account balance in each year will need to bring the prior years balance forward one year at the appropriate rate of return and then adjust for current year cash flows. Create a line graph that uses 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. What is the final balance in your retirement account? Why?

Of this question, Part B is what I'm looking for.