[5 - 6 - trunk] invoice tax - rounding issues and computation method

I first encountered that problem with a Lexware software.

There are two ways to calculate the tax:
- by columns (vertical)
- by lines (horizontal)

Lexware (and obviously OpenERP) use horizontal calculations, while the most common usage is vertical.

So the totals are, while feeling wrong, actually correct.

(German link)
http://support.lexware.de/portal_support/00897-0000/supportProductFAQDetail?ID=000000000015732&searchTerms=&area=&version=2008&batchStart:int=40

In Germany, both ways are accepted by tax offices.
I don't know about other countries.

In you picture:

A * B = C
1,99 * 0,1 = 0,20
0,38 * 0,1 = 0,04
5,68 * 0,1 = 0,57
0,45 * 0,1 = 0,05
1,05 * 0,1 = 0,11
2,87 * 0,1 = 0,29

SUM(C) => 1,26

while

SUM(A) = 12,42
* 0,1 = 1,24

Before changing this completly, I would like to suggest a (company based?) configuration option to choose either horizontal or vertical calculation.

But I can understand that this may get a very low priority.
Except when it's actually forbidden to calculate horizontally in some country, that would make it a very high priority.

I fully agree with an "option" - so everyone can choose

our "Austrian" experience tells me that people will call and ask why we can't calculate 10% correctly.

Please consider, that this has also to be changed for Sales and Purchase

On Wednesday 26 January 2011, you wrote:
> I fully agree with an "option" - so everyone can choose
>
> our "Austrian" experience tells me that people will call and ask why we
> can't calculate 10% correctly.

10% ? That would fit everyone :P

> Please consider, that this has also to be changed for Sales and Purchase

I second the choice of a company-wide setting on that.

I think we had discussed that last year, on the community meeting, and
concluded it would be the best practice. Didn't we?

may be - the method could also be defined for each tax rate.

especially for incoming invoices it could be a nice feature to automatically check the alternate calculation method if net + vat != total.

Hello DR Ferdinand,

I have checked the issue in stable 6.0 and seems the issue has been resolved. I have attached the screen shot for your reference, So please check it and notify us if you still face the same problem again.

For now I am closing this bug.

Thanks.

Yes with ONE line it's always correct
please try with the provided data

Hello Dr Ferdinand,

Thanks for the information!

I have checked again with the several line as in your example and did face the same problem. So I am confirming this bug.

Thanks.

Hello everyone,

yes as far as i remember this issue has already been discussed several times, with always the same conclusion: the 2 ways to compute (by line or by column) are allowed by the law, so we can't speak here of 'bug'.

We don't plan to improve that for version 6.1, if you think it's a huge lack in openerp i encourage you to create a poll for this idea on http://feedback.openerp.com, in the accounting section. But i remind you that this "lack" doesn't prevent at all to use the software and you can always adjust manually the tax by hand.

Thanks for your contribution
Quentin

Hi Quentin,

as I read in the German Forum this is a big problem for german companies which will lead to a problem in growing in the german market. Can you please mention some discussions for that, so that we can understand why the Core Development has decided to compute this a) via horizontal calculations and b) the second big discussion in the german forum why you compute float and not int datatypes.

Thx in advance

Felix

Hi everyone,

we discussed the problem a lot in the forum.
We think, this is a serious bug and that OpenERP is hardly suitable for the German market before this one is fixed.
It's not about choosing the right tax calculation method, this is about rounding and data types.
So the sums in OpenERP differ from manual calculated and mathematical correct rounded sums.
This leads to incorrect invoices, which can cause a lot of trouble.

I hope, we can encourage the developers to rethink the problem.

I'm with you, Conrad, at "hardly suitable for the German market", but it is _not_ a bug, neither are there real rounding errors.
See my post above on vertical and horizontal calculation.

It is all about acceptance.

Most German companies use vertical calculation.

Our own accounting department, which is very experienced, did not know about horizontal accounting before I (then a freelancer) wrote the first invoice with Lexware Büro easy. Neither did my tax consultant.
The company actually recalculated vertical and payed some cents less, which would have resulted in a (small) tax felony... (but I booked it as discount).

We use 19% here, so the calculation type rounding issues are not as obvious as with Ferdinands 10%, but customers *will* notice and wonder about it.

So, if there already is a feedback poll?, we would be glad to support it. Or open one.

Regards,
Marco

I refer to a post in the German forum by user 59590

A calculation on an Ubuntu 10.10 and Centos 5 installation
Python 2.6.6 Centos Python 2.4
round(45.885.2) outputs 46,890000000000001 -> +1 Cent
Fedora 14 mit Python 2.7
round(45.885,2) = 45.88

