CASE WHEN to calculate tax on a tier based tax system

marginal tax rate calculator
effective tax rate calculator
tax calculator
2020 tax brackets
2019 tax calculator
tax tables
u.s. tax rate schedule 2019
income tax rate 2019

I have a table with user incomes and i wish to calculate their income tax percentage based on that income. The issue is that the tax rate is different for each bracket e.g.:

MinLimit| MaxLimit| TaxRate
0       | 14000   | 10.50
14001   | 48000   | 17.50
48001   | 70000   | 30.00
70001   | 1000000 | 33.00

So if the income of 1 person is 49,000 then they would be taxed as follows:

14000 * 0.1050 = 1470
34000 * 0.1750 = 5950 (34,000 is income between 14k -48k)
1000  * 0.30   = 300  (1000 is remaining income)
total = 1470 + 5950 + 300 = 7720

I am running on SQL Server 2017 Express. I have tried running a chained CASE-WHEN statement i.e.

CASE WHEN
     THEN

     WHEN
     THEN 
and so on...

but I can figure out how to add the logic of subtracting the remaining amount. Please find my code below.

SELECT 'emp_name' AS 'Director', 
SUM(ABS([Transaction Amount])) AS 'INCOME',
CASE WHEN (SUM(ABS([Transaction Amount])) < 14000)
     THEN ((SUM(ABS([Transaction Amount])) - 14000) * 0.1050)

     WHEN (SUM(ABS([Transaction Amount])) > 14000 and (SUM(ABS([Transaction Amount])) < 48001))
     THEN (((SUM(ABS([Transaction Amount])) - 14000) * 0.1050) - 48000) * 0.1750 end AS 'Income Tax'
FROM Transactions

EDIT 1: Input Data:

Transaction Type| PAYEE  | Transaction Amount
DEBIT           | DEBIT  | -184.00
CREDIT          | CREDIT | 4000.00
...

Output Data:

Director | INCOME  | Income Tax
emp_name | 45100.00| NULL

Please let me know where I am going wrong or if my thinking is incorrect.

A correlated subquery may be the simplest to read and understand:

declare @t table (MinLimitExclusive int, MaxLimitInclusive int, TaxRate decimal(5,2))
insert into @t(MinLimitExclusive,MaxLimitInclusive,TaxRate) values
(0    ,14000  , 10.50),
(14000,48000  , 17.50),
(48000,70000  , 30.00),
(70000,1000000, 33.00)

declare @transactions table (Income decimal(10,2))
insert into @transactions (Income) values (49000)

select
    (Income - MinLimitExclusive) * TaxRate / 100 +
    (select SUM((rates2.MaxLimitInclusive - rates2.MinLimitExclusive) * rates2.TaxRate / 100) 
    from @t rates2 where rates2.MaxLimitInclusive <= rates.MinLimitExclusive)

from
    @transactions tr
        inner join
    @t rates
        on
            tr.Income > rates.MinLimitExclusive and tr.Income <= rates.MaxLimitInclusive

It's remarkably simplified when you realise that the only maths you need to do related to the actual income is related to the bracket it actually fits in - all of the lower rate brackets, by implication, were used in their entirety so you can compute those other taxes purely from the rates table.

I've changed your rates data slightly to make the computations straightforward and not need lots of +/-1 adjustments.

Tax Brackets 2018: How They Work, Examples, and Myths, Find how tax brackets work and how to calculate tax bill. In a progressive tax system, rates are based on the concept that high-income taxpayers In this case​:. Trust accounting uses a tier system to allocate taxable income among beneficiaries. Generally, Tier 1 distributions are made to those who are required to receive the income from the trust or estate, such as a surviving spouse beneficiary in a QTIP trust. Tier 1 distributions are governed by section 662 (a) (1).

I suggest that you start with a MinLimit of 1 instead of 0. The rest of the calculation is straight forward:

declare @taxslabs table (minlimit int, maxlimit int, taxrate decimal(18, 2));
insert into @taxslabs values
(1,     14000,   10.50),
(14001, 48000,   17.50),
(48001, 70000,   30.00),
(70001, 1000000, 33.00);

select persons.*, taxslabs.*, taxableamount, taxableamount * taxrate / 100 as taxamount
from (values
    (1, 49000),
    (2, 70000),
    (3, 70001)
) as persons(id, income)
cross join @taxslabs as taxslabs
cross apply (select case when income <= maxlimit then income else maxlimit end - minlimit + 1) as ca(taxableamount)
where minlimit <= income

You can place this query inside a subquery and use GROUP BY ... SUM() or SUM() OVER (PARTITION BY) to calculate the sum of taxes.

Sample output:

| id | income | minlimit | maxlimit | taxrate | taxableamount | taxamount        |
|----|--------|----------|----------|---------|---------------|------------------|
| 1  | 49000  | 1        | 14000    | 10.50   | 14000         | 1470.000000      |
| 1  | 49000  | 14001    | 48000    | 17.50   | 34000         | 5950.000000      |
| 1  | 49000  | 48001    | 70000    | 30.00   | 1000          | 300.000000       |
| 2  | 70000  | 1        | 14000    | 10.50   | 14000         | 1470.000000      |
| 2  | 70000  | 14001    | 48000    | 17.50   | 34000         | 5950.000000      |
| 2  | 70000  | 48001    | 70000    | 30.00   | 22000         | 6600.000000      |
| 3  | 70001  | 1        | 14000    | 10.50   | 14000         | 1470.000000      |
| 3  | 70001  | 14001    | 48000    | 17.50   | 34000         | 5950.000000      |
| 3  | 70001  | 48001    | 70000    | 30.00   | 22000         | 6600.000000      |
| 3  | 70001  | 70001    | 1000000  | 33.00   | 1             | 0.330000         |

