(d) Calculate the monthly payment (use a positive number) in cell G11:G15 (8 points) and the total interests (use a positive number) in H11:H15 (4 points). (e) Calculate the percentage of Down Payment to the House Cost in cell I11:I15. (4 points) (f) Lenders often want borrowers to have a certain percentage down payment. Northeast Mortgage Company requires the borrowers pay at least a 20% down payment, which is shown in cell B8. If borrowers do not put in 20% of the cost of the house as a down payment, then they should pay the private mortgage insurance (PMI) fee. PMI serves to protect lenders from absorbing loss if the borrower defaults on the loan, and it enables borrowers with less cash to secure a loan. Calculate PMI in cell J11:J15 (5 points). If a person does not need to pay PMI, his/her Monthly PMI should be zero. In cell B19:B20, using Excel functions to figure out the PMI status of certain loan numbers. When the PMI is larger than zero, it should return Yes, otherwise No

LEL 1 2 3 5 Date and Time 6 7 10 11 12. 13 14 15 16 17 CH82554 18 19 20 21 22 23 A Table 1. Basic Information Input Area Northeast Mortgage Company # Pmts Per Year: PMI Rate Down Pmt Rate: Loan # 798504 502321 891546 655369 504017 Loan # 891546 504017 12 0.38% 20.00% House Cost Down Payment $ 425,750.00 $ 86,000.00 $ 335,900.00 $ 75,500.00 $ 205,500.00 $ 30,000.00 $285,900.00 $ 50,000.00 $ 329,900.00 $ 70,000.00 PMI Status D Table 2. Annual Percentage Rates (APR) Years APR 15 3.25% 25 3.50% 30 3.75% Years Amount Financed 25 30 25 15 30 F APR Monthly Payment H Total Interests 1 %Down J Monthly PMI L