Amount you've slresdy osved Desired monthly income in todsy's dollare Required monthly income when you retire Required balance on day you retire Required monthly sovinge to fund retirement Check figure: 32,620.38 Hint, you want the future value of the todsy'z dollar z value st the inflation ratc. Uze annusl compounding for inflation in thiz calculstion. ($4,703,164.06) Check figure: 3,625,590.40 Hint: remember thiz is s monthly snnuity with monthly ratee! Find ite present value. Report se s positive value. 581.64 Report this se s positive value. This uses the =PMT function. Many times, people plan for s retirement income that grows over time--becsues they recognize that if they have s conetont retirement income, their purchseing power will decline over time, so their atondard of living will decline during their retirement. This, of course, requires that you sccumulste more to fund this growing retirement poyment. It is esey to find the present value of a growing snnuity using s modificstion of Excel's built-in PV function. If r is your monthly dizcount rate (rate of return on your invetmente) and g is the monthly rate st which you'd like your monthly retirement income to grow, then in the PV(rste, periods, payment, [fv]) function, replace rate with (rg)(1+g) and replace poyment with CFO = CF1 (1+g). So if you want the present value of a growing snnuity over 12 months with s dizcount rate (which iz the rate you expect to earn on your invetmente) of 0.75% per month (9\% s yesr) and you want your income to grow st 0.25% per month ( 3% s yesr) where your first withdrawsl will be $10,000 st the end of the first month, then you'd use =PV(0.75%,12,1000011.0025) and get $115,908. Now, try thiz out yourzelf. Uze the formula I gove you and check your snzwer by calculsting the growing paymente explicitly and finding their NPV. dizcount rate growth rate monthe CF1 PV growing snnuity with formuls Month growing payment NPV' using NPV' function 0.75% 0.25% 12.00 10,000.00 (115,908.83) check figure. It iz negstive becsuze the PV function returnes negstive value when you put in positive inputs. 10,000.00 This should be the some se the PV of the growing annuity, sbove, except now positive. 8 9 9 11 12 Now, using the inputz to the retirement problem sbove (beginning retirement income in B11), how much must you sccumulste if you want your retirement income to grow by 0.25% s month ( 3% s yesr--which i z the inflation rate)? Required balance on the day you retire if your income iz to grow at 37 s year compounded monthly: \#\#\#\#\#\# Check figure. Report se s positive number. Required monthly sovinge to schieve this balsnce. PMIT function. iT8.53 Check figure. Report se s positive number. Now create an amortiestion table that showe your investment balance st the end of esch month during your pre-retirement asvinge period. Your ending balonec thould be the smount your apecify. Your monthly sovinge iz $0.00 what you calculated above You want to accumulate $0.00 what you calculated above Ending balonce from smortisation toble Cell reference the ending balance on the amortiestion table. The value should equal the amount you want to sccumulate, above. Beginning monthly rate of osvinge [+] or 10,278.46 Check figure