Youd like to buy a new car, but you dont 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. 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.

60 Month Rate

2.59% 2.90% 2.24%

Bank of America 1.99% 1.99%

5. Under Name of Institution in Cell A7, add the names of the four (4) institutions (banks, savings loans or credit unions, etc.). 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 (in months, i.e. 36), the monthly payment for the smaller term loan should be left blank, the length of the longer term loan (in months, i.e. 60) 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.

Bank

36 Month Rate

Branch Banking and Trust Co.

2.49%

Hamilton Bank

2.40%

PNC Bank

2.24%

Capstone Project 2 Instructions 1

6. Review the information below for a price on a new car.

Make

Model

Year

Dealership

$31,775.00

Honda

CR-V EX-L AWD with Navigation

2015

Anderson Honda

7. 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.

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. 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.

9. 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 (i.e. discounted price) and the new cost of the car. Make sure all values, including the headings of this section, are properly formatted.

10. 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.

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 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.

12. Format the worksheet with appropriate number formats, styles, colors, and font sizes. Make sure the final worksheet is easy to read.

(APR) for both a 36 month term and a 60 month term for new cars. 5. Under "Name of Institution" in Cell A7, add the names of the four (4) institutions (banks, savings loans or credit unions, ete.). 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 (in months, i.e. 36), the monthly payment for the smaller term loan should be left blank, the length of the longer term loan (in months, i.e, 60) the monthly payment for the longer tem loan should also be left blank at this time. Make sure all values, including the headings of this section, are properly formatted. 10. 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. 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 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. 12. Format the worksheet with appropriate number formats, styles, colors, and font sizes. Make sure the final worksheet is easy to read. 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. 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. 6. Review the information below for a price on a new car. 7. 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. 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. Calculate the monthly payment for t rter 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 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 (i.e. discounted price) and the new cost of the car. Make sure all values, including the headings of this section, are properly formatted