Assume you recently graduated and accepted a job with a consulting firm. Although you like your...

  

Transcribed Image Text:

Assume you recently graduated and accepted a job with a consulting firm. Although you like your job, you are also looking forward to retiring at age 65. To ensure your retirement is comfortable, you intend to invest $5,000 of your salary into a retirement fund at the end of each year. a. Complete the spreadsheet model found on the Excel file. Use a return rate of 6.1% in your calculations. If you start contributing money at age 24, how much money would you expect to have in your retirement fund at age 65? Hint: Shown below is a screenshot of the calculations required for this assignment. To quickly populate the age column, enter the first two or three values, and then drag down across the cells that you want to fill. X✓ fx 6.1% A B D E 1 Return Interest Beginning Balance Annual Contribution F Ending Balance 2 Age Rate (%) Earned 3 24 0 0.061 =C3*83 5000 =B3+03+E3 4 25 =F3 -$C$3 =C4 B4 =$E$3 =B4+04+E4 5 26 F4 -$C$3 -C5 B5 -$E$3 -B5+05+E5 6 27 =F5 =$C$3 =C6*B6 =$E$3 =B6+D6+E6 7 28 =F6 -$C$3 =C7*87 =$E$3 =87+D7+E7 b. In the second part, you need to analyze how your retirement fund balance varies with respect to changes in the Return Rate and the Annual Contribution. Complete the table labeled 'Ending Balance at Age 65 as a Function of Return Rate and Annual Contribution' by creating a two-way Data Table similar to the one created in-class. The table should include return Rate values between 2.5% and 7.5%, and Annual Contribution values between 2,500 and 7,500. Use the Return Rate in cell C3 and the Annual Contribution in cell E3 as the input cells. Reference the Ending Balance at age 65 cell as the table output. Use the data table to answer the following sample questions: • If the return rate is 7.5%, how much do you need to contribute each year in order to have an ending balance of $1,058,793.26? • How much money would you expect to have when you retire if your annual contribution is 3,000 and the return rate is 5.5%? • If you contributed 3,500 each year, would any of the return rates used in the data table result in an ending balance greater than $1,000,000? c. Refer to part a. How much money would you need to contribute each year in order to have an ending balance of 2,000,000 dollars at age 65? Hint: Use the Goal Seek tool like we studied in class. The "Changing Cell" in Goal Seek will be the "Annual Contribution" at age 24. Question 2 1.25 pts If you contributed 4,000 each year, would any of the return rates used in the data table result in an ending balance greater than $1,000,000? yes no 1.25 pts O Question 3 Enter the value in cell A10. Question 4 1.25 pts Use Goal Seek to estimate how much money you would need to contribute each year in order to have an ending balance of 2,000,000 dollars at age 65 (Use a return rate of 6.1% in your calculations. When entering your answer, do not use the $ sign). Question 5 1.25 pts Use the data table to answer the following question: If the return rate is 7.0%, how much do you need to contribute each year in order to have an ending balance of $1,037,845.08? (When entering your answer, do not use the $ sign). Question 6 1.25 pts Use the data table to answer the following question: How much money would you expect to have when you retire if your annual contribution is 3,000 and the return rate is 5.5%? (When entering your answer, do not use the $ sign). Question 7 1.25 pts If you start contributing 5,000 at age 24, how much money would you expect to have in your retirement fund at age 65? (Use a return rate of 6.1% in your calculations. When entering your answer, do not use the $ sign). Question 8 Enter the value in cell S3. 1.25 pts Assume you recently graduated and accepted a job with a consulting firm. Although you like your job, you are also looking forward to retiring at age 65. To ensure your retirement is comfortable, you intend to invest $5,000 of your salary into a retirement fund at the end of each year. a. Complete the spreadsheet model found on the Excel file. Use a return rate of 6.1% in your calculations. If you start contributing money at age 24, how much money would you expect to have in your retirement fund at age 65? Hint: Shown below is a screenshot of the calculations required for this assignment. To quickly populate the age column, enter the first two or three values, and then drag down across the cells that you want to fill. X✓ fx 6.1% A B D E 1 Return Interest Beginning Balance Annual Contribution F Ending Balance 2 Age Rate (%) Earned 3 24 0 0.061 =C3*83 5000 =B3+03+E3 4 25 =F3 -$C$3 =C4 B4 =$E$3 =B4+04+E4 5 26 F4 -$C$3 -C5 B5 -$E$3 -B5+05+E5 6 27 =F5 =$C$3 =C6*B6 =$E$3 =B6+D6+E6 7 28 =F6 -$C$3 =C7*87 =$E$3 =87+D7+E7 b. In the second part, you need to analyze how your retirement fund balance varies with respect to changes in the Return Rate and the Annual Contribution. Complete the table labeled 'Ending Balance at Age 65 as a Function of Return Rate and Annual Contribution' by creating a two-way Data Table similar to the one created in-class. The table should include return Rate values between 2.5% and 7.5%, and Annual Contribution values between 2,500 and 7,500. Use the Return Rate in cell C3 and the Annual Contribution in cell E3 as the input cells. Reference the Ending Balance at age 65 cell as the table output. Use the data table to answer the following sample questions: • If the return rate is 7.5%, how much do you need to contribute each year in order to have an ending balance of $1,058,793.26? • How much money would you expect to have when you retire if your annual contribution is 3,000 and the return rate is 5.5%? • If you contributed 3,500 each year, would any of the return rates used in the data table result in an ending balance greater than $1,000,000? c. Refer to part a. How much money would you need to contribute each year in order to have an ending balance of 2,000,000 dollars at age 65? Hint: Use the Goal Seek tool like we studied in class. The "Changing Cell" in Goal Seek will be the "Annual Contribution" at age 24. Question 2 1.25 pts If you contributed 4,000 each year, would any of the return rates used in the data table result in an ending balance greater than $1,000,000? yes no 1.25 pts O Question 3 Enter the value in cell A10. Question 4 1.25 pts Use Goal Seek to estimate how much money you would need to contribute each year in order to have an ending balance of 2,000,000 dollars at age 65 (Use a return rate of 6.1% in your calculations. When entering your answer, do not use the $ sign). Question 5 1.25 pts Use the data table to answer the following question: If the return rate is 7.0%, how much do you need to contribute each year in order to have an ending balance of $1,037,845.08? (When entering your answer, do not use the $ sign). Question 6 1.25 pts Use the data table to answer the following question: How much money would you expect to have when you retire if your annual contribution is 3,000 and the return rate is 5.5%? (When entering your answer, do not use the $ sign). Question 7 1.25 pts If you start contributing 5,000 at age 24, how much money would you expect to have in your retirement fund at age 65? (Use a return rate of 6.1% in your calculations. When entering your answer, do not use the $ sign). Question 8 Enter the value in cell S3. 1.25 pts

Answer rating: 100% (QA)

Using the formulas given the calculations are as follows Formulas The solution to ... View the full answer