Ask Your Question

# How to calculate an additive tax in Calc?

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 close merge delete

## 3 Answers

Sort by » oldest newest most voted

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.

more

## Comments

Excellent suggestion! It does indeed look clean and simple! With a vlookup function with an inner array it's ease

=VLOOKUP(A3;{0|0;500|12;1000|28};2)


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.

more 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.

more

## Comments

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!

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.

## Stats

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

Seen: 163 times

Last updated: Jan 09