
Excel: Calculate Sliding Scale Tax/Commission. Calculate Bracket Tax/Commission
See Also: Calculate Sliding
Scale Tax Custom Excel Function VBA.
Download Working Examples
Calculating tax, or commission that is based on a sliding scale, or by bracket,
can be complicated. The formula below is one that can be used using Excel's built
in function/formulas. That is, the IF function/formula and the SUM function/formula.
The formula that can be used is;
=IF(A5>Level4Tax,SUM((A5Level4Tax *Level4TaxRate,Level3TaxAmount*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate),IF(A5>Level3Tax,SUM((A5Level3Tax)*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate),IF(A5>Level2Tax,SUM((A5Level2Tax)*Level2TaxRate,Level1TaxAmount*Level1TaxRate),SUM((A5Level1Tax)*Level1TaxRate)),0)))
I have color coded the formula for easier reading. As you can see, this formula uses Named Ranges for easier reading and modification. A named range can be created by selecting the cell, then typing the name wanted in the Name Box (left of formula bar) and pushing Enter.
To make this easier to read, I have placed the cell names next to their named cell. In the formula above, only the grey cells are being used. The last column (Amount of Tax Payable on) is the result of subtracting the Level*Tax (one row down) from the Level*Tax on the same row. For example, $13,000.00 (Level1TaxAmount) is derived by subtracting Level2Tax (25000) from Level1Tax (12000). That is:
=Level2TaxLevel1Tax
If you prefer, these key numbers, can become Named Constants as opposed to Named Ranges . For example, to create the Named Constant: Level1Tax you would go to Insert>Name>Define and type: Level1Tax in the Names in workbook: box, then: =12000 in the Refers to: box, then click Add.
Using Excel Vlookup Formula
There is another way, which some may prefer where the VLOOKUP function/formula is used. This method relies on some "Quick deductions" being pre calculated and placed at the end of the white & grey table shown above.
=A3*VLOOKUP(A3,$B$14:$G$18,3)VLOOKUP(A3,$B$14:$G$18,6)
This is best seen by Downloading Working Example Thanks to Albert Tsang for this excellent method.
We can go one step further toward simplifying the calculation by using Named Formulas for each tax level calculation. After doing this, we can then use;
=IF(A1>Level4Tax,Level4TaxCalc,IF(A1>Level3Tax,Level3TaxCalc,IF(A1>Level2Tax,Level2TaxCalc,IF(A1>Level1Tax,Level1TaxCalc,0))))
Here are the steps to achieve this. BTW, this method can also be used
to overcome the 7 nested
IF Function limitation .
1) Create Named Ranges, or Named Constants that will hold the figures needed. See
screen shot above.
2) Place you Gross pays in cell A1 down.
3) Select cell B1 and go to Insert>Name>Define.
4) In the Names in workbook: box type: Level1TaxCalc Then, in the
Refers to: box type: =SUM((A1Level1Tax)*Level1TaxRate) Then click
Add. **Note how we have referred to cell A1. This now makes the
Named Formula (Level1TaxCalc) always look on the same row in the immediate column
to the left for the gross pay.
5) Repeat step 4 using the names and formulas shown below;
Level2TaxCalc
=SUM((A1Level2Tax)*Level2TaxRate,Level1TaxAmount*Level1TaxRate)
Level3TaxCalc
=SUM((A1Level3Tax)*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate)
Level4TaxCalc
=SUM((A1Level4Tax)*Level4TaxRate,Level3TaxAmount*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate)
It is important to know that Level*TaxCalc will ALWAYS look on the
same row, but left column for the gross pay figure.
See Also: Calculate Sliding
Scale Tax Custom Excel Function VBA.
Download Working Examples
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates