Thursday, August 24, 2017

The IPMT function

The list of problems solved with Excel Finance functions can be found at the following page:
http://www.dailymathproblem.com/p/excel-finance-functions.html

This post looks at a problem solved with the IPMT function:

Note that this problem could be also solved with the CUMIPMT function.

Consider two people one with good credit and one with bad credit.   Each person has a car loan, a 30-year FRM, and a personal student loan.   The date of origination of each loan, the term of each loan, and the interest rate on each loan for the two people are presented in the table below.  What was the interest paid on these loans for the two people in 2014?

 Loan information for Two People Bad Credit Rate Good Credit Rate Term of Loan in Years Loan Origination Date Initial Loan Balance Car Loan 0.11 0.04 6 1/1/13 \$15,000 Personal Student Loan   Rate 0.12 0.06 20 1/1/10 \$45,000 Mortgage Rate 0.055 0.0325 30 6/1/13 \$320,000

Answer:   I will use the IPMT function to get the monthly loan information for each loan and each month in 2014.     The input variables for the IPMT function are 1) RATE (monthly interest rate), 2) PER (age of the loan in each month), 3) NPER (the term of the loan), and 4) PV (the loan balance.)

The monthly interest rate is simply the annual rate divided by 12.

The term of each loan and the initial loan balance for each loan is given in the chart.

The age of the loan on January 1 2014 and on subsequent dates can be obtained by counting from the loan origination date.   January 2014 is the 13th month of the car loan, the 49th month for the personal student loan, and the 7th month for the mortgage.

The three charts below give monthly and 2014 total interest payment calculations from the car loan, the personal student loan, and the mortgage for the person with bad and good credit.

·      The person with bad credit pays a total of \$22,462 in interest in 2014, (\$1,342 on the car loan, \$3,748 for the student loan, and \$17,373 on the mortgage.)

·      The person with good credit pays a total of \$12,358 in interest in 2014 (\$467 on the car loan, \$1,691 on the student loan, and \$10,200 on the mortgage.)
 Car Loan Assumptions Bad Credit Good Credit Rate 0.11 0.04 Term 72 72 Loan Balance \$15,000 \$15,000 Car Loan 2014 Interest Payments 13 -\$120.37 -\$42.48 14 -\$118.86 -\$41.84 15 -\$117.33 -\$41.19 16 -\$115.79 -\$40.55 17 -\$114.23 -\$39.90 18 -\$112.66 -\$39.25 19 -\$111.08 -\$38.60 20 -\$109.48 -\$37.95 21 -\$107.87 -\$37.29 22 -\$106.24 -\$36.63 23 -\$104.59 -\$35.97 24 -\$102.94 -\$35.31 Total -\$1,341.44 -\$466.95

 Personal Student Loan Assumptions Bad Credit Good Credit Rate 0.12 0.06 Term 120 120 Loan Balance \$45,000 \$45,000 Personal Student Loan Interest Payments 2014 49 -\$330.24 -\$150.73 50 -\$327.08 -\$148.98 51 -\$323.90 -\$147.23 52 -\$320.68 -\$145.47 53 -\$317.43 -\$143.70 54 -\$314.15 -\$141.92 55 -\$310.83 -\$140.13 56 -\$307.49 -\$138.33 57 -\$304.11 -\$136.52 58 -\$300.69 -\$134.71 59 -\$297.24 -\$132.88 60 -\$293.76 -\$131.05 Total -\$3,747.59 -\$1,691.64

 Mortgage Rate Assumptions Bad Credit Good Credit Rate 0.055 0.0325 Term 360 360 Loan Balance \$320,000 \$320,000 Mortgage Interest Payments in 2014 7 -\$1,456.92 -\$858.06 8 -\$1,455.27 -\$856.61 9 -\$1,453.62 -\$855.16 10 -\$1,451.95 -\$853.71 11 -\$1,450.28 -\$852.25 12 -\$1,448.60 -\$850.78 13 -\$1,446.91 -\$849.31 14 -\$1,445.21 -\$847.84 15 -\$1,443.51 -\$846.37 16 -\$1,441.80 -\$844.89 17 -\$1,440.08 -\$843.40 18 -\$1,438.35 -\$841.92 Total -\$17,372.50 -\$10,200.31