Question

Suppose your credit card balance is ​$17000 . The minimum payment is ​$347​, and the annual percentage rate is 20.1​%.

a.  If you make a constant monthly payment of ​$347, how long will it take you to pay off the credit card​ balance?

b.  How much interest will you pay if you elect to make the minimum​ payment?

Please complete using Excel and show all formulas used.

Answer 1

Answer :

a. To pay off Credit card balance with monthly payment $347 , it takes 104 months i.e 8.6years.

b. Total interest to be paid if we elect to pay minimum amount is $18892.23.

Calculations in excel format is shown below :

Answer a.

Interest = Balance amount *1.675% (where interest rate /12 = 20.1%/12 = 1.675%) i.e 1.675% is monthly interest rate

Principle = Payment amount - Interest amount

Balance = Previous month balance - Principle amount

Calculation for 1st month

Interest =17000*1.675% = 284.75

Principle = 347-284.75 = 62.25

Balance = 17000 - 62.25 = 16937.75

follow the same calculation for every month.

Month	Payment	Interset	Principle	Balance
				17000
1	347	284.75	62.25	16937.75
2	347	283.7073	63.29269	16874.46
3	347	282.6472	64.35284	16810.1
4	347	281.5692	65.43075	16744.67
5	347	280.4733	66.52672	16678.15
6	347	279.359	67.64104	16610.51
7	347	278.226	68.77403	16541.73
8	347	277.074	69.92599	16471.81
9	347	275.9027	71.09725	16400.71
10	347	274.7119	72.28813	16328.42
11	347	273.501	73.49896	16254.92
12	347	272.2699	74.73006	16180.19
13	347	271.0182	75.98179	16104.21
14	347	269.7455	77.25449	16026.96
15	347	268.4515	78.5485	15948.41
16	347	267.1358	79.86419	15868.54
17	347	265.7981	81.20191	15787.34
18	347	264.438	82.56204	15704.78
19	347	263.055	83.94496	15620.83
20	347	261.649	85.35104	15535.48
21	347	260.2193	86.78067	15448.7
22	347	258.7658	88.23424	15360.47
23	347	257.2878	89.71217	15270.76
24	347	255.7852	91.21484	15179.54
25	347	254.2573	92.74269	15086.8
26	347	252.7039	94.29613	14992.5
27	347	251.1244	95.87559	14896.63
28	347	249.5185	97.48151	14799.14
29	347	247.8857	99.11432	14700.03
30	347	246.2255	100.7745	14599.26
31	347	244.5375	102.4625	14496.79
32	347	242.8213	104.1787	14392.61
33	347	241.0763	105.9237	14286.69
34	347	239.3021	107.6979	14178.99
35	347	237.4981	109.5019	14069.49
36	347	235.664	111.336	13958.16
37	347	233.7991	113.2009	13844.95
38	347	231.903	115.097	13729.86
39	347	229.9751	117.0249	13612.83
40	347	228.0149	118.9851	13493.85
41	347	226.0219	120.9781	13372.87
42	347	223.9956	123.0044	13249.86
43	347	221.9352	125.0648	13124.8
44	347	219.8404	127.1596	12997.64
45	347	217.7105	129.2895	12868.35
46	347	215.5449	131.4551	12736.9
47	347	213.343	133.657	12603.24
48	347	211.1043	135.8957	12467.34
49	347	208.828	138.172	12329.17
50	347	206.5136	140.4864	12188.68
51	347	204.1605	142.8395	12045.85
52	347	201.7679	145.2321	11900.61
53	347	199.3353	147.6647	11752.95
54	347	196.8619	150.1381	11602.81
55	347	194.3471	152.6529	11450.16
56	347	191.7901	155.2099	11294.95
57	347	189.1904	157.8096	11137.14
58	347	186.5471	160.4529	10976.69
59	347	183.8595	163.1405	10813.54
60	347	181.1269	165.8731	10647.67
61	347	178.3485	168.6515	10479.02
62	347	175.5236	171.4764	10307.54
63	347	172.6514	174.3486	10133.19
64	347	169.731	177.269	9955.926
65	347	166.7618	180.2382	9775.688
66	347	163.7428	183.2572	9592.43
67	347	160.6732	186.3268	9406.104
68	347	157.5522	189.4478	9216.656
69	347	154.379	192.621	9024.035
70	347	151.1526	195.8474	8828.187
71	347	147.8721	199.1279	8629.059
72	347	144.5367	202.4633	8426.596
73	347	141.1455	205.8545	8220.742
74	347	137.6974	209.3026	8011.439
75	347	134.1916	212.8084	7798.631
76	347	130.6271	216.3729	7582.258
77	347	127.0028	219.9972	7362.261
78	347	123.3179	223.6821	7138.578
79	347	119.5712	227.4288	6911.15
80	347	115.7618	231.2382	6679.911
81	347	111.8885	235.1115	6444.8
82	347	107.9504	239.0496	6205.75
83	347	103.9463	243.0537	5962.697
84	347	99.87517	247.1248	5715.572
85	347	95.73583	251.2642	5464.308
86	347	91.52715	255.4728	5208.835
87	347	87.24798	259.752	4949.083
88	347	82.89714	264.1029	4684.98
89	347	78.47341	268.5266	4416.453
90	347	73.97559	273.0244	4143.429
91	347	69.40243	277.5976	3865.831
92	347	64.75268	282.2473	3583.584
93	347	60.02503	286.975	3296.609
94	347	55.2182	291.7818	3004.827
95	347	50.33086	296.6691	2708.158
96	347	45.36165	301.6384	2406.52
97	347	40.30921	306.6908	2099.829
98	347	35.17214	311.8279	1788.001
99	347	29.94902	317.051	1470.95
100	347	24.63842	322.3616	1148.589
101	347	19.23886	327.7611	820.8274
102	347	13.74886	333.2511	487.5763
103	347	8.166903	338.8331	148.7432
104	347	2.491448	344.5086	-195.765
	Total int.	18892.23

Answer b.

Add all amounts in interest column by using SUM formula i.e =Sum(number 1,number 2,..,Number n) , which is $18892.23.