CPFWorks - a CPF calculator
CPF is well know in Singapore as Central Provident Fund. It is like a pension scheme that is used in other countries. The amount a salaried worker and his company contribute to the account is dependent on the salary of a worker, the age as well as the type of worker (local or permanent resident). Both the employer and employee will each contribute about 20% of the gross salary.
The Central Provident Fund will be allocated into 3 different accounts: Ordinary, Medisave and Special accounts. Each one of them has a different interest rates and are meant to meet the needs of the employee under different circumstances. One of the main reason is retirement.
The contribution is computed based on a complicated formula which is dependent on the age of the worker, the amount he earned as well as the group (local, 1st, 2nd or 3rd year permanent resident). So to compute my first staff pay, I downloaded the free autoexcel program from CPF board. However, I decided to develop my own payroll program 2 months later because the auto-eXcel program was not robust enough to meet my needs. I have to constantly monitor the staff age and when she turned from 1st year PR to 2nd year PR. The program does not do that for me. I managed to find a simpler way to calculate the amount to contribute instead of using multiple nested IF for the calculation. The formula involved a combination of INDEX, MATCH and LOOKUP.
In this second version, we have added a new worksheet to store the employees' records. There is no need to manually copy the employee into the calculation worksheet. You just have to enter the last day of the payroll month and the employees who are required to be paid will show up calculation worksheet. All you have to do is to enter the extra work details such as overtime hours, allowances, etc and the amounts will be computed automatically for each employee. The enhancement will definitely make it easier for the user to manage the payroll at a small fraction of the cost (a few thousands dollars) you have to pay for those proprietary payroll software.
We are giving away a free version that can be used to compute the CPF of 10 employees. To try out this free version, just enter your contact details below. Try it out, I am sure you will love what you see.
Send me the free
Keep up with our work!
PS: We need you to click on the link in our confirmation email to confirm your name and email address s we can verify you are a real person. Please look out for it.
New! CommentsHave your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here's how...
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft