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