You will build a model of CPF used and accrued interest on a home over the years you expect to own it.
Kelvin's question after lesson 2.4 was simple: if they sold the flat in ten years, how much would go back into CPF? His CPF statement could show the figure for a home he already owned. For a home he was about to buy, he needed a model. This exercise builds one in a spreadsheet, and the same sheet works for a home you own today.
You need a spreadsheet, the current OA interest rate from cpf.gov.sg, and either your housing figures from your CPF statement or the expected figures for a purchase. The rate in the worked example is made up.
At the top of the sheet, make labelled cells for the CPF used at purchase (downpayment, stamp duty and legal fees together), the monthly instalment paid from CPF, the OA interest rate, and the number of years you expect to own the home before selling.
If you already own the home, start from today instead of the purchase date. Put the principal used so far and the accrued interest to date, both from your CPF statement, into two cells, and treat their total as your opening amount.
Kelvin and Mei's inputs, with example figures for the two of them together: S$60,000 of CPF at purchase, an instalment of S$1,800 a month fully from CPF, an example OA rate of 3%, and a planned sale after ten years. In real life CPF records each owner's usage separately, so if you buy with someone, either build one sheet each or note that the total splits between you.
Make one row per year of ownership, with five columns: year, opening amount, interest for the year, CPF used during the year, and closing amount.
The opening amount in year one is the CPF used at purchase. Interest is the opening amount times the OA rate. CPF used during the year is the monthly instalment times 12. The closing amount is the opening amount plus interest plus the year's CPF used, and it becomes the next row's opening amount.
This adds the year's instalments at the end of each year, so they earn no interest in the year they are paid. CPF's own calculation is more precise, but for planning a sale years away, the difference is small. Your CPF statement always has the exact figure for a home you own.
Kelvin and Mei's first rows: year one opens at S$60,000, earns S$1,800 of interest and adds S$21,600 of instalments, closing at S$83,400. Year two opens at S$83,400, earns S$2,502 and adds S$21,600, closing at S$107,502. Year three closes at S$132,327.06.
Add a column that keeps a running total of the principal, meaning the CPF used without any interest. At your expected sale year, the closing amount is the total refund, and the closing amount minus the principal is the accrued interest.
After ten years, Kelvin and Mei's principal is S$276,000: the S$60,000 at purchase plus S$21,600 a year for ten years. The closing amount is S$328,254.78, so the accrued interest is S$52,254.78. If they sold in year ten, about S$328,000 of the sale price would go back into their CPF accounts before any cash reached them.
Copy the rows into a second block and change only the CPF instalment. Kelvin and Mei try S$1,000 a month from CPF, with the other S$800 paid in cash.
In the second block, the principal after ten years is S$180,000, the closing amount is S$218,201.53, and the accrued interest is S$38,201.53. The refund is S$110,053.25 lower than in the all-CPF version. They paid S$96,000 more in cash over the ten years, and the difference between the two figures is the interest that cash would have earned at the OA rate, as lesson 2.4 explained.
If your sheet shows a refund difference larger than the cash you paid, that is expected. If it shows a difference smaller than the cash, check that you haven't added interest to the cash column by mistake.
Yours is done when it has labelled inputs with today's OA rate, one row per year to your expected sale, principal and refund columns, and two payment mixes side by side, each showing the refund and accrued interest at your sale year. Add one line under each block giving the cash you would have paid in that mix over the same years. That line, next to the refund, is what lets you judge the trade.
If the sale year is uncertain, add a cell for it and watch how the refund grows with each extra year of ownership. Many owners are surprised by how steeply it climbs in the later years, because the interest compounds on a growing balance.
Build the model for your home or a home you are planning to buy, and use your own sale year and payment mixes.
Build the model for your home or a planned one and write the refund due at your expected sale year under both payment mixes.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).