Tuesday, November 17, 2015

Question One: Mortgage Choice

When I wrote this post quite awhile ago the 30-year FRM was 3.4 and the 15-year FRM was 2.9

The gap between the 30-year FRM and 15-year FRM is even larger today.  Today the rates are 3.6 for the 30-year FRM and 2.7 for the 15-year FRM.

Homebuyers today should definitely consider the 15-year FRM.   The first step is to redo the calculations below with current rates.


Question One:  A person is considering taking out a $180,000 mortgage and must choose between a 15-year FRM and a 30-year FRM.   The interest rate on the 15-year mortgage is 2.90 while the interest rate on the 30-year mortgage is 3.40.  

What are the monthly payments on the two loans?

What are the total interest payments on the two loans over the life of the loan?

What is the after-tax cost of the interest payments on the two loans?

What is the tax savings from the tax deductibility of mortgage interest?

What is remaining loan balance after 15 years for the two loans?




Answer:  The monthly mortgage payment calculation is directly calculated from the PMT function in Excel.   The variables inputted into the PMT function are the interest rate, the term and the loan balance.

The lifetime interest cost is calculated two ways.   The first way involves noting that the difference between total payments and the repaid loan balance is equal to interest payments.  (180* $1234-$180,000) =$42,193.

The second way involves calculating cumulative interest payments directly from the CUMIPMT function in Excel.  Put Rate=0.029/12, NPER=180, PV=$180,000 STARTPERIOD=1, ENDPERIOD=180, and Type=0 into CUMIPMT and get $42,193.)

The after tax cost of interest payments is (1-MTR) x INTEREST.

The tax savings from interest payments is MTR x INTEREST

The mortgage balance after 15 years is obtained directly from the FV function in Excel. Note FV (RATE=0.029/12,NPER=180, PMT=-1234,PV-180000) is equal to $0.  This is a good way to check your work since the balance on a 15-year mortgage held for 15 years must be $0.

The complete answers are laid out in the table below.





A Comparison of 15-year and 30-year FRM
15-year FRM
30-year FRM
Notes
Rate
0.029
0.034
Assumption
Period
180
360
Assumption
Loan
$180,000
$180,000
Assumption
Payment
-$1,234
-$798
Calculation From Payment Function
Interest Cost Calculation One
$42,193
$107,376
Calculation: Total Payments - Loan Balance
Interest Cost Calculation Two
$42,193
$107,376
Calculation From CUMIPT Function
Marginal Tax Rate
0.3
0.3
Assumption
After Tax Interest Cost
$29,535
$75,163
Calculation: (1-mtr)*Interest Cost
Tax Savings from Mortgage Deductibility
$12,658.05
$32,212.75
Tax Savings from  Mortgage Deduction
Mortgage Balance After Fifteen Years
$0
-$112,435
Calculation:  From FV Function
Total Mortgage Payments Over 15 Years
-$222,193
-$143,688
Calculation 180*MONTHLY MORTGAGE PAYMENT



Discussion of Comparison of 15-year and 30-Year FRM:

·      Over a 15-year period the homeowner with the 30-year FRM has accumulated $112,435 less house equity than the homeowner with the 15-year FRM.

·      Over the 15-year period, the homeowner with the 15-year mortgage has paid over $78,000 more in mortgage payments than the homeowner with the 30-year mortgage. However, the owner with the 30-year mortgage is not done yet.

·      The additional tax savings from the use of the 30-year FRM is around $20,000.

     Other financial math problems can be found here.

No comments:

Post a Comment