you can edit the numbers to be rounded with a simple function like that:

def runden(zahl,stellen):
zahl = zahl*10**(stellen+1)
zahl = round(zahl,-1)
zahl = zahl*1/10**(stellen+1)

runden(45.885,2) => 45.89

The problem is the (wrong) rounding of each price and summing up those wrong rounded prices.

@#11
I created a poll: http://feedback.openerp.com/forums/77459-general-improvements/suggestions/1449257-solve-invoice-tax-rounding-issue?ref=title

lets see how many votes this issue will get.

some more thougths
we should distinguish between
* supplier invoices
Austrian law requires that the tax shown on the invoice matches the one which is posted.
hence it is necessary to adapt the calculated values
the total is required by OpenERP , the vat must match - requreid by law, so we have to adapt very often the net (sub_total) which is calcuated qty*price and this is not possible - so the user has to add an extra line with cent difference - not very elegant

* customer invoices
the problem araises typically if invoices are created in another module (as for one of our clients ) or in another system for multiple lines with the same tax rate.
It becomes very visible for "easy to compute" tax rates like 10% reduced VAT in Austria for food and certain services (rent,...)

there is no way to ask the client to recalculate the rax manually - without beeing thrown out immediately.

the poll:
nice idea, but IMHO OpenERP should rely more on experts, because especially in a "market to be developed" the poll might not reach a significant number, but turn away interested prospects and potential partners, just my 2c on this.

Hello,

I confirm both tax computation methods are usually allowed and we never found one country which does not allow both. After having checked a few softwares, we noticed both practices exists on the market. We think it's not a big constraint, because you can always change the amount if your supplier uses the other method.

The reasons we did per line and not globally:

1. Prices with Tax Included
-----------------------------------

It's the only way to make invoices having prices with tax included. If a product has prices with tax included the way you compute the tax is very different and a small delta must be applied to the tax itself, to reach the product's price. Suppose a product is 4€, having a VAT of 19.6% (like in France). If you compute, you have 3.34*1.196=3.99 and 3.35*1.196=4.01. The real amount must be: Without VAT: 3.34, VAT: 0.66€.

The only way to compute this is to work by line and not globally, if you mix several products having different taxes.

2. Complex taxes
-----------------------

OpenERP has the strongest tax engine I ever saw in any accounting software. It allows to do a lot of things: multiple taxes on a product, a tax that is applied over another tax (like in Quebec/Canada), a tax which is a mix of several taxes, not only pure percentage but fix price or python expression.

When several taxes are mixed in one invoice, you can not always recompute globally, easily.

Even worse, some taxes can never be computed globally. In Belgium, you have the SACEM tax (Sabam in France, a tax on the art work of artists). This tax computation looks like:
  - it applies only if the painting/music is less than 50 years old
  - the price is computed according to the base price. I don't remember the exaxt amount, but it looks like:
     - from 1 to 500€: 10€ of tax
     - from 501 to 5000€: 30€ of tax
     - ...

This is simply impossible to compute globally. It's not a "so special" use case, we have at least 6 customers in production that are using this tax in the auction module.

Workaround if you really need it
-----------------------------------------

Computing globally is very complex and not suitable if we want to keep this code clean. However, there is a quick workaround we already used for some customers, with only two lines of code to change:
  - keep a 'per line' computation
  - but don't do rounding at the end of the line, just apply the rounding at the end.
This will give you the same effect than a global tax computation, but will works in all scenario.

I prefer not to apply this on OpenERP has it's not very clean, but it seems to work. (I did not tested with tax included)

But, what about the reasons to implements an option that allows horitzontal per line or vertical per totals taxes?
This way does OpenERP more flexible for adjust taxes at the needs of users of different countries / companies.

@Fabien
please could you provide these 2 lines of code to avoid rounding on a per line basis - as I said it will work for us too.
IMHO if these 2 lines could be the "alternate" computation method, OpenERP might be more suitable for many

the specification could go into the tax definition (bool: rounding per line) - so both methods would be available at the same time.

@Fabien
The problems are not the tax computation methods. the problem is the incorrect calculation result of rounding subtotals and the faulty round() function in python.
With a simple function i can get the correct values for each rounding sum. See @#15. Where can i find your provided 2 lines code workaround for a test.

Thank you #21, I think nobody is reading what we already discussed. It is not about the calculation method!

I think this has to be splitted into 2 different bug reports.

The problem Ferdinand reported in THIS bug is about horizontal vs. vertical calculation method.

The bug you, Conrad and mrockel, are talking about is the python floating point behaviour and that decimal should be used instead of float. The problem may look similiar, but it is a very different problem.

