+ Reply to Thread
Results 1 to 6 of 6

Calculation of tax based on tax slabs

  1. #1
    Registered User
    Join Date
    05-06-2004
    Posts
    7

    Calculation of tax based on tax slabs

    Dear All,

    I have a typical problem. actually we need to calculate tax on a total income where the tax rate would change depending upon the tax slab in which the total taxable income falls. Tax would be calculated on the basis of following

    Slab No. Range Tax rate
    1 $0-$500 0%
    2 $500-$1000 10%
    3 $1000-$1500 15% plus $50
    4 $1500-$2000 20% plus $125
    5 >$2000 30% plus $225

    Now the problem is that either I write a very lengthy "if" formula (which we have to rewrite if the tax slab changes). also this is not a one tme excercise, since tax has to be calculated in the same worksheet at 4-5 places and the taxable income would also change if other components are changed. Can you please suggest some way out???

    Thanks in advance.

    Vikesh

  2. #2
    pinmaster
    Guest
    Hi
    First,create a table for your tax brackets somewhere in your workbook like this:

    A-B-C
    0-0-0
    500-10%-0
    1000-15%-50
    1500-20%-125
    2000-30%-225

    then select your table and hit CTRL+F3 and type taxbrackets or anything you want, then use this formula:
    =A1*VLOOKUP(A4,taxbrackets,2,1)+VLOOKUP(A4,taxbrackets,3,1)

    Hope that helps!
    JG

  3. #3
    pinmaster
    Guest
    Oops...
    Slight correction to the formula

    =A1*VLOOKUP(A1,list3,2,1)+VLOOKUP(A1,list3,3,1)

    Sorry about that!
    JG

  4. #4
    pinmaster
    Guest
    Hi, it's me again.
    Forgot to ask, in your example 500 falls between 2 brackets, 0 to 500 and 500 to 1000, which bracket would it fall into? In my suggestion it falls between 500 and 1000 so if your income is 500 the tax would 50, if that is not correct then change 500, 1000, 1500 and 2000 to 500.01, 1000.01...etc. so that 500 would not be taxable but 500.01 would be.

    Again, hope that helps!
    JG

  5. #5
    Registered User
    Join Date
    05-06-2004
    Posts
    7
    Thanks for the reply. Thought I haven't used the formula u suggested, however I apologies for the ambiguity I left in my question. Actually, the tax bracket would work as follows:

    If the taxable income is $650, then though it falls under slab 2, however the tax would be computed as under-

    upto $500- no tax, and for the balance $150 since it falls in slab 2 hence $150*10%=$15. Therefore, the total tax liability would be $0+$15=$15.

    Similarly if the taxable income is $1100, then tax would be calculated as under:

    upto $500- no tax, for next $500-tax=($500*10%)=$50 and for the balance of $100 tax would be ($100*15%)=$15. Hence the total tax payable would be ($0+$50+$15)=$65.

    Hope u understand the problem and will help me out.

    Regards,

    Vikesh

  6. #6
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    The first part of the tax payable can be calculated using the following formula...

    =SUMPRODUCT(--(A1>{500,1000,1500,2000}),(A1-{500,1000,1500,2000}),{0.1,0.05,0.05,0.1})

    It's not clear as to how the second part of the tax is calculated. If it's progressive...

    =SUMPRODUCT(((A1>{500,1000,1500,2000})*(A1-{500,1000,1500,2000})*{0.1,0.05,0.05,0.1})+((A1>{500,1000,1500,2000})*{0,50,125,225}))

    ...otherwise...

    =SUMPRODUCT(--(A1>{500,1000,1500,2000}),(A1-{500,1000,1500,2000}),{0.1,0.05,0.05,0.1})+LOOKUP(A1,{0,0;500,0;1000,50;1500,125;2000,225})

    Hope this helps!

    Quote Originally Posted by vikesh
    Thanks for the reply. Thought I haven't used the formula u suggested, however I apologies for the ambiguity I left in my question. Actually, the tax bracket would work as follows:

    If the taxable income is $650, then though it falls under slab 2, however the tax would be computed as under-

    upto $500- no tax, and for the balance $150 since it falls in slab 2 hence $150*10%=$15. Therefore, the total tax liability would be $0+$15=$15.

    Similarly if the taxable income is $1100, then tax would be calculated as under:

    upto $500- no tax, for next $500-tax=($500*10%)=$50 and for the balance of $100 tax would be ($100*15%)=$15. Hence the total tax payable would be ($0+$50+$15)=$65.

    Hope u understand the problem and will help me out.

    Regards,

    Vikesh

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1