Ask Your Question

How to calculate an additive tax in Calc?

asked 2019-01-08 20:20:36 +0200

mmalmeida gravatar image

updated 2019-01-08 20:21:14 +0200

Consider a tax (e.g income tax) which is applied in the format:

  • 0-500€: 0%
  • 500-1000€: 12%
  • 1000-1500€: 28%

So if a person earns 500€, tax is 0, if they earn 1500, then the first 500€ have no tax, the next 500€ have 12%, and the final 500€ have 28%.

What is the best way in Calc to determine the earnings by providing the salary.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2019-01-09 01:07:50 +0200

Lupp gravatar image

I would avoid VLOOKUP or similar constructs.
Assume the limits are in A2:A4, and A6 has an unhrealistic "greater than any" value, say 1E300 (1 with 300 zeroes).
The rates are in B2:B4, and B6 contains the rate for amounts exceeding 1500.
Then =$F2*B$5-SUMPRODUCT(IF($F2>$A$2:$A$4;$A$2:$A$4;$F2);$B$3:$B$5-$B$2:$B$4) does the trick, and you wont need a longer formula if the table of rates is scaled up to any number of limits and associated rates.
The formula may look long or even complicated. In fact it is simple and efficient in spreadsheets because it doesn't try to sequentialise the task.

See this attached example doument.

edit flag offensive delete link more


Excellent suggestion! It does indeed look clean and simple!

mmalmeida gravatar imagemmalmeida ( 2019-01-09 10:53:29 +0200 )edit

This looks very clever and nice, but it would be helpful if you could break this down into explanations of each (non-obvious) element, e.g.:
a) how it works to compare a value against a range inside the IF
b) how the "then" part of the IF is allowed to be a range
c) how you are allowed to subtract one range from another, and
d) why you are subtracting the sum of the product from $F2*B$5.
BTW I think it's missing $ before that B$5.
(Slightly edited by @Lupp for better readability. Not yet studied or answered.)

aspiers gravatar imageaspiers ( 2021-01-17 19:50:41 +0200 )edit

answered 2019-01-08 23:22:05 +0200

Hello @mmalmeida

One of the possibilities is to use combination of VLOOKUP functions on the predefined Tax Table, as @m.a.riosv proposed. I have created an example spreadsheet with all step by step explanation on how to use VLOOKUP to calculate progressive tax rates.

edit flag offensive delete link more


Thank you for the excellent suggestion - and for the detailed example - kudos for that! I ended up accepting @Lupp's suggestion as it seems simpler - although from what I gather the rationale is the same for both methods!

mmalmeida gravatar imagemmalmeida ( 2019-01-09 10:54:35 +0200 )edit

I also like @Lupp method better, cause it is not so obvious as VLOOKUPs, so it seems more interesting to me :) Voted for it as well.

SM_Riga gravatar imageSM_Riga ( 2019-01-09 12:40:40 +0200 )edit

answered 2019-01-08 22:44:53 +0200

m.a.riosv gravatar image

With a vlookup function with an inner array it's ease


where cell A3 has the searched value, of course you can substitute the inner array {0|0;500|12;1000|28} with a range of cells with those values for search. Take care of use the proper separators as is in Menu/Tools/Options/LibreOffice calc/Formula.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-08 20:20:36 +0200

Seen: 811 times

Last updated: Jan 09 '19