Monday, September 11, 2017

Credit Card Repayment Strategies Solved in Excel

Credit Card Repayment Strategies Solved with Excel Financial Functions

Question Number 13 in the Excel Financial Function tutorial introduces two new Excel functions the NPER function and the PPMT function.

The Excel Financial Function Tutorial can be found here:

Question Thirteen:   Consider a person with $15,000 in credit card debt at a 12 percent interest rate.   A person is considering two repayment strategies on the credit card. 

Strategy one involves pay $450 per month 3 percent of $15,000 or $450 until the card is repaid.

Strategy two involves paying 3 percent of the initial monthly balance each month where the initial monthly balance in the current month is equal to the previous month initial balance minus the principal reduction from the payment in the previous month. 

Assume that payments are made at the end of period.

Describe the difference in the trajectory of the loan balance for these two repayment strategies.  

Analysis:

Strategy One:   Credit card reduction when payments remain fixed at 3 percent of the initial balance of the loan.

 First let us calculate number of months for loan repayment and outstanding loan balance for strategy one.


Repayment Period
Strategy One
Rate
0.01
PMT
450
PV
-15000
FV
0
type
0
NPER
40.7


The repayment period is calculated directly from the NPER function.

NPER(0.12/12,450,-15000,0,0) = 40.7 months.

The NPER function tells us that the loan will be totally repaid on the 41st payment.

Now let us look at the trajectory of the loan balance.

The outstanding balance at the end of each month is the initial monthly balance minus the principal reduction in the month.  The syntax of the PPMT function is

 PPMT( rate, per, nper, pv, [fv], [type] )

The NPER and PV of the loan decrease over time.   The interest rate is 0.01 (0.12/12).  PER is equal to 1 for the current period.  

PPMT is subtracted from the initial monthly loan balance to get the ending monthly loan balance, which is the initial balance for the subsequent month.

The table describing the trajectory of the outstanding balance and number of months until the loan is repaid is presented below.


Credit Card Reduction Results Strategy One:

Period
NPER
PMT
PV
Principal Paid Per Period
Principal at End of Period
1
40.7
450
-$15,000
$300
-$14,700
2
39.7
450
-$14,700
$303
-$14,397
3
38.7
450
-$14,397
$306
-$14,091
4
37.7
450
-$14,091
$309
-$13,782
5
36.7
450
-$13,782
$312
-$13,470
6
35.7
450
-$13,470
$315
-$13,154
7
34.7
450
-$13,154
$318
-$12,836
8
33.7
450
-$12,836
$322
-$12,514
9
32.7
450
-$12,514
$325
-$12,189
10
31.7
450
-$12,189
$328
-$11,861
11
30.7
450
-$11,861
$331
-$11,530
12
29.7
450
-$11,530
$335
-$11,195
13
28.7
450
-$11,195
$338
-$10,857
14
27.7
450
-$10,857
$341
-$10,516
15
26.7
450
-$10,516
$345
-$10,171
16
25.7
450
-$10,171
$348
-$9,823
17
24.7
450
-$9,823
$352
-$9,471
18
23.7
450
-$9,471
$355
-$9,116
19
22.7
450
-$9,116
$359
-$8,757
20
21.7
450
-$8,757
$362
-$8,394
21
20.7
450
-$8,394
$366
-$8,028
22
19.7
450
-$8,028
$370
-$7,659
23
18.7
450
-$7,659
$373
-$7,285
24
17.7
450
-$7,285
$377
-$6,908
25
16.7
450
-$6,908
$381
-$6,527
26
15.7
450
-$6,527
$385
-$6,142
27
14.7
450
-$6,142
$389
-$5,754
28
13.7
450
-$5,754
$392
-$5,361
29
12.7
450
-$5,361
$396
-$4,965
30
11.7
450
-$4,965
$400
-$4,565
31
10.7
450
-$4,565
$404
-$4,160
32
9.7
450
-$4,160
$408
-$3,752
33
8.7
450
-$3,752
$412
-$3,339
34
7.7
450
-$3,339
$417
-$2,923
35
6.7
450
-$2,923
$421
-$2,502
36
5.7
450
-$2,502
$425
-$2,077
37
4.7
450
-$2,077
$429
-$1,648
38
3.7
450
-$1,648
$434
-$1,214
39
2.7
450
-$1,214
$438
-$776
40
1.7
450
-$776
$442
-$334
41
0.7
450
-$334
$447
$113
42
-0.3
450
$113
#NUM!
#NUM!


