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,

• automatically upgrade the staff to the next age category,
• automatically upgrade the staff (with PR status) from one group to another,
• calculate the amount employer and employee need to contribute to the fund,
• automatically calculate the community fund (CDAC, MBMF, SINDA, ECF),
• calculate the amount of Skill Development Fund to contribute,
• generate the file for electronic submission to the Central Provident Fund board.

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.