Retirement with Repeating Contributions and a Constant Rate of Return The Retire3 worksheet contains a revised...

       

Transcribed Image Text:

Retirement with Repeating Contributions and a Constant Rate of Return The Retire3 worksheet contains a revised retirement model for Emma. In this model, contributions and withdrawals will both increase by 2% each year. Emma would still like to retire at age 65 and expects to live another 30 years after retirement. She makes a yearly savings contribution which will increase by 2% each year for 40 years. The starting withdrawal amount is $80,000 and will increase by 2% each year during retirement. Her goal is to optimize (minimize) the yearly contribution that will fund the retirement and maintain a non-negative balance when she reaches age 95. The rate of return also varies throughout the model. The return rate will vary as a function of the balance in the retirement account according to the schedule in range 12:17. Notice that the values for return in column E are determined by a VLOOKUP function that references this schedule of returns. 4.1 Use solver to determine the minimum contribution amount Emma should make. a. Optimize the contribution in cell D3 such that when it increases by 2% for 40 years it will fund a draw of $80,000 that also increases by 2% each year. b. Once Emma starts withdrawing money from the account, the account should have a non-negative balance. c. Select the most appropriate solver engine given the model given the use of the VLOOKUP function in the model. d. The require bounds on variables should be off for this model. Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Beginning Balance $0.00 $1.13 $2.43 $3.92 $5.63 $7.58 $9.82 $12.37 $15.27 $18.58 $22.35 $26.63 $31.50 $37.03 $43.30 $50.42 $58.50 $67.65 $78.03 $89.79 $103.11 $118.19 $135.27 $154.60 $176.48 $201.24 $229.26 $260.95 Contribution $1.00 $1.02 $1.04 $1.06 $1.08 $1.10 $1.13 $1.15 $1.17 $1.20 $1.22 $1.24 $1.27 $1.29 $1.32 $1.35 $1.37 $1.40 $1.43 $1.46 $1.49 $1.52 $1.55 $1.58 $1.61 $1.64 $1.67 $1.71 Return 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% Withdrawal Ending Balance $1.13 $2.43 $3.92 $5.63 $7.58 $9.82 $12.37 $15.27 $18.58 $22.35 $26.63 $31.50 $37.03 $43.30 $50.42 $58.50 $67.65 $78.03 $89.79 $103.11 $118.19 $135.27 $154.60 $176.48 $201.24 $229.26 $260.95 $296.80 Break Point $0 $200,000 $600,000 $1,000,000 $1,500,000 Rate 13% 10% 7% 5% 10% 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 $296.80 $337.36 $383.22 $435.08 $493.73 $560.05 $635.03 $719.80 $815.63 $923.97 $1,046.43 $1,184.87 -$89,058.65 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A $1.74 $1.78 $1.81 $1.85 $1.88 $1.92 $1.96 $2.00 $2.04 $2.08 $2.12 $2.16 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A $80,000.00 $81,600.00 $83,232.00 $84,896.64 $86,594.57 $88,326.46 $90,092.99 $91,894.85 $93,732.75 $95,607.41 $97,519.55 $99,469.94 $101,459.34 $103,488.53 $105,558.30 $107,669.47 $109,822.86 $112,019.31 $114.259.70 $337.36 $383.22 $435.08 $493.73 $560.05 $635.03 $719.80 $815.63 $923.97 $1,046.43 $1,184.87 -$89,058.65 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 59 60 61 62 63 64 65 66 67 68 69 70 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A $116,544.89 $118,875.79 $121,253.31 $123,678.37 $126,151.94 $128,674.98 $131,248.48 $133,873.45 $136,550.92 $139,281.94 $142,067.58 $144,908.93 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Retirement with Repeating Contributions and a Constant Rate of Return The Retire3 worksheet contains a revised retirement model for Emma. In this model, contributions and withdrawals will both increase by 2% each year. Emma would still like to retire at age 65 and expects to live another 30 years after retirement. She makes a yearly savings contribution which will increase by 2% each year for 40 years. The starting withdrawal amount is $80,000 and will increase by 2% each year during retirement. Her goal is to optimize (minimize) the yearly contribution that will fund the retirement and maintain a non-negative balance when she reaches age 95. The rate of return also varies throughout the model. The return rate will vary as a function of the balance in the retirement account according to the schedule in range 12:17. Notice that the values for return in column E are determined by a VLOOKUP function that references this schedule of returns. 4.1 Use solver to determine the minimum contribution amount Emma should make. a. Optimize the contribution in cell D3 such that when it increases by 2% for 40 years it will fund a draw of $80,000 that also increases by 2% each year. b. Once Emma starts withdrawing money from the account, the account should have a non-negative balance. c. Select the most appropriate solver engine given the model given the use of the VLOOKUP function in the model. d. The require bounds on variables should be off for this model. Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Beginning Balance $0.00 $1.13 $2.43 $3.92 $5.63 $7.58 $9.82 $12.37 $15.27 $18.58 $22.35 $26.63 $31.50 $37.03 $43.30 $50.42 $58.50 $67.65 $78.03 $89.79 $103.11 $118.19 $135.27 $154.60 $176.48 $201.24 $229.26 $260.95 Contribution $1.00 $1.02 $1.04 $1.06 $1.08 $1.10 $1.13 $1.15 $1.17 $1.20 $1.22 $1.24 $1.27 $1.29 $1.32 $1.35 $1.37 $1.40 $1.43 $1.46 $1.49 $1.52 $1.55 $1.58 $1.61 $1.64 $1.67 $1.71 Return 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% Withdrawal Ending Balance $1.13 $2.43 $3.92 $5.63 $7.58 $9.82 $12.37 $15.27 $18.58 $22.35 $26.63 $31.50 $37.03 $43.30 $50.42 $58.50 $67.65 $78.03 $89.79 $103.11 $118.19 $135.27 $154.60 $176.48 $201.24 $229.26 $260.95 $296.80 Break Point $0 $200,000 $600,000 $1,000,000 $1,500,000 Rate 13% 10% 7% 5% 10% 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 $296.80 $337.36 $383.22 $435.08 $493.73 $560.05 $635.03 $719.80 $815.63 $923.97 $1,046.43 $1,184.87 -$89,058.65 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A $1.74 $1.78 $1.81 $1.85 $1.88 $1.92 $1.96 $2.00 $2.04 $2.08 $2.12 $2.16 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% 13% #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A $80,000.00 $81,600.00 $83,232.00 $84,896.64 $86,594.57 $88,326.46 $90,092.99 $91,894.85 $93,732.75 $95,607.41 $97,519.55 $99,469.94 $101,459.34 $103,488.53 $105,558.30 $107,669.47 $109,822.86 $112,019.31 $114.259.70 $337.36 $383.22 $435.08 $493.73 $560.05 $635.03 $719.80 $815.63 $923.97 $1,046.43 $1,184.87 -$89,058.65 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 59 60 61 62 63 64 65 66 67 68 69 70 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A $116,544.89 $118,875.79 $121,253.31 $123,678.37 $126,151.94 $128,674.98 $131,248.48 $133,873.45 $136,550.92 $139,281.94 $142,067.58 $144,908.93 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

Answer rating: 100% (QA)

1 Emma is currently 25 years old She will retire at age 65 so she has 40 years to contribute before retiring 2 Her initial yearly contribution is set ... View the full answer