Question

Consider a 30-year, $170,000 mortgage with a rate of 5.70 percent. Seven years into the mortgage, rates have fallen to 5 percent. What would be the monthly saving to a homeowner from refinancing the outstanding mortgage balance at the lower rate?

Answer 1

First we calculate the monthly payment for the original loan.

Monthly loan payment is calculated using PMT function in Excel :

rate = 5.7% / 12   (converting annual rate into monthly rate)

nper = 30*12 (30 year loan with 12 monthly payments each year)

pv = 170000 (loan amount)

PMT is calculated to be $986.68

Now, we calculate the principal paid off after 7 years (84 months) using CUMPRINC function in Excel :

rate = 5.7% / 12   (converting annual rate into monthly rate)

nper = 30*12 (30 year loan with 12 monthly payments each year)

pv = 170000 (loan amount)

start period = 1 (We are calculating principal paid off between 1st and 84th month)

end period = 84 (We are calculating principal paid off between 1st and 84th month)

type = 0 (each payment is made at the end of month)

CUMPRINC is calculated to be $18,443.41

The balance loan principal outstanding after 7 years =  $170,000 - $18,443.41 = $151,556.59

Monthly loan payment after refinancing at lower rate is calculated using PMT function in Excel :

rate = 5% / 12   (converting annual rate into monthly rate)

nper = 23*12 (30 year loan with 12 monthly payments each year)

pv = 151556.59 (balance loan amount outstanding after 7 years)

PMT is calculated to be $925.11

Monthly saving to homeowner =  $986.68 - $925.11

Monthly saving to homeowner =  $61.57