Ask Your Question
0

How to calculate an additive tax in Calc?

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

mmalmeida gravatar image

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

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
2

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

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

Comments

Excellent suggestion! It does indeed look clean and simple!

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

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

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

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!

mmalmeida gravatar imagemmalmeida ( 2019-01-09 10:54:35 +0100 )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 +0100 )edit
1

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

m.a.riosv gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 38 times

Last updated: Jan 09