Calculate Tax on Salary VBA Function

Written by kazamraza no comments

Use the keyboard shortcut, Alt + F11 to open the Visual Basic Editor (VBE).

Add Module from Insert menu.

Copy the following lines of code and paste into Module.

Syntax


Function TaxableAmount(Salary)
    If Salary <= 400000 Then
        TaxableAmount = 0
    ElseIf Salary > 400001 And Salary <= 500000 Then
        TaxableAmount = (Salary - 400000) * 0.02
    ElseIf Salary > 500001 And Salary <= 750000 Then
        TaxableAmount = 2000 + ((Salary - 500000) * 0.05)
    ElseIf Salary > 750001 And Salary <= 1400000 Then
        TaxableAmount = 14500 + ((Salary - 750000) * 0.1)
    ElseIf Salary > 1400001 And Salary <= 1500000 Then
        TaxableAmount = 79500 + ((Salary - 1400000) * 0.125)
    ElseIf Salary > 1500001 And Salary <= 1800000 Then
        TaxableAmount = 92000 + ((Salary - 1500000) * 0.15)
    ElseIf Salary > 1800001 And Salary <= 2500000 Then
        TaxableAmount = 137000 + ((Salary - 1800000) * 0.175)
    ElseIf Salary > 2500001 And Salary <= 3000000 Then
        TaxableAmount = 259500 + ((Salary - 2500000) * 0.2)
    ElseIf Salary > 3000001 And Salary <= 3500000 Then
        TaxableAmount = 359500 + ((Salary - 3000000) * 0.225)
    ElseIf Salary > 3500001 And Salary <= 4000000 Then
        TaxableAmount = 472000 + ((Salary - 3500000) * 0.25)
    ElseIf Salary > 4000001 And Salary <= 7000000 Then
        TaxableAmount = 597000 + ((Salary - 4000000) * 0.275)
    ElseIf Salary > 7000001 Then
        TaxableAmount = 1422000 + ((Salary - 7000000) * 0.3)
    End If
End Function

 

Example
◢ A B C D E
1 12 Months Salary Formula Annual Tax Monthly Tax  
2 1,020,000 =TaxableAmount(A2) 41,500 3,458  
3          
4          

 

.