Custom Function to Calculate Sliding Scale Tax

< Back to Search results

 Category: [Excel]  Demo Available 

Custom  Function to Calculate Sliding Scale Tax


Excel VBA: Calculate Sliding Scale Tax. Calculate Bracket Tax


Excel VBA Custom Function: Calculate Sliding Scale Tax/Commission.

Built in Excel Functions. Download Working Examples 

The codes below are for  custom excel functions that have been written to calculate tax based on a sliding scale, or bracket tax. The first one is based entirely on the built-in method that can be used to calculate sliding scale tax and requires named ranges , or constants. The second is more self contained and requires no named ranges, or constants.

To use either codes, go to Tools>Macro>Visual Basic Editor (Alt+F11) and then Insert>Module and paste the code near the bottom of this page.

The first would be used in any cell like: =TaxPayable(A2) where A2 has the gross amount.

The second would be used in any cell like: =Tax_Payable(A2,12000,22%,13000,25000,30%,7000,32000,38%,13000,45000,45%) where A2 has the gross amount.

To make this easier to read, I have placed the cell names next to their named cell. In the first code below, 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:


Function TaxPayable(Amount As Currency) As Currency

Select Case Amount

   Case Is > Range("Level4Tax")

        TaxPayable = ((Amount - Range("Level4Tax")) * Range("Level4TaxRate")) + _

                            Range("Level3TaxAmount") * Range("Level3TaxRate") + _

                            Range("Level2TaxAmount") * Range("Level2TaxRate") + _

                            Range("Level1TaxAmount") * Range("Level1TaxRate")


   Case Is > Range("Level3Tax")

        TaxPayable = ((Amount - Range("Level3Tax")) * Range("Level3TaxRate")) + _

                            Range("Level2TaxAmount") * Range("Level2TaxRate") + _

                            Range("Level1TaxAmount") * Range("Level1TaxRate")



   Case Is > Range("Level2Tax")

        TaxPayable = ((Amount - Range("Level2Tax")) * Range("Level2TaxRate")) + _

                            Range("Level1TaxAmount") * Range("Level1TaxRate")


   Case Is > Range("LowTax")

        TaxPayable = ((Amount - Range("Level1Tax")) * Range("Level1TaxRate"))


   Case Else

        TaxPayable = 0

  End Select


End Function

=TaxPayable(A2) where A2 has the gross amount

Function Tax_Payable(GrossAmount As Currency, _
L1_TaxStart As Currency, L1_TaxPercentage As Currency, _
Optional L2_TaxStart As Currency, Optional L2_TaxPercentage As Currency, _
Optional L3_TaxStart As Currency, Optional L3_TaxPercentage As Currency, _
Optional L4_TaxStart, Optional L4_TaxPercentage As Currency)

Dim L1TaxStart As Currency, L2TaxStart As Currency, _
L3TaxStart As Currency, Level4Tax As Currency

L1TaxStart = L2_TaxStart - L1_TaxStart
L2TaxStart = L3_TaxStart - L2_TaxStart
L3TaxStart = L4_TaxStart - L3_TaxStart
Level4Tax = L4_TaxStart

    With WorksheetFunction
        Select Case GrossAmount

           Case Is > Level4Tax
                Tax_Payable = .Sum((GrossAmount - L4_TaxStart) _
                    * L4_TaxPercentage, L3TaxStart * L3_TaxPercentage, _
                                    L2TaxStart * L2_TaxPercentage, L1_TaxPercentage * L1TaxStart)
            Case Is > L3_TaxStart
                Tax_Payable = .Sum((GrossAmount - L3_TaxStart) * _
                    L3_TaxPercentage, L2TaxStart * L2_TaxPercentage, _
                                    L1TaxStart * L1_TaxPercentage)
            Case Is > L2_TaxStart
                Tax_Payable = .Sum((GrossAmount - L2_TaxStart) * _
                    L2_TaxPercentage, L1TaxStart * L1_TaxPercentage)
            Case Is > L1_TaxStart
                Tax_Payable = .Sum((GrossAmount - L1_TaxStart) _
                    * L1_TaxPercentage)
           Case Else
                Tax_Payable = 0
          End Select
    End With
End Function



  • A1 houses Gross pay.

  • 12000 (11999.99 and below is tax free) is where level 1 tax starts. 0.22 (22%) is level 1 tax percentage.

  • 25000 is where level 2 tax starts. 0.30 (30%) is level 2 tax percentage.

  • 32000 is where level 3 tax starts. 0.38 (38%) is level 3 tax percentage.

  • 45000 (top tax bracket) is where level 4 tax starts. 0.45 (45%) is level 4 tax percentage.

Download Working Examples


See also:

Index to Excel VBA Code
Select Case Statement in Excel VBA
Send Emails From Excel
Working With Shapes In Excel VBA
Create a Sheet Index of Excel Worksheets


See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)