Program PAYE Tax using if statement

[rogers]

Hello,
I want you to help me with the programming syntax using IF Statement for this income rate below:

Under, but not above Le150,000 Nil
The next Le 300,000 @ 15%


The next Le 300,000 @ 20%
Excess over Le 750,000 @ 30%

[ItsMe]

Perhaps a Select Case statement would be more suitable. You can replace the message box with some calculations or just assign a caonstant to a field/control.

Dim dblLe As Double
dblLe = Me.ControlLE.Value 'The control name that represents Le value

Select Case dblLe
Case Is < 150000
MsgBox "Less than 150,000"
Case 150000 To 299999
MsgBox "between 150,000 and 299,999"
Case 300000 To 749999
MsgBox "between 300,000 and 749,999"
Case Else
MsgBox "Greater than 749,999"
End Select

[rogers]

Thanks ItsMe,
I want to calculate Paye tax , eg

If an employee receives a gross salary of K160, 000.00 per month his/her tax will be calculated as follows:
Taxable income Rate Tax
Total K160, 000.00 - -
First K20, 000.00 0% K0.00
Next K5, 000.00 15% K750.00
Excess K135, 000.00 30% K40, 500.00
Total tax payable is K0.00 + K750.00 + K40, 500.00 = K41, 250.00

I need assistance to code the above

[ItsMe]

Considering the values in post #1, this seems to work.

Dim dblLe As Double
dblLe = Me.ControlLE.Value 'The control name that represents Le value
Dim dblTaxOwed As Double
Dim dblPerc As Double

Select Case dblLe
Case Is < 150000
    MsgBox "Less than 150,000"
Case 150000 To 299999
    dblPerc = 0.15
    MsgBox "between 150,000 and 299,999"
Case 300000 To 749999
    dblPerc = 0.2
    MsgBox "between 300,000 and 749,999"
Case Else
    dblPerc = 0.3
    MsgBox "Greater than 749,999"
End Select
dblTaxOwed = dblLe * dblPerc
    MsgBox "dblTaxOwed = " & dblTaxOwed

[ssanfu]

This is my method to calculate the tax

'---------------------------------------------------------------------------------------
' Procedure : CalcTax
' Author    : ssanfu
' Date      : 12/13/2013
' Purpose   : Calculate tax
'
'Example
'    Taxable income Rate Tax
'    Total K160, 000.00 - -
'    First K20,000.00 at 0% = K0.00
'    Next K5,000.00 at 15% = K750.00
'    Excess K135,000.00 at 30% =  K40, 500.00
'   Total tax payable is K0.00 + K750.00 + K40, 500.00 = K41, 250.00
'---------------------------------------------------------------------------------------
Function CalcTax(pSalary As Double) As Double
    Const FirstAmt As Double = 20000
    Const SecondAmt As Double = 5000

    Const FirstPerCent = 0
    Const SecondPercent = 0.15
    Const ThirdPercent = 0.3

    Dim dSalary As Double
    Dim dblTaxOwed As Double
    Dim dblPerc As Double
    Dim TaxAmt As Double

    'init variables
    CalcTax = 0
    dSalary = pSalary
    TaxAmt = 0

    'first tax amount
    If dSalary >= FirstAmt Then
        TaxAmt = FirstAmt * FirstPerCent
        dSalary = dSalary - FirstAmt
    End If
    'second tax amt
    If dSalary >= SecondAmt Then
        TaxAmt = TaxAmt + (SecondAmt * SecondPercent)
        dSalary = dSalary - SecondAmt
    End If
    
    'third tax amount - the excess
    If dSalary > 0 Then
        TaxAmt = TaxAmt + (dSalary * ThirdPercent)
    End If

    'total tax
    CalcTax = TaxAmt

End Function

Paste the code in a standard module.

In the immediate window, enter

? CalcTax(160000.00)

then press ENTER
41250 should be returned


In a query, if there is a field named "GrossSalary", you would have

Taxes:CalcTax(GrossSalary)

When the query is executed, the column "Taxes" would display(should) "41250".

[rogers]

Thanks SSANFU,
I think this would solve the problem. But the question is how or where would i execute this code.
I tried copy and paste in access query and got an error msg.

[ssanfu]

In the query, what is the name of the salary field?

Taxes:CalcTax(XXXXXX)

To add the tax amount, in the query, replace "XXXXXX" with the name of your salary field.

If you can't get the query to return the taxes, post the SQL of the query.

[rogers]

SELECT [tblActiveStaff].[StaffNumber], [tblActiveStaff].[SSNumber],
[FirstName] & " " & [Othernames] & " " & [Surname] AS FullName,


[tblActiveStaff].[Category], [tblActiveStaff].[Designation],


[tblActiveStaff].[EmploymentStatus], [tblActiveStaff].[BasicSalary],


[tblActiveStaff].[Month], [tblActiveStaff].[ServiceStatus],
nz([Salary_Advance],0) AS SalAdvance, nz([OtherAllowances],0) AS OtherAllow,


