Case 4: House mortgage payment Your cousin, Jeff, is planning to purchase a house. He needs to borrow $280,000 from a bank. The interest rate of borrowing is 3.25%. He plans to pay off the loan in 25 years. Because you are in Asper School, Jeff asks you to calculate the weekly and monthly payment for the house mortgage. Jeff wants to see how the interest rate and payment frequency (bi-weekly or monthly) affect for each payment. You decide to do a data table to illustrate the result. By default, the payment value is displayed as negative value. For this assignment displaying the payment value as a negative value is acceptable. Note: In Canada, mortgages typically amortize over a long term such as 25 years, but lock in interest rates for no more than five years. For this case we assume that the interest rate will not change over the lifetime the mortgage to simplify the calculations. Instructions: - You must use the PMT function to solve the problem. You must use cell reference (relative and/or absolute cell references) in the PMT function. - Format the cells so that payments have $ in front of them, with 1000 separator, and two decimal places. - Calculate the monthly payment and bi-weekly payment in cell B8, B9, B10 and B11, by using the values in B3-B5. - Calculate the monthly payment in the table of Payment Option 1: Monthly - The interest rates are listed in C16C23, and the number of payments in years are listed in D16-G16. The Amount of loan is still $280,000. - You must use one of the following two methods to do the calculation for the value in cell D17-G23 - Method 1: Enter PMT function in cell D17, and then you copy the function/formula in D17 to the rest of the cells to get the correct value. (you must use appropriate cell references in cell D17 to do this) - Calculate all payments for Payment Option 2: Monthly, Payment Option 3: Bi-Weekly, and Payment Option 4: Bi-Weekly. - You must use either Method 1 or Method 2 listed above to do the calculation. For Payment Option 2, 3, and 4, if you use Method 1, you must enter the PMT function in cell J17,D31, and J31 respectively, and fill the rest of the table by copying the function in cell J17,D31, and J31 respectively to the rest of the table