A B D E F G H L M Pre-Retirement Number of Years Contributing Retirement Analysis of Projected Distributions INPUT AREA Pre-retirement Current retirement account balance Number of years contributing Anticipated interest earned PRE-retirement (APR) Periodic contribution amount Contribution frequency (Select 1 for monthly, enter 2 for semi-monthly, and 3 for weekly) $35,000 25 7.50% $750 3 1 Complete a 2-dimensional data table (G4:M15). 2 The output cell is the Anticipated Periodic Distribution. 3 4 The horizontal input cell is pre-retirement number of 5 years contributing (ranging from 12 to 22 years 6 in increments of 2 years) 7 8 The vertical input cell is pre-retirement APR earned 9 (ranging from 5% to 10% in increments of 0.5%) 10 11 Use CUSTOM formatting to display a blank cell (empty 12 string "") in the cell containing the reference to the 13 output cell 14 15 Apply CONDITIONAL formatting to the data table to 16 display all cells with a periodic distribution less 17 than $2,000 with bold font and a light yellow fill. 18 19 What combination of Pre-retirement APR and 20 pre-retirement years withdrawing provides a 21 periodic distribution of approximately $2,500? 22 23 24 25 26 27 28 29 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) Pre-retirement Interest Earned 20 4.50% 3 CALCULATIONS AREA Pre-retirement Periodic rate earned Number of periods Total account value at retirement 0.14% 1300 $3,094,169 Post-retirement: Periodic rate earned Number of periods 0.09% 1040 OUTPUT AREA Anticipated periodic distribution (i.e. payment) $4,513 30 Do NOT change any of the values in the retirement analysis 21 A B D E F G H L M Pre-Retirement Number of Years Contributing Retirement Analysis of Projected Distributions INPUT AREA Pre-retirement Current retirement account balance Number of years contributing Anticipated interest earned PRE-retirement (APR) Periodic contribution amount Contribution frequency (Select 1 for monthly, enter 2 for semi-monthly, and 3 for weekly) $35,000 25 7.50% $750 3 1 Complete a 2-dimensional data table (G4:M15). 2 The output cell is the Anticipated Periodic Distribution. 3 4 The horizontal input cell is pre-retirement number of 5 years contributing (ranging from 12 to 22 years 6 in increments of 2 years) 7 8 The vertical input cell is pre-retirement APR earned 9 (ranging from 5% to 10% in increments of 0.5%) 10 11 Use CUSTOM formatting to display a blank cell (empty 12 string "") in the cell containing the reference to the 13 output cell 14 15 Apply CONDITIONAL formatting to the data table to 16 display all cells with a periodic distribution less 17 than $2,000 with bold font and a light yellow fill. 18 19 What combination of Pre-retirement APR and 20 pre-retirement years withdrawing provides a 21 periodic distribution of approximately $2,500? 22 23 24 25 26 27 28 29 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) Pre-retirement Interest Earned 20 4.50% 3 CALCULATIONS AREA Pre-retirement Periodic rate earned Number of periods Total account value at retirement 0.14% 1300 $3,094,169 Post-retirement: Periodic rate earned Number of periods 0.09% 1040 OUTPUT AREA Anticipated periodic distribution (i.e. payment) $4,513 30 Do NOT change any of the values in the retirement analysis 21
