Income Tax Calculation Formula

See if this works for you:

Formula:

Please Login or Register  to view this content.

Wrong as per TMS post!!! Nothing to do with text!

The attached is more usual layout of the table where column A are MINIMUM values of a range(0-24000,24001-53000)

formula in K4

=INDEX($G$3:$G$7,IFERROR(MATCH($K$2,$A$4:$A$7,1)+1,1))

Not sure why that would happen. It works OK with your sample file. What is different?

With some confirmation checks.

@zanshin777.... The Turkish income tax rate schedule is a progressive ("tiered") tax rate, just like most other countries (e.g. USA).

That is confirmed by the table in the left-hand image below, from https://turkishlaborlaw.com/news/bus...ome-tax-tariff .

In summary:

Please Login or Register  to view this content.

Thus, for a taxable income of 30,000, the tax is 24000*15% + (30000-24000)*20% = 4800.

For one implementation, see the right-hand image and Excel file attached below.

For example, with the taxable income in A10, the tax can be calculated by the following formula:

=SUMPRODUCT((A10>$A$2:$A$6)*(A10-$A$2:$A$6),$D$2:$D$6)

The formula in D2 is =C2-N(C1) .

@zanshin777, you might use semicolon (";") instead of comma (",") to separate parameters. If you download the Excel file, all should be adjusted for your regional settings automagically.

-----
PS.... For a breakdown of the tax calculation by bracket, see the last image below and the "zanshin" worksheet.

Formulas:
D4: =MAX(0, (MIN(B4,$F$4)-A4)*C4)
D9: =SUM(D4:D8)

TMS's formula works.

… to the extent that it selects the "correct" entry from your table.

However, the solution provided by curiouscat would be a more appropriate calculation to work with.

Although the solutions from John Topley and myself select the entry you wanted, the underlying calculations are incorrect.

Formula:

Please Login or Register  to view this content.

1) MATCH first argument. (K2-0.1) … fudge to cater for exact match

2) IFERROR first argument. (+1) … move up one bracket from the one picked by the MATCH

3) IFERROR second argument (1) … force selection of first bracket if there is an error

4) Why is it an array formula? … it's not an array formula

Let's say Income is entered as 12000. Income tax would be 3600.



1) 12000 - 0.1 = 11999,9

MATCH(11999.9, A3:A7, 1)

I think we don't need to subtract 0.1 because 3rd argument (1) guarantees It takes the area less than the specified value. (12000) ???

However how does the same formula calculate for the cell A7??? It requires to find the values bigger than 650000. Also A7 is mixed cell with both text and numbers. How does It decide It is required to find bigger than 650000?



2) The formula adds +1 to the row number It finds with the MATCH.

So It should find the result of Row 2 in the lookup range. (Row 1 + 1)

However somehow It found the result of row 1 as it should do.

Why +1 isn't added to the row 1.




3)Let's say the result is on the second row in the lookup range. However If It comes across an error It gives the result of the first row. (IFERROR last argument, 1)

I don't understand that.




4) It has brackets on the both sides.

Please look at the comparison of the formulae in the attached example file.

Note that John has adjusted the layout of the table (and standardised it).

Originally Posted by zanshin777

Since Income is 30000 (K2) (It falls the area of 53000, so the values up to 53000) the formula on the K4 has to return 8880.

Originally Posted by zanshin777

Let's say Income is entered as 12000. Income tax would be 3600.

GIGO!

The tax on 12000 is 1800, not 3600.

And no portion of tax on 30000 "falls in the area of 53000", since 30000 is less than 53000(!).

Arguably, you simply might be labeling the tax brackets ("areas") incorrectly, referring to them by their top end instead of their bottom end. AFAIK, "everyone" refers to tax brackets by their bottom end.

-----

Be that as it may, what is the basis for your calculation of 8880 "total" based on income of 30000? "Total" of what?!

I mean, I see how you calculated it. But what sense does it make?!

The first part of the calculation is the correct portion of tax in the 0-to-24000 tax bracket, namely: 3600 = 30000*15%. Good!

But the second part is totally bizarre: (taxableIncome - firstBracketTax)*secondBracketRate, where firstBracketTax is 30000*15% and secondBracketRate is 20%.

Then you add the two parts to derive 8880(!). And you call it the "total". "Total" of what?!

And eventually, you derive a total of 139314(!) -- much greater than taxable income (30000). What does that number represent?!

-----

Originally Posted by zanshin777

The formulas on the column G calculates the taxes both based upon income K2 and the area It falls down [...].
I want a formula on the cell K4 which picks the tax calculated amount in the regarded area.

Please clarify what you want to calculate, without using your numbers and formulas, which seem to be incorrect.

For example, for a given "taxable income"(*), do you want to determine the top tax bracket and the amount of tax that is attributed to it?

Or are you trying to calculate something altogether different; for example: given a required "take-home income"(**), what is the taxable income?


-----
(*) "Taxable income" is the part of income that is taxable after subtracting certain deductions that some countries allow. There might be none in your country. I don't know.

