Mortgage Calculator
Inputs				Intermediate Calculations
Negotiated Cost of Housing		$ 375,000.00		APR Based on Credit Rating	3.25%
Additional Down Payment		$ 5,000.00		Min Down Payment Required	$ 18,750.00
Date of First Payment		9/13/2019		Annual Property Tax	$ 2,812.50
Credit Rating		Excellent		Annual PMI	$ 1,756.25
Constants				Outputs
Property Tax Rate		0.75%		Total Down Payment	$ 23,750.00
Down Payment to Avoid PMI		20.00%		Amount of the Loan	$ 351,250.00
PMI Rate		0.50%		Monthly Payment (P&I)	$ 1,528.66
Term of Loan in Years		30		Monthly Property Tax	234.38
# of Payments Per Year		12		Monthly PMI	146.35
				Total Monthly Payment	$ 1,909.39
Credit	Down Payment	APR		Date of Last Payment
Excellent	5%	3.25%
Good	10%	3.50%
Fair	15%	4.25%
Poor	20%	5.25%

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