Build a CPF projection to retirement under three scenarios, and a dated list of CPF actions.
Kelvin, 32, has finished every earlier piece of the course: a contribution worksheet, a housing model, a top-up plan, a hurdle rate decision, a map of accounts at 55 and a CPF LIFE comparison. The next job is to pull those six pieces into one spreadsheet, and you do the same with your own.
The spreadsheet is the capstone project of the course. Set aside about an hour, and more if any earlier piece still needs finishing. Once built, it takes your OA, SA and MediSave forward year by year to 55, forms the Retirement Account (RA) and grows that RA to payout age under three scenarios. For each scenario it gives an estimated CPF LIFE payout. At the bottom sits a list of things to do over the next twelve months, each with a date. Use the three scenarios you picked in Lesson 8.1, "Project your balances to retirement in three scenarios".
Open a new workbook and give all your inputs a single tab. Put your current balances and salary there, with the housing figures from the model you built in Lesson 2.5 and the top-up plan from Lesson 3.5. Your yearly contributions come from the contribution worksheet in Lesson 1.4. Add the current rates and limits, copied from cpf.gov.sg, and type the date you copied them in the cell beside them.
If you choose to work at today's prices, add an inflation assumption to the tab and set each rate to the nominal rate minus inflation.
Kelvin's tab shows S$40,000 in the OA, S$15,000 in the SA and S$25,000 in MediSave (example figures). Their share of the flat takes S$30,000 from CPF this year and then S$900 a month. Kelvin works at today's prices, with a Basic Healthcare Sum (BHS) of S$70,000 and a Full Retirement Sum (FRS) of S$240,000. The OA rate is 0.5%, which is a made-up 3% less 2.5% inflation. The SA, MediSave and RA rate is 2.5%, which is a made-up 5% less the same 2.5% inflation.
Give each year its own row and fill it with the formulas from Lesson 6.4, "Map your accounts at 55 under three scenarios". Four rules have to sit inside those formulas.
MediSave stops at the BHS, and anything above it moves on to the next account. Cash top-ups to the SA end once the SA reaches the FRS, which is also where CPF rules stop them. Housing needs a check every year: when the OA does not hold enough for that year's instalment, set the OA payment to zero and enter the amount you would need to find in cash in a cash shortfall column. That column records, for each year the OA falls short, what you would have to pay in cash. The last rule applies at 55. Form the RA the way Lesson 6.1 sets out, and send any SA above the retirement sum across to the OA.
After 55, grow the RA at its own rate each year until payout age. Kelvin leaves out any contributions after 55, which keeps the sheet simpler and the answer on the cautious side.
When one set of rows runs correctly, copy it into three blocks, one for each scenario.
Kelvin's base case has no top-ups, and the SA hits the FRS at 52. By 55 the SA and OA together hold S$414,803.00. The RA takes S$240,000 of that, so S$414,803.00 - S$240,000 = S$174,803.00 moves to the OA.
In the top-up case, Kelvin adds S$3,000 a year from age 34 until the SA reaches the FRS at 49, which comes to S$42,000 of top-ups in total. The RA at 55 is still S$240,000. The difference shows up in the OA, which ends at S$233,710.53 compared with S$174,803.00. When the SA would have reached the FRS anyway, topping it up adds nothing to payouts, and the extra money that moves to the OA grows at the lower OA rate.
In the stress case, a more expensive flat and a career break leave Kelvin's OA empty at 40. From 40 to 55 the cash shortfall column adds up to about S$25,359.57 that Kelvin would have to pay in cash for instalments. The SA reaches the FRS later, at 54, and S$30,021.85 left over goes to the OA.
With no contributions after 55, the RA in all three cases grows to about S$307,220.29 at today's prices by 65.
Take each scenario's RA balance at payout age and enter it in the CPF LIFE estimator from Lesson 7.4. At today's prices, the payout ranges the CPF Board publishes for today's retirement sums give a fair idea of what each level would pay. The higher your RA balance when you join CPF LIFE, the higher your payout, but the balance is not the only thing that counts. Two members with the same RA can get different payouts, because men receive higher payouts than women, since men have a shorter average life expectancy. See how the CPF Board calculates payouts on cpf.gov.sg. For Kelvin, all three scenarios are for the same member and reach the same RA at 65, so all three give the same CPF LIFE estimate.
Next, draw a chart of total CPF against age with a line for each of the three scenarios and a horizontal line at the retirement sum.
Then go through the results and work out what each one means you should do, and put a month against every action. A result that surprises you is worth having, because it can change your plans. Kelvin changes two decisions. The planned SA top-ups are dropped, since the SA reaches the FRS without them and the money would only end up in the OA at the lower rate. The next savings goal becomes an instalment reserve: cash equal to six months of Kelvin's share of the flat, set aside to pay instalments, for example during a career break. The stress case is what showed that the real risk is finding cash for instalments during a career break. The top-up for Kelvin's mother stays in the plan.
Kelvin's next twelve months, by month:
October: make a new CPF nomination and get it witnessed. November: check whether Kelvin's mother qualifies for matching, and check the IRAS relief limits. December, third week: pay S$2,000 into Kelvin's mother's Retirement Account. January: do the yearly CPF check and put the new rates into the workbook. March: flat completion. Check the CPF used and the HPS cover on the statement. June: once the buffer is full, start the cash instalment reserve of six months of Kelvin's share. September: review whether to aim for the Enhanced Retirement Sum later, and whether to invest any OA money under the conditions in Lesson 4.4.
Your workbook counts as finished when it contains dated inputs, three projection blocks that run to payout age with the RA formed at 55, a cash shortfall column, an estimated CPF LIFE payout for each scenario, a chart, and a twelve-month action list with a month on every line.
Build the projection with your own figures, chart the three scenarios, and write your action list for the next twelve months.
Build the projection, chart the three scenarios, and write your CPF action list for the next twelve months with a date against each item.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).