e$.$ Assign range names to cells containing individual values in the Inputs and Constants sections. Do not use the Create from Selection feature because the labels are stored in merged cells. Assign a range name to the lookup table.

f$.$ Type labels in the Intermediate Calculations and Outputs sections in column E and assign a range name to each cell in the ranges F$4$:F$7$ and F$10$:F$12.$ Widen column E as needed.

g$.$ Enter formulas in the Intermediate Calculations and Outputs sections using range names to calculate the following:

APR based on the borrower's credit rating by using a lookup function. Include the range lookup argument to ensure an exact match. For example, a borrower who has an Excellent rating gets a $3\mathrm{.}25\%$ APR.

Minimum down payment required amount by using a lookup function and calculation. Include the range$\_$lookup argument to ensure an exact match. For example, a borrower who has an Excellent rating is required to pay a minimum of $5\%$ down payment of the negotiated purchase price. Multiply the function results by the negotiated cost of the house. Hint: The calculation comes after the closing parenthesis.

Annual property tax based on the negotiated cost of the house and the annual property tax rate.

Annual PMI. If the borrower's total down payment $($required and additional$)$ is $20\%$ or higher of the negotiated purchase price $($multiply the cost by the PMI avoidance percentage$),$ PMI is zero. If the total down payment is less than $20\%,$ the borrower has to pay PMI based on multiplying the amount of the loan by the PMI rate.

Total down payment, which is sum of the required minimum down payment $($calculated previously$)$ and any additional down payment entered in the Inputs section.

Amount of the loan, which is the difference between the negotiated cost of the house and the total down payment.

Monthly payment of principal and interest using the PMT function.

Monthly property tax, the monthly PMI, and the total monthly payment.

Last payment date using the EDATE function. The function's second argument must calculate the correct number of months based on the total length of the loan. For example, if the first payment date is $\frac{5}{1}\frac{?}{2016},$ the final payment date is $\frac{4}{1}\frac{?}{2046}$ for a $30-$year loan. The last argument of the function must subtract $1$ to ensure the last payment date is correct. If the last payment date calculated to $5/1/2046,$ you would be making an extra payment.

h$.$ Format each section with fill color, bold, underline, number formats, borders, and column widths as shown in the figure.

i$.$ Paste a list of range names in the Range Names worksheet. Insert a row above the list and type and format column labels above the two columns in the list of range names.

j$.$ Center the worksheet data horizontally between the left and right margins.

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

I. Save and close the workbook, and submit based on your instructor's directions.