Sunday, May 5, 2013

Empirical guidance for the mortgage term decision

Question:  A homebuyer is considering taking out a \$200,000 loan and is mulling two options.  The first option is a 30-year FRM at 3.50%. The second option is a 15-year FRM at 2.7%.  The 15-year FRM comes with a larger monthly payment.  Some homebuyers might choose the 30-year FRM and invest the difference in mortgage payments.  What rate of return on financial assets would make the 30-year FRM the preferred choice?

Answer:   The first step is to calculate the monthly mortgage payment for both loans.  The monthly mortgage payment is obtained from the PMT function in Excel.

For the thirty-year mortgage we get

\$898.09=PMT(0.035/12, 360,\$200,00)

For the 15-year mortgage we get

\$1,353.49=PMT(0.027/12, 180, \$200,000)

The difference in the 15-year versus 30-year mortgage payment is \$454.40.

The homebuyer must decide whether to borrow with a 15-year FRM and take the lower interest rate or borrow with the 30-year FRM and invest the additional \$454.40 per month.

The relative advantage of the 15-year FRM over the 30-year FRM depends upon how long the homebuyer plans to own the property.   We solve the problem for holding periods of 5, 10, and 15 years.

The amount of debt paid off at the Nth year is \$200,000 minus the future value of the loan.  The future value of the loan is calculated from the FV function in Excel where FV is a function of interest rate  (0.035/12 or 0.27/12), the number of payments (60, 120, or 180), the payment (\$898.09 or \$1352.49) and the loan balance (\$200,000).

 30-year FRM 15-year FRM Difference Interest Rate 0.035 0.027 -0.008 Number of Payments 360 180 -180 Loan Amount 200000 200000 0 Monthly Payment (\$898.09) (\$1,352.49) (\$454.40) Paid off Debt after 5 Years \$20,605.85 \$57,905.05 \$37,299.19 Paid off Debt after 10 Years \$45,146.25 \$124,169.45 \$79,023.20 Paid off Debt after 15 Years \$74,372.46 \$200,000.00 \$125,627.54

In order for the homeowner to be better off with the thirty-year loan she must earn more than the paid off debt amount by investing \$454.40 per month over the holding period.  This rate of return can be calculated with the RATE function in Excel.  Inputs for RATE are the number of payments, the monthly mortgage payment, the initial value of the annuity, and the future value of the annuity.

Using values from the five-year holding period you can confirm that

12.2%=12 x RATE(60,\$454.40,0,\$37,299.19)

We can confirm that this answer is correct by placing 12.2%/12 into the future value function along with other inputs N=60 and PMT=\$454.40 to get final value \$37,299.19.

 Rate on annuity Final Value of annuity Required Return 5-year Holding Period 12.2% \$37,299.15 Required Return 10-year Holding Period 7.1% \$79,023.12 Required Return 15-year Holding Period 5.4% \$125,627.41

At this time, the advantages of a 15-year FRM over the 30-year FRM for those who can qualify are compelling.  This issue was also discussed at Finance Memos