As a financial consultant, you work with a family who plans to purchase a $35,000 car. You want to create a worksheet containing variable data (the price of the car, down payment, date of the first payment, and borrowers credit rating) and constants (sales tax rate, years, and number of payments in one year). Borrowers pay 0.5% sales tax on the purchase price of the vehicle and their credit rating determines the required down payment percentage and APR. Your worksheet needs to perform various calculations.

Type the labels in the range A3:A12. For each label, such as Negotiated Cost of Vehicle, merge the cells, such as the range A4:B4. Use the Format Painter to copy the formatting to the remaining nine labels. Next type and format the Inputs and Constants values in column C.

Type Credit, Down Payment, and APR in the range A14:C14, type the four credit ratings in the first column, the required down payment percentages in the second column, and the respective APRs in the third column. Next format the percentages, and then indent the percentages in the cells as needed.

Type labels in the Intermediate Calculations and Outputs sections in column E.

Enter formulas in the Intermediate Calculations and Outputs sections to calculate the following:

APR based on credit rating: Use a Lookup function that references the borrowers credit rating and the table array in range. Include the range_lookup argument to ensure an exact match.

Minimum down payment required: Use a lookup function and calculation. Use the credit rating as the lookup value, and the table array A15:C18. Include the range_lookup argument to ensure an exact match. Multiply the function results by the negotiated cost of the house.

Sales tax: Multiply the negotiated cost of the vehicle by the sales tax rate.

Total down payment: The sum of the minimum down payment required and any additional down payment made.

Amount of the loan: The difference between the negotiated cost of the house and the total down payment.

Monthly payment: Principal and interest using the PMT function.

Format each section with fill color, bold, underline, number formats, borders, and column widths as needed.

Insert a footer with your name on the left side, the sheet name in the center, and the file name code on the right side of both sheets.

Save and close the workbook. Based on your instructors directions, submit e02m2Loan_LastFirst.

Please show formulas for calculations on answers.

Example Spreadsheet:

Auto Loan Calculator 4 Inputs Intermediate Calculations 7 10 Constants Outputs 12 13 14 15 Credit 16 17 18 19 20 21 Down Payment APR Auto Loan Calculator 4 Inputs Intermediate Calculations 7 10 Constants Outputs 12 13 14 15 Credit 16 17 18 19 20 21 Down Payment APR