I did some of thew work listed below but if it is wrong, please correct it.
1. 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 the 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 percentage in the cells needed.
B. 2. Assign Range names to cells containing individual values in the Inputs and Constants section. Do not use the Create from Selection feature because the labels are stored in merged cells. Assign a range name to the lookup table.
C. 3. 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.
D. 4. Enter Formulas in the Intermediate Calculations and Outputs sections using range names to calculate the following:
- · APR based in the borrower’s credit rating by using a look up 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 credit 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 borrows total down payment (required and additional) is 20% or higher of the negotiated purchased price (multiply the cost of 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 the 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 principle 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 functions 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 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 to 5/1/2046, you would be making an extra payment.
E. 5. 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.
F. 6. Center the worksheet data horizontally between the left and right margins.
|Due By (Pacific Time)
||10/11/2014 12:00 pm