Question

Your bank offers the following options on a $200,000 mortgage:

i) A 15-year, 4% loan with no points

ii) A 15-year, 3.5% loan, with 1.5 discount points

a) Compute the monthly payments (PMT) under each option, the interest portion of the first payment

(INT) under each option, and the cost of the points.

PMT (option i) =

PMT (option ii) =

INT (option i) =

INT (option ii) = $

Cost of points =

Assume that you plan to repay the loan in 5 years. What is the present value of the monthly savings

(difference in payments) if you take the option with the points? (Use the 3.5% interest rate to discount the

monthly savings).

show your work with formulas.

Answer 1

Loan = 200000,

Option 1 , Period = 15 year = 15 x 12 = 180 months, Rate of interest = 4% per year, Points = 0

Monthly rate = 4%/12

To find the monthly payment, we will use pmt function in excel

Formula to be used in excel: =pmt(rate,nper,-pv)

Using pmt function in excel, we get monthly payment for option 1 = 1479.38

Interest portion for first payment under option 1 = Beginning balance of loan x monthly interest rate = 200000 x (4%/12)

= 666.666 = 666.67

Option 2 Period = 15 year = 15 x 12 = 180 months, Rate of interest = 3.5% per year, Points = 1.5

Monthly rate = 3.5%/12

To find the monthly payment, we will pmt function in excel

Formula to be used in excel: =pmt(rate,nper,-pv)

Using pmt function in excel, we get the monthly payment under option 2 = 1429.77

Interest portion for first payment under option 1 = Beginning balance of loan x monthly interest rate = 200000 x (3.5%/12)

= 583.333 = 583.34

1 point = 1% of Loan Amount

Cost of points = 1.5 points = 1.5 % of Loan amount = 1.5% x 200000 = 3000

If one plans to repay loan in 5 years, Then period of loan = 5 years = 5 x 12 = 60 months

Now we will calculate the monthly payment under both the option for period of 5 years

Option 1

Formula to be used in excel: =pmt(rate,nper,-pv)

Using pmt function, we get monthly payment in option 1 for loan of 5 years = 3683.30

Option 2

Formula to be used in excel: =pmt(rate,nper,-pv)

Using pmt function, we get monthly payment in option 2 for loan of 5 years = 3638.35

Monthly savings = monthly payment under option 1 - monthly payment under option 2

= 3683.30 -3638.35 = 44.95

We will now calculate present value of monthly savings using pv function in excel

Formula to be used in excel: =pv(rate,nper,-pmt)

Using pv function in excel we get present value of monthly savings if one takes the option with points = 2470.90