http://ofbiz.116.s1.nabble.com/OFBiz-Users-VAT-and-rounding-tp135121p135138.html
currency-amount to floating-point. Are there any significant flow-on
problems this will lead to. I am suspecting there will be problems for order
On Behalf Of David E. Jones
Yes, that's a problem... When the tax rate is 3 digits of precision instead
digits. Another option would be to specify that a certain price includes
or, well, or I don't know. This wouldn't be a problem if are only selling
> 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>