Steps to Perform:

Step

Instructions

$1$

Start Excel. Download and open the file named Exp$22\_$Excel$\_$ChO$2\_$ML$2\_$Vacation

Property xIsx. Grader has automatically added your last name to the beginning of the filename.

$2$

Write an IF function in cell D$9$ that calculates the down payment for the mortgage. The down payment is $15\%$ of the purchase price if the customer is considered high risk or $5\%$ of the purchase price if considered low risk. The criteria to assess the risk of the loan is based on the credit score value in cell B$6.$ Be sure to use the appropriate absolute or mixed cell references when referencing the input values in the range B$4$:B$6.$ Then use the fill handle to copy the formula down, stopping in cell D$28.$

$3$ Enter a formula to calculate the percent financed in cell E$9.$ The percent financed is the amount financed$/$purchase price. Next use the fill handle to copy the formula down, stopping cell E$28.$

$4$ Enter a formula in cell F$9$ to calculate the amount financed. The amount financed is the purchase price$-$down payment. After entering the formula, use the fill handle to copy the formula down, stopping in cell F$28.$

$5$ Use the VLOOKUP function in cell G$9$ to lookup the mortgage rate based on the customer credit score and the data in the range D$4$:E$6.$ Be sure to use the appropriate absolute or mixed cell references. Use the fill handle to copy the formula down, stopping in cell G$28.$

$6$

Calculate the rate per period in cell $H9.$ All mortgages being financed will be paid monthly. After completing the formula, use the fill handle to copy the formula down, stopping in cell H$28.$

$7$

$8$

$9$

$10$

Calculate the total number of payment periods in cell J$9$ based on the years financed in cell $19.$ After completing the formula, use the fill handle to copy the formula down, stopping in cell J$28.$

Use the PMT function to calculate the total monthly payment in cell $K10$ based on the periodic interest rate, number of periods, and amount financed. Ensure the results are a positive value then use the fill handle to copy the function, down stopping in cell K$28.$

Use Quick Analysis to calculate the total of all payments in cell K$29.($Quick Analysis is not available for Mac. If using a Mac, use AutoSum to calculate the SUM of the range K$9$:K$28).$

Use the appropriate statistical functions in the range B$32$:B$36$ to calculate descriptive statistics based on the row headings in the range $A32$:$B36.$ In cell $B32,$ calculate the total count of loans based on column $A,$ lowest payment in cell $B33$ based on the data in column $K,$ highest payment in B$34,$ average payment in $B35,$ and median payment in $B36.$

Use the XLOOKUP function in cell E$32$ to lookup the Lot # in cell D$32$ and return the corresponding down payment, $\%$ finances, and amount financed.