For this case assume: You are considering the purchase of a new automobile. Besides the search for the right car for you in terms of performance, style, and color, you wish to see the impact of a car loan and how it will fit into your personal budget. You've been working with a few banks on potential car loans and terms and wish to determine for yourself the payment schedule, monthly payment, and most importantly, just how much interest you will pay over the life of the loan You also want to run a few scenarios to determine which is the best option for you. To complete this exercise, you will use multiple aspects of Excel. These include: 1. Setting up and formatting a loan payment (amortization) schedule 2. Using the "absolute cell reference" and "autofill features in Excel 3. Using the formula function PMT and IPMT to calculate monthly payment based on a given interest rate and length of load 4. Copy a worksheet and adjust given values to answer a "what-if" scenario. Your ideal car that you wish to purchase is listed at $44,900 and you have saved an initial down payment of $5,000. You have identified multiple car loans: 1. 60-month, 4.22% 2. 72-month, 4.10% Case requirements: Using Excel: 1. Create a loan amortization schedule for both loan options for the life of the loan. Note: create the schedule in Excel; do not download or use a pre-prepared template from another source. Determine the monthly payment, and the $ amount of interest and principal paid monthly. Use the format below as illustrated in the textbook, table 5-6 as your guide in setting up the schedule. table 5.6 AMORTIZATION SCHEDULE OVER FOUR YEARS (9 PERCENT APR) B D E F 1 Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 2. Note the following: a. In the above image, to calculate the monthly payment, you can use the PMT formula. b. Also, to determine interest and principal portions of the monthly payment, you can use the Excel functions IPMT and PMT formulas. c. To easily complete the amortization tables, use the following Excel functions: i. Absolute cell reference ii. Autofill iii. PMT and IPMT function/formula Once the amortization schedules are completed, answer the following questions in a narrative: 1. What is the total amount of $ interest paid for each car loan assuming the loan is completed on schedule (not paid off early)? 2. You've learned that making an extra principle payment once a year results in less total interest paid as well as paying off the loan in full, faster. a. Using the 60-month loan, you plan to make a $500 payment towards the load balance at the end of years 1, 2, 3, and 4. b. Create a new amortization schedule to show the impact of the additional $500 payment. i. Hint: use the worksheet copy function to create a copy of the original loan schedule. ii. Insert a new column "Addt'l Payment between "principal paid (E)" and "ending balance (F) (see above amortization schedule set up) iii. Adjust the formula so the additional payment is accounted for in the ending balance for those 4 periods. iv. Insert the extra payment at the end of years 1 through 4. c. By making the 4 extra yearly payments of $500, what is the $ savings in interest over the life of the loan, and how many months of payments do you save as compared to the original 60-month amortization? Provide a detailed summary of the loan analysis, including the pros and cons of the three options. Include what you learned from the exercise, both from a finance and budget perspective as well as interest expense perspective. Insert the narrative summary into your spreadsheet. Note: Submit one Excel file for this assignment. Place each amortization schedule on it's own worksheet Name each worksheet tab (right click on the tab, select 'rename'). 48 month 4.22% 60 month 4.10% 60 month 4.10% extra pmt