Payroll - Complex If Statement for PAYE (1 Viewer)

[mtn]

I am trying to build a small payroll and so far everything has been OK until now. The challange I have is calculating the PAYE (Pay As You Earn) personal tax from the employee total earning.

I my country the tax rate for PAYE is applied on a graduated scale as shown below:

- First $30,000 at 5%
- Next $30,000 at 10%
- Next $50,000 at 15%
- Next $50,000 at 20%
- Above $160,000 at 25%

So what I want is for my textbox "txtPAYE to evaluate "txtEarning" based on those garduated scale with an IF statement. I don't know how possible that is. It would have been very strait forward for me if it was for a tax that has a constant rate.

Any idea would be greatly appreciated.

Thanks.

 

[dkinley]

Complex, indeed.

Updated: Removed entry because I realized the fallacy in my thought process until I saw it typed. Will work on this a bit more before posting final proposal ...

Apologies,
-dK

 

[dkinley]

Okay ... here is something ... validate the logic tho ...

Dim dEarning As Double
Dim dPaye As Double
 
dEarning = Nz(Me.txtEarning, 0)
dPaye = 0
 
'is there anything in earnings?
If dEarning > 0 Then
 
    'assign first 30000 @ 5% (if less than compute)
    If dEarning >= 30000 Then
        dPaye = 1500
    Else
        dPaye = dPaye + (dEarning * 0.05)
    End If
    dEarning = dEarning - 30000
 
    'anything left in earnings?
    If dEarning > 0 Then
 
        'assign second 30000 @ 10%
        If dEarning >= 30000 Then
            dPaye = 4500
        Else
            dPaye = dPaye + (dEarning * 0.1)
        End If
        dEarning = dEarning - 30000
 
        'anything left in earnings?
        If dEarning > 0 Then
 
            'assign third 50000 @ 15%
            If dEarning >= 50000 Then
                dPaye = 12000
            Else
                dPaye = dPaye + (dEarning * 0.15)
            End If
            dEarning = dEarning - 50000
 
            'anything left in earnings?
            If dEarning > 0 Then
 
                'assign fourth 50000 @ 15%
                If dEarning >= 50000 Then
                    dPaye = 22000
                Else
                    dPaye = dPaye + (dEarning * 0.2)
                End If
                dEarning = dEarning - 50000
 
                'anything left in earnings?
                If dEarning > 0 Then
 
                    'assign remainder @ 25%
                    dPaye = dPaye + (dEarning * 0.25)
                End If
            End If
        End If
    End If
End If
 
'populate the control with PAYE calculation
Me.txtPAYE = dPaye

-dK

 

[jzwp22]

I came up with a custom function. I created a table where you would enter the tax info. If the tax rates change (as they always do), you would just need to make the changes in the table. You would not need to edit the code. A sample database with the table and the function is attached. You can call the function whenever you need it in a query, form or report; you just need to supply the salary to the function. I included a simple form that illustrates the use of the function.

 

[vbaInet]

The OP needs to explain further what those NEXT rates actually mean. It could be a calculation that takes into consideration the scale for each closing financial year for the first 5 years, or it could be for the first 3 years and afterwards a fixed 25%.

We need more information. A scenario would be helpful.

 

[mtn]

Thank you everyone. Thanks for the effort, time and ideas.

Both samples worked with the same result giving me exactly what I want.

The figures and logical ideas I am working with is from here: http://www.firs.gov.ng/personal-income-tax.aspx. I don't know if I was able to relate that well in my post.

I hope to post my DB here in the next few days for your comments.

Thanks once again.

 

[gemma-the-husky]

i have to say you are crazy trying to write a payroll system

its obviously not the UK, but in the UK there are tax free calculations, certain deductions/pay elements that apply pre/post tax - there are special rules for directors (VP's)

if you get it wrong the company pays

its not just tax. there are pensions/national insurance/social security -any number of additional things to consider.

----------
unless you have a much simpler legal environment, i would buy a payroll system, without doubt.

 

[fkeller]

Okay ... here is something ... validate the logic tho ...

Dim dEarning As Double
Dim dPaye As Double
 
dEarning = Nz(Me.txtEarning, 0)
dPaye = 0
 
'is there anything in earnings?
If dEarning > 0 Then
 
    'assign first 30000 @ 5% (if less than compute)
    If dEarning >= 30000 Then
        dPaye = 1500
    Else
        dPaye = dPaye + (dEarning * 0.05)
    End If
    dEarning = dEarning - 30000
 
    'anything left in earnings?
    If dEarning > 0 Then
 
        'assign second 30000 @ 10%
        If dEarning >= 30000 Then
            dPaye = 4500
        Else
            dPaye = dPaye + (dEarning * 0.1)
        End If
        dEarning = dEarning - 30000
 
        'anything left in earnings?
        If dEarning > 0 Then
 
            'assign third 50000 @ 15%
            If dEarning >= 50000 Then
                dPaye = 12000
            Else
                dPaye = dPaye + (dEarning * 0.15)
            End If
            dEarning = dEarning - 50000
 
            'anything left in earnings?
            If dEarning > 0 Then
 
                'assign fourth 50000 @ 15%
                If dEarning >= 50000 Then
                    dPaye = 22000
                Else
                    dPaye = dPaye + (dEarning * 0.2)
                End If
                dEarning = dEarning - 50000
 
                'anything left in earnings?
                If dEarning > 0 Then
 
                    'assign remainder @ 25%
                    dPaye = dPaye + (dEarning * 0.25)
                End If
            End If
        End If
    End If
End If
 
'populate the control with PAYE calculation
Me.txtPAYE = dPaye

-dK

As a very new access user, where would you be putting this code and how. I understand the code just not where/how to put it into a query or form or report??? Thanks for any answer.

 

[FuzMic]

Hi

PAYE seems to suggest it is in Malaysia. The Inland Revenue Board of Malaysia has a formula with numerous parameters to compute it.

 

[fkeller]

I came up with a custom function. I created a table where you would enter the tax info. If the tax rates change (as they always do), you would just need to make the changes in the table. You would not need to edit the code. A sample database with the table and the function is attached. You can call the function whenever you need it in a query, form or report; you just need to supply the salary to the function. I included a simple form that illustrates the use of the function.

I see the form but not the table, or function?? can you post again please