A mortgage for $125 000 is amortized over twenty-five years with an interest rate of 5.25% compounded semiannually. 1. Find the monthly payments by rounding up and by rounding down. 2. Use the rounded-up value for the payments and an excel spreadsheet to create an amortization table. What is the value of the final payment? 3. Use the rounded-down value for the payments to create another amortization table. What is the value of the final payment? When the final payment for year four is made, an additional $10 000 is paid as well. The bank has a policy of charging a penalty of three months simple interest (5.25% pa on the $10 000) to make up for its lost revenue. The penalty is taken off the $10 000. 4. Use the rounded-up value for the payments and an excel spreadsheet to create an amortization table which takes into account the extra nct payment at the end of year four. (Regular payments will be continued until the end of the amortization period). 5. When will the final payment be made? 6. What is the value of the final payment? These are individual case studies with different numbers. The formulas to be used will be discussed in class in week 10. The completed case study will be due at the beginning of class in week 14. You may work together in discussing the problems but each submission must be your own work. Any files that are the same will receive a mark of 0. NOTES FOR CASE STUDY Payment Interest Paid Principal Repaid Outstanding Balance E2 B3 C3 D3 E3 Value of Mortgage Value of Payment - either 'rounded" up or down to the closest cent = E2 * ((1 + i)^(1/6) - 1) = B3 - C3 = E2 - D3 An "eco" bonus of 1 mark will be given if all three spreadsheets are printed on one page by hiding rows