Optimise your CPF retirement with this CPF Tracker spreadsheet tool
Your help is needed
You are reading this post because you have some questions about your CPF and are seeking answers for them.
I built this CPF Tracker spreadsheet tool 5 years ago to help me forecast what the Full Retirement Sum is likely to be when I am 55 and how much I would have in my Special Account by then.
I am planning to re-design this tool into a calculator that is much easier to use. Help me improve the tool by sharing the questions that you would like the new tool to answer.
How many years do I need to work to accumulate enough money in my CPF for retirement?
Should I transfer money from my Ordinary Account to my Special Account?
Can I work lesser years and have sufficient money to retire if I top up my Special Account with cash?
4 years ago, these were the questions I wanted to find answers for.
At that time, there weren’t any financial bloggers who had an answer for this so I decided to design my own spreadsheet. For the lack of creative cells in my head, I called it the CPF Tracker tool.
Seeing that the Seedly Facebook community has been asking the same questions in the past few months prompted me to make some changes to my CPF Tracker tool to make it more user friendly for others. It took some time, but it’s finally done!
Click here to view my CPF Tracker
Before you start, click on “File” and select “Make a copy…” to save a copy of this spreadsheet in your Google Drive.
Every week, I’ll be sharing practical tips and invaluable knowledge to guide you on your path to financial independence.
Monthly CPF Contributions
Let’s start with the Monthly CPF Contribution worksheet.
I started this CPF Tracker in 2015 at the age of 32. Feel free to change the year and age to suit you.
Decide on how long you plan to be working and your estimate monthly salary. Note that any monthly salary amount above $6,000 will not make any impact because CPF’s Ordinary Wage Ceiling is currently capped at $6,000.
Forecasting your Full Retirement Sum
Using the Full Retirement Sum for the last 4 years to predict the average yearly increments for Full Retirement Sum. You can extend the number of years on your own to forecast how your Full Retirement Sum is like.
If your Full Retirement Sum is different from mine (I reach age 55 in 2038), Update cell M15 in the CPF Calculations worksheet accordingly.
CPF Calculations
Next, click to the CPF Calculations worksheet to make a few changes.
Input the final figures in your CPF accounts for last year in Column M. The CPF OA, CPF SA and CPF MA worksheets will use these figures to forecast your CPF account figures in future.
If you have existing mortgage loans, enter your monthly mortgage loan repayment numbers in Column J. The CPF OA worksheet will make the respective deduction each month.
Transferring money from Ordinary Account to Special Account
Are you wondering if transferring money from your CPF Ordinary Account to your Special Account to accumulate a higher interest rate of up to 5% will help you reach your Full Retirement Sum faster?
In the CPF OA worksheet, I created something to simulate an answer for this question.
Go to Column P and enter the amount you plan to transfer from your Ordinary Account to your Special Account. The CPF Tracker will simulate what happens when you transfer the money in the month of December from OA to SA.
Topping up your CPF accounts with cash
What about topping up your CPF account with cash? Besides the tax benefits that comes with your CPF cash top up, how does it accelerate your retirement?
I’ve added something in all the 3 worksheets, CPF OA, CPF SA and CPF MA, to simulate an answer for this as well. Column O of all 3 worksheets helps to simulate performing a cash top up to the respective CPF account in the month of December.
How forecasting your CPF retirement can help you plan for retirement
With this CPF Tracker tool, I hope you are able to forecast how long you will need to work to meet your CPF retirement needs.
Ideally, you should aim to reach 55 with a positive figure in your CPF OA account after repaying your mortgage loan. Bulk of your Full Retirement Sum should be funded by your CPF SA account for optimum effectiveness because funding it with CPF OA will take too long. This is due to the difference in interest rates between both accounts.
Let me know what you think about my CPF Tracker and what you would like to see improved in the comments below.
Note: The main challenge in the CPF Tracker was calculating the interest rates at the end of each year. CPF has a slightly more complex way of calculating interests that I wasn’t able to replicate in my CPF Tracker. That said, I feel that the deviations were not that far for me to spend the effort to make additional changes.
Additional note: The CPF Tracker ignores the extra 1% for the first $60,000 in the CPF account because it’s just too tedious to code it in the spreadsheet formula.
Hello, thanks for this, it’s been super useful and I’m having a lot of fun planning different scenarios.
Two points for improvement
– Should have a section under CPF OA for lump sum payment of HDB?
– What is your assumed mortgage interest rate? it’s not mentioned
Thank you! I’ll see how I can add a lum sum area under CPF OA for lump sum payment. The only way to do this right now is to just manually update the balance at that month to reflect the lump sum payment.
As for the mortgage interest rate, I didn’t include something for it as I felt it really differs for everyone and I went the easy way, letting everyone just input their mortgage payment amount instead, disregarding the interest.
Do you think there’s a need to factor in mortgage interest?
Hi, does this formula take into account employer contribution? At which sheet/table does the employer contribution get calculated at?
Hi,
The Monthly CPF Contribution worksheet should encompass of both employer and employee contribution as part of the OA and SA calculation. There’s no separate worksheet or table for that.
Thanks Mickey ,for sharing the good xls. CPF MA circular referece error got the wrong value..How can I send you the xls to show the error.
Hi Raj, dropped you an email to clarify things. The template should be used in Google docs and not be downloaded.