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
Number of Payments
Loan Amount
Monthly Payment
Paid off Debt after 5 Years
Paid off Debt after 10 Years
Paid off Debt after 15 Years

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
Required Return 10-year Holding Period
Required Return 15-year Holding Period

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

No comments:

Post a Comment