(**) "Take-home income" is "taxable income" minus total tax.

Originally Posted by zanshin777

I don't understand your tax calculation for now however.

I have no idea who you are talking to (who "your" is).

Originally Posted by zanshin777

I tried It for the values on the new table of mine; 15000, 25000, 55000, 192000, 655000.
The results comply with the ones on this website; https://gelir-vergisi.hesaplama.net/hesaplama.do

No, they do not!

For 192,000, you calculate a total tax of 51,890. But the website calculates 47,090. See the image below.

I do not read Turkish, so I rely on the Google translation. I have annotated the website results with my interpretation, which is consistent.

Note the part that I mark "Ignore". That is the calculation that you are having trouble duplicating. I have not tried, because I consider it to be useless, if not nonsense.

The website results are consistent with the calculator that I provided. See the image below and attached Excel file.

I have reorganized the bracket breakdown calculations and added some calculations to be as complete as the website.

I identified the tax brackets by their lowest amounts. The website uses their highest amounts. We simply disagree. And actually, it is better to refer to the tax brackets by their marginal rate (15%, 20%, 27% etc).

The difference is the OP has used 130000 for 35% rather than 190000.

Attached has SUMPRODUCT calculation using Marginal Rates.

I missed your SUMPRODUCT calculation so apologies as I was not inferring any of your calculations are incorrect.: we are in agreement that the OP's calculations are wrong!

Originally Posted by JohnTopley

we are in agreement that the OP's calculations are wrong!

But not just zanshin777.

For the "tax bracket" (Turkish: Vergi Dilimleri) calculation for a "tax base" (Vergi Matrahı) of 192,000, the hesaplama.net website description translates as follows (by Google):

15% up to 24,000 TL
3,600 TL for 24,000 TL of 53.000 TL, 20% for the excess
9,400 TL for 53,000 TL of 190,000 TL, 27% for the excess
46,390 TL for 190,000 TL of 650,000 TL, 35% for the excess
207,390 TL for 650,000 TL of more than 650,000 TL, 40% for more

I'm looking for someone to make some sense of that.

You are misreading this

15% up to 24,000 TL
3,600 TL for 24,000 TL of 53.000 TL, 20% for the excess
9,400 TL for 53,000 TL of 190,000 TL, 27% for the excess
46,390 TL for 190,000 TL of 650,000 TL, 35% for the excess
207,390 TL for 650,000 TL of more than 650,000 TL, 40% for more

The bold figures are the CUMULATIVE tax NOT the tax for given band so 3600 for first 24000 + 5800 for 24001-53000 band = 9400 for income of 53000 TL with 207390 the amount of tax for income of 650,000 TL.

Originally Posted by JohnTopley

You are misreading this [....] The bold figures are the CUMULATIVE tax NOT the tax for given band so 3600 for first 24000 + 5800 for 24001-53000 band = 9400 for income of 53000 TL

Bingo! I should have noticed.

More later. Gotta get my bike ride in (smile).

Originally Posted by zanshin777

Yes, I was wrong about the table. Now I fixed It. [....] The results comply with the ones on this website; https://gelir-vergisi.hesaplama.net/hesaplama.do

As I noted previously: no, they do not.

But as JohnTopley noted, the reason, in part, is due to a typo: 130000 should be 190000 in C5 and D6.

In addition, 30190 should be 46390 in C6. And 212190 should be 207390 in C7.

The corrections are consistent with the hesaplama.net webpage.

-----

Originally Posted by JohnTopley

You [curiouscat] are misreading this [....] The bold figures are the CUMULATIVE tax NOT the tax for given band

Yes, I did misunderstand the Vergi Dilimleri (tax bracket) data on the hesaplama.net webpage.

Nevertheless, I still quibble with their labeling of the tax brackets -- what zanshin777 calls "areas" -- based on the top end instead of the bottom end.

For posterity, I believe the following VLOOKUP design might be more intuitive. It is a form that is used in the US IRS tax instructions (IRS Pub 17).

See the image below and the "vlookup" worksheet in the attached Excel file. You might also look again at the "sumproduct" worksheet and the image below.

The primary formulas for the VLOOKUP method are (copy down into adjacent cells):

Cumulative tax, C5: =C4+(B4-A4)*D4

Total tax, C13: =VLOOKUP(A13,$A$4:$D$8,3) + (A13 - VLOOKUP(A13,$A$4:$D$8,1))*VLOOKUP(A13,$A$4:$D$8,4)

Top tax bracket, L13: =VLOOKUP(A13,$A$4:$D$8,4)

Consult the Excel file for other formulas, notably in D13:H13.

Note that the sum in column I is just a "check". Compare with column B or C. It can be eliminated.

Also note that E4:E8 ("of amount over") is provided "readability" across the column titles. It is not referenced, since it simply duplicates A4:A8.

Of course, there are many other possible implementations, including a compact MAX/MIN formula instead of VLOOKUP. But IMHO, they might be as difficult to understand as the SUMPRODUCT method.