Incorrect tax/net price rounding on invoice
Giedrius Slavinskas
I've created an invoice with decimal product quantity, prices and found
out that tax is calculated differently from untaxed amount:
tax base = round( line_1_amount + line_2_amount + ... )
untaxed = round(line_1_amount) + round(line_2_amount) + ...
So in my case, i've got the tax base different from an untaxed amount.
And because of this, tax amount is calculated incorrectly also. See
attached image.
After this, I did some searching and found the discussion that was going
on some time ago:
http://groups.google.com/group/tryton/browse_thread/thread/7722292e1bf3a579?pli=1
So now i'm not pretty sure if this 'incorrectness' is the bug or the
feature :)
Why i think its wrong? Please open the attached invoice. If I sell all 4
products on one invoice the total price for customer will be 222,73€,
but if I write an invoice for each product (4 invoices), then customer
will pay 222,72€ in total (total tax amount will be 38,65€ instead of
38,66€), which is less..
Also having "Tax Base" different from "Total (excl. taxes)" does not
make any sense.
Giedrius Slavinskas
Cédric Krier
This sounds better as it is what we do when writing the amount for tax
report (in account.tax.line) but I don't think the current behavior is
wrong and this will not prevent some odd results due to rounding.
--
Cédric Krier
B2CK SPRL
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59
Email/Jabber: cedric...@b2ck.com
Website: http://www.b2ck.com/
Dr. Axel Braun
This (tax calculation per line) is at least not allowed in Germany
Cheers/Axel
--
Dr.-Ing. Axel K. Braun
Mobile: +49.173.7003.154
VoIP/Skype: axxite
PGP Fingerprint: CB03 964D 1CFA E87B AA63 53F3 1BD6 F53A EB48 EF22
Public Key available at http://www.axxite.com/axel....@gmx.de.asc
This mail was *not scanned* before sending.
It was sent from a secure Linux-Desktop:
IBM ThinkPad Z60m
OS: openSUSE 11.3 (i586)
Kernel: 2.6.34.10-0.4-desktop
KDE: 4.6.5 (4.6.5) "release 8"
Cédric Krier
> Am Mittwoch, 28. Dezember 2011 schrieb Giedrius Slavinskas:
> > Here is the patch that fix this issue:
> > http://codereview.tryton.org/210007
> >
> > Rounding is applied on each tax line instead of sum.
>
> This (tax calculation per line) is at least not allowed in Germany
But how do you encode it in the accounting?
And so what is the precision to keep for computation of taxes?
Dr. Axel Braun
> On 29/12/11 10:17 +0100, Dr. Axel Braun wrote:
> > Am Mittwoch, 28. Dezember 2011 schrieb Giedrius Slavinskas:
> > > Here is the patch that fix this issue:
> > > http://codereview.tryton.org/210007
> > >
> > > Rounding is applied on each tax line instead of sum.
> >
> > This (tax calculation per line) is at least not allowed in Germany
>
> But how do you encode it in the accounting?
> And so what is the precision to keep for computation of taxes?
I think this was already answered in the former thread about taxes and
rounding:
http://groups.google.com/group/tryton/browse_thread/thread/7722292e1bf3a579?pli=1
Additional Udos question was answered here:
http://www.carookee.net/forum/bilanzbuchhalter-fachthemen/24837833-0-01113
regarding the precision: Rounding is only allowed on the final sum, with the
standard business rounding to two digits
Albert Cervera i Areny
A Dijous, 29 de desembre de 2011 12:35:53, Dr. Axel Braun va escriure:
> Am Donnerstag, 29. Dezember 2011 schrieb C�dric Krier:
> > On 29/12/11 10:17 +0100, Dr. Axel Braun wrote:
> > > Am Mittwoch, 28. Dezember 2011 schrieb Giedrius Slavinskas:
> > > > Here is the patch that fix this issue:
> > > > http://codereview.tryton.org/210007
> > > >
> > > > Rounding is applied on each tax line instead of sum.
> > >
> > > This (tax calculation per line) is at least not allowed in Germany
> >
> > But how do you encode it in the accounting?
> > And so what is the precision to keep for computation of taxes?
>
> I think this was already answered in the former thread about taxes and
> rounding:
> http://groups.google.com/group/tryton/browse_thread/thread/7722292e1bf3a579
> ?pli=1
>
> Additional Udos question was answered here:
> http://www.carookee.net/forum/bilanzbuchhalter-fachthemen/24837833-0-01113
>
> regarding the precision: Rounding is only allowed on the final sum, with
> the standard business rounding to two digits
Btw, that is also the way it should work in Spain.
> Cheers/Axel
--
Albert Cervera i Areny
Tel: +34 93 553 18 03
Cédric Krier
> Am Donnerstag, 29. Dezember 2011 schrieb Cédric Krier:
> > On 29/12/11 10:17 +0100, Dr. Axel Braun wrote:
> > > Am Mittwoch, 28. Dezember 2011 schrieb Giedrius Slavinskas:
> > > > Here is the patch that fix this issue:
> > > > http://codereview.tryton.org/210007
> > > >
> > > > Rounding is applied on each tax line instead of sum.
> > >
> > > This (tax calculation per line) is at least not allowed in Germany
> >
> > But how do you encode it in the accounting?
> > And so what is the precision to keep for computation of taxes?
>
> I think this was already answered in the former thread about taxes and
> rounding:
> http://groups.google.com/group/tryton/browse_thread/thread/7722292e1bf3a579?pli=1
>
> Additional Udos question was answered here:
> http://www.carookee.net/forum/bilanzbuchhalter-fachthemen/24837833-0-01113
>
> regarding the precision: Rounding is only allowed on the final sum, with the
> standard business rounding to two digits
Ok, I think we should mixe both way. Which means rounding on each lines
the base amount and rounding at the end the tax amount.
Like that we will have a base amount equal to the untaxed amount and the
tax value will stay as it is now.
Giedrius Slavinskas
Kt, 2011 12 29 10:17 +0100, Dr. Axel Braun rašė:
> Am Mittwoch, 28. Dezember 2011 schrieb Giedrius Slavinskas:
> > Here is the patch that fix this issue:
> > http://codereview.tryton.org/210007
> >
> > Rounding is applied on each tax line instead of sum.
>
> This (tax calculation per line) is at least not allowed in Germany
>
> Cheers/Axel
>
In many business taxes (VAT) are already rounded per unit and not per
unit_price*quantity (or the tax sum). If you go to the shopping center,
you will see that the taxes there, are already included in the listing
price, so that means they are rounded per unit. If it would be rounded
per unit_price*quantity or the tax sum, you can get a strange situations
when buying for example 2 pens which list price is 1€ you must pay
2.01€..
Btw, if you read the law of VAT, you will see that this tax adds value
for single product, material or service and not for invoice.. So
logically, if you have rounding on the sum of taxes, that means you
adding value for the invoice.
Normally, VAT is counted like this:
VAT = round(unit_1_price * VAT_% / 100) * quantity_1 +
round(unit_2_price * VAT_% / 100) * quantity_2 +
..
VAT = round(VAT)
So speaking in words, we need to know what is the added value (in same
precision as the list price) for a single product. After that, we need
to sum up all VAT for a single product by its quantity. In case, when
all quantities are integer we always get an accurate VAT without need of
rounding tax lines or the tax sum. But if we have decimal quantity, we
need to round one more time. And think this the situation what the
Germany laws are speaking about. If we round each tax line, we will get
an incorrectness:
round( round(unit_1_price * VAT_% / 100) * quantity_1 ) +
round( round(unit_1_price * VAT_% / 100) * quantity_2 )
is not equal to
round( round(unit_1_price * VAT_% / 100) * quantity_3 )
where quantity_3 = quantity_1 + quantity_2
so we must round the sum and not the lines.
p.s. "This (tax calculation per line) is at least not allowed in
Germany", is it because its written in law or just "some accountant
said"?
Cédric Krier
> In many business taxes (VAT) are already rounded per unit and not per
> unit_price*quantity (or the tax sum). If you go to the shopping center,
> you will see that the taxes there, are already included in the listing
> price, so that means they are rounded per unit.
This is a special cases of sales with taxes included.
> If it would be rounded
> per unit_price*quantity or the tax sum, you can get a strange situations
> when buying for example 2 pens which list price is 1 EURO you must pay
> 2.01 EURO..
I don't understand your example
But any way, the tax computation is not a strictly increasing function.
> Btw, if you read the law of VAT, you will see that this tax adds value
> for single product, material or service and not for invoice.. So
> logically, if you have rounding on the sum of taxes, that means you
> adding value for the invoice.
>
> Normally, VAT is counted like this:
> VAT = round(unit_1_price * VAT_% / 100) * quantity_1 +
> round(unit_2_price * VAT_% / 100) * quantity_2 +
> ..
> VAT = round(VAT)
This is very strange because you can have a unit_price with a precision
lower than the currency one. So if you round such unit_price tax
computation, you will have 0.
> So speaking in words, we need to know what is the added value (in same
> precision as the list price) for a single product. After that, we need
> to sum up all VAT for a single product by its quantity. In case, when
> all quantities are integer we always get an accurate VAT without need of
> rounding tax lines or the tax sum. But if we have decimal quantity, we
> need to round one more time. And think this the situation what the
> Germany laws are speaking about. If we round each tax line, we will get
> an incorrectness:
> round( round(unit_1_price * VAT_% / 100) * quantity_1 ) +
> round( round(unit_1_price * VAT_% / 100) * quantity_2 )
> is not equal to
> round( round(unit_1_price * VAT_% / 100) * quantity_3 )
> where quantity_3 = quantity_1 + quantity_2
> so we must round the sum and not the lines.
Yes but:
round( unit_1_price * VAT_% / 100 * quantity_1 +
unit_1_price * VAT_% / 100 * quantity_2 )
is equal to
round( unit_1_price * VAT_% / 100 * quantity_3 )
And this is what we do in Tryton. The only issue is about the display of
base amount in the tax lines of invoice which should be rounded by lines
to have the same value as the untaxed amount.
Giedrius Slavinskas
On 29 Gruo, 15:34, Cédric Krier <cedric.kr...@b2ck.com> wrote:
> On 29/12/11 15:12 +0200, Giedrius Slavinskas wrote:
>
> > In many business taxes (VAT) are already rounded per unit and not per
> > unit_price*quantity (or the tax sum). If you go to the shopping center,
> > you will see that the taxes there, are already included in the listing
> > price, so that means they are rounded per unit.
>
> This is a special cases of sales with taxes included.
is not. Taxes
are always included. In this case, its just shown on the label.
>
> > If it would be rounded
> > per unit_price*quantity or the tax sum, you can get a strange situations
> > when buying for example 2 pens which list price is 1 EURO you must pay
> > 2.01 EURO..
>
> I don't understand your example
0,17 vat = 1€
actually VAT = 0,83*0,21= 0,1743
So when rounding is done on tax line (or tax sum), buying 3 pens would
cost 3,01 because:
0,83 * 3 = 2,49€ and VAT = 2,49 * 0,21 = 0,5229 ~ 0,52.
So 2,49 + 0,52 = 3,01€
> But any way, the tax computation is not a strictly increasing function.
0,83 * 100 = 83€ for pens and VAT = 83 * 0,21 = 17,43€
So total = 83 + 17,43 = 100,43€ (43 cents more than should be)
> > Btw, if you read the law of VAT, you will see that this tax adds value
> > for single product, material or service and not for invoice.. So
> > logically, if you have rounding on the sum of taxes, that means you
> > adding value for the invoice.
>
> > Normally, VAT is counted like this:
> > VAT = round(unit_1_price * VAT_% / 100) * quantity_1 +
> > round(unit_2_price * VAT_% / 100) * quantity_2 +
> > ..
> > VAT = round(VAT)
>
> This is very strange because you can have a unit_price with a precision
> lower than the currency one. So if you round such unit_price tax
> computation, you will have 0.
you will never buy
or sell such a single unit. That means, you have choosen too small
unit of measure.
If there is a case to have the product in such a small unit of measure
(or price), then
you need to setup the accounting transactions to have more precision
than
the currency one.
>
> > precision as the list price) for a single product. After that, we need
> > to sum up all VAT for a single product by its quantity. In case, when
> > all quantities are integer we always get an accurate VAT without need of
> > rounding tax lines or the tax sum. But if we have decimal quantity, we
> > need to round one more time. And think this the situation what the
> > Germany laws are speaking about. If we round each tax line, we will get
> > an incorrectness:
> > round( round(unit_1_price * VAT_% / 100) * quantity_1 ) +
> > round( round(unit_1_price * VAT_% / 100) * quantity_2 )
> > is not equal to
> > round( round(unit_1_price * VAT_% / 100) * quantity_3 )
> > where quantity_3 = quantity_1 + quantity_2
> > so we must round the sum and not the lines.
>
> Yes but:
>
> round( unit_1_price * VAT_% / 100 * quantity_1 +
> unit_1_price * VAT_% / 100 * quantity_2 )
>
> is equal to
>
> round( unit_1_price * VAT_% / 100 * quantity_3 )
>
> And this is what we do in Tryton. The only issue is about the display of
> base amount in the tax lines of invoice which should be rounded by lines
> to have the same value as the untaxed amount.
>
internet.
What if i want to sell a pen for 1€? I make the most accurate list
price
i can get 1 / 1,21 = 0,826446281 ~ 0,8264.
But its wrong even for single unit 0,8264 * 1,21 = 0,999944 ~ 0,99
And if i sell 10000 of them 10000 * 0,8264 * 1,21 = 9999,44
Cédric Krier
>
> On 29 Gruo, 15:34, Cédric Krier <cedric.kr...@b2ck.com> wrote:
> > On 29/12/11 15:12 +0200, Giedrius Slavinskas wrote:
> >
> > > In many business taxes (VAT) are already rounded per unit and not per
> > > unit_price*quantity (or the tax sum). If you go to the shopping center,
> > > you will see that the taxes there, are already included in the listing
> > > price, so that means they are rounded per unit.
> >
> > This is a special cases of sales with taxes included.
>
> Yes, it is different from sales point of view. But from accounting -
> is not. Taxes
> are always included. In this case, its just shown on the label.
I can not agree on this statement.
> >
> > > If it would be rounded
> > > per unit_price*quantity or the tax sum, you can get a strange situations
> > > when buying for example 2 pens which list price is 1 EURO you must pay
> > > 2.01 EURO..
> >
> > I don't understand your example
>
> Let's say we have 21% VAT, so Pen price in shop = 0,83 unit_price +
> 0,17 vat = 1€
> actually VAT = 0,83*0,21= 0,1743
> So when rounding is done on tax line (or tax sum), buying 3 pens would
> cost 3,01 because:
> 0,83 * 3 = 2,49€ and VAT = 2,49 * 0,21 = 0,5229 ~ 0,52.
> So 2,49 + 0,52 = 3,01€
This is because you are not using a correct unit price.
If you use a correct one:
unit_price = 1 / D('1.21') = D('0.8264462809917355371900826446')
tax = unit_price * 3 * D('0.21') = Decimal('0.5206611570247933884297520661')
unit_price * 3 + tax = Decimal('3.000000000000000000000000000')
QED :-)
All this misunderstanding comes from the point that you are looking at
tax included price which can not work and will not work in the ERP.
If you have such needs, you must define a way to retro-compute the taxes
for each line you are saling.
> > > Btw, if you read the law of VAT, you will see that this tax adds value
> > > for single product, material or service and not for invoice.. So
> > > logically, if you have rounding on the sum of taxes, that means you
> > > adding value for the invoice.
> >
> > > Normally, VAT is counted like this:
> > > VAT = round(unit_1_price * VAT_% / 100) * quantity_1 +
> > > round(unit_2_price * VAT_% / 100) * quantity_2 +
> > > ..
> > > VAT = round(VAT)
> >
> > This is very strange because you can have a unit_price with a precision
> > lower than the currency one. So if you round such unit_price tax
> > computation, you will have 0.
>
> Basically, when unit price is lower when precision of the currency,
> you will never buy
> or sell such a single unit. That means, you have choosen too small
> unit of measure.
No. I got a request when working at Tiny for a customer who was salling
battery to have a unit price with 3 digits.
And yes of course you don't sale unit of such product but you want to be
able to sale any kind of multiple of it not only by 10 pieces.
> If there is a case to have the product in such a small unit of measure
> (or price), then
> you need to setup the accounting transactions to have more precision
> than
> the currency one.
No, you can not do write accounting transaction with a lower precision
than the currency.
> > > So speaking in words, we need to know what is the added value (in same
> > > precision as the list price) for a single product. After that, we need
> > > to sum up all VAT for a single product by its quantity. In case, when
> > > all quantities are integer we always get an accurate VAT without need of
> > > rounding tax lines or the tax sum. But if we have decimal quantity, we
> > > need to round one more time. And think this the situation what the
> > > Germany laws are speaking about. If we round each tax line, we will get
> > > an incorrectness:
> > > round( round(unit_1_price * VAT_% / 100) * quantity_1 ) +
> > > round( round(unit_1_price * VAT_% / 100) * quantity_2 )
> > > is not equal to
> > > round( round(unit_1_price * VAT_% / 100) * quantity_3 )
> > > where quantity_3 = quantity_1 + quantity_2
> > > so we must round the sum and not the lines.
> >
> > Yes but:
> >
> > round( unit_1_price * VAT_% / 100 * quantity_1 +
> > unit_1_price * VAT_% / 100 * quantity_2 )
> >
> > is equal to
> >
> > round( unit_1_price * VAT_% / 100 * quantity_3 )
> >
> > And this is what we do in Tryton. The only issue is about the display of
> > base amount in the tax lines of invoice which should be rounded by lines
> > to have the same value as the untaxed amount.
> >
>
> This is true as long as you are not selling things in the shop or
> internet.
Tax included must be managed in a separate workflow.
> What if i want to sell a pen for 1€? I make the most accurate list
> price
> i can get 1 / 1,21 = 0,826446281 ~ 0,8264.
> But its wrong even for single unit 0,8264 * 1,21 = 0,999944 ~ 0,99
> And if i sell 10000 of them 10000 * 0,8264 * 1,21 = 9999,44
Increase the precision of the unit price but the best solution is a
separate workflow.
Graeme Gellatly
Giedrius Slavinskas
On Dec 29 2011, 6:29 pm, Cédric Krier <cedric.kr...@b2ck.com> wrote:
>
> All this misunderstanding comes from the point that you are looking at
> tax included price which can not work and will not work in the ERP.
> If you have such needs, you must define a way to retro-compute the taxes
> for each line you are saling.
>
be not suitable for other business except retailers.
But the current tax computation is still inaccurate. I've made a fix
in the third patchset here:
http://codereview.tryton.org/210007
In the patch taxes are calculated like this:
tax_group_amount = round( round(line_total)*tax_group_rate +
round(line_total)*tax_group_rate + ... )
it is identical (no accuracy lost) to: tax_group_amount =
round( round(tax_group_base) * tax_group_rate )
It fixes inaccuracies in such a invoice:
Line1: unitprice=0.1260, quantity=1, subtotal=0.13
Line2: unitprice=0.1260, quantity=1, subtotal=0.13
Line3: unitprice=0.1260, quantity=1, subtotal=0.13
Line4: unitprice=0.1260, quantity=1, subtotal=0.13
Line5: unitprice=0.1260, quantity=1, subtotal=0.13
With patch:
vat_21_base=0.65, VAT_21%=0.14 (correct because 0.65 * 0.21 = 0.14)
Without patch:
vat_21_base=0.65, VAT_21%=0.13 (wrong)
--
Giedrius Slavinskas
UAB Inovera
+370.672.41216 | gied...@inovera.lt | www.inovera.lt
Cédric Krier
Sorry but it is correct because if you get a line like this:
Line1: unitprice=0.1260, quantity=5, subtotal=0.63
vat_21_base=0.63, VAT_21%=0.13
The way, we implemented, is to have the same value if we compute with 1
or 5 lines.
But of course, it is impossible to have an implementation that will
always work the best for every cases.
Morevover, your example is a case that should never happen in real life.
Giedrius Slavinskas
On 23 Sau, 16:00, Cédric Krier <cedric.kr...@b2ck.com> wrote:
> Sorry but it is correct because if you get a line like this:
>
> Line1: unitprice=0.1260, quantity=5, subtotal=0.63
>
> vat_21_base=0.63, VAT_21%=0.13
>
is 0.1323,
but 21% of 0.65 is not 0.13.
Of course these incorrect amounts are very small, and they can be
ignored. But the main purpose should be to have the correct invoice
without questionable amounts. I don't want to have a customer who
thinks that I don't know how to count :)
> The way, we implemented, is to have the same value if we compute with 1
> or 5 lines.
>
> But of course, it is impossible to have an implementation that will
> always work the best for every cases.
>
> Morevover, your example is a case that should never happen in real life.
>
the problem.
Cédric Krier
>
> On 23 Sau, 16:00, Cédric Krier <cedric.kr...@b2ck.com> wrote:
> > Sorry but it is correct because if you get a line like this:
> >
> > Line1: unitprice=0.1260, quantity=5, subtotal=0.63
> >
> > vat_21_base=0.63, VAT_21%=0.13
> >
>
> In this case, it is correct, because vat_21_base=0.63 and 21% of this
> is 0.1323,
> but 21% of 0.65 is not 0.13.
>
> Of course these incorrect amounts are very small, and they can be
> ignored. But the main purpose should be to have the correct invoice
> without questionable amounts. I don't want to have a customer who
> thinks that I don't know how to count :)
Any solution will always have such strange result.