How to calculate CPF using Excel

CPF is the Singapore version of a pension fund which employers must contribute to the employees’ personal CPF account. Its calculation is pretty straightforward if you are calculating it for a single employee. But it becomes very time-consuming when we do it for many employees because of the many permutations based on age, salary and employee type. For example, the CPF contribution of employees aged below 55 years old vary based on the salary range given in the table below.

Performing the calculation for a single employee can be achieved by selecting the formula to use and substituting the Salary with the actual amount of salary. But this simple approach cannot be applied because we have different formulas for different pay scale.

In order to be able to look up the formula, we have to split the above formulas into2 components, the percentage component and the fixed amount component. Using the formula for the salary scale of “$500 to < $750” as an example, this is what we have done:

Create 2 tables with the following layout:

In one table, we will put in the precentage component of the formula and the other, the fixed amount component. The completed tables should look like this:

Name the tables CPF_v and CPF_f using named range. The v and f at the end of the name refers to variable component and fixed component.

Using VLOOKUP approximate match, we can find the salary scale the salary falls into and pick up the percentage and the fixed amount related to the salary. For example, the salary amount of $700 will find $500 as the largest value among those ($0, $50, $500) and lookup 77% as the percentage value and minus 300 as the fixed amount. Creating a formula using 2 VLOOKUP formulas would allow us to calculate the CPF contribution for $700.

The completed formula (with salary in A2) is

=VLOOKUP(A2,CPF_v,2)*A2+VLOOKUP

(A2,CPF_f,2)

The above formula uses column 2 (of both tables) which contained the CPF rates for employees below 50 years old. For those who are 50 and above, different rates applies. Work out the rates for each age category and place the results from column 3 onwards. The ages must be arranged in ascending order.

For the VLOOKUP formula to switch to other age group (changing the column index), we can make use of the MATCH formula. MATCH works the same way as VLOOKUP, except that it returns the position of the value in the range instead of the value in the cell. Using MATCH approximate setting, we can further modify the formula (with the age in B2 and the header of the table in Sheet1!$T$21:$Y$21) to

=VLOOKUP(A2,CPF_v,MATCH

(B2,Sheet1!$T$21:$Y$21,1))*A2+

VLOOKUP(A2,CPF_f,MATCH

(B2,Sheet1!$T$21:$Y$21,1))

With this formula, we can calculate the CPF of all employees irregardless of their salary and age. And the computation is instant and accurate.