Calculations are done in Excel using formulas. Show Work Case Information: Years until retirement:30 Amount to withdraw each year:$ 100,000 Years to withdraw in retirement:25 Interest rate:8.5% Inflation rate:3.0% Employer's annual contribution:$ 2,100 Years until trust fund distribution:25 Amount of trust fund distribution:$ 29,500 In order to answer any of these questions, first we need to know how much your friend will need when she is ready to retire. Since this amount will be the same for each of the parts of the problem, solve for this amount below: Amount needed at retirement: 1The amount your friend must save each year to fund her retirement is: Amount to save each year: 2The lump sum your friend must deposit today to fund her retirement is: Lump sum deposited today: 3To find the amount of the annual deposit now, it is easier to break down the components of the problem. Doing so for each of the following to find your friend's annual deposit, we get: Value of employer's contribution at retirement: Value of trust fund at retirement: Amount to save each year now: 4Annual retirement payment in 31 years equal to $100,000 in today's dollars 5Growing annuity factor for retirement period (wo r-g) Dividing by r-g Amount needed at retirement 6PV now of amount needed at retirement Growing annuity factor for working period (wo r-g) Dividing by r-g Amount to save each year 7Amount needed to save this year and then increasing by 3.0% each year to reach the amount needed at retirements