2 3 Input Area 4 Today's Date: 11/1/2022 5 #Pmts Per Year: 12 6 PMI Rate: 7 Down Pmt Rate: 0.38% 20.0% 8 Townsend Mortgage Company APR Table Years APR 10 3.00% 15 3.250% 25 3.625% 30 3.750% K L M N Summary Statistics Down Amount Monthly 9 Loan # Borrower Last House Cost Payment Financed % Down Years APR Payment Monthly PMI Statistics House Cost Amount Financed 10 392786 Smith $ 725,500 $160,000 $ 565,500 22.1% 12 3.000% $4,681.13 $ Total $ 1,782,700 $ 1,427,200 11 393000 Patel $ 335,900 $ 75,500 $ 260,400 22.5% 30 3.750% $1,205.95 $ Average $ 356,540 $ 285,440 12 393025 Krause $ 205,500 $ 20,000 $ 13 392600 Reinard $ 285,900 $ 14 392638 Reed $ 229,900 $ 50,000 $ 50,000 $ 185,500 35,900 179,900 9.7% 25 3.625% 17.5% 21.7% 15 3.250% 30 3.750% $941.14 $ $1,657.60 $ $833.14 $ 58.74 74.70 Median $ 285,900 $ 235,900 Lowest $ 205,500 $ 179,900 Highest $ 725,500 $ 565,500 15 # of Mortgages $ 5 $ 5 16 17 18 19 Case Analysis 20 21 1. For each Loan, create calculations for % Down (F10:F14), APR (H10:H14), Monthly Payment (110:114), and Monthly PMI (J10:J14) using the following formulas: Down Payment divided by House Cost 22 23 % Down APR 24 Monthly Payment Use the VLOOKUP function, using Years as the Lookup_Value, the APR Table (H5:17)as the table array, and find the nearest match. Use the PMT function and include data from the APR, Years, Amount Financed columns. (Use # Pmts Per Year (B5) to change annual values to monthly values). Make sure results are a positive number Case Analysis % Down Down Payment divided by House Cost 418:H14), Monthly Payment (110:114), and Monthly PMI (J10:114) using the following formulas: APR Monthly Payment Monthly PMI Use the VLOOKUP function, using Years as the Lookup_Value, the APR Table (H5:17)as the table array, and find the nearest match. Use the PMT function and include data from the APR, Years, Amount Financed columns. (Use # Pmts Per Year (B5) to change annual values to monthly values). Make sum Use the IF function. Evaluate whether %Down (column F) is greater than or equal to the Down Payment Rate (B7). If it is, do nothing (use 2. In the Summary Statistics table (L10:N15), calculate the Total, Average, Median, Lowest, Highest, and Number of Mortgages based House Cost and Amount Financed for all the loans or 0). If it is not, then use the mn 3. Based on the results of the above calculations, answer the following questions by highlighting the correct response. (Note: you may manipulate the table above, as needed, to get your answe 1 Which is the least amount of down payment (in dollars) would Krause need to make to avoid paying Monthly PMI a. $42,000 b. $41,500 c. $40,000 2 What would be the APR if the Patel's took out a 20 year loan? a. 3.25% b. 3.65% c. 3.75% 3 If the Reinard's took out a loan for 30 years instead of 15 years, what would their monthly payment be? a. $1,092.49 b. $1,902.49 c. $1,029.49 Case Analysis 2 What would be the APR if the Patel's took out a 20 year loan? a. 3.25% b. 3.65% c. 3.75% 3 If the Reinard's took out a loan for 30 years instead of 15 years, what would their monthly payment be? a. $1,092.49 b. $1,902.49 c. $1,029.49 C. 4 What option should the Reed's consider to reduce their monthly payment? a. Opt for a shorter term loan b. Decrease the down payment amount c. Opt for a lower interest rate 5 What is the difference between Average and Median? a. Median is the total divided by the count of items, and Average is the difference between high and low b. Average is the middle point, and Median is the total divided by the count of items c. Median is the middle point, and Average is the total divided by the count of items 6 Looking at the Median and Average statistics, why is Average higher than the Median a. The Average is skewed by a house with a higher cost b. The Median is skewed by a house with a higher cost c. The Average is skewed because of the small number of houses