Question

A borrower has two options for a home mortgage. 1. Option A is a 15-year fixed rate mortgage with a 3.9% interest rate, convertible monthly. 2. Option B is a 30-year fixed rate mortgage with a 4.7% interest rate, convertible monthly. Assuming the borrower wishes to borrow $200,000, how much more interest will be paid under option B than under option A?

Answer 1

Under Option B $165,000 will be paid more as interest than Option A.

SOLUTION

	Option A	Option B
Interest Rate	0.33%	0.39%
N	180	360
PV	200,000	200,000
FV	0	0
PMT (Fixed Payment)	($1,469.37)	($1,037.28)
IPMT (Interest Part)	($650.00)	($783.33)
Total Interest Paid (Interest Part of Fixed Payment * No. of Periods)	($117,000.00)	($282,000.00)

Difference in Interest Paid = 282,000 - 117,000 = 165,000

STEPS

1. We calculate the monthly interest rate of both the bonds. Rate for A = 3.9%/12 =.33% Rate for B = 4.7%/12 = .39%

2. We calculate the time period for both bonds in monthly periods. N for A = 15*12 = 180 and N for B = 30*12 = 360

3. The present value for both bonds will be 200,000 with future value being 0 since both the bonds will be paid throughout the respective time periods.

4. We write down the time periods in an excel sheet. For Bond A we write values between 1 to 180 and for Bond B we will write values between 1 to 360.

5. Next step is to calculate the PMT. This tells the value of each fixed payment. Use PMT function and put N , Int, PV and FV values from steps 1,2 and 3. The time period will be at end.

6. Next Step is to calculate the IPMT. This function in excel tells us about the interest part of the fixed payment for each time period. Use IPMT function and put N , Int, PV and FV values from steps 1,2 and 3. The time period will be at end. An additional requirement in IPMT is that it will require us to fill in the period. We can put 1 here since interest payments will be fixed here for each period.

7. The next part is to calculate the interest paid. This can be computed as Interest Part of Fixed Payment * Number of Periods.

8. In the end, we find the difference between interest paid under Option A and Option B.