Question

1. If you just bought a house for $400,000 (including on closing costs) on Signal Mountain by getting a fixed 30-year mortgage at a 5% interest rate, what would be your monthly payment?

2. How much less money would you pay if you refinanced the loan in the previous problem after 15 years for a new 15-year loan at 3.5% interest rate? Be sure to include a $2,000 refinancing cost to the total remaining balance on the new loan

Answer 1

Solution 1) Loan amount = $400,000

Number of years = 30

Since the payments are made on a monthly basis, thus, the number of periods (Nper) = 30*12 = 360

Interest rate = 5%

Since the payments are made on a monthly basis, thus, Rate = 5%/12

Future value of Loan = 0

The monthly installment (PMT) can be calculated using the PMT function in the Excel

=PMT(Rate, Nper, PV, FV, Type)

= PMT(5%/12, 360,400000,0,0)

= -2,147.29

= $2,147.29

Solution 2) The principal repaid after the 15 years can be calculated using the CUMPRINC function in the Excel

= CUMPRINC(Rate, Nper, PV, start_period, end_period, type)

= CUMPRINC(5%/12, 360, 400000, 1, 180, 0)

= -128464.365

= $128,464.37

Loan outstanding at the end of 15 years = 400,000 - 128,464.37 = $271,535.63

Refinancing Charges = $2000

Total new loan refinanced = 271,535.63 + 2000 = 273,535.63

Rate = 3.5%/12

Nper = 15*12 = 180

The monthly installment (PMT) can be calculated using the PMT function in the Excel

=PMT(Rate, Nper, PV, FV, Type)

= PMT(3.5%/12, 180, 273535.63, 0, 0)

= -1955.46

= $1,955.46

The monthly installment is lowered by = 2,147.29 - 1,955.46 = $191.83

Initially total amount to be paid for 15 years = 2,147.29*180 = 386,512.20

New amount to be paid for 15 years = 1,955.46*180 = 351,982.52

Savings in the total amount paid = 386,512.20 - 351,982.52 = $34,529.68

Please comment in case of any doubt or clarification required. Please Thumbs Up!!