please answer using excel and show your work thank you.

WINTER 2022 PROBLEM ONE (20 MARKS) You are planning to buy a house in Toronto that has a price of $1,500,000. One of the local banks has offered you a mortgage at a quoted rate of 3% per year. Interest will be compounded semi- annually. The bank has indicated that they will require 20% down payment. The bank is prepared to lend you the remainder of the purchase price of the house. The amortization period will be 25 years and the term of the mortgage will be 2 years. You are going to make monthly payments on your mortgage. The payments will be made at the end of each month. You have heard from your friend who has just completed AFF210, that by making additional payments on your mortgage during the initial term, you can reduce the remaining balance at the end of the initial term. Your friend has shown you a sample amortization schedule template (below). Sample Mortgage Schedule Payment Principal Interest Number Amount Expense at the incurred Beginning during the month 1 2 Balance Owing Before Payment Amount of Payment Principal Principal Interest Component Amount Paid / of Payment at the Amount End of of Period Payment a) Answer the following questions: i. What is the amount of your periodic payment? ii. How much will you pay in total on your mortgage over the life of your mortgage? iii. What is total interest that will be paid over the life of your mortgage? iv. What is total you paid during the term of the mortgage? V. What is the balance owing on the mortgage at the end of the term? vi. How much principal will you have paid off during the initial term of your mortgage? vii. How much interest will you have paid off during the initial term of your mortgage? b) Prepare a mortgage amortization schedule to illustrate how the mortgage will be repaid over the next 25 years and calculate the following: i. Use the amortization table to determine the first payment where the interest portion of the payment is less than 20% of the total payment. Identify the payment number where this + Automatic Zoom Sample Mortgage Schedule Payment Principal Interest Number Amount Expense at the incurred Beginning during the month Balance Owing Before Payment Amount of Payment Principal Principal Component Amount of Payment at the End of Period Interest Paid / Amount of Payment 2 a) Answer the following questions: i. What is the amount of your periodic payment? il. How much will you pay in total on your mortgage over the life of your mortgage? iii. What is total interest that will be paid over the life of your mortgage? iv. What is total you paid during the term of the mortgage? What is the balance owing on the mortgage at the end of the term? vi. How much principal will you have paid off during the initial term of your mortgage? vii. How much interest will you have paid off during the initial term of your mortgage? V. b) Prepare a mortgage amortization schedule to illustrate how the mortgage will be repaid over the next 25 years and calculate the following: i. Use the amortization table to determine the first payment where the interest portion of the payment is less than 20% of the total payment. Identify the payment number where this occurs c) You have decided to increase your monthly payments by $200 per month for the life of the mortgage (until the mortgage is paid off). How long does it take to pay off your mortgage? ii. How much interest do you save by making the extra payment of $200 per month