You will build a month-by-month payoff schedule for your education debt.
Hui Min knows what she owes now: about S$20,000 to her mother's Ordinary Account, in this example, with a repayment schedule from the CPF Board. She has decided to pay in cash and, after lesson 5.3, to pay a little faster than the schedule requires. What she does not have is a number. Her budget from lesson 4.4 has S$250 pencilled in for the loan, a guess she made before she knew the terms.
This exercise replaces the guess with a schedule. You will work out the monthly amount for a payoff date you choose, see every month's balance, and link the result to your budget. It takes about 25 minutes in Excel or Google Sheets. The figures are made-up examples.
Put four inputs at the top of a sheet, each in its own cell:
Balance today, from your latest statement Yearly interest rate, from your agreement or the CPF Board Target number of months to pay it off Planned extra payment, if any, and the month it will go in
Hui Min enters a balance of S$20,000 and an interest rate of 2.5% a year. That rate is used here for illustration only, and her statement is where the real figure comes from. She chooses a target of seven years, which is 84 months.
One rule about the target: it can be sooner than your lender's schedule, never later. The schedule's monthly amount is the least you must pay. For the CPF Education Scheme, the CPF Board sets the longest repayment period; for a bank loan, the agreement does.
The PMT function works out the fixed monthly payment that clears a loan in a set number of months. In a spare cell, type:
=PMT(rate/12, months, -balance)
using your cells for each part. The rate is divided by 12 because payments are monthly. The minus sign in front of the balance makes the answer come out as a positive number.
For Hui Min, =PMT(2.5%/12, 84, -20000) gives about S$259.78 a month. Over seven years she would pay about S$1,822 in interest. If she chose eight years instead, the payment would fall to about S$230.08, but she would pay interest for longer, into her mother's account more slowly. She keeps seven.
Check your result against the lender's own figure. If your target matches their schedule, the numbers should be close. Small differences come from the way lenders work out interest, and their statement is the one that counts.
Below the inputs, make five columns: month, opening balance, interest, principal and closing balance. Row 1 starts with the balance today. Then:
Interest is the opening balance times the yearly rate divided by 12. Principal is the monthly payment minus the interest. Closing balance is the opening balance minus the principal. The next row's opening balance is the previous row's closing balance. Fill the formulas down until the balance reaches zero.
Hui Min's first three months, rounded to the cent: in month 1, interest is S$41.67 and principal S$218.11, leaving S$19,781.89. In month 2, interest is S$41.21 and principal S$218.57, leaving S$19,563.32. In month 3, interest is S$40.76 and principal S$219.02, leaving S$19,344.30. Each month, a little less goes to interest and a little more to the balance. Seeing that shift is the point of the table.
Add a sixth column for extra payments, and subtract it in the closing balance formula. Put any planned lump sum in the month you expect to pay it, such as part of an AWS or bonus.
Hui Min plans to put S$1,000 from her first AWS into the loan at the end of month 12. With that one payment, and the same S$259.78 a month, the balance reaches zero in month 80 instead of month 84, and she pays about S$157 less in interest. Any further extra payment is a decision for each bonus, under the raise rule in lesson 7.2.
Before you plan an extra payment, check your buffer and any early repayment rules, as lesson 5.3 set out.
Finally, connect the schedule to the budget from lesson 4.4, Write your first-year budget. Round the PMT result up to a convenient figure and put it in the loan repayment line, or link the cell directly so the budget updates if you change the target.
Hui Min rounds up to S$260. That is S$10 more than her guess, so her everyday spending line drops from S$1,923 to S$1,913. Small, but now the number is real.
A finished schedule has the four inputs, a PMT result you have checked against the lender, a table that runs to a zero balance on your target date, any planned extra payment in place, and the monthly amount carried into your budget.
Complete the payoff schedule with a target end date and copy the monthly amount into your budget sheet.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).