This post
shows how to use an Excel spreadsheet to evaluate whether to borrow with a
15year or 30year fixed rate mortgage.
Question
One: A person is considering taking out a $180,000 mortgage and
must choose between a 15year FRM and a 30year FRM. The interest
rate on the 15year mortgage is 2.90 while the interest rate on the 30year
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 aftertax 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 (1MTR) 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,PV180000) is equal to $0.
This is a good way to check your work since the balance on a 15year mortgage
held for 15 years must be $0.
The
complete answers are laid out in the table below.
A
Comparison of 15year and 30year FRM


15year
FRM

30year
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:
(1mtr)*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 15year and 30Year FRM:
 Over a
15year period the homeowner with the 30year FRM has accumulated $112,435
less house equity than the homeowner with the 15year FRM.
 Over
the 15year period, the homeowner with the 15year mortgage has paid over
$78,000 more in mortgage payments than the homeowner with the 30year
mortgage. However, the owner with the 30year mortgage is not done yet.
 The
additional tax savings from the use of the 30year FRM is around $20,000.
No comments:
Post a Comment