Saturday, October 31, 2015

A Credit Card Problem Solved In Excel.

This post uses Excel to find out how long it takes to repay credit cards when one pays only the minimum balance.  

Can you solve this problem if there is an annual fee on each credit card?


Question Four:  A person has $15,000 in credit card debt divided over 5 credit cards with identical terms.   The interest rate on all five cards is 12%.  The minimum required monthly payment is 3.0% of the monthly balance.  

How long would it take for the person to repay the entire $15,000 in credit card debt if the person continues to pay 3.0% of  $15,000?

How long would it take for the person to cut the loan balance in half if the person continues to make this monthly payment?


Answer:   We have the interest rate, the initial loan balance, and the payment but we do not have the term of the loan.   The actual amount that we will pay per month is $450 (3.0 percent of $15,000.)   We can insert the interest rate (monthly of course) and the loan balance with different estimates of the term in the PMT function.    The actual repayment period is the term that gives us a value of the PMT function equal to the actual monthly payment of $450.

At 40 months the PMT function tells us the required payment is $456.    At 41 months the payment function gives us a payment of $447.77.

The loan will be completely repaid on payment 41.

This result can be confirmed by observing that the future value of the loan becomes $0 at somewhere between 40 and 41 months.

The credit card balance is obtained from the FV function.   The final chart reveals that the credit card balance has fallen by 50% on the 23rd monthly payment.




Payment by Term for $15,000 balance at 12% Interest
Payment at 12 % Interest Rate
Payment Term
Credit Card Balance
Note
-$1,332.73
12
$15,000
-$706.10
24
$15,000
-$498.21
36
$15,000
-$395.01
48
$15,000
-$333.67
60
$15,000
-$293.25
72
$15,000
-$264.79
84
$15,000
-$243.79
96
$15,000
$15,000
$15,000
-$498.21
36
$15,000
-$487.02
37
$15,000
-$476.42
38
$15,000
-$466.37
39
$15,000
-$456.83
40
$15,000
-$447.77
41
$15,000
Payment <= $450 at 12 %
-$439.13
42
$15,000
-$430.91
43
$15,000
-$423.07
44
$15,000
-$415.58
45
$15,000
-$408.42
46
$15,000
-$401.57
47
$15,000

Somewhere between 40 and 41 months the payment on the loan is $450 the required minimum monthly payment in this problem.







Loan Balance at Months 40 and Months 41
FV
Rate
NPER
PAY
Loan Balance
-$334.09
0.12
40
-$450
$15,000
$112.57
0.12
41
-$450
$15,000
Somewhere between months 40 and 41 the loan balance is $0.





Estimate of the Amount of Time It Takes to Reduce a $15,000 Credit Card Debt to $7,500
FV at 12%
Term
Payment
Credit Card Balance
-$11,195
12
-$450
$15,000
-$6,908
24
-$450
$15,000
-$450
$15,000
-$8,394
20
-$450
$15,000
-$8,028
21
-$450
$15,000
-$7,659
22
-$450
$15,000
-$7,285
23
-$450
$15,000
Half point at 12%
-$6,908
24
-$450
$15,000
At 23 months the credit card balance is less than 50%.   At 22 months the balance is a bit more than 22 months.

This problem and others like it can be found in the workbook Solving Financial Problems in Excel






No comments:

Post a Comment