Question

Bo Horvat bought a property valued at $1,170,000.00 with $170,000.00 down payment and a

mortgage amortized over 25 years. He makes equal payments due at the end of every month.

Interest on the mortgage is 4.85% compounded semi-annually and the mortgage is renewable

after five years.

a) What is the size of each monthly payment?

b) What is the outstanding balance at the end of the first five-year term?

P1 =

P2 = Bal =

c) What is the total interest of the mortgage for the first five years?

P1 =

P2 = Int =

d) If the mortgage is renewed after five years at 6.50% compounded annually after the first

5 year fixed term, what will be the size of each monthly payment for the rest of the 20

years?

Answer 1

a) What is the size of each monthly payment?

Monthly Interest Rate = (1 + Semi annual Interest)^(1/6) - 1 = (1 + 2.425%)^1/6 = 0.4001%

Loan Amount = Property value - Down Payment = $1170000 - 170000 = $1000000

Monthly Payment = Loan Amount / PVAF(0.4001%, 300) = 1000000 / 174.4910

Monthly Payment = $5730.96

b) What is the outstanding balance at the end of the first five-year term?

P1 = Loan Amount - Cumulative Principal Paid till year 1

P1 = 1000000 - =CUMPRINC(0.4001%,300,1000000,1,12,0)

P1 = 1000000 - 21217.28 = $978782.72

P2 = Loan - Cumulative Principal Paid till year 1

P2 = 1000000 - =CUMPRINC(0.4001%,300,1000000,1,24,0)

P2 = 1000000 - 43476.07 = $956523.93

P3 = Loan Amount - Cumulative Principal Paid till year 1

P3 = 1000000 - =CUMPRINC(0.4001%,300,1000000,1,36,0)

P3 = 1000000 - 66827.51 = $933172.49

P4 = Loan Amount - Cumulative Principal Paid till year 1

P4 = 1000000 - =CUMPRINC(0.4001%,300,1000000,1,48,0)

P4 = 1000000 - 91325.22 = $908674.78

P5 = Loan Amount - Cumulative Principal Paid till year 1

P5 = 1000000 - =CUMPRINC(0.4001%,300,1000000,1,60,0)

P5 = 1000000 - 117025.50 = $882974.52

c) What is the total interest of the mortgage for the first five years?

P1 = =CUMIPMT(0.4001%,300,1000000,1,12,0) = $47554.18

P2 = =CUMIPMT(0.4001%,300,1000000,12,24,0) = $46512.67

P3 = =CUMIPMT(0.4001%,300,1000000,25,36,0) = $45420.03

P4 = =CUMIPMT(0.4001%,300,1000000,37,48,0) = $44273.75

P5 = =CUMIPMT(0.4001%,300,1000000,49,60,0) = $43071.20

d) If the mortgage is renewed after five years at 6.50% compounded annually after the first 5 year fixed term, what will be the size of each monthly payment for the rest of the 20 years?

Monthly Payment = Loan Amount / PVAF(0.542%, 240) = 882974.52 / 134.125

Monthly Payment = $6583.22