Mortgage questions solved
with PMT, CUMIPMT, CUMPRINC and FV.
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 for the first five years of the loan?
What are total principal
reductions for the two loans over five years?
What is the loan balance
after five years?
Financial Analysis
The monthly payments, fiveyear
interest totals, and loan balance at 60 months for the 15year and 30year
mortgages are presented below
Mortgage Calculations


Term

15

30

Initial Balance

$180,000

$180,000

Interest Rate

0.029

0.034

Monthly Payment

$1,234.41

$798.27

Five Years of Interest Payments

$22,515

$29,072

Principal Payments over First Five Years of the Loan  Method One

$51,549.38

$18,824.12

Principal Payments over First Five Years of the Loan  Method Two

$51,549.38

$18,824.12

Loan Balance Method One

$128,450.62

$161,175.88

Loan Balance Method Two

$128,450.62

$161,175.88

Discussion of Calculations:
The most straight forward way
to calculate the monthly mortgage payment in Excel is through the payment
function, PMT. Remember to convert the
annual interest rate to a monthly interest rate and the number of periods from
years to months.
The most straight forward way
to calculate the interest over the first five years of the loan is through the cumulative
interest function CUMIPMT. Remember to
convert all annual figures from years to months, set start period to 1 for
first month, end period to 60 for 60^{th} month and type to 0 for
interest received at end of period.
There are two ways to obtain
principal payments over the first five years.
Method one involves
multiplying monthly payments by number of months (60) and subtracting
cumulative interest paid.
Method two involves the direct
use of the cumulative principal function, CUMPRINC.
There are two ways to obtain
the loan balance.
Method one involves subtracting
cumulative principal from the initial loan balance.
Method two involves the use
of the FV function.
The formulas for these
calculations are laid out in the table below.
Formulas for Calculations


Term (years)

cell c3

Initial loan balance

Cell c4

Interest rate (years)

cell 5

Monthly PMT

=PMT(C5/12,C3*12,C4)

Five years Interest

=CUMIPMT(C5/12,C3*12,C4,1,60,0)

Principle reduction
method 1

=60*C6+C7

Principle reduction method
2

=CUMPRINC(C5/12,C3*12,C4,1,60,0)

Loan Balance Method 1

=C4C9

Loan Balance Method 2

=FV(C5/12,60,C6,C4,0)

The results for the thirtyyear
mortgage can be obtained by inserting input into cells d3, d4 and d5 and then
copying the formulas to column d.
Authors Note: I hope these hints on financial functions
and spreadsheet design are useful to you.
I am creating a series of financial hint articles. The question list
currently under development can be found here.
Excel Finance Function
Problem List:
No comments:
Post a Comment