A B C 3 Project Description: 4 In this problem, you will calculate the monthly payments associated to a $20,000 car loan for various rates and terms. 5 Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers. 7 Steps to Perform: Points 8 Step Instructions Possible 9 1 Start Excel. Download and open the workbook named: 0 10 Chapter_9-6_Payment_Start 2 In cell B12, calculate the monthly payment for a car loan if the annual percentage rate is equal to the value in cell B11 and the number of years is equal to the value in cell A12. Fill cell B12 across to cell E12. 6 11 Format cells B12:E12 as Currency with 2 decimal places. 3 In cell B13, calculate the monthly payment for a car loan if the annual percentage rate is equal to the value in cell B11 and the number of years is equal to the value in cell A13. Fill cell B13 across to cell E13. 6 12 Format cells B13:E13 as Currency with 2 decimal places. 4 In cell B14, calculate the monthly payment for a car loan if the annual percentage rate is equal to the value in cell B11 and the number of years is equal to the value in cell A14. Fill cell B14 across to cell E14. 6 13 Format cells B14:E14 as Currency with 2 decimal places. 5 In cell B15, calculate the monthly payment for a car loan if the annual percentage rate is equal to the value in cell B11 and the number of years is equal to the value in cell A15. Fill cell B15 across to cell E15. 6 14 Format cells B15:E15 as Currency with 2 decimal places. 6 In cell B16, calculate the monthly payment for a car loan if the annual percentage rate is equal to the value in cell B11 and the number of years is equal to the value in cell A16. Fill cell B16 across to cell E16. 6 15 Format cells B16:E16 as Currency with 2 decimal places. In cells R6:Y19, insert a Line Chart to show the changes in monthly payment for various rates and terms. Select range A11:E16. On the Insert tab, click Recommended Charts, and then click Line. Use cell references to B11, C11, D11, and E11 for Series Names. Apply Style 1 on the Design tab. Add a chart title and choose the Above Chart option. Replace Chart Title with Monthly Payments for $20,000 Loan of 16 Various Rates and Terms. Add a horizontal axis title. Replace Axis Title for the horizontal axis with Terms (years). 17 8 In cell B24, calculate the total interest paid for a car loan if the annual percentage rate is equal to the value in cell B23 and the number of years is equal to the value in cell A24. Fill cell B24 across to cell E24. 6 Format cells B24:E24 as Currency with 2 decimal places. 9 In cell B25, calculate the total interest paid for a car loan if the annual percentage rate is equal to the value in cell B23 and the number of years is equal to the value in cell A25. Fill cell B25 across to cell E25. 6 BL Format cells B25:E25 as Currency with 2 decimal places. 10 In cell B26, calculate the total interest paid for a car loan if the annual percentage rate is equal to the value in cell B23 and the number of years is equal to the value in cell A26. Fill cell B26 across to cell E26. 6 19 Format cells B26:E26 as Currency with 2 decimal places. 20 11 In cell B27, calculate the total interest paid for a car loan if the annual percentage rate is equal to the value in cell B23 and the number of years is equal to the value in cell A27. Fill cell B27 across to cell E27. 6 Format cells B27:E27 as Currency with 2 decimal places. 12 In cell B28, calculate the total interest paid for a car loan if the annual percentage rate is equal to the value in cell B23 and the number of years is equal to the value in cell A28. Fill cell B28 across to cell E28. 21 6 Format cells B28:E28 as Currency with 2 decimal places. In cells R22:Y35, insert a Column Chart to show the relationship between total interest paid and the percentage rate. Select range A23:E28. On the Insert tab, click Recommended Charts, then open the All 13 Charts tab, and then choose Clustered Column with the years on the horizontal axis and the interest charged on the vertical axis. Use cell references to B23, C23, D23, and E23 for Series Names. Apply Style 1 on the Design tab. Add a chart title and choose the Above Chart option. Replace Chart Title with Total Interest Charged for $20,000 Loan. Add a horizontal axis title. Replace Axis Title for the horizontal axis 22 with Terms (years). 23 14 Save your file and submit for grading- 0 24 Total Points 68A B C D E F G H K M N O P Q R S T U V W X Payment 2 Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers. This problem will compute the monthly payments associated to a $20,000 car loan for various rates and terms. 6 7 Loan: $20,000 a. a.) a.) a. Years Monthly Payments a.) Complete the table which computes these monthly payments, and make them positive by 1% 4% 7% 10% multiplying each one by -1. Format as currency with two decimal places. 12 13 b.) Create a well-labeled line chart displaying the information from this table. Include a key. The axis label and the title should be chosen from the list: a. Terms (years) b. Monthly Payments for $20,000 Loan of Various Rates and Terms C.) C.) C.) C. c.) Complete the table which computes the total interest paid over the life of each loan using the Years Interest Charged monthly payments times the total number of payments minus what you borrowed. Format as 1% 4% 7% 10% currency with two decimal places. w 25 26 d.) Create a well-labeled column chart displaying the information from this second table. Include a key. The axis label and the title should be chosen from the list: 28 a. Total Interest Charged for $20,000 Loan b. Terms (years)