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.
  1. Use the TODAY function to insert the current date in the next row.
  2. Below you will find information on four banks and the Annual Percentage Rate (APR) for both a 36 month term and a 60 month term for new cars.

Bank 36 Month Rate 60 Month Rate

Branch Banking and Trust Co. 2.49% 2.59%

Hamilton Bank 2.40% 2.90%

PNC Bank 2.24% 2.24%

Bank of America 1.99% 1.99%

  1. Under “Name of Institution” in Cell A7, add the names of the four (4) institutions. Continue to fill out this section of the worksheet using the information given above. Add 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, the monthly payment for the smaller term loan should be left blank, the length of the longer term loan 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.
  2. Review the information below for a price on a new car. Make Model Year Dealership Price Honda CR-V EX-L AWD with Navigation 2015 Anderson Honda $31,775.00
  1. In the next area of the worksheet, enter the make, model, and year of the new car, the car dealership selling the car, and the price of the car according to the information above. Make sure all values, including the headings of this section, are properly formatted.
  1. 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. Calculate the monthly payment for the 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.
  2. In the next area of the worksheet, see what happens to the lowest monthly payment of the 60 month term loan if you were able to find the new car for a 20% discount. Include the same information found in step 5 but also include the discount percentage, the amount of the discount and the new cost of the car. Make sure all values, including the headings of this section, are properly formatted.
  3. You have decided to purchase the car using the longer loan term. In the next area of the worksheet, calculate the total amount you would end up paying if you went with each lender adding rows as needed. 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.
  1. 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 vertical axis while the names of the banks will appear on the horizontal 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.
  2. Format the worksheet with appropriate number formats, styles, colors, and font sizes. Make sure the final worksheet is easy to read.
  3. 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.