if you could please help me solve this excel exercise

yes this is excel

D3 B D E F G Personal Investments 1 Growth Rate 2 Investment Fund 3. Treasury Bond 4 Corporate Bond 5 Domestic Dividend 6. Mid Cap Domestic 7 Emerging Markets 8 Energy 9 Total Invested Current Percent Principal Balance to Total $ 10,000 S 10,400 $ 15,000 $ 15,750 $ 20,000 $ 19,400 $ 15,000 S 14,250 $ 10,000 S 10,000 $ 10,000 S 10,800 Target Growth 2 Year Rate Future Value 4.5% 5.0% 8.0% 9.5% 10.5% 9.0% 10 Average 11 12 13 14 15 16 17 19 19 20 21 22 + Investments Mortgage Auto Lease Summary Ready A B C D E F Mortgage Information 2 4.90% 3 30 4. $275,000 Annual Interest Rate Repayment Years Price of House Down Payment Principal of Loan Monthly Payments 5 $55,000 6 00 1 1 9 10 11 12 13 14 15 16 17 Investments Mortgage Auto Lease Summary Red B D E F Automobile Lease 1 Annual Interest Rate 2 2.50% Terms of Lease (in months) 3 48 4 Price of Car $26,750 Residual Value 5 at end of Lease $19,000 Monthly Payments 6 7 8 9 10 11 12 13 14 15 16 17. 1 1 Investments Mortgage Auto Lease Summary Ready B C D E Financial Summary Principal of Investments 2 Year Future Value of Investments : 2 Year Investment Gains 5 Two Years of Mortgage Payments 6 Two Years of Lease Payments 7 Investment Gains Less Mortgage and Lease Payments 8 9 10 11 12 13 14 15 16 17 18 19 20 CHAPTER SKILLS TEST Starter File: Chapter 2 Skils Test Difficulty: Level 2 Moderate Answer the following questions by executing the skills on the starter file required for this test. Answer each question in the order in which it appears. If you do not know the answer, skip to the next question. Open the starter file listed above before you begin this test. 1. Enter a function in cell 9 on the Investments worksheet that calculates the total of the values in the range 83:88 2 Copy the function in cell B9 and paste it into cells c9 and 69. 3. Enter a formula in cell 3 on the investments worksheet that calculates the growth rate for the Investments. Your formula should first subtract the value in the invested Principal column from the value in the current Balance column. Then, divide this result by the value in the invested Principal column. 4. Copy the formula in cells and paste it into the range 4:E8. 5. Copy the formula in cell 3 and paste it into cell E9 using the Paste Formulas option 6. Enter a formula in cell 03 on the investments worksheet that divides the Current Balance by the total in cell 09. Add an absolute reference to C9 in this formula 7. Copy the formula in cells and paste it into the range 04.08. In cells on the Investments Worksheet use the Future Value function to calculate the future value of the investment 10 2 years. Use the target Growth rate to define the Rate argument. This is not an annuity so there are no periodic investments. Use the current Balance to define the Pv argument. Assume that the investment is made at the beginning of the period. 9. Copy the function in cell G3 and paste it into the range G4 G8. 10. Enter a function in cell B10 on the Investments worksheet that calculates the average of the values in the range B3:38. 11. Copy the function in cell B10 and paste it into cells C10 and G10. 12. On the Mortgage worksheet, use the data provided to enter a formula in cell B6 to calculate the principal of the loan that will be required to purchase the house. 13. On the Mortgage worksheet, use the PMT function in cell B7 to calculate themonthly payments of the mortgage. Use cell locations from this worksheet to define each argument of the function. Assume that payments are made at the end of each month. 14. On the Auto Lease worksheet, use the PMT function in cell B6 to calculate themonthly lease payments. Use cell locations from this worksheet to define each argument of the function. Assume that the lease payments are due at the beginning of each month. 15. On the Auto Lease worksheet, use Goal Seek to change the Annual interest rate in cell B2 so the monthly payments are exactly $200. 16. In cell E2 on the Summary worksheet, use a cell reference to display the value in cell B9 in the investments worksheet. 17. In cell E3 on the Summary worksheet, use a cell reference to display the value in cell G9 in the Investments worksheet 18. Enter a formula in celt Ed on the Summary worksheet that subtracts the Principal of investments from the 2 Year Future Value of investments. 19. Enter a formula in cell 15 on the Summary worksheet that calculates the amount of mortgage payments that will be made over 2 years. Your formula should multiply the value in B7 on the Mortgage worksheet by 24 19. Enter a formula in cell FS on the Summary worksheet that calculates the amount of mortgage payments that will be made over 2 years. Your formula should multiply the value in B7 on the Mortgage worksheet by 24. 20. Enter a formula in cell F6 on the Summary worksheet that calculates the amount of lease payments that will be made over 2 years. Your formula should multiply the value in B6 on the Auto Lease worksheet by 24. 21. Enter a formula in cell F7 on the Summary worksheet that subtracts the sum of the values in the range F5:F6 from the value in cell F4. Saylor URL: http://www.saylor orr/books. GEO96 Saylor.org 190 22 Sort the data in the range A2:68 on the Investments worksheet. Sort the data based on the values in the Invested Principal column in ascending order (smallest to largest). For duplicate values in this column, sort using the values in the Target Growth Rate column in descending order (largest to smallest) 23. Save the workbook by adding your name in front of the current workbook name le "your name Chapter 2 Skills Test") 24. Close the workbook and Excel D3 B D E F G Personal Investments 1 Growth Rate 2 Investment Fund 3. Treasury Bond 4 Corporate Bond 5 Domestic Dividend 6. Mid Cap Domestic 7 Emerging Markets 8 Energy 9 Total Invested Current Percent Principal Balance to Total $ 10,000 S 10,400 $ 15,000 $ 15,750 $ 20,000 $ 19,400 $ 15,000 S 14,250 $ 10,000 S 10,000 $ 10,000 S 10,800 Target Growth 2 Year Rate Future Value 4.5% 5.0% 8.0% 9.5% 10.5% 9.0% 10 Average 11 12 13 14 15 16 17 19 19 20 21 22 + Investments Mortgage Auto Lease Summary Ready A B C D E F Mortgage Information 2 4.90% 3 30 4. $275,000 Annual Interest Rate Repayment Years Price of House Down Payment Principal of Loan Monthly Payments 5 $55,000 6 00 1 1 9 10 11 12 13 14 15 16 17 Investments Mortgage Auto Lease Summary Red B D E F Automobile Lease 1 Annual Interest Rate 2 2.50% Terms of Lease (in months) 3 48 4 Price of Car $26,750 Residual Value 5 at end of Lease $19,000 Monthly Payments 6 7 8 9 10 11 12 13 14 15 16 17. 1 1 Investments Mortgage Auto Lease Summary Ready B C D E Financial Summary Principal of Investments 2 Year Future Value of Investments : 2 Year Investment Gains 5 Two Years of Mortgage Payments 6 Two Years of Lease Payments 7 Investment Gains Less Mortgage and Lease Payments 8 9 10 11 12 13 14 15 16 17 18 19 20 CHAPTER SKILLS TEST Starter File: Chapter 2 Skils Test Difficulty: Level 2 Moderate Answer the following questions by executing the skills on the starter file required for this test. Answer each question in the order in which it appears. If you do not know the answer, skip to the next question. Open the starter file listed above before you begin this test. 1. Enter a function in cell 9 on the Investments worksheet that calculates the total of the values in the range 83:88 2 Copy the function in cell B9 and paste it into cells c9 and 69. 3. Enter a formula in cell 3 on the investments worksheet that calculates the growth rate for the Investments. Your formula should first subtract the value in the invested Principal column from the value in the current Balance column. Then, divide this result by the value in the invested Principal column. 4. Copy the formula in cells and paste it into the range 4:E8. 5. Copy the formula in cell 3 and paste it into cell E9 using the Paste Formulas option 6. Enter a formula in cell 03 on the investments worksheet that divides the Current Balance by the total in cell 09. Add an absolute reference to C9 in this formula 7. Copy the formula in cells and paste it into the range 04.08. In cells on the Investments Worksheet use the Future Value function to calculate the future value of the investment 10 2 years. Use the target Growth rate to define the Rate argument. This is not an annuity so there are no periodic investments. Use the current Balance to define the Pv argument. Assume that the investment is made at the beginning of the period. 9. Copy the function in cell G3 and paste it into the range G4 G8. 10. Enter a function in cell B10 on the Investments worksheet that calculates the average of the values in the range B3:38. 11. Copy the function in cell B10 and paste it into cells C10 and G10. 12. On the Mortgage worksheet, use the data provided to enter a formula in cell B6 to calculate the principal of the loan that will be required to purchase the house. 13. On the Mortgage worksheet, use the PMT function in cell B7 to calculate themonthly payments of the mortgage. Use cell locations from this worksheet to define each argument of the function. Assume that payments are made at the end of each month. 14. On the Auto Lease worksheet, use the PMT function in cell B6 to calculate themonthly lease payments. Use cell locations from this worksheet to define each argument of the function. Assume that the lease payments are due at the beginning of each month. 15. On the Auto Lease worksheet, use Goal Seek to change the Annual interest rate in cell B2 so the monthly payments are exactly $200. 16. In cell E2 on the Summary worksheet, use a cell reference to display the value in cell B9 in the investments worksheet. 17. In cell E3 on the Summary worksheet, use a cell reference to display the value in cell G9 in the Investments worksheet 18. Enter a formula in celt Ed on the Summary worksheet that subtracts the Principal of investments from the 2 Year Future Value of investments. 19. Enter a formula in cell 15 on the Summary worksheet that calculates the amount of mortgage payments that will be made over 2 years. Your formula should multiply the value in B7 on the Mortgage worksheet by 24 19. Enter a formula in cell FS on the Summary worksheet that calculates the amount of mortgage payments that will be made over 2 years. Your formula should multiply the value in B7 on the Mortgage worksheet by 24. 20. Enter a formula in cell F6 on the Summary worksheet that calculates the amount of lease payments that will be made over 2 years. Your formula should multiply the value in B6 on the Auto Lease worksheet by 24. 21. Enter a formula in cell F7 on the Summary worksheet that subtracts the sum of the values in the range F5:F6 from the value in cell F4. Saylor URL: http://www.saylor orr/books. GEO96 Saylor.org 190 22 Sort the data in the range A2:68 on the Investments worksheet. Sort the data based on the values in the Invested Principal column in ascending order (smallest to largest). For duplicate values in this column, sort using the values in the Target Growth Rate column in descending order (largest to smallest) 23. Save the workbook by adding your name in front of the current workbook name le "your name Chapter 2 Skills Test") 24. Close the workbook and Excel