Custom Function to Calculate Sliding Scale Tax

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Excel VBA: Calculate Sliding Scale Tax. Calculate Bracket Tax

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download

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

See Also: Calculate Sliding Scale Tax 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:

=Level2Tax-Level1Tax

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(Amount As Currency, L1_Tax As Currency, L1_Tax_Rate As Currency, _
L1_Taxable_Amount As Currency, Optional L2_Tax As Currency, Optional L2_Tax_Rate As Currency, _
Optional L2_Taxable_Amount As Currency, Optional L3_Tax As Currency, Optional L3_Tax_Rate As Currency, _
Optional L3_Taxable_Amount As Currency, Optional L4_Tax As Currency, Optional L4_Tax_Rate As Currency) As Currency

Select Case Amount
   Case Is > L4_Tax
        Tax_Payable = (Amount - L4_Tax) * L4_Tax_Rate + L3_Taxable_Amount * L3_Tax_Rate + _
                            L2_Taxable_Amount * L2_Tax_Rate + L1_Taxable_Amount * L1_Tax_Rate
                            
   Case Is > L3_Tax
        Tax_Payable = (Amount - L3_Tax) * L3_Tax_Rate + L2_Taxable_Amount * L2_Tax_Rate + _
                            L1_Taxable_Amount * L1_Tax_Rate
                            
                            
   Case Is > L2_Tax
            Tax_Payable = (Amount - L2_Tax) * L2_Tax_Rate + L1_Taxable_Amount * L1_Tax_Rate
      
                            
   Case Is > L1_Tax
           Tax_Payable = (Amount - L1_Tax) * L1_Tax_Rate
        
                            
   Case Else
        Tax_Payable = 0
  End Select
  
End Function

=Tax_Payable(A2,12000,22%,13000,25000,30%,7000,32000,38%,13000,45000,45%) where A2 has the gross amount.

See Also: Calculate Sliding Scale Tax Built in Excel Functions. Download Working Examples

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

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 special@ozgrid.com 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