Re: [OFBiz] Users - VAT and rounding
Posted by David E. Jones on Sep 30, 2005; 3:39am
URL: http://ofbiz.116.s1.nabble.com/OFBiz-Users-VAT-and-rounding-tp135121p135125.html
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-tp135121p135125.html
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
smime.p7s (3K) | Free forum by Nabble | Edit this page |