Reference no: EM131854728
Suppose that you have a friend who is turning 34 today and that her salary for the next year is $95,000. Suppose that she is paid on an annual basis at the end of each year and that she will receive $95,000 one year from today, out of which she will make a deposit into her retirement account.
She plans to retire at age 68 (when she earns her last salary and makes her last deposit into her retirement account) and plans to make her first withdrawal from her account at age 69.
She expects to need an amount as retirement income at that point that is 90% of her salary for the last year she works. After age 69, her retirement income will need to grow with inflation. All living expenses between now and retirement will be covered by her annual salary in each year. All living expenses after retirement will be covered by her retirement income in each year, except for the medical expenses described below.
She has saved $85,000 to date. Assume that her employer will contribute 4% of her annual income to her retirement account while she works and that she will also make an additional contribution each year until she retires. Once she reaches retirement she will not make any additional contributions to her account, although she will continue to earn a return on her investments.
Assume that, given the state of health care costs and a family history of healthcare issues, she will need to have an amount of $850,000 available by age 60 to cover healthcare costs above and beyond what her health insurance plan is likely to cover.
Treat this figure as an expected one-time outflow at this point. Of course, actual future health care costs are unlikely to be a one-time event and to differ from this amount. What we are doing is treating the $850,000 as the present (or future) value at age 60 of all possible additional costs over time. Save based on the prediction that the one-time expense will occur at that time. Assume that these health care costs will be paid out of her retirement account.
She predicts that her salary will grow at a rate of 3% and that her retirement income needs will grow at 2.5% to reflect long-term inflation forecasts. The higher growth rate in her salary reflects the expectation that she will receive promotions and performance raises and that absent promotions and performance pay her salary will keep pace with inflation.
The appropriate interest rate for her working life is 10% and declines to 6% after she retires. Assume interest is compounded annually and you do not need to adjust the rates of return for expected inflation.
Assume that she will live until age 88, and that her last annual withdrawal occurs at that point. Also, assume that she wishes to have a balance of $2 million remaining at 88. This balance is available as a safety net if she lives longer than expected and as a bequest to her children if she doesn't live past 88. Ignore spousal income, savings, and retirement needs.
Assignment:
(a) Build a spreadsheet model to determine the % of her coming year's salary that she needs to save to cover her future financial needs. Assume that she will save the same % of her salary each year during her working life, and that the $ amount she saves each year will grow at the same rate as her salary. Solve directly for the amount using the appropriate TVM equations (e.g., present value of a growing annuity).
(b) Also create a row or column in your spreadsheet model that tracks the amount in her investment account. The balance in each year brings the prior year's balance forward and then adjusts for any additional cash inflow or outflow in the current year. This calculation can be used to verify that your answer from part (a) is correct.
Create a line graph that uses her account balance on the vertical axis and age on the horizontal axis. At what age is her account balance at its maximum? Is this at age 67, 68, 69, or some other point? Explain your answer.
Describe other relevant features of your graph of the account balance across time.
*When you do my assignment, I want you to use the excel file I attached with it. thanks.