Go up to year 70

B C D F H K L M N P R S T 1 20 pts. Deleting input data is optional but solver settings must be saved. You started planning for your retirement. Assume a fixed horizon (given below) and a given annual inflation rate. For the first years you will be making contributions at the beginning of the year (adjusted for the inflation beginning in year 2) in order to meet the following retirement goal: 4 Annual Inflation: 2.00% Starting in year K, withdraw a desired amount (IN TODAY'S DOLLARS) at the beginning of each year until the end of the horizon. Initial Contribution: For the first few years, you will be using more aggressive investments (higher expected return). For the next few years assume a different return another 6 for the remainder of the years. 7 Desired annual withdrawal: $ 100,000.00 Build a model to answer the question: What is the minimum first contribution to accomplish such a retirement goal? 8 Year withdrawals begin: 43 HINT: Determine your inflation-adjusted contributions and withdrawals first. For your first withdrawal in year K, calculate the future value using K-1 years 9 and type=end of the period. 10 Year from: Year to: Exp. Return 1 15 12.50% The interest between Year 1 and Year 15 (inclusive) is 12.50% 12 16 35 8.00% The interest between Year 16 and Year 35 (inclusive) is 8.00% NOTE: In this problem you can hard-code the value of zero (0) in the right-hand side of the Solver's constraints 13 36 70 5.00% The interest between Year 36 and Year 70 (inclusive) is 5.00% This model will be tested on different sets of input data. 14 15 Add more columns below (I suggest four more), all added cells must be calculated cells Year Beg. Bal. Contr. 1 OS 2 3 4 5 6 7 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 8 9 10 11 12 13 14 15 16 17 18 19 20 B C D F H K L M N P R S T 1 20 pts. Deleting input data is optional but solver settings must be saved. You started planning for your retirement. Assume a fixed horizon (given below) and a given annual inflation rate. For the first years you will be making contributions at the beginning of the year (adjusted for the inflation beginning in year 2) in order to meet the following retirement goal: 4 Annual Inflation: 2.00% Starting in year K, withdraw a desired amount (IN TODAY'S DOLLARS) at the beginning of each year until the end of the horizon. Initial Contribution: For the first few years, you will be using more aggressive investments (higher expected return). For the next few years assume a different return another 6 for the remainder of the years. 7 Desired annual withdrawal: $ 100,000.00 Build a model to answer the question: What is the minimum first contribution to accomplish such a retirement goal? 8 Year withdrawals begin: 43 HINT: Determine your inflation-adjusted contributions and withdrawals first. For your first withdrawal in year K, calculate the future value using K-1 years 9 and type=end of the period. 10 Year from: Year to: Exp. Return 1 15 12.50% The interest between Year 1 and Year 15 (inclusive) is 12.50% 12 16 35 8.00% The interest between Year 16 and Year 35 (inclusive) is 8.00% NOTE: In this problem you can hard-code the value of zero (0) in the right-hand side of the Solver's constraints 13 36 70 5.00% The interest between Year 36 and Year 70 (inclusive) is 5.00% This model will be tested on different sets of input data. 14 15 Add more columns below (I suggest four more), all added cells must be calculated cells Year Beg. Bal. Contr. 1 OS 2 3 4 5 6 7 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 8 9 10 11 12 13 14 15 16 17 18 19 20