Question

use excel

The typical American household owes approximately $15,000 in credit card debt. Assume you owe this amount today and will not to put another charge on your credit card until this debt is paid off. Your credit card has an APR of 18% and minimum payments are calculated as 2% of outstanding balance or $15, whichever is greater. Using two (2) decimal places for dollar amounts, determine: a) How many years will it take to pay the entire amount assuming you only make the minimum payments? b) At what month does the payment hit the $15 minimum? c) What is the total amount paid? d) What is the total amount of interest paid?

Answer 1

Given Data

Amount Owed - $15,000.00

APR – 18.00%

Minimum Payments – 2.00% of outstanding balance or $15.00, whichever is higher

Calculating monthly interest rate = 18.00%/12 = 1.50%

The calculation of payments in Excel is made as given in the below table:

A	B = Closing Balance of previous month	C = B x 2%	D = B + C	E = (2% of D or $15.00, whichever is greater)	F = D - E
Excel Formulas for month 2	= F12	B12*1.50%	B12+C12	IF((D12*2%)>15,D12*2%,15)	D12-E12
Months	Opening Balance	Monthly Interest	Amount Due	Minimum Payment	Closing Balance
1	$15,000.00	$225.00	$15,225.00	₹304.50	$14,920.50
2	$14,920.50	$223.81	$15,144.31	₹302.89	$14,841.42
3	$14,841.42	$222.62	$15,064.04	₹301.28	$14,762.76
4	$14,762.76	$221.44	$14,984.20	₹299.68	$14,684.52
5	$14,684.52	$220.27	$14,904.79	₹298.10	$14,606.69
6	$14,606.69	$219.10	$14,825.79	₹296.52	$14,529.28
7	$14,529.28	$217.94	$14,747.21	₹294.94	$14,452.27
8	$14,452.27	$216.78	$14,669.05	₹293.38	$14,375.67
9	$14,375.67	$215.64	$14,591.31	₹291.83	$14,299.48
10	$14,299.48	$214.49	$14,513.97	₹290.28	$14,223.70
11	$14,223.70	$213.36	$14,437.05	₹288.74	$14,148.31
12	$14,148.31	$212.22	$14,360.53	₹287.21	$14,073.32
13	$14,073.32	$211.10	$14,284.42	₹285.69	$13,998.74
14	$13,998.74	$209.98	$14,208.72	₹284.17	$13,924.54
15	$13,924.54	$208.87	$14,133.41	₹282.67	$13,850.74
16	$13,850.74	$207.76	$14,058.50	₹281.17	$13,777.33
17	$13,777.33	$206.66	$13,983.99	₹279.68	$13,704.31
18	$13,704.31	$205.56	$13,909.88	₹278.20	$13,631.68
19	$13,631.68	$204.48	$13,836.16	₹276.72	$13,559.43
20	$13,559.43	$203.39	$13,762.82	₹275.26	$13,487.57
21	$13,487.57	$202.31	$13,689.88	₹273.80	$13,416.08
22	$13,416.08	$201.24	$13,617.32	₹272.35	$13,344.98
23	$13,344.98	$200.17	$13,545.15	₹270.90	$13,274.25
24	$13,274.25	$199.11	$13,473.36	₹269.47	$13,203.90
25	$13,203.90	$198.06	$13,401.95	₹268.04	$13,133.92
26	$13,133.92	$197.01	$13,330.92	₹266.62	$13,064.31
27	$13,064.31	$195.96	$13,260.27	₹265.21	$12,995.06
28	$12,995.06	$194.93	$13,189.99	₹263.80	$12,926.19
29	$12,926.19	$193.89	$13,120.08	₹262.40	$12,857.68
30	$12,857.68	$192.87	$13,050.55	₹261.01	$12,789.54
31	$12,789.54	$191.84	$12,981.38	₹259.63	$12,721.75
32	$12,721.75	$190.83	$12,912.58	₹258.25	$12,654.33
33	$12,654.33	$189.81	$12,844.14	₹256.88	$12,587.26
34	$12,587.26	$188.81	$12,776.07	₹255.52	$12,520.55
35	$12,520.55	$187.81	$12,708.35	₹254.17	$12,454.19
36	$12,454.19	$186.81	$12,641.00	₹252.82	$12,388.18
37	$12,388.18	$185.82	$12,574.00	₹251.48	$12,322.52
38	$12,322.52	$184.84	$12,507.36	₹250.15	$12,257.21
39	$12,257.21	$183.86	$12,441.07	₹248.82	$12,192.25
40	$12,192.25	$182.88	$12,375.13	₹247.50	$12,127.63
41	$12,127.63	$181.91	$12,309.55	₹246.19	$12,063.35
42	$12,063.35	$180.95	$12,244.30	₹244.89	$11,999.42
43	$11,999.42	$179.99	$12,179.41	₹243.59	$11,935.82
44	$11,935.82	$179.04	$12,114.86	₹242.30	$11,872.56
45	$11,872.56	$178.09	$12,050.65	₹241.01	$11,809.64
46	$11,809.64	$177.14	$11,986.78	₹239.74	$11,747.05
47	$11,747.05	$176.21	$11,923.25	₹238.47	$11,684.79
48	$11,684.79	$175.27	$11,860.06	₹237.20	$11,622.86
49	$11,622.86	$174.34	$11,797.20	₹235.94	$11,561.26
50	$11,561.26	$173.42	$11,734.68	₹234.69	$11,499.98
51	$11,499.98	$172.50	$11,672.48	₹233.45	$11,439.03
52	$11,439.03	$171.59	$11,610.62	₹232.21	$11,378.41
53	$11,378.41	$170.68	$11,549.08	₹230.98	$11,318.10
54	$11,318.10	$169.77	$11,487.87	₹229.76	$11,258.11
55	$11,258.11	$168.87	$11,426.99	₹228.54	$11,198.45
56	$11,198.45	$167.98	$11,366.42	₹227.33	$11,139.09
57	$11,139.09	$167.09	$11,306.18	₹226.12	$11,080.06
58	$11,080.06	$166.20	$11,246.26	₹224.93	$11,021.33
59	$11,021.33	$165.32	$11,186.65	₹223.73	$10,962.92
60	$10,962.92	$164.44	$11,127.36	₹222.55	$10,904.82

Kindly note that due to restriction of the number of characters for submitting the answer, I am not able to paste the entire sheet. You can use the mentioned formulas and extend it till the end to get the final answers which are given below.

1. It will take 657 months i.e. 54 years and 9 months to make the entire payment assuming only minimum payments are made.

2. The payment hits the minimum $15.00 amount in the 568^th month.

3. The total amount paid is $55,975.60.

4. The total interest paid is $40,975.60.