+ Reply to Thread
Results 1 to 8 of 8

Need formula to derive gross salary from net salary, using bracketed/tiered tax deductable

  1. #1
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Need formula to derive gross salary from net salary, using bracketed/tiered tax deductable

    EDIT: Title should read 'Need formula to derive gross salary from net salary, using bracketed/tiered tax deduc. table (tax deduction table)

    Hi all,

    With reference to the attached.

    I am looking for a formula to write in cell D4, that will be capable of taking the the 'net' salary in cell D3 and calculating what the gross salary should be, by calculating what the salary tax deductions are by using the tax brackets in the table below (cells B6 - H12).

    In columns L - Q I have inserted an example of how the salary tax brackets work, to assist members on here to understand better.

    Thanks in advance
    Attached Files Attached Files
    Last edited by STUARTXL; 08-24-2018 at 08:23 AM.

  2. #2
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: Need formula to derive gross salary from net salary, using bracketed/tiered tax deduct

    Nevermind, figured it out. Wonder if I can give myself rep....

    Thanks anyway, everyone.

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Need formula to derive gross salary from net salary, using bracketed/tiered tax deduct

    Mind sharing the solution?
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  4. #4
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: Need formula to derive gross salary from net salary, using bracketed/tiered tax deduct

    Hi Modytrane,

    Sorry, only just seen this request.

    Attached is the formula that solves my query. The only thing I see that could be improved (aside from the length of the formula) is the end section:
    INDEX($F$8:$F$12,MATCH(D3,$I$7:$I$11,1)

    This element of the formula attempts to return the tax rate of the highest bracket that the salary falls into, by comparing the net salary in cell D3 and comparing it to the 'maximum net salary' in column I.
    In the example in the sheet, assuming we have have a net salary of 3.039 million, that means that the highest bracket that the salary falls into is bracket 4 (the stated net salary exceeds the maximum net salary of bracket 3, but falls short of the maximum net salary of bracket 4, hence the net salary is somewhere in between i.e. falls somewhere in bracket 4), which has a tax rate of 22.5%. As you can see from the formula above though, I've had to re-align the INDEX and MATCH elements to achieve this, since I don't know how else to achieve this, but I'm sure this isn't the best way to achieve this result.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,392

    Re: Need formula to derive gross salary from net salary, using bracketed/tiered tax deduct

    Thanks for sharing!

    You can't give yourself rep, sorry, but you can select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: Need formula to derive gross salary from net salary, using bracketed/tiered tax deduct

    Haha, I know I can't give myself rep, I was jesting. Thread marked as solved

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,392

    Re: Need formula to derive gross salary from net salary, using bracketed/tiered tax deduct

    So was I.

  8. #8
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Need formula to derive gross salary from net salary, using bracketed/tiered tax deduct

    Thanks for sharing the solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 11-22-2017, 05:27 AM
  2. How to calculate Gross Salary from given Net Salary
    By Basab in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-17-2015, 01:15 AM
  3. [SOLVED] Daily Attendance Sheet of Employees for Gross Salary
    By it_electronics2000 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-17-2015, 01:00 AM
  4. [SOLVED] Need formula for prorating monthly salary based on hire/fire date and annual salary.
    By Excel_Help_Pls in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-06-2015, 12:56 AM
  5. which formular can i use to get gross salary if i know net salary
    By TsoaiQueen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2014, 06:21 PM
  6. From net salary to gross
    By blueocean84 in forum Excel General
    Replies: 12
    Last Post: 10-30-2013, 08:55 AM
  7. Net to gross salary
    By TK34 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-16-2013, 08:58 AM

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