(a) In cell B5, input the current date and time, which should be the date and time whenever John examines the spreadsheet. (2 points) (b) Calculate the amount financed in cell E11:E15. (3 points) (c) The APRs (annual percentage rates) vary based on the number of years to pay off the loan. Table 2 shows the details of APRs when the numbers of years to pay off the loan are 15, 25, and 30. Fill in the cells F11:F15 using any look-up functions. (5 points)

(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. The following formula shows how to calculate PMI:

??? = ?????? ???????? (??? ????/???????? ??? ????)

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" (8 points).

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