Question

Suppose your credit card balance is $5,227.54. The minimum payment amount is $75,

and the annual percentage rate is 12%. (10 pts) If you make a constant monthly payment

of $75, beginning today, how long will it take you to payoff the credit balance? (5 pts)

What is the payoff amount in 3 years?

Answer 1

Solution 1: Calculation of the period to payoff the credit balance.

Following are the steps to be followed on Microsoft Excel:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "NPER"
Step 4: Insert Rate = 0.12/12 PV = -5227.54 PMT = 75

NPER = 120
Therefore, it will take around 120 months to payoff the credit balance of $5227.54.

Solution 3: Calculation of the pay off amount in 3 years

Following are the steps to be followed on Microsoft Excel:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "IPMT"
Step 4: Insert Rate = 0.12/12 Per = 1 Nper = 120 PV = -5227.54

IPMT = $52.28

Principal payment = Total payment – Interest payment

Ending balance = Beginning balance – Principal payment

Keeping the Rate, Nper and PV as constant drag the formula till 120^th Month, you will derive the schedule

Month	Beginning Balance	Total Payment	Interest Payment	Principal Payment	Ending Balance
1	5227.54	75.00	52.28	22.72	5204.82
2	5204.82	75.00	52.05	22.95	5181.86
3	5181.86	75.00	51.82	23.18	5158.68
4	5158.68	75.00	51.59	23.41	5135.27
5	5135.27	75.00	51.35	23.65	5111.62
6	5111.62	75.00	51.12	23.88	5087.74
7	5087.74	75.00	50.88	24.12	5063.62
8	5063.62	75.00	50.64	24.36	5039.25
9	5039.25	75.00	50.39	24.61	5014.64
10	5014.64	75.00	50.15	24.85	4989.79
11	4989.79	75.00	49.90	25.10	4964.69
12	4964.69	75.00	49.65	25.35	4939.34
13	4939.34	75.00	49.39	25.61	4913.73
14	4913.73	75.00	49.14	25.86	4887.87
15	4887.87	75.00	48.88	26.12	4861.74
16	4861.74	75.00	48.62	26.38	4835.36
17	4835.36	75.00	48.35	26.65	4808.72
18	4808.72	75.00	48.09	26.91	4781.80
19	4781.80	75.00	47.82	27.18	4754.62
20	4754.62	75.00	47.55	27.45	4727.17
21	4727.17	75.00	47.27	27.73	4699.44
22	4699.44	75.00	46.99	28.01	4671.43
23	4671.43	75.00	46.71	28.29	4643.15
24	4643.15	75.00	46.43	28.57	4614.58
25	4614.58	75.00	46.15	28.85	4585.72
26	4585.72	75.00	45.86	29.14	4556.58
27	4556.58	75.00	45.57	29.43	4527.15
28	4527.15	75.00	45.27	29.73	4497.42
29	4497.42	75.00	44.97	30.03	4467.39
30	4467.39	75.00	44.67	30.33	4437.07
31	4437.07	75.00	44.37	30.63	4406.44
32	4406.44	75.00	44.06	30.94	4375.50
33	4375.50	75.00	43.76	31.24	4344.26
34	4344.26	75.00	43.44	31.56	4312.70
35	4312.70	75.00	43.13	31.87	4280.83
36	4280.83	75.00	42.81	32.19	4248.63
Total		2700.00	1721.10	978.91

	$
Total payment	2,700.00
Total Interest paid	1,721.10
Total Principal repaid	978.91
Closing balance of the loan	4,248.63