Tax Bracket Calculator For Your 2019, 2020 Tax Rates., Taxpayer Case Study for Tax Rate and Bracket Calculation. Take a look at this case Income Tax Rate Tables by Filing Status and Income Tax Bracket tiers. The US Tax system is "progressive", which means people with higher taxable income pay a higher federal tax rate. Rates are assessed in brackets defined by an upper and lower threshold. The amount of income that falls into a given bracket is taxed at the corresponding rate for that bracket.

i think this relevant query using group by on transaction table and join to rate taxe table can be down excepted result :

CREATE TABLE #Transaction  
(
    tID             int PRIMARY KEY,  
    tIdUser         varchar(50),
    Amount          decimal(9,3)
 );  
CREATE TABLE #RefTaxe  
(
    pID             int PRIMARY KEY, 
    minLimit        int, 
    maxLImit        int,
    rate            decimal(9,3)
); 




INSERT INTO #Transaction 
SELECT 1, 'User1', 1259.3
UNION
SELECT 2, 'User1', 10259.3
UNION
SELECT 3, 'User3', 30581.3
UNION 
SELECT 4, 'User2', 75000.36
UNION 
SELECT 5, 'User2', 15000.36
UNION 
SELECT 6, 'User4', 45000.36
UNION 
SELECT 7, 'User4', 5000.36


INSERT INTO #RefTaxe 
select 1,0,14000,10.50
UNION
SELECT 2,14001,48000,17.50
UNION
SELECT 3,48001,70000,30.00
UNION
SELECT 4,70001,1000000,33.00


-- SELECT * FROM #Transaction

-- SELECT * FROM #RefTaxe

--  SELECT tIdUser,SUM(AMOUNT) as SumAmount, CAST(FLOOR(SUM(AMOUNT))as int) as SumAsInt     FROM #Transaction GROUP BY tIdUser
/***/
-- Perform select 
/***/
SELECT tIdUser, SumAmount as 'DetaxedAmount' ,SumAmount * (rate/100) as TaxOfAmount, SumAmount+ SumAmount * (rate/100) as TaxedAmount
FROM #RefTaxe RT    
JOIN (
    SELECT tIdUser,SUM(AMOUNT) as SumAmount,  CAST(FLOOR(SUM(AMOUNT))as int) as SumAsInt
    FROM #Transaction GROUP BY tIdUser
)   AS GroupedTR ON RT.minLimit <= SumAsInt AND RT.maxLImit >= SumAsInt


/***/

DROP TABLE #Transaction
DROP TABLE #RefTaxe

Result output :

tIdUser DetaxedAmount   TaxOfAmount     TaxedAmount
User1   11518.600     1209.453000      12728.053
User2   90000.720     29700.237600     119700.958
User3   30581.300     5351.727500      35933.028
User4   50000.720     15000.216000     65000.936

How Does the Marginal Tax Rate System Work?, This is done at each income level up to the taxpayer's total income, in this case, $120,000. Based on the tax rate schedule above, the $120,000  The United States tax system follows a structure that is similar to what is used by most countries. There are income brackets, and you pay your taxes based on what you made the previous year. Of course we’re not going to go too much into the details of the tax system, but this can help understand how to utilize Excel for calculations.

FCC Record: A Comprehensive Compilation of Decisions, Reports, , In Vision Cable's amended filing of February 7, 1995, Vision Cable calculated The Commission has stated that tax-paying business entities must gross-up their tax Optional Procedures with Respect to Pending Pre-May 15 Benchmark Cases, Preferred Service Package should be treated as rate-regulated service tiers. Tier II taxes are withheld for the benefit of the private railroad pension system. Like payroll taxes, these taxes are obligatory. The current withholding rate for Tier II taxes is 4.9 percent for employees and 13.1 percent for employers. Tier II taxes are only levied on the first $87,000 of your annual earnings.

Understanding State Income Tax vs. Federal Income Tax, The United States has a multi-tiered income tax system under which taxes are a greater percentage rate, as is the case with the federal income tax system. Some states base their marginal tax brackets for this purpose on the federal tax code,  Tax base is defined as the income or asset balance used to calculate a tax liability , and the tax liability formula is tax base multiplied by tax rate . The rate of tax imposed varies depending

Progressive tax, A progressive tax is a tax in which the tax rate increases as the taxable amount increases. It can also apply to adjustments of the tax base by using tax exemptions, tax credits, or selective taxation that creates progressive distribution effects. In that case the tax on $20,000 of income (computed by adding up tax in each  While it would be nice to pay taxes at the lower capital gains rate on the entire gain, you’ll pay up to 25% (based on your ordinary tax rate) on the part that’s tied to depreciation deductions.

Comments
  • Could you write the input data and output data?
  • @StepUp added input/ouput :)
  • Thanks @Damien --> used your code as inspiration and with some slight modifications it worked. Much appreciated! :)