459,268 Members | 1,309 Online
Need help? Post your question and get tips & solutions from a community of 459,268 IT Pros & Developers. It's quick & easy.

# How to Calculate Personal income Tax Based on Bands?

 P: 4 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 Apr 6 '20 #1
8 Replies

 Expert 100+ P: 106 First, modify your table to hold numeric values: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers YourSalary = 20000   TaxRate = DMax("[TaxRate]", "[Tax]", "[Salary] <= " & Str(YourSalary) & "") YourTax = YourSalary * TaxRate    YourTax -> 7000 Apr 7 '20 #2

 Expert Mod 10K+ P: 12,430 @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. Apr 7 '20 #3

 Expert 100+ P: 106 Perhaps. There is no single method for calculating tax. The questioneer must provide the rules for the case in question. Apr 7 '20 #4

 P: 4 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 Apr 9 '20 #5

 P: 4 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? Apr 9 '20 #6

 P: 4 Sorry 20,000*35%-1,500=5,500 Apr 9 '20 #7

 Expert 100+ P: 106 If so, just deduct the 1500: Expand|Select|Wrap|Line Numbers 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. Apr 10 '20 #8

 Expert 100+ P: 106 If you modify the table like this: you can run a function to use the method mentioned by Rabbit: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers ? 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  Apr 10 '20 #9