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?
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 23^{rd} 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
This problem and others like it can be found in the workbook Solving Financial Problems in Excel
No comments:
Post a Comment