Question

The Grewals agreed to monthly payments on a mortgage of $336,000.00 amortized over 20 years. Interest for the first five years was 4.5% compounded semi-annually.

a. Determine the Grewals’ monthly payments.

b. Determine the balance owing after the 5-year term.

c. Before renewing for another term of 5 years at 4.3% compounded semiannually, the Grewals make an additional payment of $12,000. If they keep the same monthly payments, by how much will the amortization period be shortened?

Answer 1

Monthly Interest rate = (1 + Semi Annual Interest)^(1/6) - 1 = (1.0225)^(1/6) - 1 = 0.3715%

a. Determine the Grewals’ monthly payments.

monthly payments = Loan Amount / PVAF(0.3715%,240)

monthly payments = 336000 / 158.6281

monthly payments = $2118.61

b. Determine the balance owing after the 5-year term.

Balance after 5 years = Loan Amount - "=CUMPRINC(0.3715%,240,336000,1,60,0)"

Balance after 5 years = 336000 - 58342.39

Balance after 5 years = 277657.61

c. Before renewing for another term of 5 years at 4.3% compounded semiannually, the Grewals make an additional payment of $12,000. If they keep the same monthly payments, by how much will the amortization period be shortened?

Balance after 12000 payment = $277657.61 - 12000 = $265657.61

Periods = "=NPER(0.36%,-2118.16,265657.61)"

Periods = 166.29

periods if the payment of 12000 is not made = 15*12 = 180 periods

Shorten in periods = 180 - 166.29 = 13.70 Periods