You will build a model of the CPF used and the accrued interest on a home over the years you expect to own it.
If you sell a home you paid for partly with CPF, the CPF you used goes back into your CPF accounts with accrued interest. This refund is deducted from the sale price before you get any cash. If you already own the home, your CPF statement shows the refund figure. If you haven't bought yet, you have to estimate it with a model. The spreadsheet below works for both cases.
Kelvin and Mei are about to buy a flat together. After lesson 2.4, Kelvin asked how much would go back into CPF if they sold the flat in ten years. A CPF statement only shows that figure for a home already owned, and they hadn't bought yet, so he built a model. Every number in his sheet is the combined total for Kelvin and Mei.
Before you start, you need three things: a spreadsheet, the current OA interest rate from cpf.gov.sg, and your housing figures. If you own the home, the housing figures are on your CPF statement. If you're buying, use the amounts you expect to pay.
At the top of the sheet, put four inputs, each with a label next to it: CPF used at purchase, the monthly instalment paid from CPF, the OA interest rate, and the number of years you expect to own the home before selling. CPF used at purchase means the downpayment, stamp duty and legal fees added together.
Kelvin entered S$60,000 for CPF used at purchase. He entered S$1,800 for the monthly instalment, which they planned to pay entirely from CPF. He used 3% as the OA rate. That rate is made up for the example, so put today's rate from cpf.gov.sg in your own sheet. His last input was a sale after 10 years.
If you already own the home, start the model from today instead of the purchase date. Take the principal used so far and the accrued interest to date from your CPF statement. Add them together to get your starting figure.
Joint buyers have one more choice to make. CPF records each owner's usage separately. You can build one sheet per owner, or keep one sheet and note on it that the total splits between the owners.
Below the inputs, give each year its own row with five columns: year, opening amount, interest for the year, CPF used during the year, and closing amount.
In year one, the opening amount is the CPF used at purchase. For a home you already own, it is the principal plus accrued interest to date. From year two on, it is the closing amount from the row above. Interest for the year is the opening amount x the OA rate. CPF used during the year is the monthly instalment x 12. The closing amount is the opening amount plus the interest plus the CPF used during the year, and it carries down as the next row's opening amount. Once the first row works, copy the formulas down to the sale year.
In Kelvin's sheet, S$1,800 x 12 gives S$21,600 of instalments a year. Year 1 starts at S$60,000, adds S$1,800 of interest and S$21,600 of instalments, and ends at S$83,400. Year 2 starts at S$83,400, adds S$2,502 of interest and S$21,600, and ends at S$107,502. Year 3 ends at S$132,327.06.
The sheet adds each year's instalments at the end of that year, so those instalments earn no interest in the year they are paid. CPF calculates the figure more precisely. For a sale several years away, the difference is small. If you already own the home, your CPF statement always has the exact amount.
Add a running total of principal next to the yearly rows. Principal is the CPF used, without any interest. Kelvin's principal starts at S$60,000 and goes up by S$21,600 each year.
In the sale-year row, the closing amount is the total refund. The closing amount minus the principal is the accrued interest. With every instalment paid from CPF, Kelvin's principal after ten years is S$276,000 (S$60,000 plus S$21,600 a year for ten years). His closing amount is S$328,254.78, so the accrued interest is S$52,254.78. If they sell in year ten, about S$328,000 of the sale price goes back to CPF.
If you don't know when you'll sell, put the sale year in a cell of its own. Each time you change it, you can see how much the refund grows for each extra year you own the home. Many owners are surprised by how steeply the refund rises in the later years. It rises faster because the interest compounds on a balance that keeps growing.
To compare payment mixes, copy the yearly rows into a second block and change only the CPF instalment. Whatever part of the monthly instalment no longer comes from CPF is paid in cash. Under each block, add one line showing the cash paid in that mix over the same number of years.
Kelvin's second block pays S$1,000 a month from CPF and S$800 a month in cash. At year ten, the principal is S$180,000, the closing amount is S$218,201.53 and the accrued interest is S$38,201.53. Compared with the all-CPF block, the refund is S$110,053.25 lower. Over the ten years, they pay S$96,000 more in cash.
The refund goes down by more than the extra cash paid. The excess is the interest that cash would have earned at the OA rate, as lesson 2.4 explained. Your own sheet should show the same pattern. If the refund difference comes out smaller than the cash paid, check whether interest was added to the cash column by mistake.
With the refund, accrued interest and cash paid side by side, you can judge whether paying more cash now is worth a smaller refund later. Before you treat the model as finished, check that it has:
labelled input cells, with today's OA rate one row for each year up to the expected sale a principal column and a refund column two payment mixes side by side, each showing the refund and accrued interest at the sale year one line under each block giving the cash paid in that mix over the same years
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 one you plan to buy. Write down the refund due in your expected sale year for both payment mixes.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).