You bought a house for $500,000 with a 10% down. The mortgage is 30 year and...

On the original 30-year loan, monthly payment is calculated using PMT function in Excel :

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

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

pv = 450000 (loan amount = house cost * (1 - down payment %) = $500,000 * (1 - 10%) = $450,000).

PMT is calculated to be $4,628.76.

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

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

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

pv = 450000 (loan amount)

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

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

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

CUMPRINC is calculated to be $29,619.02.

The balance loan principal outstanding after 10 years =  $450,000 - $29,619.02 = $420,380.98.

On the refinanced loan, monthly payment is calculated using PMT function in Excel :

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

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

pv = 420380.98 (balance loan principal outstanding after 10 years).

PMT is calculated to be $3,011.74.

Saving in monthly payment = $4,628.76 - $3,011.74

Saving in monthly payment = $1,617.02

A5 fx =PMT(12%/12,30*12,450000) D EE F B C 5 ($4,628.76!

A6 f =CUMPRINC(12%/12,30*12,450000,1,120,0) D E F G B C 6 $ (29,619.02)

A8 for EPMT(6%/12,20*12,420380.98) D E F B C 8 ($3,011.74)!