You are an assistant to John Moore, a mortgage broker at the Northeast Mortgage Company. John spends his days reviewing mortgage rates and trends, meeting with clients, and preparing paperwork. He relies on your expertise in using Excel to help analyze mortgage data. Today, John provided you with sample mortgage data: loan number, house cost, down payment, mortgage rate, and the length of the loan in years. He has asked you to perform some basic calculations so that he can check the output provided by his system to verify that it is calculating results correctly.

(a) In cell B5, input the current date and time, which should be the date and time whenever John examines the spreadsheet. (b) Calculate the amount financed in cell E11:E15. (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.

(please provide excel code )

1 23456 A Table 1. Basic Information Input Area B C Northeast Mortgage Company Date and Time # Pmts Per Year: 7 PMI Rate 8 Down Pmt Rate: 9 Loan # 10 11 12 798504 502321 891546 13 14 655369 504017 15 16 17 18 Loan # 19 891546 20 504017 21 22 23 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 E 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 LL APR G Monthly Payment H Total Interests %Down I J Monthly PMI K L 1 23456 A Table 1. Basic Information Input Area B C Northeast Mortgage Company Date and Time # Pmts Per Year: 7 PMI Rate 8 Down Pmt Rate: 9 Loan # 10 11 12 798504 502321 891546 13 14 655369 504017 15 16 17 18 Loan # 19 891546 20 504017 21 22 23 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 E 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 LL APR G Monthly Payment H Total Interests %Down I J Monthly PMI K L