Question

Sarah has maxed out her credit card and owes $20,000. The annual interest on her credit card is 18%.

1. The minimum payment for some credit cards is 2% of what you owe. What is Sarah’s payment?
2. Suppose Sarah continues to pay the amount found in part 1, how long until she has paid off her credit card dept? Assume she doesn’t charge anything else.
3. What is the total amount she spent to cover the $20,000 debt?
4. Suppose Sarah decides to pay $550 per month, how long until she pays off the credit card?
5. How much does she save by paying $550 a month?
6. Suppose Sarah is getting married in 18 months, what should she pay every month to clear her credit card by the time she is married?

This assignment must be done using excel.

Answer 1

Sarah's payment is 2% of what she owes i.e. 2% of 20000$ = 400 $

Now if she continues to pay 400 $ monthly, with interest being charged at 18% per annum or 18% / 12 per month = 1.5% per month then the interest that will be charged for the first month shall be 1.5% * (20000$-400$) = 294$ i.e. the interest is charged on amount due after payment for that period is made.

The number of periods can be calculated in excel using the formulae = nper (1.5%,-400,20000,0,1) = 90.1974 or 91 months.

For calculating the total amount spent, the worksheet is prepared as below:

Month No.	Opening Balance = Closing Balance of previous month + Interest Charged for Previous Month	Pay 2% of 20000 or outstanding amount (whichever is less)	Closing Balance Opening Balance - amount paid	Interest Charged =1.5% * Closing Balance
1	20000	400	19600	294
2	19894	400	19494	292
3	19786	400	19386	291
4	19677	400	19277	289
5	19566	400	19166	287
6	19454	400	19054	286
7	19340	400	18940	284
8	19224	400	18824	282
9	19106	400	18706	281
10	18987	400	18587	279
11	18866	400	18466	277
12	18742	400	18342	275
13	18618	400	18218	273
14	18491	400	18091	271
15	18362	400	17962	269
16	18232	400	17832	267
17	18099	400	17699	265
18	17965	400	17565	263
19	17828	400	17428	261
20	17690	400	17290	259
21	17549	400	17149	257
22	17406	400	17006	255
23	17261	400	16861	253
24	17114	400	16714	251
25	16965	400	16565	248
26	16813	400	16413	246
27	16660	400	16260	244
28	16503	400	16103	242
29	16345	400	15945	239
30	16184	400	15784	237
31	16021	400	15621	234
32	15855	400	15455	232
33	15687	400	15287	229
34	15516	400	15116	227
35	15343	400	14943	224
36	15167	400	14767	222
37	14989	400	14589	219
38	14808	400	14408	216
39	14624	400	14224	213
40	14437	400	14037	211
41	14248	400	13848	208
42	14055	400	13655	205
43	13860	400	13460	202
44	13662	400	13262	199
45	13461	400	13061	196
46	13257	400	12857	193
47	13050	400	12650	190
48	12839	400	12439	187
49	12626	400	12226	183
50	12409	400	12009	180
51	12190	400	11790	177
52	11966	400	11566	173
53	11740	400	11340	170
54	11510	400	11110	167
55	11277	400	10877	163
56	11040	400	10640	160
57	10799	400	10399	156
58	10555	400	10155	152
59	10308	400	9908	149
60	10056	400	9656	145
61	9801	400	9401	141
62	9542	400	9142	137
63	9279	400	8879	133
64	9013	400	8613	129
65	8742	400	8342	125
66	8467	400	8067	121
67	8188	400	7788	117
68	7905	400	7505	113
69	7617	400	7217	108
70	7326	400	6926	104
71	7029	400	6629	99
72	6729	400	6329	95
73	6424	400	6024	90
74	6114	400	5714	86
75	5800	400	5400	81
76	5481	400	5081	76
77	5157	400	4757	71
78	4828	400	4428	66
79	4495	400	4095	61
80	4156	400	3756	56
81	3813	400	3413	51
82	3464	400	3064	46
83	3110	400	2710	41
84	2750	400	2350	35
85	2386	400	1986	30
86	2015	400	1615	24
87	1640	400	1240	19
88	1258	400	858	13
89	871	400	471	7
90	478	400	78	1
91	79	79	0	0
TOTAL	1108041	36079.43	1071962	16079.43

Therefore, the total amount paid is 36079.43 + 16079.43 = 52159 $

If she pays 550$, number of periods = nper (1.5%,-550,20000,0,1) = 51.776 or 52 months.

and she pays a total of 28478+8478 = 36955$ and therefore saves 15203.40$ (52159-36955)

The amount she needs to pay to finish the debt in 18 month is calculated using the formula = PPMT(1.5%,1,18,-20000,0,1) = 1257.26$