Build a spreadsheet that shows how long a card balance takes to clear and what it costs under different payment plans.
To find out how long a card balance will take to clear and how much interest it will cost, build a spreadsheet with one row per month. It answers the question in seconds and keeps answering when you change the balance or the payment.
Jun Hao has a S$3,000 card balance (an invented example). From lesson 2.2 he knew that paying only the minimum would take him about ten years. He did not know what to pay instead. He tried figures like S$150 and S$300 in his head, but each one felt like a guess, and none of them told him when the balance would be gone. His S$3,000 is the worked example below, and the exercise takes about 25 minutes in Excel or Google Sheets.
The card terms come from lesson 2.2 and are made up: 26% a year, with a minimum payment rule of 3% of the statement balance or S$50, whichever is higher, capped at the amount owed. When you build your own copy, replace the rate and the minimum rule with the ones from your own card's terms.
Put labels in column A and values in column B:
B1: starting balance, 3000 B2: yearly interest rate, 26% B3: minimum payment percentage, 3% B4: minimum payment floor in dollars, 50 B5: fixed monthly payment to test, 150 (S$150)
Shade these five cells so you know which numbers are yours to change. Everything else in the sheet works from them.
In row 8, type these headings in columns A to E: Month, Opening balance, Interest, Payment, Closing balance.
Row 9 is the first month. In A9 type 1. In B9 type `=B1`, so the opening balance in month 1 is your starting balance. In C9 type `=B9*$B$2/12`, which is one month's interest on the opening balance.
D9 holds the payment. For the minimum-only plan, type `=MIN(MAX($B$3*(B9+C9),$B$4),B9+C9)`. This takes 3% of the statement balance, raises it to S$50 if it is lower, and caps it at the amount owed so the last payment does not overshoot. In E9 type `=B9+C9-D9`: the closing balance is the opening balance plus interest minus the payment.
Row 10 links each month to the one before. In A10 type `=A9+1`. In B10 type `=E9`, because each month's opening balance is the previous month's closing balance. Copy C9 to E9 down into row 10. Then copy row 10 down to about row 300. The minimum-only plan runs for years, so the table needs that many rows.
Real cards often calculate interest daily, and the way the minimum is worked out depends on the issuer. In the example MoneySense gives, the minimum is a percentage of the principal owed. Check your own card's terms, and MoneySense, for how yours is calculated. This model uses a monthly approximation, which is close enough to compare plans.
Somewhere to the right of the table, add two results. For months taken, type `=COUNTIF(D9:D300,">0.005")`, which counts every month with a payment above 0.005. For total interest, type `=SUM(C9:C300)`, the sum of the interest column.
With the example figures, the minimum-only plan should show 125 months and about S$4,532 in interest, which is more than ten years. If your numbers differ, check that C9 divides by 12, and that the dollar signs (absolute references) are on B2, B3 and B4.
To test a fixed payment, change D9 to `=MIN($B$5,B9+C9)` and copy it down the column. The MIN keeps the last payment from going past what is owed. With S$150 in B5, the balance clears in 27 months with about S$975 in interest, just over two years. Change B5 to 200 and it clears in 19 months with about S$668 in interest.
For the twelve-month payment, the amount that clears the balance in exactly a year, type `=PMT(B2/12,12,-B1)` into an empty cell. For the example it gives about S$286.59. Now type the same formula, `=PMT(B2/12,12,-B1)`, into B5 itself. The fixed-payment table then clears in 12 months, with about S$439 in interest. If you type the rounded figure S$286.59 into B5 instead, a few cents may spill into a thirteenth month; putting the formula in B5 makes the payment exact.
Put the three plans side by side and the cost of paying slowly is plain. The twelve-month plan asks for about three times the minimum each month, and it saves about S$4,093 against paying minimums (S$4,532 minus S$439).
Jun Hao saw the twelve-month figure and knew he could not find S$287 every month. He decided he could manage S$200. His model told him that meant 19 months and about S$668 in interest. He could also see that each extra S$50 he found later would shorten the payoff.
Your own model starts with a balance you choose and the rate and minimum rule from your own statement and terms. Some cards use a different minimum rule, for example interest plus a percentage of the balance. If yours does, you only need to change one formula, the one in D9, to match it.
A finished model has five shaded inputs, a monthly table that runs until the balance reaches zero, the two results for months taken and total interest, a column D you can switch between the minimum rule and a fixed payment, and the PMT figure for a twelve-month plan. The monthly figure that clears your balance in twelve months is the number to bring out of this exercise.
Keep a short note next to the totals with the three results for your balance: months and interest for the minimum, for a fixed amount you could actually afford, and for twelve months. If your card's minimum rule is not a simple percentage with a floor, write the rule beside the inputs and adjust the formula in D9 to match it.
Build the model for a balance you choose and run the three plans. Write down the monthly amount that clears it in twelve months.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).