Mortgage refinancing Six years ago, Ayra purchased a brand-new condominium in Edmonton for $500,000. Ayra had been saving for her first home purchase ever since she graduated from UTSC, and she was able to make the 20% required down payment to avoid mortgage loan insurance. At that time, the interest rate on a 10-year fixed rate mortgage loan was 7.120% and the rate on a 25-year fixed rate mortgage loan was 9.240%. Ayra decided to take on a 25-year fixed rate mortgage loan. Please remember interest rate on mortgage loan is always compounded semiannually. Today, Ayra works at Bank of Edmonton (BOE) as a marketing manager. A couple of weeks ago during lunch, she was having a conversation with some of her colleagues at the mortgage department. Mortgage loan rates had fallen, and she was advised to see whether she could refinance her mortgage loan to save some money. Normally, the penalty for refinancing a mortgage loan equals the amount of interest paid over the past three months. However, as a BOE employee, Ayra does not have to pay the penalty. Following are the fixed mortgage loan rates BOE is offering: Term Rate 6 months 4.0% year 3.14 2 year 3.14 3 year 3.95 4 year 4.74 5 year 5.34 7 year 5.95 10 year 6.75 25 year 8.75 1. Ayra's Monthly Mortgage Payment (Before Refinancing) Formula used: 1 v T L B [((ER AL In Excel, you can use the PMT function. & Principal (P): $400,000 Annual interest rate (semi-annually compounded): 9.240%/2 = 4.620% for each compounding period. Monthly rate (r): 0.007621 Number of periods (n): 300 months Formula in Excel: =PMT(9.007621, 300, -400000) This will give you the monthly payment. Format it to 2 decimal places. 2. Total Paid Over Six Years To calculate total paid and interest paid: * Months (n): 72 (6 years) * Monthly payment (calculated in step 1): $3,375.15 Total paid: =3,375.15 * 72 To calculate interest paid over the 6 years, you can use IPMT and PPMT: * IPMT gives you the interest portion of a payment for a given period. * PPMT gives you the principal portion. Interest paid: =SUM(IPMT(0.007621, ROW(1:72), 300, -400000)) Remaining balance after six years: =CUMPRINC(.007621, 300, 400000, 1, 72, @) \f4. Loan Balance with Increased Payment If Ayra increased her monthly payment by $100, calculate the remaining balance after 72 months. New payment: =PMT(0.007621, 300, -100000) + 100 Remaining balance after 72 months using CUMPRINC: =CUMPRINC(0.007621, 308, 400000, 1, 72, ) 5. Number of Years to Pay Off the Loan Use the NPER function to calculate how long it will take to pay off the loan with the new payments. s Rate: 0.007621 (monthly interest rate) e Payment: $3,475.15 (new monthly payment) * Present value: -356,886.14 (remaining balance) Formula: =NPER(@.007621, -3475.15, 356886.14) \f