Mini Project: Retirement Objectives (5 points) How much do you need to save each year to meet your retirement objectives? Create a spreadsheet that calculates "your number". 1. Calculate how many years you have until retirement. 2. Calculate the future value of your annual spending in retirement. 3. Calculate the present value of your future annual spending in retirement (from Step 2). 4. Calculate annual payments required to achieve PV of Your Number (from Step 3). Deliverables Due 24-Mar Scoring Rubric Metric Points Step 1 correct formula/answer 1 Step 2 correct formula/answer Step 3 correct formula/answer Step 4 correct formula/answer 2 Total 5 Extra Credit: Table that shows cash flows by year Table should automatically update when any variable is changed, i.e. it should not require any manual input or adjustments when changes to any variables are madeStep 1: Calculate how many years you have until retirement. Inputs 1. Birthday [named rage = birthday], 2. Retirement age [named rage = age_retirement] Output Age [named rage = age] = (TODAY() - birthday) / 365 Retirement date = DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) + days_to_retirement) Days to retirement [named rage = days_to_retirement] = (age_retirement - age) * 365 Years to retirement (rounded up) [named rage = years_to_retirement] = ROUNDUP(days_to_retirement / 365, 0) Step 1: How many years until retirement? nputs Birthday 1/28/1976 Retirement age 65 Output Age 46.7 =(TODAY()-birthday)/365 Retirement date 1/11/2041 =DATE (YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())+days_to_retirement) Days to retirement 6,691 =(age_retirement-age)*365 Years to retirement (rounded up) 19.00 =ROUNDUP(days to retirement/365,0) Step 2 (FV Annual Spending): Calculate the future value of your annual spending in retirement. Inputs 1. Annual budget [named rage = budget_annual], 2. Return on investments [named rage = return], 3. Inflation rate [named rage = inflation] (use 2.5%) Output Annual budget (FV) = FV(inflation, years_to_retirement, 0, budget_annual)Step 2: How much do you anticipate spending each year in retirement? Inputs Annual budget (current) 50,000 Return on investment 6.0% nflation rate 2.5% Output Annual budget (FV) $79,932.51 =FV(inflation, years_to_retirement,0,-budget_annual) Step 3 (PV of Your Number): Calculate the present value of your future annual spending in retirement (from Step 2). Inputs 1. Years in retirement [named rage = years_in_retirement] Output "Your Number" (PV) [named rage = retirement_objective] = PV(return, years_in_retirement, spending_retirement, 0, 1) Step 3: How much do you need when you retire? Inputs Years in retirement 30.00 Output "Your Number" (PV) $1,166,272.94 =PV(return, g retirement,0,1) Step 4 (Annual PMT): Calculate annual payments required to achieve PV of Your Number (from Step 3). Inputs 1. Savings (current) [named rage = savings_current] Output Save annual (PMT) = PMT(return, years_to_retirement, savings_current, -retirement_objective, 1) Save monthly (PMT) = PMT(return/12, years_to_retirement*12, savings_current, -retirement_objective, 1) Step 4: How much do you need to save each year (month)? Inputs Savings (current) 25,000 Output Save annual (PMT) $30,476.87 =PMT(return, years_to_retirement, savings_current,-retirement_objective,1) Save (monthly) $2,556.57 =PMT(return/12,years_to_retirement*12,savings_current,-retirement_objective,1)