Question

Josh and his new wife Samantha are buying their first house together. They secured a loan for $435,000 at a rate of 9.275% per year for 30 years.

SHOW FORMULAS USED

1)How much are the monthly payments?

2)6 years later, the couple decides they want to refinance their home. How much do they currently owe on the home?

3)The bank is offering a special deal of 6.35% on all home refinances. What would their new monthly payment be?

4)The couple is saving money for their first child. Now that they have refinanced their home, how much will the couple save every month?

5)The couple has made significant upgrades to their home which is currently valued at $575,000. Based on how much they owe at the time of refinance, how much equity do they have?

Answer 1

1]

Monthly loan payment is calculated using PMT function in Excel :

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

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

pv = 435000 (loan amount)

PMT is calculated to be $3,586.52

2]

We calculate the principal paid off after 6 years (72 months) using CUMPRINC function in Excel :

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

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

pv = 435000 (original loan amount)

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

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

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

CUMPRINC is calculated to be $21,502.03

The balance loan principal outstanding after 6 years =  $435,000 - $21,502.03 = $413,497.97

3]

Monthly loan payment is calculated using PMT function in Excel :

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

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

pv = 413497.97 (loan amount)

PMT is calculated to be $2,800.64

4]

Monthly saving =  $3,586.52 - $2,800.64 = $785.88