By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,268 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
8 Replies


cactusdata
Expert 100+
P: 106
First, modify your table to hold numeric values:

Expand|Select|Wrap|Line Numbers
  1. Id    Salary        TaxRate
  2. 1    0,00        0,00%
  3. 2    601,00        10,00%
  4. 3    1.651,00    15,00%
  5. 4    3.201,00    20,00%
  6. 5    5.251,00    25,00%
  7. 6    7.801,00    30,00%
  8. 7    10.900,00    35,00%
Then look up the tax rate and perform the calculation:

Expand|Select|Wrap|Line Numbers
  1. YourSalary = 20000
  2.  
  3. TaxRate = DMax("[TaxRate]", "[Tax]", "[Salary] <= " & Str(YourSalary) & "")
  4. YourTax = YourSalary * TaxRate 
  5.  
  6. YourTax -> 7000
Apr 7 '20 #2

Rabbit
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

cactusdata
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

cactusdata
Expert 100+
P: 106
If so, just deduct the 1500:

Expand|Select|Wrap|Line Numbers
  1. YourSalary = 20000
  2.  
  3. TaxRate = DMax("[TaxRate]", "[Tax]", "[Salary] <= " & Str(YourSalary) & "")
  4. YourTax = YourSalary * TaxRate - 1500
  5.  
  6. YourTax -> 5500 
Of course, add a condition to set tax to zero if calculated as negative.
Apr 10 '20 #8

cactusdata
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
  1. Public Function CalculateTax(ByVal Salary As Currency) As Currency
  2.  
  3.     Dim Records As DAO.Recordset
  4.  
  5.     Dim Sql         As String
  6.     Dim Tax         As Currency
  7.     Dim ThisTax     As Currency
  8.     Dim ThisRange   As Currency
  9.     Dim LastRange   As Currency
  10.     Dim LastRate    As Currency
  11.  
  12.     Sql = "Select Salary, TaxRate From Tax Order By 1"
  13.     Set Records = CurrentDb.OpenRecordset(Sql)
  14.  
  15.     Debug.Print "Level", "Delta", "Rate", "Tax", "Total tax"
  16.     Do
  17.         ThisRange = Records!Salary.Value
  18.         If ThisRange > Salary Then
  19.             If LastRange > 0 Then
  20.                 ThisRange = Salary
  21.             Else
  22.                 Exit Do
  23.             End If
  24.         End If
  25.         ' Tax of range.
  26.         ThisTax = (ThisRange - LastRange) * LastRate
  27.         Tax = Tax + ThisTax
  28.         Debug.Print ThisRange, ThisRange - LastRange, LastRate, ThisTax, Tax
  29.         LastRange = ThisRange
  30.         LastRate = Records!TaxRate.Value
  31.         Records.MoveNext
  32.     Loop Until Records.EOF
  33.     Records.Close
  34.  
  35.     ' Top tax.
  36.     ThisTax = (Salary - LastRange) * LastRate
  37.     Tax = Tax + ThisTax
  38.     Debug.Print " ~", Salary - LastRange, LastRate, ThisTax, Tax
  39.  
  40.     CalculateTax = Tax
  41.  
  42. End Function
Output will be similar to:

Expand|Select|Wrap|Line Numbers
  1. ? CalculateTax(20000)
  2. Level         Delta         Rate          Tax           Total tax
  3.  600           600           0             0             0 
  4.  1650          1050          0,1           105           105 
  5.  3200          1550          0,15          232,5         337,5 
  6.  5250          2050          0,2           410           747,5 
  7.  7800          2550          0,25          637,5         1385 
  8.  10900         3100          0,3           930           2315 
  9.  ~             9100          0,35          3185          5500 
  10.  5500 
Apr 10 '20 #9

Post your reply

Sign in to post your reply or Sign up for a free account.