Retirement Planning Spreadsheet Vertex42.comRetirement Savings Plan Estimated Future Value Present Value of Investment 10,000 $ Estimated Future Value Expected Annual Return 5.00% Total Payments Current Age 35 Total Interest Age at Retirement 65 Annual Payment 2,000 $ # of Payments (First N Years) 35 Years until retirement 30 Year Age Rate Invested (Payments) Cumulative Payments Interest Cumulative Interest 10,000 1 35 5.00% 2,000 12,000 500.00 500.00 2 36 5.00% 2,000 14,000 625.00 1,125.00 3 37 5.00% 2,000 16,000 756.25 1,881.25 4 38 5.00% 2,000 18,000 894.06 2,775.31 5 39 5.00% 2,000 20,000 1,038.77 3,814.08 6 40 5.00% 2,000 22,000 1,190.70 5,004.78 7 41 5.00% 2,000 24,000 1,350.24 6,355.02 8 42 5.00% 2,000 26,000 1,517.75 7,872.77 9 43 5.00% 2,000 28,000 1,693.64 9,566.41 10 44 5.00% 2,000 30,000 1,878.32 11,444.73 11 45 5.00% 2,000 32,000 2,072.24 13,516.97 12 46 5.00% 2,000 34,000 2,275.85 15,792.82 13 47 5.00% 2,000 36,000 2,489.64 18,282.46 14 48 5.00% 2,000 38,000 2,714.12 20,996.58 15 49 5.00% 2,000 40,000 2,949.83 23,946.41 16 50 5.00% 2,000 42,000 3,197.32 27,143.73 17 51 5.00% 2,000 44,000 3,457.19 30,600.92 18 52 5.00% 2,000 46,000 3,730.05 34,330.96 19 53 5.00% 2,000 48,000 4,016.55 38,347.51 20 54 5.00% 2,000 50,000 4,317.38 42,664.89 21 55 5.00% 2,000 52,000 4,633.24 47,298.13 22 56 5.00% 2,000 54,000 4,964.91 52,263.04 $-$20,000 $40,000 $60,000 $80,000 $100,000 $120,000 $140,000 $160,000 $180,000 $200,000 20 30 40 50 60 70 Age Balance Cumulative Payments23 57 5.00% 2,000 56,000 5,313.15 57,576.19 24 58 5.00% 2,000 58,000 5,678.81 63,255.00 25 59 5.00% 2,000 60,000 6,062.75 69,317.75 26 60 5.00% 2,000 62,000 6,465.89 75,783.63 27 61 5.00% 2,000 64,000 6,889.18 82,672.82 28 62 5.00% 2,000 66,000 7,333.64 90,006.46 29 63 5.00% 2,000 68,000 7,800.32 97,806.78 30 64 5.00% 2,000 70,000 8,290.34 106,097.12 31 65 5.00% 2,000 72,000 8,804.86 114,901.97 32 66 5.00% 2,000 74,000 9,345.10 124,247.07 33 67 5.00% 2,000 76,000 9,912.35 134,159.43 34 68 5.00% 2,000 78,000 10,507.97 144,667.40 35 69 5.00% 2,000 80,000 11,133.37 155,800.77 36 70 5.00% 0 80,000 11,790.04 167,590.81 37 71 5.00% 0 80,000 12,379.54 179,970.35 38 72 5.00% 0 80,000 12,998.52 192,968.86 39 73 5.00% 0 80,000 13,648.44 206,617.31 40 74 5.00% 0 80,000 14,330.87 220,948.17 41 75 5.00% 0 80,000 15,047.41 235,995.58 42 76 5.00% 0 80,000 15,799.78 251,795.36 43 77 5.00% 0 80,000 16,589.77 268,385.13 44 78 5.00% 0 80,000 17,419.26 285,804.39 45 79 5.00% 0 80,000 18,290.22 304,094.60 46 80 5.00% 0 80,000 19,204.73 323,299.33 47 81 5.00% 0 80,000 20,164.97 343,464.30 48 82 5.00% 0 80,000 21,173.22 364,637.52 49 83 5.00% 0 80,000 22,231.88 386,869.39 50 84 5.00% 0 80,000 23,343.47 410,212.86 51 85 5.00% 0 80,000 24,510.64 434,723.51 52 86 5.00% 0 80,000 25,736.18 460,459.68 53 87 5.00% 0 80,000 27,022.98 487,482.66 54 88 5.00% 0 80,000 28,374.13 515,856.80 55 89 5.00% 0 80,000 29,792.84 545,649.64 56 90 5.00% 0 80,000 31,282.48 576,932.12 57 91 5.00% 0 80,000 32,846.61 609,778.73 58 92 5.00% 0 80,000 34,488.94 644,267.66 59 93 5.00% 0 80,000 36,213.38 680,481.04 60 94 5.00% 0 80,000 38,024.05 718,505.10© 2005 Vertex42, LLC Estimated FV Making Annual Payment Each Year $176,097.12 $176,097.12 $70,000.00 $106,097.12 Balance 10,000 $ 12,500.00 15,125.00 17,881.25 Random Rates min -4.00% 20,775.31 max 10.00% 23,814.08 average 5.00% 27,004.78 30,355.02 FALSE 33,872.77 37,566.41 41,444.73 45,516.97 49,792.82 54,282.46 58,996.58 63,946.41 69,143.73 74,600.92 80,330.96 86,347.51 92,664.89 99,298.13 106,263.04 50 60 70 Age Instructions/Help: 1. Change the values with the yellow background and see how the estimated Future Value changes. 2. Set Chart Axis to Fixed Values When making comparisons by changing the inputs, it helps to set the y-axis on the chart to some maximum $ amount. Rightcllic on the $ axis, select Format Axis, go to the Scale tab, uncheck the box next to Maximum, and set the max $. For example, see what happens when you change the # of payments. 3. Using Random Rates If you check the "Use Random Rates" box below, then the Rate column will calculate a random rate between the specified min and max. To recalculate, press F9. Use Random Rates113,576.19 121,255.00 129,317.75 137,783.63 146,672.82 156,006.46 165,806.78 176,097.12 186,901.97 198,247.07 210,159.43 222,667.40 235,800.77 247,590.81 259,970.35 272,968.86 286,617.31 300,948.17 315,995.58 331,795.36 348,385.13 365,804.39 384,094.60 403,299.33 423,464.30 444,637.52 466,869.39 490,212.86 514,723.51 540,459.68 567,482.66 595,856.80 625,649.64 656,932.12 689,778.73 724,267.66 760,481.04 798,505.10