## 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:

#### 1 comment:

1. Knowledgeable information you have shared here about Mortgage questions. If you are looking for best Contractor Mortgage Broker Uk,then visit cismortgage.