As a financial consultant, you work with people who are planning to buy a new house. You want to create a worksheet containing variable data (the price of the house, down payment, date of the first payment, and borrower's credit rating) and constants (property tax rate, years, and number of pay- ments in one year). Borrowers pay 0.5% private mortgage insurance (PMI) on the loan amount if they do not make at least a 20% down payment. A borrower's credit rating determines the required down payment percentage and APR. For example, a person with an excellent credit rating may make only a 5% down payment with a 3.25% APR loan. A person with a fair credit rating will make a 15% down payment and have a higher APR at 5.25%. Your worksheet needs to perform various calculations. The filled cells in column F indicate cells containing formulas, not values. Refer to Figure 2.43 as you complete this exercise. 1 2 3 Inputs 4 Negotiated Cost of House 5 Additional Down Payment 6 Date of First Payment 7 Credit Rating 8 9 Constants 10 Property Tax Rate 11 Down Payment to Avoid PMI A 12 PMI Rate 13 Term of Loan in Years. 14 # of Payments Per Year B 15 16 Credit Down Payment 17 Excellent 5% 18 Good 10% 19 Fair 15% 20 Poor 20% 21 . . $375,000.00 5,000.00 $ . Mortgage Calculator . 5/1/2016 Excellent 0.75% 20.00% 0.50% 30 12 D APR 3.25% 3.50% 4.25% 5.25% E Intermediate Calculations APR Based on Credit Rating Min Down Payment Required Annual Property Tax Annual PMI Outputs Total Down Payment Amount of the Loan Monthly Payment (P&I) Monthly Property Tax Monthly PMI Total Monthly Payment Date of Last Payment a. Start a new Excel workbook, save it as e02m2Loan_LastFirst, rename Sheet1 Payment, add a new sheet, and then rename it Range Names. b. Select the Payment sheet, type Mortgage Calculator in cell A1, and then merge and center the title on the first row in the range A1:F1. Apply bold, 18 pt size, and Gold, Accent 4, Darker 25% font color. 3.25% $ 18,750.00 $ 2,812.50 $ 1,756.25 c. Create and format the Inputs and Constants areas by doing the following: Type the labels in the range A3:A20. For each label, such as Negotiated Cost of House, merge the cells, such as the range A4:B4, and apply Align Text Left. You will have to merge cells for nine $ 23,750.00 $351,250.00 $1,528.66 234.38 146.35 $ 1,909.39 4/1/2046 labels. Enter and format the Inputs and Constants values in column C. Create the lookup table in the range A16:C20 to use the credit ratings to identify the appropriate required percentage down payment and the respective APR by doing the following: . Type Credit, Down Payment, and APR in the range A16:C16. 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. Format the percentages, apply Align Text Right, and then indent the percentages in the cells as needed. 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 F4:F7 and F10:F12. Widen column E as needed. g. Enter formulas in the Intermediate Calculations and Outputs sections using range names to calcu- late the following: . . 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 multi- plying the amount of the loan by the PMI rate. Total down payment, which is sum of the required minimum down payment (calculated previ- ously) 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. 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.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 cal- culation comes after the closing parenthesis. . 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 pay- ment date is 5/1/2016, the final payment date is 4/1/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. 1. Save and close the workbook, and submit based on your instructor's directions.