Retirement with Repeating Contributions and a Constant Rate of Return The Retire3 worksheet contains a revised...
Fantastic news! We've Found the answer you've been seeking!
Question:
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
Expert Answer:
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
Related Book For
Fundamental Managerial Accounting Concepts
ISBN: 978-1259569197
8th edition
Authors: Thomas Edmonds, Christopher Edmonds, Bor Yi Tsay, Philip Olds
Posted Date:
Students also viewed these accounting questions
-
A dust storm destroys the majority of a farmer's peanut crop, causing him to raise the price of his peanut butter. What kind of shift does this create in the market for bread, a complement to peanut...
-
Greg Johnson needs financial advice, and he has come to your team for help. Greg would like to retire in 10 years or sooner, if financially possible. However, he is fearful that he may not meet his...
-
Amanda Liu who is 50 years old would like to retire at age 60. She is currently earning $160,000 per year in todays $s and would like to have 60% of that income per year during retirement. She...
-
1. Does Amazon have a special responsibility to their employees given the current COVID-19 pandemic? Are they meeting this responsibility? Why or why not? 2. How should Amazon prioritize their...
-
Define F on R3 by F(x) = (0, 0, cx3)x and let M be a compact three-dimensional manifold-with-boundary with MC {x: x3 <0}. The vector field F may be thought of as the downward pressure of a fluid of...
-
At January 1, 2020, Hennein Company had plan assets of $280,000 and a projected benefit obligation of the same amount. During 2020, service cost was $27,500, the settlement rate was 10%, actual and...
-
Computer Associates International, Inc., delivers the software that manages e-business. CA's world-class solutions address all aspects of e-business process management, information management, and...
-
Flaming Foliage Sky Tours is a small sightseeing tour company in New Hampshire. The firm specializes in aerial tours of the New England countryside during September and October, when the fall color...
-
9. A right triangle has legs of lengths 2cm and 5cm. Find the exact length of the hypotenuse, then use a calculator to approximate that length. Finally, approximate the measure of the non-right...
-
1. Absent any resource constraints, which marketing strategy do you think is most viable? Which is the second-best strategy? Which is the least viable? 2. What overall food trends were taking place...
-
Dozier Company produced and sold 1,000 units during its first month of operations. It reported the following costs and expenses for the month: Direct materials Direct labor Variable manufacturing...
-
Consider a turning operation performed on steel whose hardness = 225 HB at a cutting speed = 2.2 m/s, feed = 0.28 mm, and depth = 4.0 mm. Using values of thermal properties found in the tables and...
-
Solar Manufacturing Company is a manufacturer of portable solar equipment. The costs involved in producing and marketing a unit of solar equipment are as follows: Cost item Cost per unit (RM) Total...
-
Pumpkin, Inc., has the following securities in its trading portfolio on December 3 1 , 2 0 2 3 : Cost Market 2 , 0 0 0 shares of Floppy Airlines, Inc. Common $ 4 5 , 2 0 0 $ 3 2 , 1 0 0 5 0 0 shares...
-
An experiment is performed with a pin-on-disk tester to evaluate the shear strength of a lubricant. The experiment involves sliding of a spherical pin of hard steel against a copper plate of hardness...
-
c) i) What are the needs and outcomes of kinematic analysis of planar complex mechanism?. Also, list the steps in acceleration analysis of a planar mechanism with high degree of complexity using...
-
3) Current (annualised) US Treasury spot rates are as follows: 6 months 1 year 18 months 2 year 0.4% 0.5% 0.6% 0.70% Assuming that Z-spread is equal to 56 basis points, calculate the bond's arbitrage...
-
Q:1 Take any product or service offered in Pakistan and apply all determinents of customer Perceived value ?
-
Maduro Sporting Goods, Inc. produces indoor treadmills. The company allocates its overhead costs using activity-based costing. The costs and cost drivers associated with the four overhead activity...
-
Chris Quill asks you to analyze the operating cost of his lawn services business. He has bought the needed equipment with a cash payment of $90,000. Upon your recommendation, he agrees to adopt...
-
Newkirk Sporting Goods Co. manufactures baseballs. According to Newkirk's 2019 budget, the company planned to incur $180,000 of fixed manufacturing overhead costs to make 200,000 baseballs. Newkirk...
-
When a team of people is able to achieve more than what its members could by working individually, this is called ____________. (a) distributed leadership (b) consensus (c) team viability (d) synergy
-
Which statement about self-managing teams is correct? (a) They can improve performance but not satisfaction. (b) They should have limited decision-making authority (c) They should operate without any...
-
Members of a team tend to become more motivated and better able to deal with conflict during the ____________ stage of team development. (a) forming (b) norming (c) performing (d) adjourning
Study smarter with the SolutionInn App