Current Special! Complete Excel
for Excel 97 - Excel 2003, only
Excel VBA Custom Function: Calculate Sliding Scale Tax/Commission.
Calculate Sliding Scale Tax
in Excel Functions.
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.
See Also: Calculate Sliding Scale Tax Built in Excel Functions. 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
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 & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates