Build a month-by-month payoff schedule for your education debt.
Many people put a figure on the loan line of their budget long before they have read the loan terms properly. If that figure is lower than the loan actually needs, each month ends with a shortfall. If it is higher, money is set aside that you could have used for something else. A guessed figure also can't tell you which month the loan will be finished.
You get a firmer figure by picking the date you want the loan cleared, calculating the fixed payment that gets you there, and listing each month's payment until nothing is owed. Excel and Google Sheets can both do this, and setting it up takes about 25 minutes.
Take Hui Min (an invented example), a young earner with an education loan. She owes about S$20,000 to her mother's CPF Ordinary Account, and the CPF Board has sent her a repayment schedule. She has chosen to repay in cash. After lesson 5.3, she also decided to go a little faster than the schedule asks. In her lesson 4.4 budget, "Write your first-year budget", the loan line says S$250 (an example figure). She wrote that in before she had seen the terms.
Start a fresh sheet. At the top, give each of four inputs its own cell: what you owe today, the yearly interest rate, how many months you want to take, and any extra payment you plan, with the month it will go in.
Your latest statement shows what you owe. The yearly rate is in your loan agreement, or you can get it from the CPF Board. You choose the number of months, but it can only be shorter than the lender's schedule and never longer. The payment shown on the lender's schedule is the least you must pay each month. For a loan under the CPF Education Scheme, the CPF Board decides the longest repayment period allowed. For a bank loan, the loan agreement sets that limit.
Hui Min types in S$20,000 as the balance, 2.5% as the yearly rate (a figure used for illustration; the real rate is on your own statement) and 84 months, or seven years, as her target. She leaves the extra payment cell blank for now.
The PMT function works out the fixed monthly payment that pays off a loan in a set number of months. In an empty cell, enter =PMT(rate/12, months, -balance), with each part pointing at the matching input cell. You divide the rate by 12 because you pay monthly. The minus sign before the balance makes the answer come out as a positive number.
For Hui Min, =PMT(2.5%/12, 84, -20000) returns about S$259.78 a month. Over the seven years she would pay about S$1,822 in interest.
Compare the PMT result with the lender's own monthly figure. If your target period is the same length as the lender's schedule, the two should be close. Lenders calculate interest in slightly different ways, which explains small gaps. If the numbers differ, use the figure on the lender's statement.
Before you settle on a period, change the months cell a few times and compare the results. A longer period brings the monthly figure down, but you pay interest for more months, and with a CPF loan the money goes back into your parent's account more slowly. At eight years, Hui Min's payment would drop to about S$230.08 a month. She turns it down for both reasons and keeps seven years.
Under the inputs, make a table with five columns: month, opening balance, interest, principal and closing balance.
In the first row, the opening balance is what you owe today. From the second row on, it is the closing balance from the row above. Interest for the month is the opening balance x yearly rate / 12. Principal is what is left of the monthly payment after interest, so the payment minus the interest. The closing balance is the opening balance minus the principal. Enter each formula in the first row, then fill it down until the closing balance reaches zero.
Hui Min's first three rows look like this. In month 1 she pays S$41.67 interest and S$218.11 principal, which leaves a closing balance of S$19,781.89. Month 2 is S$41.21 interest and S$218.57 principal, leaving S$19,563.32. Month 3 is S$40.76 interest and S$219.02 principal, leaving S$19,344.30.
The interest part shrinks a little each month and the principal part grows by the same amount. The full table shows this shift over the whole loan. The last row should show a zero balance in the month you set as your target.
To plan an extra payment, add a sixth column for it. Then change the closing balance formula so it subtracts that column as well as the principal. Enter the lump sum in the month you expect to have the cash, such as part of an AWS or a bonus. Before you plan one, check that your buffer is in place and read the early repayment rules. Lesson 5.3 covers both.
Hui Min plans to put S$1,000 from her first AWS into the loan at the end of month 12, and she keeps paying S$259.78 every month. Her table now reaches zero in month 80 rather than month 84, and she pays about S$157 less in interest. She will decide on any more lump sums one bonus at a time, using the raise rule in lesson 7.2.
Go back to your budget and replace the guessed loan figure with the calculated one. You can do this in one of two ways. Round the PMT figure up to a convenient amount and type it into the loan repayment line. Or link the budget cell to the PMT cell, so the budget updates each time you change the target months. If the new figure is different from your old guess, change another budget line to cover the gap.
Hui Min rounds S$259.78 up to S$260, which is S$10 above her S$250 guess. To cover the extra S$10, she lowers her everyday spending line from S$1,923 to S$1,913.
Your schedule is complete when it has:
the four inputs in their own cells at the top a PMT result you have checked against the lender's figure a table that runs down to a zero balance on your target date any extra payment you plan, entered in its month the monthly amount moved into your budget
Finish your payoff schedule with a target end date, then copy the monthly amount into your budget sheet.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).