EXCEL PROJECT Choose a credit card balance between $2500 and $3000 – this will b

Question

EXCEL PROJECT

Choose a credit card balance between $2500 and $3000 – this will be your starting balance on the table. In this part, we are going to pay it off. Assume the interest rate to 15% of the unpaid balance. Use the column headings below. Assuming you charge $300 per month and you pay $400 per month, add rows to the table until you have paid off your debt (that is, until the balance is between zero and 100 dollars). In this scenario, we assumed that you diligently paid $400 per month on your credit card until the balance was almost paid off. Most credit cards, however, suggest minimum monthly payments that are well below $400 per month. Let us assume that the criterion for calculating the minimum monthly payment on this credit card is 2% of your balance or $10.00 whichever is higher. Add another column to the table that will show the minimum monthly payment. (hint: use the Max function)

Instructions for Part 2

At the bottom of the spreadsheet, label the second tab, “Part 2”.

Make a credit-card table on the worksheet as described. .

Have the interest rate of 15% as a separate labeled cell.

Have the minimum payment percent of 2% as a separate labeled cell.

The table should have the following columns:

#

Month

Payment

Balance

Plus Charges

Interest

New Balance

MinPymt

For example,

#

Month

Payment

Balance

Plus Charges

Interest

New Balance

MinPymt

1

January

$400.00

$2,600.00

$300.00

$36.25

$2,936.25

$58.73

All entries in columns “Old”, “Interest’’, “New”, and “MinPymt” must be formulas.

Center and bold the table titles.

# means month number. This column should be 1, 2, 3, etc.

“Payment” means the $400 payment.

“Balance” is the total amount of debt. For the first line of the table, you will choose a debt between $2500 and $3000. In subsequent lines, “Balance = New Balance - $400”.

“Plus Charges” will be $300 of spending charges per month.

“Interest” is 15% of the sum of the “Balance”, “Plus Charges”.   Use an Excel formula to calculate this amount.

“New Balance” is the sum of the “Balance”, “Plus Charges”, and “Interest”.   Use an Excel formula to calculate this amount.

“MinPymt” is 2% of the “New Balance” or $10, whichever is higher. Use an Excel formula to calculate this amount. (Max function is helpful.)

Copy/Paste line 2 to create the table.

After you complete 20+ lines of the table, analyze the results and provide suggestions for the credit card holder.

#

Month

Payment

Balance

Plus Charges

Interest

New Balance

MinPymt

Explanation / Answer

It will take 34 periods to pay off the credit card balance

A B C = F - B D E F= C+D+E F # Month Payment Balance = New Balance -$400 Plus Charges Interest =( C+D)x15%/12 New Balance MinPymt = Max (2% x New balance);10 1 January $400 $2,600 $300 $36.25 $2,936.25 $58.73 2 Feburary $400 $2,536.25 $300 $35.45 $2,871.7 $57.43 3 March $400 $2,471.7 $300 $34.65 $2,806.35 $56.13 4 April $400 $2,406.35 $300 $33.83 $2,740.18 $54.8 5 May $400 $2,340.18 $300 $33 $2,673.18 $53.46 6 June $400 $2,273.18 $300 $32.16 $2,605.35 $52.11 7 July $400 $2,205.35 $300 $31.32 $2,536.66 $50.73 8 August $400 $2,136.66 $300 $30.46 $2,467.12 $49.34 9 September $400 $2,067.12 $300 $29.59 $2,396.71 $47.93 10 October $400 $1,996.71 $300 $28.71 $2,325.42 $46.51 11 November $400 $1,925.42 $300 $27.82 $2,253.24 $45.06 12 December $400 $1,853.24 $300 $26.92 $2,180.15 $43.6 13 January $400 $1,780.15 $300 $26 $2,106.15 $42.12 14 Feburary $400 $1,706.15 $300 $25.08 $2,031.23 $40.62 15 March $400 $1,631.23 $300 $24.14 $1,955.37 $39.11 16 April $400 $1,555.37 $300 $23.19 $1,878.56 $37.57 17 May $400 $1,478.56 $300 $22.23 $1,800.8 $36.02 18 June $400 $1,400.8 $300 $21.26 $1,722.06 $34.44 19 July $400 $1,322.06 $300 $20.28 $1,642.33 $32.85 20 August $400 $1,242.33 $300 $19.28 $1,561.61 $31.23 21 September $400 $1,161.61 $300 $18.27 $1,479.88 $29.6 22 October $400 $1,079.88 $300 $17.25 $1,397.13 $27.94 23 November $400 $997.13 $300 $16.21 $1,313.34 $26.27 24 December $400 $913.34 $300 $15.17 $1,228.51 $24.57 25 January $400 $828.51 $300 $14.11 $1,142.62 $22.85 26 Feburary $400 $742.62 $300 $13.03 $1,055.65 $21.11 27 March $400 $655.65 $300 $11.95 $967.59 $19.35 28 April $400 $567.59 $300 $10.84 $878.44 $17.57 29 May $400 $478.44 $300 $9.73 $788.17 $15.76 30 June $400 $388.17 $300 $8.6 $696.77 $13.94 31 July $400 $296.77 $300 $7.46 $604.23 $12.08 32 August $400 $204.23 $300 $6.3 $510.53 $10.21 33 September $400 $110.53 $300 $5.13 $415.67 $10 34 October $400 $15.67 $300 $3.95 $319.61 $10 35 November $400 -$80.39 $300 $2.75 $222.36 $10

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.