I need help with E, F, & G. It should be done on excel. Please show steps and formulas.

Calculator FROM As a financial consultant, you work with people who are planning to buy a new house. You want to ATCH 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. B C D E F A Mortgage Calculator A W N = Inputs Intermediate Calculations 3.25% Negotiated Cost of House $ 375,000.00 APR Based on Credit Rating $ 5,000.00 Min Down Payment Required $ 18,750.00 Additional Down Payment $ 5/1/2016 Annual Property Tax 2,812.50 6 Date of First Payment Credit Rating Excellent Annual PMI $ 1,756.25 8 9 Constants Outputs 10 Property Tax Rate 0.75% Total Down Payment $ 23,750.00 11 |Down Payment to Avoid PMI 20.00% Amount of the Loan $ 351,250.00 12 PMI Rate 0.50% Monthly Payment (P&I) $1,528.66 13 Term of Loan in Years 30 Monthly Property Tax 234.38 14 # of Payments Per Year 12 Monthly PMI 146.35 15 Total Monthly Payment $ 1,909.39 16 Credit Down Payment APR Date of Last Payment 4/1/2046 17 Excellent 5% 3.25% 18 Good 10% 3.50% 19 Fair 15% 4.25% 20 Poor 20% 5.25% tgage Data 21 a. Start a new Excel workbook, save it as e02m2Loan_LastFirst, rename Sheetl Payment, add a new sheet, and then rename it Range Names. b. Select the Payment sheet, type Mortgage Calculator in cell Al, and then merge and center the font color. title on the first row in the range Al:F1. Apply bold, 18 pt size, and Gold, Accent 4, Darker 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 labels. cells, such as the range A4:B4, and apply Align Text Left. You will have to merge cells for nine Enter and format the Inputs and Constants values in column C. d. 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. needed. Format the percentages, apply Align Text Right, and then indent the percentages in the cells ase. 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: . 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. 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. 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