Strategy Two:   Credit card reduction when monthly payments are equal to 3 percent of the initial balance each month.

Under the second strategy the decline in the monthly balance reduces the monthly payment each month.   How does the reduced monthly payment impact the trajectory of the number of months needed to repay the loan?

The loan balance after the first monthly payment is reduced to $14,700.   The monthly payment for the second month is $441.00 (($14,700 x 0.03).   The annual interest rate remains 12 percent.       The NPER calculation is presented below.


Repayment Period for Second
 Month Strategy Two
Rate
0.01
PMT
$441.00
PV
-$147,00
FV
0
type
0
NPER
40.7

The number of remaining payments before elimination of the loan balance remains unchanged at 40.7 months.

The loan balance is never reduced to $0 under strategy two.

Below is the trajectory for the reduction in the loan balance under strategy two.

Credit Card Reduction Results Strategy Two:

Period
NPER
PMT
PV
Principal Paid Per Period
Principal at End of Period
1
40.7
450
-$15,000
$300
-$14,700
2
40.7
$441.00
-$14,700
$294
-$14,406
3
40.7
$432.18
-$14,406
$288
-$14,118
4
40.7
$423.54
-$14,118
$282
-$13,836
5
40.7
$415.07
-$13,836
$277
-$13,559
6
40.7
$406.76
-$13,559
$271
-$13,288
7
40.7
$398.63
-$13,288
$266
-$13,022
8
40.7
$390.66
-$13,022
$260
-$12,761
9
40.7
$382.84
-$12,761
$255
-$12,506
10
40.7
$375.19
-$12,506
$250
-$12,256
11
40.7
$367.68
-$12,256
$245
-$12,011
12
40.7
$360.33
-$12,011
$240
-$11,771
13
40.7
$353.12
-$11,771
$235
-$11,535
14
40.7
$346.06
-$11,535
$231
-$11,305
15
40.7
$339.14
-$11,305
$226
-$11,079
16
40.7
$332.36
-$11,079
$222
-$10,857
17
40.7
$325.71
-$10,857
$217
-$10,640
18
40.7
$319.19
-$10,640
$213
-$10,427
19
40.7
$312.81
-$10,427
$209
-$10,218
20
40.7
$306.55
-$10,218
$204
-$10,014
21
40.7
$300.42
-$10,014
$200
-$9,814
22
40.7
$294.42
-$9,814
$196
-$9,618
23
40.7
$288.53
-$9,618
$192
-$9,425
24
40.7
$282.76
-$9,425
$189
-$9,237
25
40.7
$277.10
-$9,237
$185
-$9,052
26
40.7
$271.56
-$9,052
$181
-$8,871
27
40.7
$266.13
-$8,871
$177
-$8,694
28
40.7
$260.81
-$8,694
$174
-$8,520
29
40.7
$255.59
-$8,520
$170
-$8,349
30
40.7
$250.48
-$8,349
$167
-$8,182
31
40.7
$245.47
-$8,182
$164
-$8,019
32
40.7
$240.56
-$8,019
$160
-$7,858
33
40.7
$235.75
-$7,858
$157
-$7,701
34
40.7
$231.03
-$7,701
$154
-$7,547
35
40.7
$226.41
-$7,547
$151
-$7,396
36
40.7
$221.88
-$7,396
$148
-$7,248
37
40.7
$217.45
-$7,248
$145
-$7,103
38
40.7
$213.10
-$7,103
$142
-$6,961
39
40.7
$208.84
-$6,961
$139
-$6,822
40
40.7
$204.66
-$6,822
$136
-$6,686
41
40.7
$200.57
-$6,686
$134
-$6,552
42
40.7
$196.55
-$6,552
$131
-$6,421


Under strategy two the loan balance has fallen to $6,421 after 42 months and the monthly payment is under $200 but at the current payment the borrower must still make 40.7 more monthly payments for the card balance to go to $0.

Assignment for students:   Create the credit card balance spread sheets with an input range at the top.   Place inputs labels interest rate in a1, loan balance in a2 and payment as a percent of loan balance in a3.   Place input values 0.18 in cell b1,  $20,000 in b2 and 0.04 in cell b3.   Create the credit card balance tables for both strategy one and strategy two.   Change interest rate and payment percentage parameters to conduct a sensitivity analysis. 

Authors Note:  For more questions involving the use of Excel Financial Functions go to the tutorial.




No comments:

Post a Comment