Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Calculate Sliding Scale Tax/Commission

| | Information Helpful? Why Not Donate.

Excel: Calculate Sliding Scale Tax/Commission. Calculate Bracket Tax/Commission

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((A5-Level4Tax *Level4TaxRate,Level3TaxAmount*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate),IF(A5>Level3Tax,SUM((A5-Level3Tax)*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate),IF(A5>Level2Tax,SUM((A5-Level2Tax)*Level2TaxRate,Level1TaxAmount*Level1TaxRate),SUM((A5-Level1Tax)*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:

=Level2Tax-Level1Tax

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((A1-Level1Tax)*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((A1-Level2Tax)*Level2TaxRate,Level1TaxAmount*Level1TaxRate)

Level3TaxCalc
=SUM((A1-Level3Tax)*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate)

Level4TaxCalc
=SUM((A1-Level4Tax)*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 Add-ins 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

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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