Complete a scenario analysis (which includes a scenario summary report) using the input variables (changing cells): (1) Number of years contributing; |(2) Anticipated Interest Earned in the pre-retirement period; and (3) Number of years withdrawing. Percentage increase / decrease 20% Retirement Analysis of Projected Distributions INPUT AREA Pre-retirement Current retirement account balance $100,000 Number of years contributing 30 Anticipated interest earned PRE-retirement (APR) 7.50% Periodic contribution amount $400 Contribution frequency 1 (Select 1 for monthly, enter 2 for semi-monthly, and 3 for weekly) The result cell is the Anticipated periodic distribution. Years contribute 30 Changing Variables: Pre-APR contribute 7.50% Years withdraw 25 Scenario: Base Best Worst Create a (1) base case; (2) best case; and (3) worst case scenario Post-retirement: Number of years withdrawing Anticipated interest earned post-retirement (APR) Distribution frequency (Select 1 for monthly, enter 2 for semi-monthly, and 3 for weekly) 25 4.50% Fill in the shaded area above using the 20% increase/decrease in the original value ... i.e. 30 x (1 + % change) HINT: for the best case, use inputs that will result in a higher periodic retirement distribution Do NOT use the cells in the grid above as changing cells for the scenario report The base case uses the values originally given in the worksheet for each of the input variables. The best/worst case scenarios increases/decreases each input variable by 20% of the original value. For example, in the best case, the #years contributing and APR prior to retirement would increase by 20% and the # of years withdrawing would decrease by 20%. Vice versa for the worst case scenario. 2 CALCULATIONS AREA Pre-retirement Periodic rate earned Number of periods Total account value at retirement Use the grid provided in the range H7:J10 to calculate the scenario inputs. 0.63% 360 $1,481,132 Place you name in the Scenario Summary comments Post-retirement: Periodic rate earned Number of periods 0.19% 600 Remember to range name changing and result cells (Columns D) before creating the scenario summary report and use the input/changing cells in the retirement calculator and NOT the grid (H7:J10) when creating the scenarios. OUTPUT AREA Anticipated periodic distribution (i.e. payment) $4,114 Do NOT change any of the values in the retirement analysis Complete a scenario analysis (which includes a scenario summary report) using the input variables (changing cells): (1) Number of years contributing; |(2) Anticipated Interest Earned in the pre-retirement period; and (3) Number of years withdrawing. Percentage increase / decrease 20% Retirement Analysis of Projected Distributions INPUT AREA Pre-retirement Current retirement account balance $100,000 Number of years contributing 30 Anticipated interest earned PRE-retirement (APR) 7.50% Periodic contribution amount $400 Contribution frequency 1 (Select 1 for monthly, enter 2 for semi-monthly, and 3 for weekly) The result cell is the Anticipated periodic distribution. Years contribute 30 Changing Variables: Pre-APR contribute 7.50% Years withdraw 25 Scenario: Base Best Worst Create a (1) base case; (2) best case; and (3) worst case scenario Post-retirement: Number of years withdrawing Anticipated interest earned post-retirement (APR) Distribution frequency (Select 1 for monthly, enter 2 for semi-monthly, and 3 for weekly) 25 4.50% Fill in the shaded area above using the 20% increase/decrease in the original value ... i.e. 30 x (1 + % change) HINT: for the best case, use inputs that will result in a higher periodic retirement distribution Do NOT use the cells in the grid above as changing cells for the scenario report The base case uses the values originally given in the worksheet for each of the input variables. The best/worst case scenarios increases/decreases each input variable by 20% of the original value. For example, in the best case, the #years contributing and APR prior to retirement would increase by 20% and the # of years withdrawing would decrease by 20%. Vice versa for the worst case scenario. 2 CALCULATIONS AREA Pre-retirement Periodic rate earned Number of periods Total account value at retirement Use the grid provided in the range H7:J10 to calculate the scenario inputs. 0.63% 360 $1,481,132 Place you name in the Scenario Summary comments Post-retirement: Periodic rate earned Number of periods 0.19% 600 Remember to range name changing and result cells (Columns D) before creating the scenario summary report and use the input/changing cells in the retirement calculator and NOT the grid (H7:J10) when creating the scenarios. OUTPUT AREA Anticipated periodic distribution (i.e. payment) $4,114 Do NOT change any of the values in the retirement analysis
