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 41^{st} 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