RE: [OFBiz] Users - VAT and rounding
Posted by David Garrett on Oct 01, 2005; 1:51am
URL: http://ofbiz.116.s1.nabble.com/OFBiz-Users-VAT-and-rounding-tp135121p135143.html
David & Si
Thanks for the response, your time and effort.
I am away travelling at the moment and responding promptly is difficult.
Your response all made sense till just before the end.
Assuming your formula was actually:
tax amount = (tax rate * after tax total) / (1 + tax rate)
... then I am in total agreement.
I did not follow the rest. If we can stay with the example I gave
(Australian GST= 10%)
Therefore
Final price= $135.80 ... This is the price the retailer show to the
consumer (mostly) ie incl GST taxRate= 10% Tax payable= (0.1 *135.80) / (1 +
0.1) = 12.34545455 = $12.35 As I understand therefore the required
basePrice= $135.80 - $12.35= $123.45 With this in place leads to totalprice=
123.45 * 1.1= 135.795 which rounds correctly for qty 1 to $135.80
In the case of the purchase of 25
We want the final price to be $3,395.00
The error occurs (given a base price of 123.45) the total price calculated
is $3,394.875 ~ $3,394.88 This is the wrong price to be displayed to the
consumer who was shown the unit price was $135.80 and is expecting
$3,395.00. It seems the ProductPrice.price being 123.45 causes problems in
the customer inc tax price at large quantities
I have attached another XLS which shows how I understand the example you
provided (which was not what I was trying to describe I wanted an inc tax
price of $135.80 therefore base $123.45)
Base= 107.35
incTax= $123.45
It works fine for qty=1 BUT fails for every other quantity.
The Tax included price is the most important. This is what the customer
expects to pay. They typically don't care abot the excl tax price. The Tax
office doesn't care about the pre tax price ... They only want their tax
amount = (tax rate * after tax total) / (1 + tax rate)
qty base rate tax $ incTax desired
status
1 107.35 15% 16.1025 123.4525 123.45 OK
2 214.7 15% 32.205 246.905 246.90 ERROR
3 322.05 15% 48.3075 370.3575 370.35 ERROR
4 429.4 15% 64.41 493.81 493.80
ERROR
etc
Does that make sense?
Do I have a misunderstanding?
Thanks
David G
-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
On Behalf Of David E. Jones
Sent: Friday, 30 September 2005 12:39 PM
To: OFBiz Users / Usage Discussion
Subject: Re: [OFBiz] Users - VAT and rounding
The calculations shown here aren't quite right and demonstrate an
interesting quirk in VAT tax calculation. I just researched this a bit ago
because I'm working (slowly) on revamping the tax calculation stuff and this
concern came up.
There are 2 ways of looking at a 15% VAT for a 110 Euro total price.
The trick is that the total (gross) price, or the price including (or
after) tax is 110 EUR. That doesn't mean that the tax amount is 13.5 EUR,
which is 110 * 0.15. The trick is that the 110 includes the tax, so it means
that
after tax total = before tax total + (tax rate * before tax total)
So, to calculate the pre-tax amount you would do:
before tax total = after tax total / (1 + tax rate)
So, for 110 EUR the before tax total is not 93.5, it is 110/1.15 = 95.65
Based on the 95.65 we can multiply that by .15 and we get: 14.35 (rounded
from 14.375) as the amount of VAT tax, and not 16.50...
To calculate the VAT amount based on the total that already includes VAT the
formula is:
tax amount = tax rate * before tax total and before tax total = after tax
total / (1 + tax rate) so tax amount = tax rate * (after tax total / (1 +
tax rate)) it's hard to simplify that much, but here's a variation on it:
tax amount = (tax rate * after tax total) / 1 + tax rate
What that last formula shows is that the difference between the 2 ways of
calculating it varies depending on the tax rate. So since VAT amounts
include the tax already you can't calculate the tax amount by just
multiplying the tax rate, you'd have to divide that by 1 + tax rate.
Does that make sense?
Keeping base prices in the system that don't include tax could lead to not
having the exact price you want, but I think it pretty much always works out
fine with the proper rounding to 2 places.
For 123.45 for example, you would calculate the base price by dividing it by
1.15 which leads to 107.347826... which rounds to 107.35. If you calculate a
15% tax on that it would be 16.1025, which rounds to 16.10, which added to
the base price results in the desired 123.45.
-David
On Sep 29, 2005, at 5:38 PM, Si Chen wrote:
> David, Manuel -
>
> I'm finally starting to understand your problem. Is this a correct
> description of it:
>
> In the US, the sales tax is calculated as an added premium to the
> price, so the customer is used to seeing $100 plus a sales tax of 10%
> or $10, for a total of $110.
>
> In Europe and Australia, the customer is shown a final price of say
> 110 Euros, of which 15% might be a VAT. So you will need to show the
> customer a net price of 110 Euros and separately record that 16.50
> Euros are due to the tax authorities.
>
> You are trying to solve the problem within the US-centric application
> by saying that the base price is 93.50 Euros, but eventually decimal
> precisions get you.
>
> What's the solution... Depends on:
> Does your customer need to see the VAT on his order? Or can you show
> him an order and invoices with just a net price that includes the VAT?
>
> Si
>
> PS Don't know if this helps, but there was this issue created a while
> ago on using BigDecimal for better precision:
> http://jira.undersunconsulting.com/browse/OFBIZ-377
>
> David Garrett wrote:
>
>
>> OK maybe I missed the obvious.
>>
>> I will try using
>> <field name="sourcePercentage" type="floating-point"><!-- for tax
>> entries this is the tax percentage --></field> Also thought the
>> amount (which I see is deprecated had more decimal points available).
>>
>>
>> The rate % will help but I suspect there may still be issues with
>> respect to the finalPrice.
>>
>> Eg See the attached XLS which has errors for most quantities when
>> base price is
>> $123.45
>>
>>
>> -----Original Message-----
>> From: [hidden email] [mailto:users-
>> [hidden email]] On Behalf Of David Garrett
>> Sent: Thursday, 29 September 2005 1:33 PM
>> To: 'OFBiz Users / Usage Discussion'
>> Subject: RE: [OFBiz] Users - VAT and rounding
>>
>> Help!!! I have run into a rounding issue similar issues , although I
>> am using Item Adjustments not calcTax.
>>
>> I am struggling. In theory it should be easy but when dealling with
>> the Tax Office calculation rules I find myself fighting with Ofbiz.
>>
>> The problem comes in using the "Line Item method" where the tax
>> payable must be maintained in maximum precision and then rounded for
>> the order total.
>> Consequently by using (item.basePrice +itemAdjustment) the total item
>> price has too many decimal places.
>>
>> The situation is even worse since what really needs to happen is that
>> the price really needs to be based on the final rounded incTax price.
>> Ref: http://www.ato.gov.au/corporate/content.asp?doc=/content/
>> mr200036.htm
>>
>> Ie given a taxRate of 10%, the VAT is 1/11 of the final selling
>> price.
>> Because of rounding of the final sale price ...
>>
>> basePrice x 10%
>> does not always equal
>>
>> Round( base x 1.1 ) / 11
>>
>>
>> Further, as indicated below customers expect the incTax price to
>> remain constant and not be subject to the rounding in the mail below.
>> This will always be a problem with ShoppingCartItem.getItemSubTotal
>> () being
>> ...
>> return (getBasePrice() * quantity * getRentalAdjustment()) +
>> getOtherAdjustments();
>>
>>
>> This would all be OK if I could set the ProductPrice.price db field
>> to sufficient precision but in postgres it is limited to 2 decimal
>> points - probably for very good SALES_TAX reasons.
>> <field-type-def type="currency-amount" sql-type="NUMERIC(18,2)"
>> java-type="Double"><validate method="isSignedDouble" /></field-
>> type-def>
>>
>> The only other solution I can see is a big job. That is, to modify
>> all the places where base price is used for calculations and allow
>> the option based on a Store/Tax setting to work based on a
>> "finalPrice". My GST is all really based on the final price that is
>> actually paid.
>>
>> I am prepared to live with these rounding issues in the short term
>> ...
>>
>> BUT ...
>> Is there a better approach that I should be following?
>> Have I missed the obvious?
>> Am I making this too complex?
>>
>> David
>>
>> -----Original Message-----
>> From: [hidden email] [mailto:users-
>> [hidden email]] On Behalf Of Manuel Meyer
>> Sent: Tuesday, 19 July 2005 7:48 PM
>> To: [hidden email]
>> Subject: [OFBiz] Users - VAT and rounding
>>
>> Hi All,
>>
>> I have a question regarding taxes and rounding.
>> In France, every product sold on a site has a VAT of 19.6%.
>>
>> When the administrator enters a product, he enters it without tax,
>> and I've added an entry in the product store tax setting, with a tax
>> rate of 19.6.
>>
>> In order to show taxes very time a product is added in the basket,
>> I've modified the calcTax service to ask the system to compute tax
>> even if a shipping address hasn't been entered, as this tax is always
>> applied.
>>
>> Up to here everything is fine, except that if an price admin enters a
>> product of 150 tax included, he will enter 125.42 as a default price,
>> and
>> 125.42 turns into 150 when you add tax on it.
>>
>> The problem is that if a user put 3 items of the same type in his
>> basket, the price is rounded to 450.01. By adding 7 products of the
>> same type, the price tax included will be 1050.02 and so on. As the
>> site is a BtoC, the customer does not want to know about VAT; he only
>> needs to see how much money he'll spend globally.
>> I've try to play with the currency format in the general.properties
>> file, as it is referenced in the code, but I still have this rounding
>> problem.
>> If I enter 150 as default price and remove the simple tax entry, the
>> accounting will be false as I won't have anymore vat entries in the
>> accounting part.
>>
>> What is the best practice to apply in terms of product price in order
>> to avoid this rounding problem?
>>
>> Any advice or comments are very welcome
>>
>> Thanks in advance and bst regards,
>> Manuel Meyer
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>> ---------------------------------------------------------------------
>> ---
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users
>
_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users
URL: http://ofbiz.116.s1.nabble.com/OFBiz-Users-VAT-and-rounding-tp135121p135143.html
David & Si
Thanks for the response, your time and effort.
I am away travelling at the moment and responding promptly is difficult.
Your response all made sense till just before the end.
Assuming your formula was actually:
tax amount = (tax rate * after tax total) / (1 + tax rate)
... then I am in total agreement.
I did not follow the rest. If we can stay with the example I gave
(Australian GST= 10%)
Therefore
Final price= $135.80 ... This is the price the retailer show to the
consumer (mostly) ie incl GST taxRate= 10% Tax payable= (0.1 *135.80) / (1 +
0.1) = 12.34545455 = $12.35 As I understand therefore the required
basePrice= $135.80 - $12.35= $123.45 With this in place leads to totalprice=
123.45 * 1.1= 135.795 which rounds correctly for qty 1 to $135.80
In the case of the purchase of 25
We want the final price to be $3,395.00
The error occurs (given a base price of 123.45) the total price calculated
is $3,394.875 ~ $3,394.88 This is the wrong price to be displayed to the
consumer who was shown the unit price was $135.80 and is expecting
$3,395.00. It seems the ProductPrice.price being 123.45 causes problems in
the customer inc tax price at large quantities
I have attached another XLS which shows how I understand the example you
provided (which was not what I was trying to describe I wanted an inc tax
price of $135.80 therefore base $123.45)
Base= 107.35
incTax= $123.45
It works fine for qty=1 BUT fails for every other quantity.
The Tax included price is the most important. This is what the customer
expects to pay. They typically don't care abot the excl tax price. The Tax
office doesn't care about the pre tax price ... They only want their tax
amount = (tax rate * after tax total) / (1 + tax rate)
qty base rate tax $ incTax desired
status
1 107.35 15% 16.1025 123.4525 123.45 OK
2 214.7 15% 32.205 246.905 246.90 ERROR
3 322.05 15% 48.3075 370.3575 370.35 ERROR
4 429.4 15% 64.41 493.81 493.80
ERROR
etc
Does that make sense?
Do I have a misunderstanding?
Thanks
David G
-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
On Behalf Of David E. Jones
Sent: Friday, 30 September 2005 12:39 PM
To: OFBiz Users / Usage Discussion
Subject: Re: [OFBiz] Users - VAT and rounding
The calculations shown here aren't quite right and demonstrate an
interesting quirk in VAT tax calculation. I just researched this a bit ago
because I'm working (slowly) on revamping the tax calculation stuff and this
concern came up.
There are 2 ways of looking at a 15% VAT for a 110 Euro total price.
The trick is that the total (gross) price, or the price including (or
after) tax is 110 EUR. That doesn't mean that the tax amount is 13.5 EUR,
which is 110 * 0.15. The trick is that the 110 includes the tax, so it means
that
after tax total = before tax total + (tax rate * before tax total)
So, to calculate the pre-tax amount you would do:
before tax total = after tax total / (1 + tax rate)
So, for 110 EUR the before tax total is not 93.5, it is 110/1.15 = 95.65
Based on the 95.65 we can multiply that by .15 and we get: 14.35 (rounded
from 14.375) as the amount of VAT tax, and not 16.50...
To calculate the VAT amount based on the total that already includes VAT the
formula is:
tax amount = tax rate * before tax total and before tax total = after tax
total / (1 + tax rate) so tax amount = tax rate * (after tax total / (1 +
tax rate)) it's hard to simplify that much, but here's a variation on it:
tax amount = (tax rate * after tax total) / 1 + tax rate
What that last formula shows is that the difference between the 2 ways of
calculating it varies depending on the tax rate. So since VAT amounts
include the tax already you can't calculate the tax amount by just
multiplying the tax rate, you'd have to divide that by 1 + tax rate.
Does that make sense?
Keeping base prices in the system that don't include tax could lead to not
having the exact price you want, but I think it pretty much always works out
fine with the proper rounding to 2 places.
For 123.45 for example, you would calculate the base price by dividing it by
1.15 which leads to 107.347826... which rounds to 107.35. If you calculate a
15% tax on that it would be 16.1025, which rounds to 16.10, which added to
the base price results in the desired 123.45.
-David
On Sep 29, 2005, at 5:38 PM, Si Chen wrote:
> David, Manuel -
>
> I'm finally starting to understand your problem. Is this a correct
> description of it:
>
> In the US, the sales tax is calculated as an added premium to the
> price, so the customer is used to seeing $100 plus a sales tax of 10%
> or $10, for a total of $110.
>
> In Europe and Australia, the customer is shown a final price of say
> 110 Euros, of which 15% might be a VAT. So you will need to show the
> customer a net price of 110 Euros and separately record that 16.50
> Euros are due to the tax authorities.
>
> You are trying to solve the problem within the US-centric application
> by saying that the base price is 93.50 Euros, but eventually decimal
> precisions get you.
>
> What's the solution... Depends on:
> Does your customer need to see the VAT on his order? Or can you show
> him an order and invoices with just a net price that includes the VAT?
>
> Si
>
> PS Don't know if this helps, but there was this issue created a while
> ago on using BigDecimal for better precision:
> http://jira.undersunconsulting.com/browse/OFBIZ-377
>
> David Garrett wrote:
>
>
>> OK maybe I missed the obvious.
>>
>> I will try using
>> <field name="sourcePercentage" type="floating-point"><!-- for tax
>> entries this is the tax percentage --></field> Also thought the
>> amount (which I see is deprecated had more decimal points available).
>>
>>
>> The rate % will help but I suspect there may still be issues with
>> respect to the finalPrice.
>>
>> Eg See the attached XLS which has errors for most quantities when
>> base price is
>> $123.45
>>
>>
>> -----Original Message-----
>> From: [hidden email] [mailto:users-
>> [hidden email]] On Behalf Of David Garrett
>> Sent: Thursday, 29 September 2005 1:33 PM
>> To: 'OFBiz Users / Usage Discussion'
>> Subject: RE: [OFBiz] Users - VAT and rounding
>>
>> Help!!! I have run into a rounding issue similar issues , although I
>> am using Item Adjustments not calcTax.
>>
>> I am struggling. In theory it should be easy but when dealling with
>> the Tax Office calculation rules I find myself fighting with Ofbiz.
>>
>> The problem comes in using the "Line Item method" where the tax
>> payable must be maintained in maximum precision and then rounded for
>> the order total.
>> Consequently by using (item.basePrice +itemAdjustment) the total item
>> price has too many decimal places.
>>
>> The situation is even worse since what really needs to happen is that
>> the price really needs to be based on the final rounded incTax price.
>> Ref: http://www.ato.gov.au/corporate/content.asp?doc=/content/
>> mr200036.htm
>>
>> Ie given a taxRate of 10%, the VAT is 1/11 of the final selling
>> price.
>> Because of rounding of the final sale price ...
>>
>> basePrice x 10%
>> does not always equal
>>
>> Round( base x 1.1 ) / 11
>>
>>
>> Further, as indicated below customers expect the incTax price to
>> remain constant and not be subject to the rounding in the mail below.
>> This will always be a problem with ShoppingCartItem.getItemSubTotal
>> () being
>> ...
>> return (getBasePrice() * quantity * getRentalAdjustment()) +
>> getOtherAdjustments();
>>
>>
>> This would all be OK if I could set the ProductPrice.price db field
>> to sufficient precision but in postgres it is limited to 2 decimal
>> points - probably for very good SALES_TAX reasons.
>> <field-type-def type="currency-amount" sql-type="NUMERIC(18,2)"
>> java-type="Double"><validate method="isSignedDouble" /></field-
>> type-def>
>>
>> The only other solution I can see is a big job. That is, to modify
>> all the places where base price is used for calculations and allow
>> the option based on a Store/Tax setting to work based on a
>> "finalPrice". My GST is all really based on the final price that is
>> actually paid.
>>
>> I am prepared to live with these rounding issues in the short term
>> ...
>>
>> BUT ...
>> Is there a better approach that I should be following?
>> Have I missed the obvious?
>> Am I making this too complex?
>>
>> David
>>
>> -----Original Message-----
>> From: [hidden email] [mailto:users-
>> [hidden email]] On Behalf Of Manuel Meyer
>> Sent: Tuesday, 19 July 2005 7:48 PM
>> To: [hidden email]
>> Subject: [OFBiz] Users - VAT and rounding
>>
>> Hi All,
>>
>> I have a question regarding taxes and rounding.
>> In France, every product sold on a site has a VAT of 19.6%.
>>
>> When the administrator enters a product, he enters it without tax,
>> and I've added an entry in the product store tax setting, with a tax
>> rate of 19.6.
>>
>> In order to show taxes very time a product is added in the basket,
>> I've modified the calcTax service to ask the system to compute tax
>> even if a shipping address hasn't been entered, as this tax is always
>> applied.
>>
>> Up to here everything is fine, except that if an price admin enters a
>> product of 150 tax included, he will enter 125.42 as a default price,
>> and
>> 125.42 turns into 150 when you add tax on it.
>>
>> The problem is that if a user put 3 items of the same type in his
>> basket, the price is rounded to 450.01. By adding 7 products of the
>> same type, the price tax included will be 1050.02 and so on. As the
>> site is a BtoC, the customer does not want to know about VAT; he only
>> needs to see how much money he'll spend globally.
>> I've try to play with the currency format in the general.properties
>> file, as it is referenced in the code, but I still have this rounding
>> problem.
>> If I enter 150 as default price and remove the simple tax entry, the
>> accounting will be false as I won't have anymore vat entries in the
>> accounting part.
>>
>> What is the best practice to apply in terms of product price in order
>> to avoid this rounding problem?
>>
>> Any advice or comments are very welcome
>>
>> Thanks in advance and bst regards,
>> Manuel Meyer
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>> ---------------------------------------------------------------------
>> ---
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users
>
_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users
taxerrors.xls (35K) | Free forum by Nabble | Edit this page |