[OFBiz] Users - VAT and rounding
Locked
12
12
[OFBiz] Users - VAT and rounding
 
|
RE: [OFBiz] Users - VAT and rounding
|
|
RE: [OFBiz] Users - VAT and rounding
|
|
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:[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:[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 |
taxerrors.xls (30K)
Re: [OFBiz] Users - VAT and rounding
|
|
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 |
RE: [OFBiz] Users - VAT and rounding
|
|
Re: [OFBiz] Users - VAT and rounding
|
|
Yes, that's a problem... When the tax rate is 3 digits of precision instead of 2 digits then certain numbers are impossible to get as an after tax amount with only 2 decimal places... One option would be to store prices with more than 2 right of decimal digits. Another option would be to specify that a certain price includes VAT, and then we would have to specify the TaxAuthority party and geo IDs and probably the percentage used as a sanity check (should still have the tax settings in the database to handle variations on this). Perhaps the biggest problem with having prices that include tax is that then you either have to have all sorts of prices for different tax authorities, or, well, or I don't know. This wouldn't be a problem if are only selling from one place, ie only have one tax "nexus", but designing for that is something I'm trying to avoid... Perhaps changing the price to more than 2 digits of precision is the best approach... -David On Sep 30, 2005, at 4:52 PM, Manuel Meyer wrote: > Hello Si, David, and all. > Thanks to bring back this interesting topic. > > First, in order to answer Si's question, we unfortunately cannot > show a > total amount without a tax amount to a customer. We can show products > without tax details, but we have to display the global VAT amount > and the > related VAT% at least before the gross amount. > > For example > Product 1 10 EUR > Product 2 20 EUR > > VAT 19.6% 4.92 EUR > Total 30 EUR > > Second, I agree with David that we usually can run with a two digits > rounding, and keeping entering prices without taxes in the backoffice. > > The things went a little bit more difficult for me, I'll give you 2 > examples: > > As I am working on a B2C site, so prices on the front office must > include > taxes when they are displayed. Only order totals must display the > total > amount of taxes. > > In France, for the products sold on the site I worked on, the VAT > rate is > 19.6%. The very first product my client wanted to put on line was a > product > with a price including VAT of 120. > 120/1.196 = 100.33 or 100.34 using only 2 digits. > 100.34 * 1.196 gives 120.01 and 100.33 * 1.196 gives 119.99 using the > standard calculation, so it was not possible to have a price of > exactly 120 > on the front office by entering a price without taxes. > > The second example is a product with a price including VAT of 150 > (in fact > the second product my client put on line...): > 150/1.196 = 125.42 and 125.42 * 1.196 = 150. > But put 3 products in the basket, and you have 450.01, so you pay 1 > cent > more than what should be. > > I don't know what kind of rules to apply here, what I've done is a > really > ugly trick. As I know that there will always be a 19.6% of taxes > for all > products, I am entering prices with VAT on the backoffice (150, > 120...), and > then set no VAT records in the SimpleSalesTaxLookup table. After, I am > calculating the VAT amount using order VAT amount = order gross > amount / > 1.196 in order to display it... > > I was thinking of may be setting flags in the SimpleSalesTaxLookup to > specify that taxes of certain types were already included in the > price, and > after link products with this VAT amount, in order not to have > 'hard wired' > VAT amounts, but the fact is that I did not find any good way to > resolve > this issue. I've tried to see how other products are dealing with this > issue, but without a real success. I might have a look at other > frameworks > used in France like oscommerce or even webspere e-commerce, because > it is > still a pending issue for me, event if the current implementation > is enough > so far. > > I'll be happy to think about that with you when ever you want if > you think > this is valuable on your side. > > I took a look at http://jira.undersunconsulting.com/browse/ > OFBIZ-377, so > things might also have changed since the last time I've updated > from svn. > > Best regards, > Manuel > > -----Message d'origine----- > De : [hidden email] [mailto:users- > [hidden email]] De > la part de David E. Jones > Envoyé : Friday, September 30, 2005 4:39 AM > À : OFBiz Users / Usage Discussion > Objet : 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 > _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
RE: [OFBiz] Users - VAT and rounding
|
|
In reply to this post by Manuel Meyer
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 |
RE: [OFBiz] Users - VAT and rounding
|
|
Re: [OFBiz] Users - VAT and rounding
|
|
RE: [OFBiz] Users - VAT and rounding
|
|
Re: [OFBiz] Users - VAT and rounding
|
|
Re: [OFBiz] Users - VAT and rounding
|
|
RE: [OFBiz] Users - VAT and rounding
|
|
Re: [OFBiz] Users - VAT and rounding
|
|
Re: [OFBiz] Users - VAT and rounding
Administrator
|
Re: [OFBiz] Users - VAT and rounding
|
|
In reply to this post by T E Schmitz
Wow, we must be having a rough morning... In OFBiz all tax is calculated on a per-line level and that is what the variables represent. The intention is not to write software that works for a single jurisdiction, or by the rules of a single tax authority. Many business have to, or want to, operate in multiple jurisdictions at the same time. There is already a "plugin" facility for tax calculation because it, as most of the logic in OFBiz, is implemented as a service and that service can be easily replaced. There are two big tricks to this: a few touch points, and the need to support multiple jurisdictions simultaneously. A lot of businesses might be able to get by with a tax plugin that supports just the single place they operate in, but many companies also need to support multiple organizations within the company that can each be in different tax jurisdictions, and they need to be able to support orders from different types of businesses and in any area of the world. Perhaps if you don't consider it in that context my comments don't make sense. For a small business the convenience of entering the price including VAT might be important, but even if we do support that we can't JUST support that because it doesn't apply everywhere, and for companies that want to support prices for products that are sold from many different places in the world and to many different places in the world it may be easier for them to universally enter prices without tax included and let configuration elsewhere (and not per product...) determine how that should be displayed and calculated. So what do we choose to implement? As with most things, it depends on the needs of the sponsor of the implementation and all we can do is temper the design to work more generally and meet more general needs to not lock out users of the software in certain places. So, in the UK can you operate with prices not including tax rather than prices including tax? Yes, of course you can. As has already been discussed this would require more digits of precision. Will we do it that way? I don't know yet, but so far I haven't seen anything cross the table that makes this using this approach universally an impossibility. The other alternative that has been discussed in this thread is to support the storing of prices with and without tax included. Clearly a single calculation for the entire world is not possible, but I don't see how any plugin approach can address this issue, not without significant work during the deployment of the system... The goal is to parameterize things sufficiently in the tax related configuration in the database to support different variations on tax calculation and display. The nice thing about this approach is that as new jurisdictions are supported it won't require top to bottom implementation of services for that jurisdiction, but rather making sure that we have sufficient parameters to support the regulations there. The worst case scenario is that new configuration parameters will have to be added to support quirks in the tax law. This is also tricky because we can't just support tax laws as they are now, but also need to support changes in tax laws over time. The goal is not to support all of the quirks of one jurisdiction, or even one jurisdiction at a time, but literally thousands of jurisdictions around the world... It is an AWFUL problem. If you happen to live in a country with a single tax jurisdiction you are very lucky! Even if that jurisdiction has some funny rules... In many countries there are multiple tax authorities, and actually the United States may be the worst example of that because there are thousands of tax jurisdictions in the country (each state, county, and city can have their own tax laws) and thousands of different products that have unique tax requirements (like variations between selling of electricity and shoes for example...). BTW, the easiest way to handle all of this right now is to NOT to tax calculation in OFBiz but rather use an external tax calculation package like TaxWare or the CyberSource online tax calculation.... But, that also isn't an option for most... The current TaxAuthority entities are being designed with this in mind. Of course in the implementation other changes will have to happen. Changing the precision of amounts stored in the database is really pretty easy and the more difficult part is the calculation in with all of the variations and such... -David On Oct 1, 2005, at 1:32 AM, T E Schmitz wrote: > (My knowledge is specifically relating to UK VAT.) > > David E. Jones wrote: > > >> 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 >> > > David, in principle your formulae are correct, but they will *not* > lead > to the correct results. > > >> 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. ... the 110 includes the tax, >> > > correct > > >> after tax total = before tax total + (tax rate * before tax total) >> > > By "total", do you mean invoice total? This would be incorrect. > Different products might incur different rates of VAT. A UK invoice > might contain differently rated products and where delivery charges > can > be attributed to products, they are rated at the same rate as the > product. > VAT is calculated at line level (as they do Australia). This means, > the > VAT amount is worked out for every "line" on an invoice, and a line is > the sub-total of quantity * Product. > > >> So, to calculate the pre-tax amount you would do: >> before tax total = after tax total / (1 + tax rate) >> > > or, to avoid the word "total": > gross = net / (1 + vatRate) > > >> tax amount = (tax rate * after tax total) / 1 + tax rate >> > > You have worked out what we call the VAT Fraction: > > First of all, we calculate the so-called VAT Fraction from the VAT > rate. > This is the rate the gross (=VAT inclusive) amount needs to be > multiplied with to calculate the amount of VAT contained in it: > > vatFraction = vatRate / (vatRate+1) > > Example: > vatRate = 17.5% ==> > vatFraction = 0.175/(1+0.175) = 0.1489 > > If a product retails at £117.50, the VAT amount is > £117.50 * vatFraction = £17.50 > > The line VAT is calculated with a 4-digit precision and stored with a > 3-digit precision (10th of a penny). The final (invoice) total > amount of > VAT has a 2-digit precision (1 penny). > > >> Keeping base prices in the system that don't include tax could >> lead to >> > > At no point does a retailer work out or even think in terms of the net > (VAT exclusive) amount. As far as data entry is concerned, I would not > dream of asking my customers to enter net prices! > > You *cannot* store the prices as VAT exclusive amounts because you > will > not be able to work out the correct VAT amount from that because: > - VAT is calculated at line level where the quantity comes in > - VAT at line level is at 3-digit precision > - gross (=VAT inclusive) amount has to be displayed for product > > >> but I think it pretty much always works out fine with the proper >> rounding to 2 places. >> > > I think I'll frame this sentence and send it to my customer when he > gets > a VAT inspection ;-) > > >> 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. >> > > No. > gross = £123.45 > vatRate = 15% (I wish!) > vatFraction = 0.1304 > vatAmount = 16.097 or 16.095 or 16.098 > depending on which algorithm you use and on whether you are a retailer > or wholesaler. > > In any case this line VAT amount *must* be held with a 3-digit > precision. The total VAT amount will be rounded to penny precision at > the bottom of the invoice. > Calculations must be carried out using BigDecimal (although Java's > BigDecimal has a few shortcomings, see > http://www2.hursley.ibm.com/decimalj/decimald.html). > > > > My knowledge is specifically relating to UK VAT. Other European > countries and Australia handle retail prices and VAT in a similar > manner but the actual calculations differ and therefore the "one > calculation > fits all" approach will *not* be suitable (see Jira issue 459). > > The only thing that will work for all is a "plug-in" archictecture > as suggested by Jacopo, which will allow OFBiz users to write a > country-specific VAT calculation, which could possibly be loaded > based on the local settings. Over time, the OFBiz users will > contribute and maintain (!) the French, UK, Australian ... > algorithms. At this point, all you can do is pick a country (for > instance using Dave Garrett's link to the Australian Revenue site) > and develop that one calculation as a template. The rules and > regulations are constantly changing. You can impossibly maintain > this alone. > > But you can provide a structure that allows us to drop in our > calculations. The crucial thing at the moment is to identify the > entity and datatype structure that will fit all. > > -- > > Regards, > > Tarlika Elisabeth Schmitz, Scotland > > _______________________________________________ > Users mailing list > [hidden email] > http://lists.ofbiz.org/mailman/listinfo/users > _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
Re: [OFBiz] Users - VAT and rounding
|
|
RE: [OFBiz] Users - VAT and rounding
|
|
Re: [OFBiz] Users - VAT and rounding
|
|
| Free forum by Nabble | Edit this page |