Question

You have just negotiated a 3-year mortgage on 500,000 amortized over 25 years at rate fo 6%. Assuming after three years the mortgage rate changes to 5%, what will your new monthly payments? (Hint: Canadian banks quote mortgage rates as a rate per year compounded semi-annually.)

Answer 1

First we will calculate monthly payments for first three years

Initial rate = 6% per year compounded semi-annually

Semi-annual rate = per year rate compounded semi-annually / 2 = 6%/2 = 3%

Effective annual interest rate = (1+ semi-annual rate)² -1 = (1+ 3%)² -1 = 1.03² -1 = 1.060900 -1 = 0.060900 = 6.09%

We know than, (1 + monthly rate)¹² - 1 = Effective annual interest rate

(1 + monthly rate)¹² - 1 = 6.09%

(1 + monthly rate)¹² = 1 + 0.0609

1 + monthly rate = (1.0609)^1/12

1 + monthly rate = 1.004938

monthly rate = 0.004938 = 0.4938%

Loan amount = 500000, Amortization period = 25 years = 25 x 12 months = 300 months, monthly rate = 0.4938%

We will use pmt function in excel to calculate monthly payment for first 3 years

Formula to be used in excel: =pmt(rate,nper,-pv)

Below is the excel screen shot

Using pmt function in excel we get monthly payment for first three years = 3198.81

Now we will calculate loan principal balance at the end of three years by building the amortization schedule for first 3 years

We know that, interest for a month = beginning balance x monthly interest rate

Principal for a month = monthly payment - interest for a month

Ending balance of a month = beginning balance - principal for the month

Beginning balance of month = Ending balance for previous month

For example in first month

Beginning balance = 500000, interest = 500000 x 0.4938% =2469.00, principal = 3198.81 - 2469 = 729.81, Ending balance = 500000 - 729.81 = 499270.19

So beginning balance for 2nd month = 499270.19. Similarly we can calculate figures for other months. Using the formulae we get following amortization schedule for first 3 years (36 months)

Month	Beginning Balance	Monthly Payment	Interest	Principal	Ending Balance
1	500000.00	3198.81	2469.00	729.81	499270.19
2	499270.19	3198.81	2465.40	733.41	498536.78
3	498536.78	3198.81	2461.77	737.04	497799.74
4	497799.74	3198.81	2458.14	740.67	497059.07
5	497059.07	3198.81	2454.48	744.33	496314.73
6	496314.73	3198.81	2450.80	748.01	495566.73
7	495566.73	3198.81	2447.11	751.70	494815.02
8	494815.02	3198.81	2443.40	755.41	494059.61
9	494059.61	3198.81	2439.67	759.14	493300.47
10	493300.47	3198.81	2435.92	762.89	492537.57
11	492537.57	3198.81	2432.15	766.66	491770.92
12	491770.92	3198.81	2428.36	770.45	491000.47
13	491000.47	3198.81	2424.56	774.25	490226.22
14	490226.22	3198.81	2420.74	778.07	489448.15
15	489448.15	3198.81	2416.89	781.92	488666.23
16	488666.23	3198.81	2413.03	785.78	487880.46
17	487880.46	3198.81	2409.15	789.66	487090.80
18	487090.80	3198.81	2405.25	793.56	486297.24
19	486297.24	3198.81	2401.34	797.47	485499.77
20	485499.77	3198.81	2397.40	801.41	484698.36
21	484698.36	3198.81	2393.44	805.37	483892.99
22	483892.99	3198.81	2389.46	809.35	483083.64
23	483083.64	3198.81	2385.47	813.34	482270.30
24	482270.30	3198.81	2381.45	817.36	481452.94
25	481452.94	3198.81	2377.41	821.40	480631.54
26	480631.54	3198.81	2373.36	825.45	479806.09
27	479806.09	3198.81	2369.28	829.53	478976.57
28	478976.57	3198.81	2365.19	833.62	478142.94
29	478142.94	3198.81	2361.07	837.74	477305.20
30	477305.20	3198.81	2356.93	841.88	476463.32
31	476463.32	3198.81	2352.78	846.03	475617.29
32	475617.29	3198.81	2348.60	850.21	474767.08
33	474767.08	3198.81	2344.40	854.41	473912.67
34	473912.67	3198.81	2340.18	858.63	473054.04
35	473054.04	3198.81	2335.94	862.87	472191.17
36	472191.17	3198.81	2331.68	867.13	471324.04

After 3 year rate changes 5% per year compounded semi annually

Semi-annual rate = per year rate compounded semi-annually / 2 = 5%/2 = 2.5%

Effective annual interest rate = (1+ semi-annual rate)² -1 = (1+ 2.5%)² -1 = 1.025² -1 = 1.050625 -1 = 0.050625 = 5.0625%

We know than, (1 + monthly rate)¹² - 1 = Effective annual interest rate

(1 + monthly rate)¹² - 1 = 5.0625%

(1 + monthly rate)¹² = 1 + 0.050625

1 + monthly rate = (1.050625)^1/12

1 + monthly rate = 1.004123

monthly rate = 0.004123 = 0.4123%

For calculating the new monthly payment

Loan = Ending balance at the end of 3 years = 471324.04 , monthly rate = 0.4123%, period remaining = 22 years = 22 x 12 months= 264 months

We will use pmt function in excel to calculate monthly payment

Formula to be used in excel: = pmt(rate,nper,-pv)

Using pmt function in excel, we get new monthly payment = 2933.17