+ Reply to Thread
Results 1 to 5 of 5

Progressive tax calculation formula, in reverse

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    Vientiane, Laos
    MS-Off Ver
    Excel 2010
    Posts
    5

    Progressive tax calculation formula, in reverse

    I came across the solution to this post - http://www.excelforum.com/excel-gene...x-formula.html - and was just wondering how to adjust the formula so that it calculates the correct tax starting from the net amount.

    Say for example, an employee is paid a fixed, after-tax salary amount, and the employer is responsible for covering the tax - the salary payment needs to be "grossed-up" to calculate the correct amount of tax.

    With 'tax brackets' as follows:

    Taxable income Tax on income (2011 – 2012) Tax rate
    $0 – $6,000 Nil 0%
    $6,001 – $37,000 15c for each $1 over $6,000 15%
    $37,001 – $80,000 $4,650 plus 30c for each $1 over $37,000 30%
    $80,001 – $180,000 $17,550 plus 37c for each $1 over $80,000 37%
    Over $180,000 $54,550 plus 45c for each $1 over $180,000 45%

    The formula to calculate tax, starting from the gross amount, is:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Any help greatly appreciated!

  2. #2
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Progressive tax calculation formula, in reverse

    This may help, I built it to go both ways so you can input the gross and calculate the net, it then takes the net and calculates the gross, so the end two figures should always match!

    The two tax tables, calculate from each other, so change the main one and the lower one should update!
    880340-progressive-tax-calculation-formula-in-reverse.xlsx
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  3. #3
    Registered User
    Join Date
    11-30-2012
    Location
    Vientiane, Laos
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Progressive tax calculation formula, in reverse

    Thanks squiggler, but it doesn't really resolve what I'm looking for. Your net to gross calculation is based on the gross to net calculation (please correct if I have understood wrong). I need to perform the net to gross calculation without the gross being known.

    It can be done, I have a complicated "IF"-based formula which does it, but it produces some incorrect calculations.

  4. #4
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Progressive tax calculation formula, in reverse

    The two parts are separate, the only reason they are both there is for the verification, you can just enter the gross in the bottom and it will come back with an answer. No part of the Gross to net is used in the net to gross formula.

    **EDIT** It looks like I accidentally referenced C8 in the second tax formula, this should have been C16 if you change them, you can delete the first 3 formulas and the second 3 will still work!


    The problem with the original method is that you have to hard code the values which is bad practice, using the table makes it easy to input the values, the second table is then calculated from the first table. Should tax rules change a simple change in the first table will then be reflected in the formula!

    Lookup is used as it makes the formula simpler to read an use in the long run!
    Last edited by squiggler47; 11-30-2012 at 01:26 PM.

  5. #5
    Registered User
    Join Date
    11-30-2012
    Location
    Vientiane, Laos
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Progressive tax calculation formula, in reverse

    Thanks Squiggler, correct! Point taken on the use of hard-coded values, this was how the formula was posted in another thread, so I have left it as is for simplicity. I plan on adapting the formula/method for my purposes, and will incorporate a table so it can be easily updated.

    I was thinking about this after I posted and had a rough idea of how the calculation should work, but you've certainly made things easier for me.

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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