Posted by
Manuel Meyer on
Sep 30, 2005; 11:52pm
URL: http://ofbiz.116.s1.nabble.com/OFBiz-Users-VAT-and-rounding-tp135121p135135.html
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:
[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