Question

Can you help me solve this question, but also show me how to properly put the formulas/equations in Excel? I tried and I am getting the wrong answer. Thank you

Assume you have a balance of $1300 on a credit card with an APR of 15%, or 1.25% per month. You start making monthly payments of $200, but at the same time you charge an additional $70 per month to the credit card. Assume that interest for a given month is based on the balance for the previous month. The following table shows how you can calculate your monthly balance. Complete and extend the table to show the balance at the end of each month until the debt is paid off. How long does it take to pay off the credit card debt?

Answer 1

a) Month Charges Payments Interest $70.00 $70.00 $70.00 $70.00 $70.00 $70.00 $70.00 $70.00 $70.00 $70.00 $70.00 $200.00 $200.00 $200.00 $200.00 $200.00 $200.00 $200.00 $200.00 $200.00 $200.00 $167.54 $16.25 $14.83 $13.39 $11.93 $10.45 $8.96 $7.45 $5.92 $4.36 $2.79 $1.20 Balance $1,300.00 $1,186.25 [Interest = $1300 x 1.25% = $16.25; Balance = $1300 + $70 - $200 + $16.25 = $1186.25] $1,071.08 [Interest = $1186.25 x 1.25% = $14.83; Balance = $1186.25 + $70 - $200 + $14.83 = $1071.08] $954.47 [Interest = $1071.08 x 1.25% = $13.39; Balance = $1071.08 + $70 - $200 + $13.39 = $954.47] $836.40 [Interest = $954.47 x 1.25% = $11.93; Balance = $954.47 + $70 - $200 + $11.93 = $836.4] $716.85 [Interest = $836.4 x 1.25% = $10.45; Balance = $836.4 + $70 - $200 + $10.45 = $716.85] $595.81 [Interest = $716.85 x 1.25% = $8.96; Balance = $716.85 + $70 - $200 + $8.96 = $595.81] $473.26 [Interest = $595.81 x 1.25% = $7.45; Balance = $595.81 + $70 - $200 + $7.45 = $473.26] $349.18 [Interest = $473.26 x 1.25% = $5.92; Balance = $473.26 + $70 - $200 + $5.92 = $349.18] $223.54 [Interest = $349.18 x 1.25% = $4.36; Balance = $349.18 + $70 - $200 + $4.36 = $223.54] $96.34 [Interest = $223.54 x 1.25% = $2.79; Balance = $223.54 + $70 - $200 + $2.79 = $96.34] $0.00 [Interest = $96.34 x 1.25% = $1.2; Balance = $96.34 + $70 - $167.54 + $1.2 = $0] NOTE: Payment for Month-11 = $96.34 + $70 - $167.54 + $1.2 = $167.54.

b) Therefore, it takes 11 months to pay-off the entire debt. EXCEL SCREENSHOT: A 1 Month 1 2 B C D E Charges Payments Interest Balance $1,300.00 $70.00 $200.00 =E2*1.25% =E2+B3-C3+D3 $70.00 $200.00 EE3*1.25% =E3+B4-C4+D4 $70.00 $200.00 =E4*1.25% =E4+B5-C5+D5 $70.00 $200.00 =E5*1.25% =E5+B6-C6+D6 $70.00 $200.00 = 6*1.25% =E6+B7-C7+D7 $70.00 $200.00 =E7*1.25% =E7+B8-C8+D8 $70.00 $200.00 E8*1.25% =E8+B9-C9+D9 $70.00 $200.00 E9*1.25% E9+B10-C10+D10 $70.00 $200.00 =E10*1.25% =E10+B11-C11+D11 $70.00 $200.00 =E11*1.25% =E11+B12-C12+D12 $70.00 =E12+B13+D13 =E12*1.25% =E12+B13-C13+D13 10 11 12 13 14 10 11