Pension contribution

[owen.cxy]

Hi, I’m using MS Excel 2007 and nothing else. I have a (column “C†of each
individual’s age) and a (column “J†of each individual’s wage). How do I
calculate the contribution of each individual based on their age and salary?

Below are the criterias.

A) Age: 35 years and below
1) Wage: less than or equal to $50 Contribution = $0
2) Wage: more than $50 but less than $500  Contribution = 5% of the
difference between (the individual’s wage and $50)
3) Wage: more than $500  Contribution = 10% of the individual’s wage up to
the first $500 + 10% of the individual’s additional wage.
E.g. If the wage is $800, Contribution = 0.1*500 + 0.1*300($800-$500)

B) Age: (Above 35 years) to (50 years and below)
1) Wage: less than or equal to $50 Contribution = $0
2) Wage: more than $50 but less than $500  Contribution = 2% of the
difference between (the individual’s wage and $50)
3) Wage: more than $500  Contribution = 5% of the individual’s wage up to
the first $500 + 5% of the individual’s additional wage.
E.g. If the wage is $800, Contribution = 0.02*500 + 0.05*300($800-$500)

C) Age: Above 50 years
1) Wage: less than or equal to $50 Contribution = $0
2) Wage: more than $50 but less than $500  Contribution = 1% of the
difference between (the individual’s wage and $50)
3) Wage: more than $500  Contribution = 2% of the individual’s wage up to
the first $500 + 2% of the individual’s additional wage.

Please help me..Thank you so much

 

[Joel]

I think your descriptions are wrong. See if the UDF code below makes sense

Function contribution(Age, Wage)

contribution = 0
If Wage > 50 Then
Select Case Age
Case Is <= 35
contribution = 0.05 * (Wage - 50)
Case 35 To 50
contribution = 0.02 * (Wage - 50)
Case Is > 50
contribution = 0.01 * (Wage - 50)
End Select
End If

If Wage > 500 Then

Select Case Age
Case Is <= 35
contribution = contribution + (0.1 * (Wage - 500))
Case 35 To 50
contribution = contribution + (0.05 * (Wage - 500))
Case Is > 50
contribution = contribution + (0.02 * (Wage - 500))
End Select
End If

End Function

 

[owen.cxy]

Hi, the code below makes sense. However how do i input in as an excel formula
for each cell relative to the person? I only know how to start a formula with
an "=" sign infront of a function e.g. =SUM. How do i input these into a cell?

 

[Joel]

You need to put the code into VBA. From worksheet type Alt-F11

Then in VBA menu go to menu Insert - Module. copy my code and paste into
module1.


then in worksheet simply
=contribution(Age, Wage)

really
=contribution(42, 5000)

or
=contribution(A1, A2)

You may have to change the security setting for macros to medium security
level

from worksheet menu (2003) Tools - Macro - Security - Medium

 

[owen.cxy]

Hi,

After attempting to edit the code to my needs, i have problem understanding
the code. Maybe you can see what i mean from this URL and understand what are
the criterias needed:

http://mycpf.cpf.gov.sg/NR/rdonlyres/9F114FAD-9ED0-479E-BC80-875DEBFA37DB/0/AnnexB.pdf

columns 3,5,7,9,11,13 is what i need to compute and display in the table. I
already have a Age column "C" and Wage column "D".

 

[Joel]

Some of the formulas could be simplified but it is easier to keep track of
the code if it exactly matches the table. check my code to make sure it is
right. Could of made some typos.

Function contribution(Age, Wage)

contribution = 0

If Wage > 500 And Wage <= 750 Then
Select Case Age
Case Is <= 35
contribution = 0.48 * (Wage - 500)
Case 35 To 50
contribution = 0.48 * (Wage - 500)
Case 50 To 55
contribution = 0.432 * (Wage - 500)
Case 55 To 60
contribution = 0.3 * (Wage - 500)
Case 60 To 65
contribution = 0.18 * (Wage - 500)
Case Is > 65
contribution = 0.12 * (Wage - 500)
End Select
End If

If Wage > 750 And Wage <= 1200 Then
Select Case Age
Case Is <= 35
contribution = 120 + (0.24 * (Wage - 750))
Case 35 To 50
contribution = 120 + (0.24 * (Wage - 750))
Case 50 To 55
contribution = 108 + (0.216 * (Wage - 750))
Case 55 To 60
contribution = 75 + (0.15 * (Wage - 750))
Case 60 To 65
contribution = 45 + (0.09 * (Wage - 750))
Case Is > 65
contribution = 30 + (0.06 * (Wage - 750))
End Select
End If

If Wage > 1200 And Wage <= 1500 Then
Select Case Age
Case Is <= 35
contribution = 120 + (0.24 * (Wage - 750))
Case 35 To 50
contribution = 120 + (0.24 * (Wage - 750))
Case 50 To 55
contribution = 108 + (0.216 * (Wage - 750))
Case 55 To 60
contribution = 75 + (0.15 * (Wage - 750))
Case 60 To 65
contribution = 45 + (0.09 * (Wage - 750))
Case Is > 65
contribution = 30 + (0.06 * (Wage - 750))
End Select
End If

If Wage > 1500 Then
Select Case Age
Case Is <= 35
contribution = (0.2 * 900) + (0.2 * (Wage - 900))
Case 35 To 50
contribution = (0.2 * 900) + (0.2 * (Wage - 900))
Case 50 To 55
contribution = (0.18 * 810) + (0.18 * (Wage - 810))
Case 55 To 60
contribution = (0.125 * 562.5) + (0.125 * (Wage - 562.5))
Case 60 To 65
contribution = (0.075 * 337.5) + (0.075 * (Wage - 337.5))
Case Is > 65
contribution = (0.05 * 225) + (0.05 * (Wage - 225))
End Select
End If
End Function

 

[owen.cxy]

Thank you for your prompt response,
another thing i would like to ask is i'm using Excel 2007 and i do not know
how to configure the macros,
I do not know how to apply this function, do i type "=contribution" into the
column that i want it to display to?
You quoted this below, what does this mean, does it mean 3 contribution
function?
"then in worksheet simply
=contribution(Age, Wage)
really
=contribution(42, 5000)
or
=contribution(A1, A2)"


Then how does the function find out where to get the age and wage, do i need
to input the column name into the function?

 

[Joel]

The UDF (User Defined function) function works exactly like any other
worksheet function except you call it =contribution() instead of a function
like =sum(). So the results will appear in the cell where the formula is
located and the age will be a cell in column c and the wage will be a cell in
colun d.

=contribution(C3,D3)