1. Create a spreadsheet to determine retirement savings using the following criteria. Annual Compensation: $60,000 (monthly $5,000) 401K (403B) Contribution Percent 6% Company match 50% up to 6% (e.g. 3% - 50 % x 6%) Tax Rate 25% Annual Investment Return Rate 8% In your spreadsheet, each of these fields should be changeable. For example, if change the base pay to $48,000, your spreadsheet will calculate new retirement amounts. The intent of this exercise is also for you to learn how to do what if analysis for any of the key inputs on retirement savings. Please include your expected 401K (403B) balance at 10 year intervals as follows: 10 years, 20 years, 30 years, 40 years. Most of you will be 32, 42, 52 and 62 (ready for retirement) for these intervals. 2. Since 401K (403B) contributions are taken out pretax, I would like for you to calculate the difference in monthly take home pay under two scenarios: a. Without 401K (403B) contributions b. With 401K (403B) contributions (hint: this number will be lower, but will be offset by your savings net of taxes) The intent of this calculation is to determine how much your take home (paycheck) will be reduced if you enroll in a 401K (403B). O Periods 26 45 20% 30% 32% 40% Periods 1 1.200 1.300 1.320 2 1.742 3 1.000 2.197 2.300 4 1.082 1.170 1.104 1.217 1.400 1 1.960 2 2.744 3 3.842 4 5.378 5 7.530 2.856 5 3.006 4.007 6 1.125 1.25 3.713 4.827 5.290 7 10% 12% 14% 10% 18% 20% 22% 24% 20% 1.020 1.040 1.050 1.000 1.100 1.120 1.140 1.160 1.100 1.200 1.220 1.240 1.260 1.000 1.082 1.124 1.106 1.210 1.254 1.300 1.346 1.392 1.440 1.488 1.530 1.588 1.638 1.061 1.125 1.191 1200 1.331 1.405 1.482 1.561 1.643 1.728 1.816 1.907 2.000 2.007 1.262 1.360 1.464 1.574 1.689 1.811 1.939 2.078 2.215 2.364 2.520 2.604 1.338 1.469 1611 1.762 1.925 2.100 2.200 2.488 2.703 2.932 3.176 3.436 1.419 1587 1.772 1.974 2.195 2.436 2.700 2.995 3.257 3.835 4.002 4.398 1.149 1.316 1.504 1714 1.949 2211 2.502 2.826 3.185 3.583 4.023 4.500 5.042 5.629 6.275 1.172 1.369 1.594 1851 2.144 2.476 2.853 3.278 3.759 4.300 4.908 5.590 6.353 7.206 8.157 1.195 1.423 1.689 1.399 2.358 2.773 3.252 3.803 4.435 5.160 5.987 6.931 1.005 9.223 10.504 1.219 1.450 1.791 2.159 2.594 3.106 3.707 4.411 5.234 6.192 7.305 8.594 10.086 11.806 13.796 1.243 1.539 18 2332 2853 3479 4.225 5.117 6.176 7.430 8.912 10.657 12.708 15.112 17.902 21.199 1.288 1.601 2012 2518 3.138 3.896 4818 5.536 7.288 8.316 10.872 13.215 1.294 1.665 2.133 2720 3.452 4.363 5.492 6.886 1.599 10.699 13.264 2.937 2.251 4.887 6.261 3.797 7.988 10.147 12.839 16.182 2.37 3.172 7.138 6.983 10.541 9.217 14758 I 9 20.661 10 12.166 16.000 281925 10 11 40.496 12 23.298 27.983 56.654 16.012 19.343 16.386 20.175 24759 30.288 13 36.337 79.371 14 48.757 1.319 1.732 1.801 20.319 25.196 25.421 32.000 40.565 31.691 39.374 51.186 111.120 155.568 15 1.346 4.177 5.474 9.296 11.974 15.407 19.742 64.359 1.373 1873 2.540 3.426 4.595 6.130 8.137 10.748 14.129 18.408 24.086 31.243 40.358 51.923 66.542 84.954 217.795 1.400 1.948 2.683 1.700 5.054 9.276 12.468 16.672 22.186 29.384 38.741 50.851 66.461 85.504 112.139 304.913 17 1.428 2026 2854 1996 5.560 7.890 10.575 14.463 19.673 25.623 35.849 48.039 64.072 85.071 112.455 148.024 428.879 1.457 2.107 3.026 4.316 6.116 8.613 12.056 16.777 23214 31.948 43.736 59.568 80.731 108.890 146.192 195.391 587 630 1.486 2.191 3.207 4.661 6.327 1.646 13.743 19.461 27.393 30.338 53.358 73.864 101.721 139.300 190.050 257.316 1.516 2.279 3.400 5.034 7.400 10.804 15.668 22.574 32.324 46.005 65.096 91.582 128.169 178.406 247.065 340.449 3604 5.437 8.140 12.100 17.861 1.546 2.370 25.185 38.142 55.206 79.418 22 113.574 228.350 321.184 161.492 449.393 8.954 13.552 3.820 5.871 20.362 23 30.376 45.008 1.577 2.465 203.480 140.831 66.247 96.889 292.300 593.199 417.539 256.385 24 374.144 35.236 53.109 4.049 6341 79.457 1.850 783.023 118.205 15.179 174.631 23.212 1.808 2.543 542.801 836.613 1171.356 1639.898 2295.857 3214.200 4499.880 6299.831 8819.764 12347.670 17296.737 62.669 40.874 17.000 95.396 25.462 478.905 323.045 144.210 216.542 1.641 2.666 4.282 6.848 25 705.641 1033.590 612.998 407.037 19.040 30.167 917.333 1364.339 25 1.673 2.772 21.325 34.390 784.538 1192.533 1800.927 27 10.835 47414 73.949 4.549 7396 11.918 55.000 87.250 1.707 2.883 4.822 7.588 13.110 14.421 23.884 35.204 63.000 102.967 5.112 8.627 1.741 2.999 44.893 74.009 121.501 29 1.776 3.119 5.418 9.317 15.863 25.750 30 1.811 3.243 5.743 10.063 17.449 29.960 50.950 85.850 143.371 7.506 14.785 28.102 52.800 58.100 180.314 327 997 35 2.000 3.946 2.208 4.801 10.296 21.725 45.259 13.051 188.884 378.721 750.378 288.512 175.936 114.475 137.371 214.642 332.955 164.845 261.364 412.864 197.814 319.474 511.952 512.867 646.212 1004.336 1550.293 2377.224 2015.381 3137.935 814.228 1025.927 1285.550 1545.505 237.376 389.758 634.820 25 19.966 4142075 24201.432 590.668 1053.402 1861.054 3258.135 5653.911 9727.860 16599.217 130161.112 1469.772 2847.038 5455.913 10347.175 19425 689 36118.865 66520.767 700037 697 B 16 17 18 19 20 21 RRRRR 28 40 19 3 9989 Show caption 9 L 11 HENDRANAZARRESRRS 12 13 14 15 16 18 19 20 21 22 24 25 26 27 28 29 30 35 1. Create a spreadsheet to determine retirement savings using the following criteria. Annual Compensation: $60,000 (monthly $5,000) 401K (403B) Contribution Percent 6% Company match 50% up to 6% (e.g. 3% - 50 % x 6%) Tax Rate 25% Annual Investment Return Rate 8% In your spreadsheet, each of these fields should be changeable. For example, if change the base pay to $48,000, your spreadsheet will calculate new retirement amounts. The intent of this exercise is also for you to learn how to do what if analysis for any of the key inputs on retirement savings. Please include your expected 401K (403B) balance at 10 year intervals as follows: 10 years, 20 years, 30 years, 40 years. Most of you will be 32, 42, 52 and 62 (ready for retirement) for these intervals. 2. Since 401K (403B) contributions are taken out pretax, I would like for you to calculate the difference in monthly take home pay under two scenarios: a. Without 401K (403B) contributions b. With 401K (403B) contributions (hint: this number will be lower, but will be offset by your savings net of taxes) The intent of this calculation is to determine how much your take home (paycheck) will be reduced if you enroll in a 401K (403B). O Periods 26 45 20% 30% 32% 40% Periods 1 1.200 1.300 1.320 2 1.742 3 1.000 2.197 2.300 4 1.082 1.170 1.104 1.217 1.400 1 1.960 2 2.744 3 3.842 4 5.378 5 7.530 2.856 5 3.006 4.007 6 1.125 1.25 3.713 4.827 5.290 7 10% 12% 14% 10% 18% 20% 22% 24% 20% 1.020 1.040 1.050 1.000 1.100 1.120 1.140 1.160 1.100 1.200 1.220 1.240 1.260 1.000 1.082 1.124 1.106 1.210 1.254 1.300 1.346 1.392 1.440 1.488 1.530 1.588 1.638 1.061 1.125 1.191 1200 1.331 1.405 1.482 1.561 1.643 1.728 1.816 1.907 2.000 2.007 1.262 1.360 1.464 1.574 1.689 1.811 1.939 2.078 2.215 2.364 2.520 2.604 1.338 1.469 1611 1.762 1.925 2.100 2.200 2.488 2.703 2.932 3.176 3.436 1.419 1587 1.772 1.974 2.195 2.436 2.700 2.995 3.257 3.835 4.002 4.398 1.149 1.316 1.504 1714 1.949 2211 2.502 2.826 3.185 3.583 4.023 4.500 5.042 5.629 6.275 1.172 1.369 1.594 1851 2.144 2.476 2.853 3.278 3.759 4.300 4.908 5.590 6.353 7.206 8.157 1.195 1.423 1.689 1.399 2.358 2.773 3.252 3.803 4.435 5.160 5.987 6.931 1.005 9.223 10.504 1.219 1.450 1.791 2.159 2.594 3.106 3.707 4.411 5.234 6.192 7.305 8.594 10.086 11.806 13.796 1.243 1.539 18 2332 2853 3479 4.225 5.117 6.176 7.430 8.912 10.657 12.708 15.112 17.902 21.199 1.288 1.601 2012 2518 3.138 3.896 4818 5.536 7.288 8.316 10.872 13.215 1.294 1.665 2.133 2720 3.452 4.363 5.492 6.886 1.599 10.699 13.264 2.937 2.251 4.887 6.261 3.797 7.988 10.147 12.839 16.182 2.37 3.172 7.138 6.983 10.541 9.217 14758 I 9 20.661 10 12.166 16.000 281925 10 11 40.496 12 23.298 27.983 56.654 16.012 19.343 16.386 20.175 24759 30.288 13 36.337 79.371 14 48.757 1.319 1.732 1.801 20.319 25.196 25.421 32.000 40.565 31.691 39.374 51.186 111.120 155.568 15 1.346 4.177 5.474 9.296 11.974 15.407 19.742 64.359 1.373 1873 2.540 3.426 4.595 6.130 8.137 10.748 14.129 18.408 24.086 31.243 40.358 51.923 66.542 84.954 217.795 1.400 1.948 2.683 1.700 5.054 9.276 12.468 16.672 22.186 29.384 38.741 50.851 66.461 85.504 112.139 304.913 17 1.428 2026 2854 1996 5.560 7.890 10.575 14.463 19.673 25.623 35.849 48.039 64.072 85.071 112.455 148.024 428.879 1.457 2.107 3.026 4.316 6.116 8.613 12.056 16.777 23214 31.948 43.736 59.568 80.731 108.890 146.192 195.391 587 630 1.486 2.191 3.207 4.661 6.327 1.646 13.743 19.461 27.393 30.338 53.358 73.864 101.721 139.300 190.050 257.316 1.516 2.279 3.400 5.034 7.400 10.804 15.668 22.574 32.324 46.005 65.096 91.582 128.169 178.406 247.065 340.449 3604 5.437 8.140 12.100 17.861 1.546 2.370 25.185 38.142 55.206 79.418 22 113.574 228.350 321.184 161.492 449.393 8.954 13.552 3.820 5.871 20.362 23 30.376 45.008 1.577 2.465 203.480 140.831 66.247 96.889 292.300 593.199 417.539 256.385 24 374.144 35.236 53.109 4.049 6341 79.457 1.850 783.023 118.205 15.179 174.631 23.212 1.808 2.543 542.801 836.613 1171.356 1639.898 2295.857 3214.200 4499.880 6299.831 8819.764 12347.670 17296.737 62.669 40.874 17.000 95.396 25.462 478.905 323.045 144.210 216.542 1.641 2.666 4.282 6.848 25 705.641 1033.590 612.998 407.037 19.040 30.167 917.333 1364.339 25 1.673 2.772 21.325 34.390 784.538 1192.533 1800.927 27 10.835 47414 73.949 4.549 7396 11.918 55.000 87.250 1.707 2.883 4.822 7.588 13.110 14.421 23.884 35.204 63.000 102.967 5.112 8.627 1.741 2.999 44.893 74.009 121.501 29 1.776 3.119 5.418 9.317 15.863 25.750 30 1.811 3.243 5.743 10.063 17.449 29.960 50.950 85.850 143.371 7.506 14.785 28.102 52.800 58.100 180.314 327 997 35 2.000 3.946 2.208 4.801 10.296 21.725 45.259 13.051 188.884 378.721 750.378 288.512 175.936 114.475 137.371 214.642 332.955 164.845 261.364 412.864 197.814 319.474 511.952 512.867 646.212 1004.336 1550.293 2377.224 2015.381 3137.935 814.228 1025.927 1285.550 1545.505 237.376 389.758 634.820 25 19.966 4142075 24201.432 590.668 1053.402 1861.054 3258.135 5653.911 9727.860 16599.217 130161.112 1469.772 2847.038 5455.913 10347.175 19425 689 36118.865 66520.767 700037 697 B 16 17 18 19 20 21 RRRRR 28 40 19 3 9989 Show caption 9 L 11 HENDRANAZARRESRRS 12 13 14 15 16 18 19 20 21 22 24 25 26 27 28 29 30 35