Question

Townsend Mortgage Company Rate Input Area Years Today's Date 15 3.750% Pmts Per Year: 12 4.250% 4.625% 0.45% Annual PMI Rate: 20.0% Down Pmt Rate Amount Down Years House Cost Down Payment Loan 392786 CNY425,750.00 CNY86,000.00 CNY339,750.01 393000 30 CNY335 900.00 CNY 75,500.00 CNY260,400.01 393025 CNY205,500.00 CNY30,000.00 CNY175,500.01 392600 CNY285 900.000 CNY50,000.00 CNY235,000.01 15 30 392638 CNY329 00.000 CNY 70,000.00 CNY259,000.01 452786 CNY400,000.00 CNY80,000.00 CNY320,000.01 30 453000 CNY350,000.00 CNY 60.000.00 CNY290,000.01 453025 CNY175.500.00 CNY 30.000.00 CNY145,500.01 452600 CNY265 950.000 CNY58.000.00 CNY207950.0l 15 452638 30 CNY329,750.00 CNY 65.000.00 750.01 Monthly Monthly PMI Payment

2. Complete the % Down column by correctly calculating the percentage of the cost of the house that the customer’s down payment represents.

3. Complete the APR column by using the VLOOKUP function and the table for Years and Rate provided to assign the correct interest rate to each loan based on the length of the loan in the Years column.

4. Complete the Monthly Payment column using the Payment (PMT) function to correctly calculate the monthly payment due for each loan.

5. If borrowers aren’t able to provide a 20% down payment they must pay a Private Mortgage Insurance (PMI) fee to protect Townsend Mortgage Company if the borrower defaults on the loan. Complete the Monthly PMI column by using the IF function to determine which loans require Private Mortgage Insurance and correctly calculating the amount of premium due each month.

Answer 1

Solution:

2. Solution in image

3.. =VLOOKUP(F8,$D$1:$E$4,2,FALSE)

4. =PMT(G8,12*F8,D8)

5. =IF(E8<20,D8*$B$4,0)