Question

Dave has owned his condo for 10 years. He paid $200,000 for the condo, and put down 20%. The interest rate was 4% for a 30-year mortgage loan. What is his loan payoff amount in 2020?

Answer 1

Loan amount = 80% * 200,000 = 160,000, interest rate = 4%, term = 360 months

Monthly payment = - PMT(4%/12, 360, 160000) = $763.86

Loan amortization table can be set up in excel as below

Month	Opening balance	Interest paid	Principal repaid	Closing balance
1	160,000	533	231	159,769
2	159,769	533	231	159,538
121	126,054	420	344	125,711
132	122,210	407	356	121,854

Opening balance (from month 2) = closing balance of prior month, interest paid = interest rate x opening balance, principal repaid = monthly payment - interest paid, closing balance = opening balance - principal repaid

As seen in table above, opening balance after 10 years (month 121) = $126,054

And closing balance at the end of 11th year (month 132) = $121,854

Hence, loan repaid during the year = 126,054 - 121,854 = $4,200.657