@mrockel @conrad You are wrong, they have been a lot of dicussions on this misunderstood topic. They are no bug related to this in OpenERP.
Can you provide a use case in OpenERP that fails ? (i don't think so, but if you have one, then create a new bug on launchpad)

@ferdinand I did this one year ago, I don't have the code anymore.

OK I will try to create a module for this - also it would be much easier to integrate the code into trunk

especially in Austria with a reduced VAT of 10% people turn away if they see that an invoice can't calculated 10% of the base.

@Fabien

See @#1.
What is wrong?

Rounding of subtotals is wrong.
Rounding with float values is wrong.
This is not only for the tax calculation

If that does not change, the program is useless around the world.

Please try and give feedback
please read module description

mrockel, please read my first comment, #2, very carefully and tell me afterwards where exactly there is an rounding error in #1.
Do not trust your eyes, trust math.

Marco,
please read,
http://docs.python.org/tutorial/floatingpoint.html#tut-fp-issues

Other surprises follow from this one. For example, if you try to round the value 2.675 to two decimal places, you get this
>>> round(2.675, 2)
2.67
The documentation for the built-in round() function says that it rounds to the nearest value, rounding ties away from zero. Since the decimal fraction 2.675 is exactly halfway between 2.67 and 2.68, you might expect the result here to be (a binary approximation to) 2.68. It’s not, because when the decimal string 2.675 is converted to a binary floating-point number, it’s again replaced with a binary approximation, whose exact value is
2.67499999999999982236431605997495353221893310546875
Since this approximation is slightly closer to 2.67 than to 2.68, it’s rounded down.

On 05. 02. 11 16:58, mrockel wrote:
> @Fabien
>
> See @#1.
> What is wrong?
>
> Rounding of subtotals is wrong.
> Rounding with float values is wrong.
> This is not only for the tax calculation
>
> If that does not change, the program is useless around the world.
>
maybe "wrong" and "useless" are kind of extreme views, this is probably
a harder way to communicate :-)

On 06. 02. 11 18:40, mrockel wrote:
> Marco,
> please read,
> http://docs.python.org/tutorial/floatingpoint.html#tut-fp-issues
>
> Other surprises follow from this one. For example, if you try to round the value 2.675 to two decimal places, you get this
>>>> round(2.675, 2)
> 2.67
> The documentation for the built-in round() function says that it rounds to the nearest value, rounding ties away from zero. Since the decimal fraction 2.675 is exactly halfway between 2.67 and 2.68, you might expect the result here to be (a binary approximation to) 2.68. It’s not, because when the decimal string 2.675 is converted to a binary floating-point number, it’s again replaced with a binary approximation, whose exact value is
> 2.67499999999999982236431605997495353221893310546875
> Since this approximation is slightly closer to 2.67 than to 2.68, it’s rounded down.
>
i hope i will not look too ridiculous, but i have the feeling that
accounting (in terms of maths) is not more than BASIC ARITHMETIC. so why
on earth would you use floating point in the first place??? there is a
function which sets the precision to whatever one needs: 2, 3, 4, maybe
5 decimals. why do you need to "float"?

i am sure you will find the midway...

bogdan

it is not related to tax, but it is the same useless "precision":

Hmmm... This discussion/debate of use of float never seems to end ???

If you need more references:

