Down Payment GY Amount of St. Catharine, ON WelandON Niagara Falls, ON milton ON Toronto, ON Vancouver, BC Average over Feb 2001) $ 320.9035 IES 531.915 791280 $ 1358 $ 1.296,8205 21.9805 16.045.45 28.595,00 39.56400 67,918.2015 44100 417.620.00 3040 SOS 3.200 751.736.00 1.290, 445,80 1.231.979.00 Fig A-2 (City Index) The PT A - City Index worksheet will display the average house price (Feb 2021) for 6 Canadian cities. The cell marked in orange (represents the down payment percentage). When that amount is entered, all of the down payment amounts should be calculated. From this, you can calculate the amount of loan you would need to purchase an average house in each city. For example - An average house in Niagara Falls sells for $531,916. If you were to put down 5%, that would be $26,595.80. This would mean that you need to secure a loan for the other 95% (ie $505,320.20) Once the values are calculated on the PT A - City Index worksheet, you will want it to calculate how much the monthly payment would be for a 10-year mortgage and a 25-year mortgage, at 4 different interest rates in each of the 6 Canadian Cities, based on how much the loan is and how much down payment is paid. Looking at the table, we can see that: Borrowing $304,862.60 in Welland, with repayment over 25 years, and annual interest rate of 2.0% = $1292.17 / month repayment & we would pay $82,789.41 in interest over the 25 years If the interest rate dropped to 1.5%, $1219.26/ month repayment & we would only pay $60,914.28 in interest over the 25 years For Part A, you are to modify these worksheets that will calculate how much total interest you are to pay for certain interest rates and certain term periods. . You will use the PMT function in Excel (use help to learn about this function). Here are some hints for the parameters: Rate - this is the monthly rate (need to divide the annual rate by 12) Nper - number of total payments made over lifetime of loan Pv - the amount of money borrowed Fv , Type - these are optional - please omit these Please fill out the PT A worksheets to make them look similar to Fig A-1 and A-2. Format the tables so that the tables look presentable (ie - table headings, proper row and column headings with similar font sizes and alignment, not all the numbers are negative numbers, etc). Clue - multiplying a negative number by -1 will result in a positive value. APCO 1P01 Winter 2020 Assignment 2 The first table in each city will display the monthly loan repayment amounts. The columns will be the payback period and the rows will be the interest rates. (use the PMT function in each cell in the table, using the row heading and column headings as parameters in the function) The second table will be the total amount paid back over the length of the loan (in other words - the loan amount plus the interest). Use the same headings as the first table and use the data from the first table in your formulas (ie - monthly payment [loan entry] * number of monthly payments). The third table will calculate the cost of the loan (ie the total amount paid back [table B entry] minus the original amount of the loan [Loan Amount entry]) Save the worksheets with the values that correspond to a down payment of 15%, interest rates from 1.5% to 3.0% (in 0.5% increments) and for 10 years of loan repayments and 25 years of loan repayments. 1 2 Down Payment 15% 3 City Down Payment Amount of Loan 4 5 6 7 8 9 10 11 12 13 14 St. Catharines, ON Welland, ON Niagara Falls, ON Hamilton, ON Toronto, ON Vancouver, BC Average House Price (Feb 2021) $ 439,600 $ 320,908 $ 531,916 $ 791,280 $ 1,358,364 $ 1,296,820 1 2 3 St. Catharines, Ontario 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Welland, Ontario 21 22 23 24 25 26 28 29 Down Payment GY Amount of St. Catharine, ON WelandON Niagara Falls, ON milton ON Toronto, ON Vancouver, BC Average over Feb 2001) $ 320.9035 IES 531.915 791280 $ 1358 $ 1.296,8205 21.9805 16.045.45 28.595,00 39.56400 67,918.2015 44100 417.620.00 3040 SOS 3.200 751.736.00 1.290, 445,80 1.231.979.00 Fig A-2 (City Index) The PT A - City Index worksheet will display the average house price (Feb 2021) for 6 Canadian cities. The cell marked in orange (represents the down payment percentage). When that amount is entered, all of the down payment amounts should be calculated. From this, you can calculate the amount of loan you would need to purchase an average house in each city. For example - An average house in Niagara Falls sells for $531,916. If you were to put down 5%, that would be $26,595.80. This would mean that you need to secure a loan for the other 95% (ie $505,320.20) Once the values are calculated on the PT A - City Index worksheet, you will want it to calculate how much the monthly payment would be for a 10-year mortgage and a 25-year mortgage, at 4 different interest rates in each of the 6 Canadian Cities, based on how much the loan is and how much down payment is paid. Looking at the table, we can see that: Borrowing $304,862.60 in Welland, with repayment over 25 years, and annual interest rate of 2.0% = $1292.17 / month repayment & we would pay $82,789.41 in interest over the 25 years If the interest rate dropped to 1.5%, $1219.26/ month repayment & we would only pay $60,914.28 in interest over the 25 years For Part A, you are to modify these worksheets that will calculate how much total interest you are to pay for certain interest rates and certain term periods. . You will use the PMT function in Excel (use help to learn about this function). Here are some hints for the parameters: Rate - this is the monthly rate (need to divide the annual rate by 12) Nper - number of total payments made over lifetime of loan Pv - the amount of money borrowed Fv , Type - these are optional - please omit these Please fill out the PT A worksheets to make them look similar to Fig A-1 and A-2. Format the tables so that the tables look presentable (ie - table headings, proper row and column headings with similar font sizes and alignment, not all the numbers are negative numbers, etc). Clue - multiplying a negative number by -1 will result in a positive value. APCO 1P01 Winter 2020 Assignment 2 The first table in each city will display the monthly loan repayment amounts. The columns will be the payback period and the rows will be the interest rates. (use the PMT function in each cell in the table, using the row heading and column headings as parameters in the function) The second table will be the total amount paid back over the length of the loan (in other words - the loan amount plus the interest). Use the same headings as the first table and use the data from the first table in your formulas (ie - monthly payment [loan entry] * number of monthly payments). The third table will calculate the cost of the loan (ie the total amount paid back [table B entry] minus the original amount of the loan [Loan Amount entry]) Save the worksheets with the values that correspond to a down payment of 15%, interest rates from 1.5% to 3.0% (in 0.5% increments) and for 10 years of loan repayments and 25 years of loan repayments. 1 2 Down Payment 15% 3 City Down Payment Amount of Loan 4 5 6 7 8 9 10 11 12 13 14 St. Catharines, ON Welland, ON Niagara Falls, ON Hamilton, ON Toronto, ON Vancouver, BC Average House Price (Feb 2021) $ 439,600 $ 320,908 $ 531,916 $ 791,280 $ 1,358,364 $ 1,296,820 1 2 3 St. Catharines, Ontario 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Welland, Ontario 21 22 23 24 25 26 28 29