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