Why the Tryton (http://tryton.org a fork of OpenERP) decided to use decimals: https://lists.launchpad.net/openerp-expert-accounting/msg00070.html

Why Fabien/OpenERP SA considers Float as the solution: https://lists.launchpad.net/openerp-expert-accounting/msg00067.html

The solution IMHO is to use Decimals which avoids all the nonsense of rounding (of course at the cost of performance, which could be improved 12x by use of cDecimal http://<email address hidden>/msg01347.html).

Quoting one of my friends in the OpenERP community "We have uml designer, view designer, report desinger, fancy dashboards but can't do a single sum(debit) :)"

@Sharoon
* sum(debit) - as long as it runs as postgres select sum(debit) will IMHO return correct results, as these fields are defined as numeric
** in V5 "numeric(16,2)"
** in V6 everything is "numeric"
is this correct ? why do we have the digits definition ?

* using Decimals will not solve the issue of this bug report because it's a design - not a technical - issue where to apply or not rounding.

of course I would like to see cDecimals but until now I have not seen any (obvious) problems caused by using float in python

Hi all, we developed a module that implements the vertical (by columns) computation of invoice taxes.
It also covers the particular case of Italian partially deductible VAT.
See http://apps.openerp.com/addon/6056

Thanks to Lorenzo !!!

the vertical calculation is an issue in many countries

I strongly suggest to include this module in standard addons as it is base for many nationalizations.

Hello,
I refactored the module because it had wrong behaviours in some cases.
See http://bazaar.launchpad.net/~openobject-italia-core-devs/openobject-italia/italian-addons/revision/134
I also added a company based configuration option to choose either horizontal or vertical calculation, as dieck suggested

Dear all,

Camptocamp will use, promote and contribute to the "account_invoice_tax_by_column" module. We'll try to work hand in hands with you all to have a rock solid module that include the problematic of CH, A, IT and DE.

Once will reach somethings good, we'll have what we need to args with OpenERP on that topic for 6.2 ;) !

Regards,

Joël

Hi,

we are refactoring the module 'account_invoice_tax_by_column' [1] , making it dependent from 'c2c_account_tax_rounding' [2] .

This is because account_invoice_tax_by_column is too bound to Italian VAT: most code introduced by account_invoice_tax_by_column is of use to Italian computations.

For every standard case (but I didn't test enough with children taxes), I would suggest to use c2c_account_tax_rounding and follow the way started by that module: avoid intermediate roundings.

Ferdinand, for c2c_account_tax_rounding, maybe you want to take a look at automated tests [3] that we are introducing in account_invoice_tax_by_column

Regards

[1] https://code.launchpad.net/~openobject-italia-core-devs/openobject-italia/refactoring_account_invoice_tax_by_column/+merge/81258
[2] http://apps.openerp.com/addon/6227
[3] http://bazaar.launchpad.net/~openobject-italia-core-devs/openobject-italia/refactoring_account_invoice_tax_by_column/files/148/account_invoice_tax_by_column/test/

Lorenzo
the c2c_account_tax_rounding is/was a "dirty" fix - until now we didn't have problems

On 07. 02. 11 06:03, Sharoon Thomas http://openlabs.co.in wrote:
> Hmmm... This discussion/debate of use of float never seems to end ???
>
> If you need more references:
>
> Why the Tryton (http://tryton.org a fork of OpenERP) decided to use
> decimals: https://lists.launchpad.net/openerp-expert-
> accounting/msg00070.html
>
> Why Fabien/OpenERP SA considers Float as the solution:
> https://lists.launchpad.net/openerp-expert-accounting/msg00067.html
>
> The solution IMHO is to use Decimals which avoids all the nonsense of
> rounding (of course at the cost of performance, which could be improved
> 12x by use of cDecimal http://www.mail-archive.com/tryton-
> <email address hidden>/msg01347.html).
>
> Quoting one of my friends in the OpenERP community "We have uml
> designer, view designer, report desinger, fancy dashboards but can't do
> a single sum(debit) :)"
>
Sharoon,

Unfortunately using decimals does not change anything for this matter.
Because your documents ARE using decimals (dp = 3 means 3 decimals and
so on).

b

On 11. 10. 11 18:09, Lorenzo Battistini - Agile BG - Domsense wrote:
> Hello,
> I refactored the module because it had wrong behaviours in some cases.
> See http://bazaar.launchpad.net/~openobject-italia-core-devs/openobject-italia/italian-addons/revision/134
> I also added a company based configuration option to choose either horizontal or vertical calculation, as dieck suggested
>
Lorenzo,

If you allow me, I would like to suggest to create your addon as a
switch (something like account_cancel) which integrates smoothly in the
standard version. Dumping it in localisations would create the need to
replicate it for countries affected, and it would loose visibility...

regards,
bogdan

On 25. 10. 11 10:01, Joël Grand-Guillaume @ CampToCamp wrote:
> Dear all,
>
>
> Camptocamp will use, promote and contribute to the "account_invoice_tax_by_column" module. We'll try to work hand in hands with you all to have a rock solid module that include the problematic of CH, A, IT and DE.
>
> Once will reach somethings good, we'll have what we need to args with
> OpenERP on that topic for 6.2 ;) !
>
> Regards,
>
> Joël
>
If you take my suggestion, you would have no argument at all. See my
previous mail...

thank you,
B

2011/11/4 Bogdan Stanciu <email address hidden>:
>
> If you allow me, I would like to suggest to create your addon as a
> switch (something like account_cancel) which integrates smoothly in the
> standard version. Dumping it in localisations would create the need to
> replicate it for countries affected, and it would loose visibility...
>

I understand your argument, indeed the generic module I suggest to use
is 'c2c_account_tax_rounding'. From today,
'account_invoice_tax_by_column' doesn't contain anything useful to
generic taxes, except of italian VAT.

I would rely on the c2c_account_tax_rounding way, or similar, just
because the computation 'by column' is much more close to computation
without roundings than the 'by line' one.
I'm not totally sure whether c2c_account_tax_rounding correctly
implements this or not, but it works for our tests.

If OpenERP S.A. took into account this issue, we could discuss with
them the best way to patch the account module.

Regards

--
Lorenzo Battistini

Maybe I found a simple and efficient way to implement computation by column, without compromises.

Everyone interested can try 'account_invoice_tax_by_column' module contained in the following branch:
https://code.launchpad.net/~openerp-community/openobject-italia/refactoring_account_invoice_tax_by_column
and give feedback.

If someone had some more crucial scenarios to test, we can add them to automated tests:
http://bazaar.launchpad.net/~openerp-community/openobject-italia/refactoring_account_invoice_tax_by_column/files/149/account_invoice_tax_by_column/test/

Thanks

I want to add that now account_invoice_tax_by_column[1] is meant to be generic again, as I moved the Italian part to l10n_it_partially_deductible_vat[2]
Sorry for recent sudden changes, but we're trying to find the best way.

Everyone who wants to share his view is obviously welcome.

Regards

[1] http://bazaar.launchpad.net/~openerp-community/openobject-italia/refactoring_account_invoice_tax_by_column/files/156/account_invoice_tax_by_column/
[2] http://bazaar.launchpad.net/~openerp-community/openobject-italia/refactoring_account_invoice_tax_by_column/files/156/l10n_it_partially_deductible_vat/

Hello!

We have the same problem with TAX calculation.
We use anglo saxons accounting in UK.
TAX rate is 20%.

$ python -V
Python 2.7.2

$ uname -a
Linux 2.6.38-gentoo-r6 #3 SMP x86_64 Intel(R) Xeon(R) CPU E5405 @ 2.00GHz GenuineIntel GNU/Linu

Sale Order info from Magento:
Subtotal (Excl.Tax) £498.74
Subtotal (Incl.Tax) £598.49

$ python
Python 2.7.2 (default, Sep 24 2011, 17:01:45)
[GCC 4.5.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> round(498.74*1.2,2)
598.49

it is correct value!

When we create Sale Order in OpenERP, we get incorect value for Total 598.48 :(
Look at attachments.

What can we do for solve that error?

Magento Order Info with TAX

does this help ?
http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/files/head:/c2c_account_tax_rounding/

just tried your example - works here
server runs Python 2.6.6

Happy New Year!

> Ferdinand @ Camptocamp (office-chricar) wrote on 2011-12-25:
> does this help ?
> http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/files/head:/c2c_account_tax_rounding/

Yes it does!
Thank you very mach!

> Ferdinand @ Camptocamp (office-chricar) wrote on 2011-12-25:
> does this help ?
> http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/files/head:/c2c_account_tax_rounding/

Not quite. as @mrockel described for most of all amounts it works now but still has problems with float issue for rounding when a (tax)amount is exactly in the middle.
e.g.
Item 1 = 96.75
Item 2 = 6.75
sub Total = 103.50
Tax 19% = 19.66
GrandTotal = 123.16

This is wrong an should be 123.17

103.50 x 19%= 19.665 so Grant Total would be 123.165 wich is rounded to 123.16 instead of 123.17

I can confirm this

Also on stock precision calculations:
This issue is not only evident on invoicing, but is also previlant on stock with unit of meausure's (UOM) precision more than 2 decimal places.
eg. weight, running meters.
Anything that has more than 1 or 2 precision level. (eg. stock quantities, cost price,sales price, pricelists) needs to be standardized throughout openerp.
We have come across several examples where packing lists cannot be validated because of simple float comparisons not working.

you might also be interested in
http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/files/head:/product_price_unit_precision/

Hi all,
I'd like to hear your view on the following merge proposal
https://code.launchpad.net/~openerp-community/openobject-addons/fix-account-trunk-tax-computation-method/+merge/104945
that should implement the famous tax computation "by column".

For who wants to try the changes for OpenERP 6.1, I'm attaching the patch

Hello
it seems to solve the problems here in Austria
Having incorrect/unexpected tax computation seems to be an show stopper here in Austria
How to proceed on this issue ?

New version of the "tax by column" implementaion is here https://code.launchpad.net/~akretion-team/openobject-addons/trunk-add-tax-rounding-option/+merge/113833
It passed our tests (see https://code.launchpad.net/~elbati/openobject-addons/add-yaml-tests-for-tax-computation/+merge/114679 ) so I would consider this bug fixed

Marking as fixed in version 7.0: a merge proposal introducing a "tax calculation rounding method" setting (global/per-line) landed at revision 7018 in trunk, courtesy of Akretion (as Lorenzo noted in comment #58).