This is an excel assignment. The directions must be followed exactly please.

Capstone Project 2 Instructions Financing a Car You'd like to buy a new car, but you don't have enough cash to purchase a car without financing the purchase price. Create an Excel worksheet that calculates the monthly payment based on the purchase price of the car, and then conduct simulated what-if analyses to see what happens to the monthly payment if you increase the amount of the loan or increase the number of years of the loan. 1. Download the starter Excel data file named Capstone_2_Starter_Data_File and same it as a new Excel workbook with the name Car_Financing_nn.xlsx where nn are your first and last initials. 2. Modify the appropriate title in the first row and format it with the Title built-in cell style. 3. Use the TODAY function to insert the current date in the next row. 4. Start your Internet browser, and go to your favorite search provider. Search the state of Maryland for four banks where you will find the Annual Percentage Rate (APR) for both a 36 month term and a 60 month term for new cars. 5. Set up columns to contain the name of each of the four (4) institutions (banks, savings loans or credit unions, etc.) that you find in a new row in the worksheet, the URL of the home page of the lender in the next column, the annual percentage rate each lender is currently offering on a new car loan in the next column for both a 36 and 60 month term loan, the length of the smaller loan (in months, not years), the monthly payment for the smaller term loan should be left blank, the length of the longer term long (in months, not years) and the monthly payment for the longer term loan should also be left blank at this time. Make sure all values, including the headings of this section, are properly formatted including home page URL's. 6. Use your favorite search provider, and search for a price on a new car that you would like to purchase. 7. In another area of the worksheet, enter the make, model, and year of the car you want to purchase, the car dealership selling the car, the URL of the Web site, and the price of the car according to that site. It does not matter if the price is the discounted price or the MSRP price. Make sure all values, including the headings of this section, are properly formatted including home page URL's. 8. In the blank columns containing the monthly payment calculations for each lending institution (in step 5), use the PMT function to calculate the monthly payment based on the annual percentage rate the lender is offering and the price of the car that you would like to purchase. Calculate the monthly payment for the Capstone Project 2 Instructions 1 shorter monthly term and also the longer monthly term for each bank rate listed. Format the monthly payments appropriately with two (2) decimals. Make sure the monthly payment appears as a positive number. 9. In another area of the worksheet, see what happens to the lowest monthly payment of the longer term loan if you were able to find the car you wanted to purchase for a price 20% less than the one you found. Include the same information found in step 5 but also include the discount percentage, the amount of the discount (i.e. discounted price) and the new cost of the car. Make sure all values, including the headings of this section, are properly formatted including home page URL's. 10. You have decided to purchase the car using the longer loan term. In another area of the worksheet, calculate the total amount you would end up paying if you went with each lender. For each lender calculate the difference between the purchase price of the car and the amount you would end up paying if you took the loan. Make sure all values, including the headings of this section, are properly formatted. 11. On the same worksheet, create a clustered column chart showing the total amount of interest you will have paid for each loan. The amount of interest paid will appear on the horizontal axis while the names of the banks will appear on the vertical axis. Make sure this chart has appropriate horizontal and vertical axis titles and an appropriate chart title. Hint: Find an open spot on the current worksheet to place the chart and make sure to size it so it looks appropriate. 12. Format the worksheet with appropriate number formats, styles, colors, and font sizes. Make sure the final worksheet is easy to read. 13. Make sure you save your final version of your spreadsheet as Car_Financing_nn where nn are your first and last initials and then submit this capstone assignment as instructed. Capstone Project 2 Instructions 2 Car Financing Bank, APR, Term, Monthly Payment Name of Institution Home Page URL APR (36 Months) APR (60 Months) Term - 36 months Term - 60 months Payment (36 Months) Payment (60 Months) Four bank interst rates for new car loans (36 and 60 month terms) and monthly payments for purchase of desired car Make, Model, Year, Dealer and Price of Desired Car Purchase Make Model Year Dealership URL of Dealership Price Make, model, year, dealer, dealership url and price of desired car What if we found the desired car at a 20% discount using the longest term and lowest APR...What would be the monthly payment? Name of Institution Home Page URL APR (36 Months) APR (60 Months) Term - 36 months Term - 60 months Payment (36 Months) Payment (60 Months) Monthly payment of lowest interest rate of longer term if car desired can be found for 20% less How much interest are we paying for each loan assuming we use the longest term for our loan? Name of Institution Price of Car Payment Total Amt. Paid Calculated difference between amount paid and cost of the car for each loan using largest term Interest Paid Discount % Discounted Price New Price