Question

Q1. Cost of home today (Yr 2019) = $760,000

Down payment today (Yr 2019) = $120,000

Currently Chosen financing option: a 25-year mortgage/loan, with semi-monthly payments (at the end of each period). The interest rate on the mortgage is 3.26% APR (annual percentage rate) compounded semi-annually.

After 5 years (2024), what will the outstanding balance on the mortgage with the same financing "option" given above??

Hint: you only need to consider the PV of the remaining mortgage payments at the same mortgage rate.

Please use (display + name) the excel function/ formula used for each coloured cell.

Answer 1. outstanding balance on mortgage (option )
period rate
# of remaining periods
semi-monthly payment
Mortgage Balance:

Ques 8) In 2024 (5 yrs from now) we will receive $200,000 from ancestral property and would like to deposit one-time payment against our mortgage at that time, if we renew the mortgage in 2024 at the same interest rate and same time left to repayment, what will our new semi-monthly payment be? Please use (display + name) the excel function/ formula used for coloured cell.

Answer 2. New semi-monthly mortgage payments
balance
new semi-monthly payment:

Answer 1

EAR= (1+3.26%/2)^2-1=3.29%

Now, say, the semi-monthly effective interest rate is x

Then (1+x)^24-1=3.29% or x=0.135%

Loan amount (PV)=760,000-120,000=640,000, rate=0.135%, tenure(nper)=25*12*2=600, PMT=?

Now below is the amortization schedule for 5 year up to 2024:

Hence, after 5 year i.e. after 120 period the remaining balance on loan is $549742.84

Now, in a similar new loan amount PV=549742.84-200000=349742.84, rate=0.135%, nper=(25-5)*12*2=480, PMT=?

Hence, the new semi-monthly payment would be $990.14