hi i need help with this TVM retirement problem. i would just like a step by step written answer to this. with the calculations and formulas

TIME VALUE OF MONEY ASSIGNMENT You have a friend who is turning 35 today. Her salary for the next year is $145,000. Suppose that she is paid on an annual basis at the end of each year and that she will receive $145,000 one year from today, out of which she will make a deposit into her retirement account. It is her goal to retire at age 65 (when she earns her last salary and makes her last deposit into her retirement account). She plans to make the first withdrawal from her account at age 66. She expects to need an amount as retirement income at that point that is 75% of her salary for the last year she works. After age 66, 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, except for the higher education expenses described below. All living expenses after retirement will be covered by her retirement income in each year. She has saved $300,000 to date. Assume that her employer will contribute 5% 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. Your friend is an avid surfer, and she currently owns a two-week timeshare in Hawaii. She plans to sell this timeshare at the age of 60 for the amount of $700,000 (net of any taxes, selling costs, fees, etc.), to be immediately deposited into her retirement account. Your friend has two young children (twins). Assume that, given the state of higher education costs, she will need to have 500,000 available by age 53 to cover anticipated out-of-pocket college costs. Treat this figure as an expected one-time outflow at this point. Of course, actual college costs are unlikely to be a one-time event and might differ from this amount. We are effectively treating the $500,000 as the present value at age 53 of anticipated higher education costs from that point forward. Save based on the prediction that a one-time expense will occur at this time (age 53). Assume that this amount will be paid out of her retirement account. Your friend predicts that her salary will grow at a rate of 3.5% and that her retirement income needs will grow at 2% 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 7% and declines to 4% after she retires. Both rates of return are nominal (i.e., include compensation for expected inflation). Thus you will not need to adjust the rates of return for expected inflation. Assume that she will live until age 85, and that her last annual withdrawal occurs at that point. Also, assume that she wishes to have a balance of $2 million remaining at 85. This balance is available as a safety net if she lives longer than expected and as a bequest to her children if she does not live past 85. Ignore spousal income, savings, and retirement needs. 1. Build a spreadsheet model to determine the percentage of your friend's coming year's salary that she needs to save to cover her future financial needs. Assume that she will save the same percentage of her salary each year during her working life, and that the dollar amount she saves each year will grow at the same rate as her salary. This \"baseline\" solution should be prominent and easy to identify near the top of your spreadsheet. You have flexibility in terms of how you solve the problem; however, it is important that your spreadsheet be created such that your solution approach is relatively easy to understand and follow. (See evaluation comments below.) 2. The necessary savings amount relies on many assumptions (problem parameters). Be sure to design your spreadsheet such that key parameters can be easily changed to obtain an updated solution. In particular, at least all of the following parameters should be linked to your solution so that I can change them if desired, and the solution will automatically update: a. b. c. d. e. f. g. h. Current salary (EOY 1) Current savings Interest rate pre-retirement Interest rate post-retirement Employer contribution rate Salary growth rate Bequest goal at end of life College cost amount It should be easy for me to find the input cells for these parameters (do not spread them all over the worksheet). I will spot-check by toggling 4-5 of the parameters and checking that your solution updates appropriately. Finally, conduct a \"what-if?\" analysis based on two specific parameters: the interest rate pre-retirement (ranging from 4% to 9% in one percent increments) and the end-of-life bequest goal (ranging from $500,000 to $3,500,000 in $500,000 increments). Provide a nicely formatted, easy-to-identify table that shows the solution for all combinations of these rates within the specified ranges. Obviously, the {interest rate = 7%, employer contribution = 5%} entry in this table should match your baseline solution. Briefly discuss/interpret the solution you obtain for the case with a $500,000 bequest goal and a 9% interest rate. 3. Create a timeline in your spreadsheet model that tracks the amount in your friend's investment account in the baseline scenario (only). 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 (1) is correct. Create a nicely formatted line graph that plots her account balance on the vertical axis against age on the horizontal axis. At what age is her account balance at its maximum? Is this at age 64, 65, 66, or some other point? Explain your answer. Describe other relevant features of your graph of the account balance across time