Using the following information:
\fK L M N 0 P Q 23 24 Post-Retirement Module 25 Age Funds When Out? Funds 26 Year Age Spending Taxes Fund BOY Fund EOY 1=Yes Gone 27 2041 65 -$156,692.2 -$23,503.83 $2,170,650 $2,055,574 78 28 2042 66 -$159,826.0 -$23,973.90 $2,055,574 $1,933,441 29 2043 67 -$163,022.5 -$24,453.38 $1,933,441 $1,803,969 30 2044 68 -$166,283.0 -$24,942.45 $1,803,969 $1,666,862 31 2045 69 -$169,608.7 -$25,441.30 $1,666,862 $1,521,818 32 2046 70 -$173,000.8 -$25,950.12 $1,521,818 $1,368,522 33 2047 71 -$176,460.8 -$26,469.13 $1,368,522 $1,206,647 34 2048 72 -$179,990.1 -$26,998.51 $1,206,647 $1,035,858 35 2049 73 -$183,589.9 -$27,538.48 $1,035,858 $855,806 36 2050 74 -$187,261.7 -$28,089.25 $855,806 $666,129 37 2051 75 -$191,006.9 -$28,651.03 $666,129 $466,455 38 2052 76 -$194,827.0 -$29,224.05 $466,455 $256,397 39 2053 77 -$198,723.6 -$29,808.54 $256,397 $35,557 40 2054 78 -$202,698.0 -$30,404.71 $35,557 -$196,479Additional Contribution 78 0 2000 4000 6000 8000 10000 12000 16000 60 69 69 70 70 71 71 72 73 61 70 71 71 72 72 73 73 74 62 72 72 73 73 74 74 75 76 Retirement 63 73 74 74 75 75 76 76 78 Age 64 74 75 76 76 77 77 78 79 65 76 76 77 78 78 79 80 81 66 77 78 78 79 80 81 81 83 67 79 79 80 81 81 82 83 84 68 80 81 81 82 83 84 84 86 69 81 82 83 84 85 85 86 88 70 83 84 85 85 86 87 88 89 Graphically, using just the even ages we have:Graphically, using just the even ages we have: Age When Funds Run Out 95 90 70 68 85 66 64 Age When Funds Run Out 80 62 75 -60 70 65 0 2000 4000 6000 8000 10000 12000 14000 16000 18000 Additional Pre-tax Contributions Obviously as retirement age and pre-tax contributions increase, so too will the age when funds run out. For a given age, contributing the maximum pre-tax additional contributions will earn Tim an additional 4 to 6 years. Delaying retirement by 5 years (from 65 to 70) will earn Tim an additional two to three years. While retirement age and pre-tax additional contributions are choices Tim can make, many other factors will have an impact on his retirement account. For example, the rate of inflation and the return on the pre-retirement funds are variables that Tim cannot directly control. The following table and chart below shows the impact of these factors on the age when funds run out.Pre Retirement Return 73 0 0.01 0.02 0.03 0.04 0.05 O 77 80 84 89 98 113 0.01 73 75 77 80 84 89 0.02 71 72 73 75 78 81 0.03 69 70 71 72 74 76 Inflation 0.04 68 69 69 70 71 73 0.05 67 68 68 59 70 71 0.06 67 67 67 68 69 69 0.07 66 66 67 67 68 68 0.08 66 66 66 67 67 67 0.09 65 66 66 66 66 67 0.1 65 65 66 66 66 66Age When Funds Run Out 110 Pre-Retirement Return 100 -0 90 -0.01 Age whenFunds Run Out -0.02 80 0.03 70 -0.04 0.05 60 0 0.02 0.04 0.06 0.08 0.1 Inflation As the chart shows, even moderate inflation can have a major impact on how long the fund lasts after retirement. Contributing more or strong returns can help mitigate the impact of inflation.Case Problem: Tim's Retirement Planning We take as the base case for Tim's retirement planning problem the parameter settings below: A B 1 Tim's Retirement Model 2 3 Parameters 4 Current Year 2013 5 Age 37 6 Current Savings $259,000 7 Rate of Return on Retirement Savings (pre retirement) 4.0% 8 Current Annual Salary $145,000 9 Expected Annual % Increase 2.0% 10 % of Salary Contributed 6.0% 11 Expected Age of Retirement 65 12 Expected Expenses After Retirement $90,000 13 Rate of Return on Retirement Savings (post retirement) 3.0% 14 Income tax rate (post retirement) 15.0% 15 % School Contribution 6.0% 16 Annual Employee (tax free) Contributions $6,000 17 Inflation Rate 2.00% While there are many ways to model this problem, any sound approach will have the following characteristics: There spreadsheet model will have a parameters section and a model
