0

It's my very first post here so hi all.
I did a lot of research before hand but couldn't find the answer I was looking for.

I want a spreadsheet in Google Sheets to automatically calculate my tax.

€0 - €11000 w/ 0%
€11000 - €18000 w/ 25%
€18000 - €31000 w/ 35%
€31000 - €60000 w/ 42%
€60000 - €90000 w/ 48%
€90000 - €1000000 w/ 50%
€1000000+ w/ 55%

So the first 11k are taxed with 0% the next 7k are taxed with 25% etc.

What I've had the following thought - give that F9 are my before tax earnings:

   =IF(0<=F9<=11000,SUM(F9*0%),(IF(11000<F9<=18000,SUM((F9-11000)*25%),(IF(18000<F9<=31000,SUM((7000*25%)+((F9-18000)*35%),SUM(F9))))))

Unfortunately it just won't work.

Am I using the right function? Is there anything out there which would make it much easier?

Thank you for your help.

pman1k
  • 3
  • 1

4 Answers4

0

Excel does not work that way X<y<z. Plus it resolves in order so it is not necessary:

=IF(F9<=11000,SUM(F9*0%),(IF(F9<=18000,SUM((F9-11000)*25%),(IF(F9<=31000,SUM((7000*25%)+((F9-18000)*35%),SUM(F9))))))

if it is less than 11000 then the first will fire and none others. then if greater than 11000 but less than 18000 the second will fire, and so on.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

Here is an example (Excel) for your problem:

In Cell A1 is your Money, paste the code in A2 for example:

=WENN(A1>10000;"10%";WENN(A1>7000;"7%";WENN(A1>5000;"5%";WENN(A1>3000;"3%";"0%"))))

FK

cchapman
  • 3,269
  • 10
  • 50
  • 68
user5914091
  • 16
0

List values of each bracket range with the percentage applied if it is above that amount, for example:

  A       B
1 11000   0.25
2 18000   0.35
3 31000   0.42
4 60000   0.48
5 90000   0.50
6 1000000 0.55

You can format the percentage values as percentages if you prefer. What is important is that the value is a decimal and not a string.

Then you can use the MATCH function to determine the row of the first value that is larger than your income. So if the cell holding your income is F9, you'd have:

=MATCH(F9;A1:A6;1)

But this only returns the row number. If we want to retrieve the tax applied, we'll need to use this to pinpoint the value in column B pertaining to that. To get a value pertaining at a certain point dynamically, we use INDEX.

So putting it together, you'd have:

=INDEX(B1:B6; MATCH(F9; A1:A6; 1); 1)

One minus this times the value in F9 to get the amount after taxes:

=F9*(1 - INDEX(B1:B6; MATCH(F9; A1:A6; 1); 1))

Why would you do it this way? It lets you change the values in an intuitive fashion without having to parse a long and arduous formula. You can also add new values with relative ease. Just remember to adjust the A1:A6 and B1:B6 ranges.

Good luck! Let me know how it turns out!

Neil
  • 5,762
  • 24
  • 36
0

You might want to try this:

=if(F9<=0,"Error",IF(F9<=11000,0,(IF(F9<=18000,(F9-11000)*25%,(IF(F9<=31000,1750+(F9-18000)*35%,IF(F9<=60000,6300+(F9-31000)*0.42,if(F9<=90000,18480+(F9-60000)*0.48,IF(F9<=1000000,32880+(F9-90000)*0.5,487880+(F9-1000000)*0.55)))))))))
M.L
  • 328
  • 2
  • 12