Friday, August 25, 2017

Excel functions (PMT, CUMIPMT, CUMPRINC, and FV) on mortgages

Mortgage questions solved with PMT, CUMIPMT, CUMPRINC and FV.


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 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, five-year interest totals, and loan balance at 60 months for the 15-year and 30-year 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 60th 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
=C4-C9
Loan Balance Method 2
=-FV(C5/12,60,-C6,C4,0)


The results for the thirty-year 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