Purpose: To demonstrate the ability to create a worksheet that utilizes financial functions and contains a data table. Problem: The time has come for you to purchase a car. After years of saving, you are ready to find the car of your dreams. You have a down payment and now wish to develop an amortization schedule so you can plan your payments. You hope to borrow no more than $26,000. Interest rates are low, but may soon be rising. Because you are not sure how long it will take to find the right car, you wish to examine several different payment plans. You hope to pay off the loan in 5 years. Develop a worksheet that will show the monthly payment, the beginning and ending balance for each year of the loan, the annual cost of the loan, and the annual interest paid for each year of the loan. Instructions: With a blank worksheet on the screen, create the worksheet partially shown in Figure E4A-1. Perform the following tasks: 1. 2. 3. 4. Microsoft Excel 365 Chapter 4 - Lab Practice A Using Financial Functions and Creating a Data Table 5. 6. 7. 8. 9. 10. 11. Select the entire worksheet and change the font size to 12. Change the column widths to the following: A = 11.00; B, C, and E= 20.00; D = 19.00. Change the row heights to the following: 1, 5, and 12 = 39.00. Enter the worksheet title, Car Loan Analysis, in cell A1. Merge and center cell Al across the range A1:E1. Change the font type to Aharoni, the font size to 20, the font color to Dark Blue, Text 2, and the background to Orange, Accent 6. Enter the following labels: A2 = Principal; A3 = Rate; A4 = Years; C4 = Payment; E4 = per month; A5 = Year; B5 = Beginning Balance; C5 = Ending Balance; D5 = Total Paid; E5 Interest. Change the font color to Dark Blue, Text 2. Enter the = NOW() function in cell E2 to display the current date and format as shown in Figure E4A-1. (Note: The date will be different from that in Figure E4A - 1.) Enter the principal amount of $26,000 in cell B2. Format the cell to currency format with no decimal places. Enter the interest rate of 8.0% in cell B3. Format the cell as shown in Figure E4A - 1. Enter the number 5 in cell B4 for the number of years. Create names for cells in the range B2:B4, using values from the column to the left. Enter the function =PMT in cell D4 to calculate the monthly payment on a loan of $26,000 (cell B2) at 8.90% (cell B3) for 5 years (cell B4). Format it as shown in Figure E4A-1. Use the fill handle to fill the range A6:A10 with the numbers 1 through 5. 12. 13. Enter the formula -Principal in cell B6 to reference the principal, which is the beginning balance for year 1. 14. Enter the-PV function in cell C6 to determine the ending balance for year 1. 15. Enter a formula in cell D6 to determine the annual amount paid on the principal/loan. 16. Enter the formula in cell E6 to calculate the amount of interest paid for the year. 17. Enter the formula =C6 in cell B7 to obtain the beginning balance for year 2. Copy this formula to the range B8:B10, Page 1

Microsoft Excel 365 Chapter 4 - Lab Practice A Using Financial Functions and Creating a Data Table Purpose: To demonstrate the ability to create a worksheet that utilizes financial functions and contains a data table. Problem: The time has come for you to purchase a car. After years of saving, you are ready to find the car of your dreams. You have a down payment and now wish to develop an amortization schedule so you can plan your payments. You hope to bofrow no more than $26,000. Interest rates are low, put may soon be rising. Because you are not sure how long it will take to find the right car, youlvish to examine several different payment plans. You bope to pay off the loan in 5 years. Develop a worksheet that will show the monthly payment, the beginning and ending balance for each year of the loan, the annual cost of the loan, and the annual interest paid for each year of the loan. Instructions: With a blank worksheet on the screen, create the worksheet partially shown in Figure EAA - 1. Perform the following tasks: 1. Select the catire worksheet and change the font size to 12 . 2. Change the column widths to the following: A=11.00;B,C, and E=20.00;D=19.00 3. Change the row heights to the following: 1,5 , and 12=39.00. 4. Enter the worksheet title, Car Loan Analysis, in cell A1. Merge and center cell A1 across the range A1:E1. Change the font type to Aharoni, the font size to 20, the font color to Dark Blue, Text 2, and the background to Orange, Accent 6. 5. Enter the following labels: A2= Principal; A3= Rate; A4= Years; C4= Payment; B4= per month; A5 = Year; BS = Beginning Balance; C5 = Ending Balance; DS = Total Paid; ES = Interest. Change the font color to Dark Blue, Text 2. 6. Enter the = NOW 0 function in cell E2 to display the current date and format as shown in Figure E4A - 1. (Note: The date will be different from that in Figure E4A - 1.) 7. Enter the principal amount of $26,000 in cell B2. Format the cell to currency format with no decimal places. 8. Enter the interest rate of 8.0% in cell B3. Format the cell as shown in Figure B4A -1. 9. Enter the number 5 in cell B4 for the number of years. 10. Create names for cells in the range B2:B4, using values from the column to the left. 11. Enter the function =PMT in cell D4 to calculate the monthly payment on a loan of $26,000 (cell B2) at 8.90% (cell B3) for 5 ycars (cell B4). Format it as shown in Figure E4A-1. 12. Use the fill handlo to fill the ninge A6:A10 with the numbers 1 through 5 . 13. Enter the formula =Principal in cell B6 to reference the principal, which is the beginning balance for year 1 . 14. Enter the -PV function in cell C6 to determine the conding balance for year I. 15. Enter a formula in cell D6 to determine the annual amount paid on the principaliloan. 16. Enter the formula in cell B6 to calculate the amount of interest paid for the year. 17. Enter the formula C6 in eell B7 to obtain the beginning talance for year 2 , Copy this formula to the range B8:B10. 18. Copy the =PV function entered in cell C6 to the range C7Cl. 19. Copy the formula in cell D6 to the range D7:D10. 20. Copy the formula in cell E6 to the range E7:E10. If all is done properly, the value in cell Clo should be zero. 21. Use the SUM function in cells D11 and E11 to sum the payment and interest amounts. 22. Format all cells as shown in Figure EAA - 1. Apply a custom border to the range A2:BA using the Aqua, Accent 5 color and the double linestyle in row 2, column 7. 23. In cell A12, enter the label, The Effect of Various Interest Rates. Format thit entry the same as the formatted entry in cell Al. 24. Enter the following labels: B13 = Rate; C13= Total Paid; D13 = Total Interest. 25. Enter the formula = D11 in cell C14 and the formula =E11 in cell DI4. 26. Enter and format the interest rates shown in Figure B4A2 into the range B15B21. Mamually enter the interest rates in cells B15:B16, and then use the fill handle to fill the range B17:B21. 27. Create a one-variable data table that displays the total amount paid and the total amount of interest for the 5 year amortization schedale created in this exercise. 28. Use conditional formatting for the range B15:B21 so that the cell with the interest rate equal to the one entered in cell B3 is formatted with a light red background and dark red toxt. 29. Set the range A1:E11 as the print arel. 30. Protect the shote so that only cells B2:B3 can be changed. Do not set a password, and make sure that all locked and unlocked cells can be selected. 31. Rename the Sbeet 1 tab to Car Loan. 32. Save the workbook 33. Print the active worksbeet. Subenit the renults as directed by your instructor