If an employer wants to provide seven or so employees additional pay to offset a surprising tax of 10% or 15%, how would that employer calculate the amount of additional pay in Excel so that there is a net zero tax impact on the employee? For example if the surprising tax amount is $900 (or $6,000 * 15%), the employer could pay the employee an extra $900, but the $900 additional payment would be taxed at the 15%, so on and so forth. Using Excel how would I calculate the amount of pay in Excel so that there is a net zero tax impact on the employee in this scenario? Would I use a formula involving exponential decay? Would I use a formula that utilizes circular references? If so, how? Thanks.
Bonus pay to offset a costExponential Decay? Circular?
Posted 16 May 2018 - 07:51 PM
I have to admit I found this one a challenge at first. My first thought was to use Goal Seek or Solver to calculate the result. You can use these tools to ask Excel to find the result you want by modifying the values in other cells. I modeled your example:
In the above example I used Goal Seek to change the value of cell D2 until cell G2 equaled 6000.
You can see much more about Goal Seek and Solver in Expert Skills Session 5: What If Analysis and Security.
This is a good solution in 'one-off' situations where you don't need to calculate a lot of results and don't want to spend the time to write a complex formula, but not great if you need a formula that you can reuse for multiple records. Fortunately I also have a formula-based solution!
The calculation that you are trying to perform is known as "grossing up". The way to calculate it is to start with the value that you want to have at the end and then divide it by 1 minus the tax rate. In this example it would be 6000 (the value that you want the employee to have at the end) divided by 1-0.15 (or 0.85). The final formula being 6000/0.85=7058.823529 - the same result as you can see above.
This is a relatively simple mathematical formula that could be created using the skills taught in Essential Skills Session 2: Doing Useful Work with Excel (Lesson 2-12: Create your own formulas).
I have attached an example workbook containing this formula and the example shown above:
GrossUp.xlsx 10.18KB 44 downloads
I hope this is the solution you were looking for, but feel free to reply if you need any more help with this.
Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users