How to Calculate Personal income Tax Based on Bands?

[Lulie]

The table band is as follows

No. Salary Range New Tax Rate
1 0 – *600 * Non-Taxable
2 601 *– 1,650 10%
3 1,651 – 3,200 15%
4 3,201 – 5,250 20%
5 5,251* – 7,800 25%
6 7,801 – 10,900 30%
7 Over 10,900 35%

Is there Any one can help me? .If i get 20,000 How much I pay tax

[cactusdata]

First, modify your table to hold numeric values:

Id    Salary        TaxRate
1    0,00        0,00%
2    601,00        10,00%
3    1.651,00    15,00%
4    3.201,00    20,00%
5    5.251,00    25,00%
6    7.801,00    30,00%
7    10.900,00    35,00%

Then look up the tax rate and perform the calculation:

YourSalary = 20000
 
TaxRate = DMax("[TaxRate]", "[Tax]", "[Salary] <= " & Str(YourSalary) & "")
YourTax = YourSalary * TaxRate 
 
YourTax -> 7000

[Rabbit]

@cactus, that is not how taxes are calculated. They are calculated at each step, only the remaining amount of salary leftover is calculated at the higher rate.

[cactusdata]

Perhaps. There is no single method for calculating tax.
The questioneer must provide the rules for the case in question.

[Lulie]

Cactusdata

When i calculate in Excl The tax is 5,500 .Because the tax system is progressive tax system.

Rabbit. It is like that you understand it well.


Thank you so much all

[Lulie]

Income per Month Tax Rate Deduction Rate
Up to 600.00 0% -
601.00 - 1,650.00 10% 60.00
1,651.00 - 3,200.00 15% 142.50
3,201.00 - 5,250.00 20% 302.50
5,251.00 - 7,800.00 25% 235.00
7,801.00 - 10,900.00 30% 955.00
Over 10,900.00 35% 1,500.00

Taxable salary is 20,000 per month. The calculation in xcel is
20,000*35*1,500 = 5,500.00

Cactusdata

I think Your formula is working if you deduct 1,500(deduction rate).Kindly include the deduction rate column in the formula and show me where i can put this formula .Can use it in query?

[Lulie]

Sorry 20,000*35%-1,500=5,500

[cactusdata]

If so, just deduct the 1500:

YourSalary = 20000
 
TaxRate = DMax("[TaxRate]", "[Tax]", "[Salary] <= " & Str(YourSalary) & "")
YourTax = YourSalary * TaxRate - 1500
 
YourTax -> 5500 

Of course, add a condition to set tax to zero if calculated as negative.

[cactusdata]

If you modify the table like this:

you can run a function to use the method mentioned by Rabbit:

Public Function CalculateTax(ByVal Salary As Currency) As Currency
 
    Dim Records As DAO.Recordset
 
    Dim Sql         As String
    Dim Tax         As Currency
    Dim ThisTax     As Currency
    Dim ThisRange   As Currency
    Dim LastRange   As Currency
    Dim LastRate    As Currency
 
    Sql = "Select Salary, TaxRate From Tax Order By 1"
    Set Records = CurrentDb.OpenRecordset(Sql)
 
    Debug.Print "Level", "Delta", "Rate", "Tax", "Total tax"
    Do
        ThisRange = Records!Salary.Value
        If ThisRange > Salary Then
            If LastRange > 0 Then
                ThisRange = Salary
            Else
                Exit Do
            End If
        End If
        ' Tax of range.
        ThisTax = (ThisRange - LastRange) * LastRate
        Tax = Tax + ThisTax
        Debug.Print ThisRange, ThisRange - LastRange, LastRate, ThisTax, Tax
        LastRange = ThisRange
        LastRate = Records!TaxRate.Value
        Records.MoveNext
    Loop Until Records.EOF
    Records.Close
 
    ' Top tax.
    ThisTax = (Salary - LastRange) * LastRate
    Tax = Tax + ThisTax
    Debug.Print " ~", Salary - LastRange, LastRate, ThisTax, Tax
 
    CalculateTax = Tax
 
End Function

Output will be similar to:

? CalculateTax(20000)
Level         Delta         Rate          Tax           Total tax
 600           600           0             0             0 
 1650          1050          0,1           105           105 
 3200          1550          0,15          232,5         337,5 
 5250          2050          0,2           410           747,5 
 7800          2550          0,25          637,5         1385 
 10900         3100          0,3           930           2315 
 ~             9100          0,35          3185          5500 
 5500