nz([OtherDeductions],0) AS OtherDeducts, [tblLoanCurrent].[LoanID],


[tblLoanCurrent].[FinalRepay], [tblLoanCurrent].[LoanStatus],

nz([MthlyRepay],0) AS MthlyRpymet, [tblLoanCurrent].[HousingLoan] AS [Housing Loan],
[tblLoanCurrent].[PersonalLoan], [tblLoanCurrent].[VehicleLoan] AS [Car Loan],
[tblLoanCurrent].[WelfareLoan] AS [Household Loan], [BasicSalary]*0.25 AS Rent,

IIf([EmploymentStatus]="Confirmed",[BasicSalary]*0.25,0) AS RentAdvance, 0 AS Transport, [BasicSalary]*0.05 AS SocialSecurity,
[BasicSalary]+[Rent]+[Transport]+[OtherAllow]+[OVERTIME]-[SocialSecurity] AS Earnings,


[Earnings]-160000 AS Taxable, IIf([Taxable]<=125000,0,


IIf([Taxable]>125000 And [Taxable]<=375000,([Taxable]-125000)*0.2,


IIf([Taxable]>375000 And [Taxable]<=625000,50000+([Taxable]-375000)*0.25,


112500+([Taxable]-625000)*0.3))) AS PayeTax,


This is the code have been using and its work well. The text highlighted in blue is where am having problems. The problem the values and formula calculation have change .
These are the new values :
Under, but not above Le150,000 Nil
The next Le 300,000 @ 15%
The next Le 300,000 @ 20%
Excess over Le 750,000 @ 30%

And so for eg, if gross monthly emolument is 1525,00.00
Tax on 150,000.00 at nil% = 0.00
Tax on 300,000.00 at 15% = 45,000.00
Tax on 300,000.00 at 20% = 60,000.00
Tax on 775,000.00 at 30% =232,500.00

Total tax due is 337,500.00


So please help me with the sql syntax that i could replace with the highlighted text above that could calculate based on the parameters given above.

[ssanfu]

The blue in the previous post would be replaced with

IIf([Taxable] <= 150000, 0, IIf([Taxable] > 150000 And [Taxable] <= 450000, ([Taxable] - 150000) * 0.15, IIf([Taxable] > 450000 And [Taxable] <= 750000, 45000 + ([Taxable] - 450000) * 0.2, 105000 + ([Taxable] - 750000) * 0.3))) AS PayeTax,

If you pasted the following revised code in a standard module

---------------------------------------------------------------------------------------
' Procedure : CalcTax
' Author    : ssanfu
' Date      : 12/15/2013
' Purpose   : Calculate tax
'
'Example
'    Taxable income Rate Tax
'    Total K152,500.00 - -
'    Under, but not above Le150,000 Nil
'    The next Le 300,000 @ 15% = 45,000.00
'    The next Le 300,000 @ 20% = 60,000.00
'    Excess over Le 750,000 @ 30% = 232,500.00
'   Total tax due is 337,500.00
'---------------------------------------------------------------------------------------
Function CalcTax(pSalary As Double) As Double
    Const FirstAmt As Double = 150000
    Const SecondAmt As Double = 300000
    Const ThirdAmt As Double = 300000
    Const FourthAmt As Double = 175000

    Const FirstPerCent = 0
    Const SecondPercent = 0.15
    Const ThirdPercent = 0.2
    Const FourthPerCent = 0.3

    Dim dSalary As Double
    Dim dblTaxOwed As Double
    Dim dblPerc As Double
    Dim TaxAmt As Double

    'init variables
    CalcTax = 0
    dSalary = pSalary
    TaxAmt = 0

    'first tax amount
    If dSalary < FirstAmt Then
        TaxAmt = 0
    Else
        TaxAmt = FirstAmt * FirstPerCent
        dSalary = dSalary - FirstAmt

        'second tax amt
        If dSalary < SecondAmt Then
            TaxAmt = TaxAmt + (dSalary * SecondPercent)
        Else
            TaxAmt = TaxAmt + (SecondAmt * SecondPercent)
            dSalary = dSalary - SecondAmt

            'third tax amount - the excess
            If dSalary < ThirdAmt Then
                TaxAmt = TaxAmt + (dSalary * ThirdPercent)
            Else
                TaxAmt = TaxAmt + (ThirdAmt * ThirdPercent)
                dSalary = dSalary - ThirdAmt

                'fourth tax amount - the excess
                If dSalary >= FourthAmt Then
                    TaxAmt = TaxAmt + (dSalary * FourthPerCent)

                End If
            End If
        End If
    End If

    'total tax
    CalcTax = TaxAmt

End Function

You could call it in your query using:

CalcTax([Taxable]) as PayeTax,


Please check using several different "Taxable" amounts to ensure "PayeTax" is correct

[rogers]

Hello,
Am here again, but this time the paye tax bracket has change.

Under, but not above Le300,000 Nil
The next Le 300,000 @ 15%
The next Le 300,000 @ 20%
Excess over Le 900,000 @ 30%

kindly help me with the code like you did the other time.
Thanks in advance