CASE WHEN to calculate tax on a tier based tax system
effective tax rate calculator
2020 tax brackets
2019 tax calculator
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 ...
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
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.
| 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.
- 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! :)