You will build a spreadsheet that shows how long a card balance lasts and what it costs under different payment plans.
Jun Hao knew from lesson 2.2 that minimums alone would take him about ten years. What he did not know was what to pay instead. S$150 a month? S$300? Every figure he tried in his head felt like a guess, and none of them told him when the balance would be gone. A small spreadsheet answers that in seconds, and it keeps answering whenever the balance or the payment changes.
In this exercise you build a card balance model in Excel or Google Sheets and run three plans through it. Allow about 25 minutes. The worked example uses Jun Hao's S$3,000 with the example terms from lesson 2.2: 26% a year, and a minimum of 3% of the statement balance or S$50, whichever is higher. These are made up for the example. When you build your own, replace them with the rate and minimum rule from your card's terms.
Put labels in column A and values in column B.
In B1, the starting balance: 3000. In B2, the yearly interest rate: 26%. In B3, the minimum payment percentage: 3%. In B4, the minimum payment floor in dollars: 50. In B5, a fixed monthly payment you want to test: 150.
Shade these five cells so you know which numbers are yours to change. If your card's minimum rule is different, for example interest plus a percentage of the balance, you will change one formula in step 2 to match it.
In row 8, type five column headings, one per column from A to E:
Month Opening balance Interest Payment Closing balance
In row 9, the first month: A9 is 1. B9 is =B1. C9 is =B9*$B$2/12, a month's interest on the opening balance. For the minimum-only plan, D9 is =MIN(MAX($B$3*(B9+C9),$B$4),B9+C9). That takes 3% of the statement balance, lifts it to S$50 if it is lower, and caps it at the amount owed so the last payment does not overshoot. E9 is =B9+C9-D9.
In row 10: A10 is =A9+1, and B10 is =E9, so each month opens with last month's closing balance. Copy C9 to E9 down into row 10. Then copy row 10 down to about row 300, since the minimum-only plan runs for years.
Real cards often work out interest daily, and the minimum is calculated on the statement balance after fees. This model uses a monthly approximation, which is close enough to compare plans.
Somewhere to the right, add two results. Months taken is =COUNTIF(D9:D300,">0.005"). Total interest is =SUM(C9:C300).
With the example inputs, the minimum-only plan shows 125 months and about S$4,532 of interest. If your model shows something else, check that C9 divides by 12 and that the dollar signs are on B2, B3 and B4.
For a fixed payment, change D9 to =MIN($B$5,B9+C9) and copy it down the column. With S$150 a month, the balance clears in 27 months with about S$975 of interest. Try S$200 and it clears in 19 months with about S$668.
For a twelve-month plan, you need the payment that clears the balance in exactly a year. In any empty cell, type =PMT(B2/12,12,-B1). With the example inputs it returns about S$286.59. Type =PMT(B2/12,12,-B1) into B5 itself, so the payment is exact, and the fixed-payment table now clears in 12 months with about S$439 of interest. If you type the rounded S$286.59 instead, a few cents may spill into a thirteenth month.
Line the three plans up and the cost of going slowly is plain. On S$3,000 at the example rate, paying the minimum costs about S$4,532 in interest over more than ten years. A fixed S$150 a month costs about S$975 over just over two years. Clearing it in twelve months costs about S$439. The twelve-month plan asks for about three times the minimum each month, and in return saves about S$4,093 against minimums alone.
Jun Hao looked at the twelve-month figure and knew he could not find S$287 every month. He could manage S$200. His model told him that meant 19 months and about S$668 in interest, and he could see that each extra S$50 he found later would shorten it.
A finished model has five shaded inputs, a monthly table that runs until the balance reaches zero, and two results: months taken and total interest. You can switch column D between the minimum rule and a fixed payment, and you have the PMT figure for a twelve-month plan.
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.
Your own model starts with a balance you choose and the rate and minimum rule from your own statement and terms. The monthly figure that clears it in twelve months is the number to bring out of this exercise.
Build the model for a balance of your choice, run the three plans, and write 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).