Question

Question #1 I owe $4,200 on a credit card. The interest rate is 14.99%. I will pay $80 each month until my balance is paid off. Create a spreadsheet for an amortization table for this loan. Submit your spreadsheet here, and then use it to answer the questions that follow.

Question #2 How many months will it take me to fully pay off my balance following this plan?

Question #3 How much does my last payment need to be to bring my final balance to exactly $0.00?

Question #4 How total interest will I end up paying according to this plan?

Question #5 Suppose that instead of $80 a month I am considering paying less. What is the least amount that my first monthly payment would need to be in order to avoid negative amortization?

Answer 1

Q1: Please refer to amortisation schedule image below (answers highlighted in blue and formula in orange)

Q2: Number of months to make full payment = 85.91 months $\approx$ 86 months

Q3: Last payment = $73.21 (86th Months opening balance + Interest = $72.31 + $0.9)

Q4: Total Interest = $2,673.21(sum of interest column)

Q5: $77

Total value owed Annual interest rate Monthly interest rate Monthly payment amount Number of months 4200 14.99% 1.25% 80 NPER( 1.25%, 80, 85.91-420o, 0)

Amortisation schedule pictures:

246 38371 8777 Ba p 4|4|4|4|4|43333333333333333333 ng t 47 12 77 42 06 70 4 9 9 2 84 04 64 24 83-41 00 57 14 70 26 27 91 Int- rat bal-sssssssssssssssssssssssss AR ba 4 4 4 4 4 4 4 333333333333333333 0 -23-436 739 10 1 12 13 14 15 16 17 18 19 20 22 23 24 25

2,673.21 Opening Balance Interest Monthly payment Closing Balance -Opening balance Monthly interest -previous month rate Opening #Given closing balance balance 26 $ 3,397.84 S 27 $ 3,360.28 S 28 $3,322.26$ 29 $ 3,283.76 S 303,244.78 S 31 $ 3,205.31$ 32 $3,165.35$ 33 3,124.89 S 34$ 3,083.93$ 35$ 3,042.45$ 36 $ 3,000.46$ 37 2,957.94 | $ 38 2914.89 $ 392,871.30 S 402,827.17 S 41$2,782.48 $ 42$2,737.24 $ 43 2,691.43 S 442,645.05 S 45 2,598.09 $ 46 S2,550.55 $ 472,502.41 S 48 S2,453.67 $ 49$2,404.32$ 502,354.35 $ yment 42.44 $ 41.98 $ 41,50 $ 41.02$ 40.53 $ 40.04$ 39.54 39.04 S 80.00$ 3,360.28 80.00$ 3,32.26 80.00$3,283.76 80.00$ 3,244.78 80.00 S 3,205.31 80.00$ 3,165.35 80.00$ 3,124.89 80.00$ 3,083.93 80.00$ 3.45 80.00$3,00.46 80.00$ 2,957.94 80.00$ 2,914.89 80.00 $ 2,871.30 80.00$ 2,827.17 80.00$ 2,782.48 2,737.24 80.00$ 2,691.43 80.00$ 2,645.05 80.002,598.09 80.00$ 2,550.55 80.00$ 2,502.41 80.00$ 2,453.67 80.00 $2,404.32 80.00$ 2,354.35 80.00$ 2,303.76 2 38.01 $ 37.48 36.95 $ 35.87 $ 35.32 34.76 33.62 33.04 S 32.45 $ 31.86 $ 31.26 $ 5 30.03 29.41 $

2,673.21 Opening Balance Interest Monthly payment Closing Balance Monthly interest closing balance balance -Given Monthly payment 2 5 3 4

2,673.21 Opening Balance Interest Monthly payment Closing Balance Monthly interest -previous month rate Opening closing balance balance + Interest -Given Monthly payment