+ Reply to Thread
Results 1 to 7 of 7

Calculate Gross from Net using Scaled Tax Rates

  1. #1
    Registered User
    Join Date
    01-08-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Calculate Gross from Net using Scaled Tax Rates

    I have built a calulcator / formula that works out the net income from the gross salary using the following scaled tax rates
    Tax Rates :
    $0 to $6,000 0.00%
    $6,000 to $37,000 15.00%
    $37,001 to $80,000 30.00%
    $80,000 to $180,000 37.00%
    $180,001 45.00%

    I now want to be able to input the net salary and calculate the Gross Salary.

    I have attached a s/sheet with the gross to net formulas and a template for the net to gross

    This has me absolutely stumped and I have expended many many hours trying to resolve
    Any expertise and help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Forumla to calculate Gross Pay from Net Pay figure using Scaled Tax Rates

    So complicated to revert!!!
    Anyway, I do as described (follow file attached):
    * Gross to Net:
    Insert column I which is Added Tax
    Tax in C8:
    Please Login or Register  to view this content.
    Medical Levy in C9:
    Please Login or Register  to view this content.
    * Net to Gross:
    Just type Net in D17, we can see result.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-08-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Forumla to calculate Gross Pay from Net Pay figure using Scaled Tax Rates

    bebo021999
    This is fantastic and it works perfectly.
    You are wonderful.
    I would never have been able to do this. Very clever.
    Thanks
    Denis

  4. #4
    Registered User
    Join Date
    01-08-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Forumla to calculate Gross Pay from Net Pay figure using Scaled Tax Rates

    bebo021999

    I need your help again.

    After some testing of the calculator, I have discovered that t results in "#N/A" when an net figure below $16,400 or a net figure of $122,000 of greater is input.
    Every amount in between seems to work fine.

    I have tried to understand the formula and mathematical calculations but I am now completely lost.

    I have attached the s/sheet for your reference.

    I desperately need your expertise and help again

    Thanks

    Denis

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Calculate Gross from Net using Scaled Tax Rates

    Sorry that I forgot case Gross >180000. I recalculated again in file attached.
    To calculate Gross (G) with given Net (N), we dont know which tax rate should be applied for G.
    Therefore, go thru case to case:
    Case 1: 6000<G<18488:
    Tax=(G-6000)*0.15+0=G*0.15-900
    N = G-Tax = G - G*0.15 + 900 = G*0.85 + 900
    G=(N-900)/0.85
    ....
    Case 5

    With given Net, we have 5 cases of Gross. From which we can choose the right.

    For ex:
    Net = 62,334
    Gross =
    Case 1(6000<G<18488) : 72,775
    Case 2 (18488<G<37001): 73,574
    Case 3 (37001<G<80000): 81,582
    Case 4 (80000<G<180001): 81,762 ==> right result
    Case 5 (180001<G) : 67,071

    Hope it clear for you.
    Last edited by bebo021999; 01-25-2012 at 04:46 AM.

  6. #6
    Registered User
    Join Date
    01-08-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Calculate Gross from Net using Scaled Tax Rates

    bebo021999

    You are a genius - it works fine but I had to add <6000 (no tax) into the formula.

    Can you tell me how you calculated the:
    "900" "6450.3" "12050.3" "<18488" "26450.75" in the formulas.
    I am sure it has something to do with the ranges in the tax scale but I can't work it out.

    Again thank you so much for your help. It has made the calculator I am building so much better and user friendly.

    Denis

  7. #7
    Registered User
    Join Date
    07-09-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2011
    Posts
    1

    Re: Calculate Gross from Net using Scaled Tax Rates

    Hi All, I am trying to do this exact same thing only with the 2015 Tax rates. The only problem is I can't just swap the formula as I too don't know where bebo021999 determined the numbers

    Can you tell me how you calculated the:
    "900" "6450.3" "12050.3" "<18488" "26450.75" in the formulas.

    Anyway the new rates are as follows, any help would be greatly appreciated.

    0 18,200 0.00%
    18,201 37,000 19.00%
    37,001 80,000 32.50%
    80,001 180,000 37.00%
    180,001 10E+100 45.00%
    plus medicare levy 2.0%

+ 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