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...
-
What is the distinction between a qualitative and a quantitative measure?
-
This experiment has two mutually exclusive events, \(A\) and \(\bar{A}\), that form a partition of the sample space \(S\). The number of elements in each set is shown in each region. Find the...
-
Explain form drag.
-
Here is information related to Shashko Company for 2012. Total credit sales ............ $1,500,000 Accounts receivable at December 31 .... 840,000 Bad debts written off ........... 37,000...
-
What a is the duties and responsibilities of the United States Army Garrison (USAG). How does USAG operate? How are tasks/work projects accomplished within USAG? What are the factors that are...
-
1. Marketing analysts use market position maps to display visually the customers perceptions of a firm in relation to its competitors regarding two attributes. Prepare a market position map for Alamo...
-
LONG TERM ASSET ASSET: FOOD TRUCK ASSET COST $ 3 5 , 0 0 0 INTEREST ON TRUCK LOAN $ 4 5 0 ACCUMULATED DEPRECIATION $ 6 , 6 0 0 PREPAID INSURANCE $ 2 , 3 0 0 BOOK VALUE $ Based on the information...
-
Now consider another 1-year rate: the rate on one-year T-bills. Call this rate RTB. It is just related to the price of the (zero-coupon) T-bill via B0,1 1/(1+ RTB). Verify that if we observe = Rrepo...
-
Consider a uniform disc of mass m, radius r, rolling without slipping on a rough surface with linear acceleration a and angular acceleration a due to an external force F as shown in the figure....
-
Lab 2-Simple Web Page Generation Background A natural arch is a rock formation with an opening underneath it. There are a variety of natural arches that can be visited in the state of Ohio. The Ohio...
-
Lehman Dairy leases its milking equipment from Chavez Finance Company under the following lease terms. The lease term is 8 years, non - cancelable, and requires equal rental payments due at the...
-
2.2 Create program statements in Python that compute a vector of y values based on the following formulas: 613 - 3t - 4 (a) y= 0.1 < t < 0.25 8 sin(5t) (b) y = 3t - 2 1515 5 4t 2 where t is a vector...
-
A well-designed AIS should provide adequate controls to ensure the proper procedures can be taken to reduce the threats in expenditure cycle". (a) Assess any FIVE (5) possible threats of expenditure...
-
Parkin Industries, a U.S. company, acquired a wholly-owned subsidiary, located in Italy, at the beginning of the current year, for 200,000. The subsidiary's functional currency is the euro. The...
-
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...
-
Butterfly-style valves used in heating and ventilating industries have a high flow coefficient. Flow coefficient can be modeled by a normal distribution with mean \(496 C_{V}\) and standard deviation...
-
Find the quartiles \[-z_{0.25} \quad z_{0.50} \quad z_{0.25}\] of the standard normal distribution.
-
The time for oil to percolate to all parts of an engine can be treated as a random variable having a normal distribution with mean 20 seconds. Find its standard deviation if the probability is 0.25...
Study smarter with the SolutionInn App