This problem shows how to use the IPMT function to
calculate interest payments for three loans for a person with good credit and
for a person with poor credit.
Question
One: Consider
two people one with good credit and one with bad credit. Each
person has a car loan, a 30year 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?
Bad Credit Rate

Good Credit Rate

Loan Term Years

Loan Origination Date

Initial Loan Balance


Car Loan

0.11

0.04

6

1/1/13

$15,000

Student Loan

0.12

0.06

20

1/1/10

$45,000

Mortgage

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 13^{th} month of the car loan, the 49^{th} month
for the personal student loan, and the 7^{th} 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

Note that this problem could be also solved with the CUMIPMT
function.
No comments:
Post a Comment