VM2 VAT calculation for UK shops

[AH]

Just started looking at my migration to VM2  Using VM2.012b and started with the basic requirement to display and calculate taxes for a product.

I read that vat discrepancies are due to rounding but, whilst this is a factually accurate description of  the variances we see, the problem is that the VAT calculation is not being handled correctly.

In the UK If you sell business to consumer and are VAT registered you are legally obliged to show all prices including VAT.
You must also give the customer a breakdown of the VAT on the invoice. 
VAT payable = qty*Final sales price - ((qty*Final sales price)/(1+Vat rate))

Example:-

Item price inc VAT = £ 24.52
QTY purchased = 10
Final sales price = £ 245.20
VAT payable = £245.20 - (£245.20/(1+20)) = £40.86666 (round to 2 dp ) = £40.87

VM shows a TAX of £40.90

My Configuration:-
Product:-
Cost price : 20.43333
base price:  20.43333
Final price: 24.52

General settings
Tax setting:- Type of arithmetic operation = Vat tax per product.
Final sales pricing:  Rounding digits = 2
All other prices are rounding to 3 digits


Please can someone explain how to get the VAT to function to meet UK legal requirements.

[jenkinhill]

For Type of Arithmetic Operation are you using VAT tax per product or Vat tax per bill?

[AH]

Jenkenhill

Tax setting:- Type of arithmetic operation = Vat tax per product.

If you use Tax per bill the item on the product page is shown without tax.

Additionally -

There is (of course) a difference in the coding for the application of methods Tax per product and  VAT tax per product
Elements displayed for each setting:-

Tax per product

Price:
Base price with tax: £24.520
Sales price: £24.52
Sales price without tax: £20.433
Discount: £0.000
Tax amount: £4.087


Vat tax per product

Price:
Sales price: £24.52
Sales price without tax: £20.430
Tax amount: £4.090

The VAT tax per product is ok for a single item display on the product page.  It is the taxation in the cart that is invalid.

When I purchase in multiple items the VAT amount is NOT

Product Tax amount * Quantity
This calculation amplifies the rounding that was performed on the single item as the Quantity ordered increases. 

Overpaying the VAT for orders on the site wher the VAT amount has been rounded up, and underpaying on rounding down. 
VAT man will not be happy, VM UK users paying VAT end up with a tax problem.


If total value is £245.20  Vat is £40.87
http://www.vatcalculator.co.uk/


I had large discussions with Zanardi and others regarding this in vm1, eventually dropped it from the bug fixes (probably due to vm2 development) so I coded a "fix" myself. 

Hoewever, this means that for UK customers paying VAT and trading with consumers (not just trade), VM2 is non compliant.

Here is the thread from VM1, the same issue is still there in VM2.

http://forum.virtuemart.net/index.php?topic=75346.msg250620#msg250620


[attachment cleanup by admin]

[AH]

Milbo

Can you confirm this is being reviewed??  Not a single reply is concerning in a non beta product!

[jjk]

VAT calculation works for me, but last week I tested something similar with my VM2 installations (2.0.12f)/SVN because somebody had the same problem.
I tried VAT Total with various settings. And the calculation and rounding is correct in my case for 1 product and also i.e. for 15 products.
The example I used was:

Product price: € 29
Product VAT is €4,63
Tax rate 19% (Germany)
Placed 15 of this in my cart.
Total: € 435
VAT Total: € 69,45

I don't show the tax amount on the product page and also not in the cart, because I have plenty of products taxed with what the Britains call the "margin scheme" (Only my profit margin is taxed - and in these cases displaying the tax is not allowed in EU countries).

I tested the above with roundings set to "3" except the sales price, which I did set to 2.

You may be able to come up with accurate results if you experiment a little bit with rounding set to more decimals for calculated prices - except final sales price. Keep in mind that VM2 only stores the cost price. Everything else is calculated. So if rounding is applied to a price before the calculated final sales price, you will end up with rounding differences compared to calculating tax from the final sales price which is i.e 120 / 1.2 x 0.2 (British VAT 20%) in accounting.


My normal relevant tax rule settings (but these are certainly not usable for everybody) are:

Type of Arithmetic Operation: VAT tax per product
Math Operation: +%
Value: 19.00 (this is for Germany)
Shopper Groups: all selected
Country: All EU countries selected

Configuration - Pricing settings are:

Show Following Prices
Show Prices: yes

           Rounding Digits
Baseprice      -1
Baseprice modificator    -1
New baseprice modified by chosen product variant  -1 
Discounted Price without tax    -1
Salesprice without tax    -1
Tax amount     -1
Baseprice with Tax, but without discounts   -1
Final salesprice      0
Salesprice with discount, but without override  -1 
Discount amount     -1
Standardized price      2 (not relevant in my case)

The -1 is a special case, telling VM2 to use the rounding of the currency. When I tested the example I typed above, I had set all roundings to "3", except Final Salesprice was set to "2".

In my special case I have 4 different tax rules. 0%, 7%, 19% and a calculated tax rate in cases there is VAT only on my profit. For the latter reason I can't display the tax amount at all in VM2 and I made template overrides, where the tax amounts are commented out.


BTW - Do you know the "The VAT Guide"? It contains also some tips concering rounding and is available here:
http://customs.hmrc.gov.uk/channelsPortalWebApp/channelsPortalWebApp.portal?_nfpb=true&_pageLabel=pageVAT_ShowContent&propertyType=document&id=HMCE_CL_001596

[AH]

jjk

Thank you for replying.

I am aware that VM only stores the cost price of the items.

"Everything else is calculated. So if rounding is applied to a price before the calculated final sales price, you will end up with rounding differences compared to calculating tax from the final sales price which is i.e 120 / 1.2 x 0.2 (British VAT 20%) in accounting."

Your statement is a summary of the BUG.  Essentially in a UK retail site where the supplier is VAT registered and sells to the general public, prices should be shown including VAT where applicable, Nett prices can also be shown.

The invoice must then break down the Nett and VAT components (by line item is best option) 

On the Cart display and in the Invoice the VAT has to be calculated accurately - Try getting away with a rounding excuse to the Tax authorities!!!

As the retail price is shown including VAT, a quantity purchase results in the final cost being QTY * VAT inclusive price.

The VAT owed is determined from the Line item inclusive amount. 

I cannot work out a setting where I can get the item to show a VAT inclusive price on the product page and calculate the VAT element correctly in the Cart and on the invoice.

Am I the only UK user with such an issue??

[jjk]

Hi Hutson,

I don't know why it works for me but not for you. The VAT tax system is the same in all EU countries, only the rates vary. At present I don't have the time to test this in detail, as I'm very busy with my normal business for the next few weeks.
Just a few ideas:
You don't have to display the tax amount in the cart, the order view and in the email invoices. Personally I don't label them "invoice", because what VM2 is able to produce doesn't comply with legal requirements in some special cases (but many other shop and accounting software has this kind of problem, too). My customers get a "real invoice" along with the goods shipped.
It is legally sufficient if you display the total amount together with a string saying "incl. 20 percent VAT".

Theoretically it might be possible to avoid rounding differences completely if you round the net price to 0 decimals (or choose a net price which has not more than two decimals after applying VAT). This way there would be no rounding and therefore no problem. (Don't have the time to test this :-)

Not shure if this helps... 

[AH]

jjk

Many thanks for your reply again.

To replicate the problem set a tax rate of 20%

Vat tax per product

Set Price:
Sales price: £24.52
Sales price without tax: £20.433


You should see the issue.

I understand your constraints regarding the invoice, but will ensure it will be compliant for our needs.

We run vm1.1.19 on JM1.5.26 currently and have had to modify the basket and checkout code to correctly display VAT.  We were hoping that we would not need to do this again for VM2!!

[Milbo]

First:
use The right TAX, use the Vattax and not just the tax. The difference is that the VAT tax is recalculated from the final price.

Furthermore, please not this rounding shit again. The tax council looks for the endsum you take and is only interested if you pay for this final amount your tax. Rounding errors lower than 1 cent per invoice are ignored.

Please guys, learn rounding.

I studied it. I studied mechatronics, which all the time dealing with rounding errors. Please read this http://en.wikipedia.org/wiki/Laplace_transform , yes I learned that. I even earned my first money with it. Furthermore I studied really a lot of physics and there it is again http://en.wikipedia.org/wiki/Propagation_of_uncertainty . Furthermore I worked for numeric mathematicians and studied a lot this http://en.wikipedia.org/wiki/IEEE_floating_point (due a job as scientist).

You cannot solve this issue. It is impossible to have correct rounded vat per item and it is the same as calculated for the whole invoice. It is mathematically impossible! We minimized it almost best as possible. The rest errors are below 1 cent.

and if you want to know how it works, learn from the swiss guys, use chf and enable rappenrounding and you will be really, really surprised, that this is correct, but it is.

[AH]

Milbo

Thank you for taking the time to reply.

I AM using the VAT Tax per product the cart (examples stated this) and checkout calculation of VAT is WRONG.

It is good news to find that you have extensive experience in use of rounding as you have stated in your posting.

What users would like is a product that calculates the tax for the cart correctly.  This is not impossible,  as long as you do not create a final line item tax figure based on the single item tax amount multiplied by the quantity purchased.

Example:-

Item price inc VAT = £ 24.52
QTY purchased = 10
Final sales price = £ 245.20
VAT payable = £245.20 - (£245.20/(1+20)) = £40.86666 (round to 2 dp ) = £40.87

VM shows a TAX in the cart of £40.90

My Configuration:-
Product:-
Cost price : 20.43333
base price:  20.43333
Final price: 24.52

General settings
Tax setting:- Type of arithmetic operation = Vat tax per product.
Final sales pricing:  Rounding digits = 2

Even if the calculation started with the  20.433333   10 qty = 204.3333

Add vat = £245.1996

Nett is 204.3333  Vat is 40.8663

http://www.vatcalculator.co.uk/

No rounding problems that I can see should affect the calculation the VAT is not £40.90 even if you round.

Tax per invoice gives a correct tax amount (hmmm!)  I would use this configuration except that the nett amount is not provided to the product when this is configured.

Very happy to discuss as I need to get this right before I migrate to VM2 and would prefer not to have to create a custom modification as I had to do for VM1.

Again, I thank you for your great efforts in creating VM2 and working to keep it in the cart software race!

[Milbo]

Hmmm maybe I should try to write something down here, which can be then summarized for a FAQ:

Preambel:
Rounding is a simplification of a value. Mathematical a 2 is not 2.0000. If we take a look, just on the 2, we have an ideal Two, in computer slang we call it integer. But a 2.00 is a value, we just know the first two digits. We dont know if it is actually a 1.995 or a 2.004. We only know 2.00. Lets assume (and this is what people automatically do) that it is 2.00, periodically repeating 0, only then 2 is actually equal to 2.000. This is very theoretic and most people directly forget it.

But lets take a pragmatic look on it. Besides the virtual computer world and other specialities, numbers are there to represent values, which must be measured before. So if we measure a length, or weight, we have a measurement error. If we measure 2 kg, we need also the variance of our measurement tool, if the variance (= measurement accuracy) is 1 kg, we just know that it is something between 3 and 1 kg. Okey, lets assume we have a normal measurement accuracy, which is usually 1 digit. So if we measure 2 kg, we just know it is between 1.5 and 2.4 (mercantile rounding assumed). Of course, you say, hey with my eye I see if it is exact 2, but how good is your eye? And just lets assume you have a digital weighing machine. So just always keep in mind, rounding is simplifying a value.

Problem:
We have a product with 1 euro and a tax of 7%. The normal final price is obviously 1.07 euro, But what happens if the product costs only 0.1 euro? The final price 0.107 cannot be measured with our coins. So we must use the simplification and round it to 0.11. So if someone is putting this product into the cart, he should pay 0.107, but he cant so he will pay 0.11 and the tax authority wants from you 0.11/(1-(100/7)). Yes! They are usualy for the VAT not interested in the steps, just the money you finally take and they recalculate it from there.

0.11/(1-(100/7)) = 0,0082795 .... HA stop ! Exactly even this number is not clear. Only the first digits are quite certain, but then it starts to wreck. I take a calculator and round the intermediate results. Okey 100/7 = 14,28571428571429. => (1-(100/7) = -13,28571428571429 => 0.11/(...) = -0,0082795698924731
But lets round now =>  0.11/13.29 = 0,0082768999247555

It is easy to see, that the first 3 interesting digits are the same 0,00827, but then it starts to differ.

So and if someone is buying it ten times, we have obviously 10 * 0.107 and not 10 * 0.11. But what is now to to choose?
If you use 10 * 0.1.07 = 1.07, then the tax authorities want 7 cent and are happy. But your customers will say, "he, I dont understand that, single item is 0.11, but 10 items are 1.07. I expected 1.10 (of course in THIS example he wont complain). You have this effect of course also if you have different items in your cart.

So then you must use the other strategy, you take 10*0.11 = 1.10. But be aware, the tax authorities want a) the tax you displayed on your tax or/and b) the tax for the final result (8 cent).

So it depends on your customers and of course of your wares. Vm2 solved both models. Use a calculation rule for products and you have the 10*0.11 = 1.10 model. Or take the rules per cart and you have the 0 * 0.1.07 = 1.07 model.

So and now we come to the next problem. the law!
In some countries, you just show the price, without tax. Here it is easy, they should just use the cart rules. In other countries (like germany) the product details page must contain all costs (even shipment now), but only for end consumers. So if it is a B2B, it makes also sense to use the cart rule for better results. For B2C they must use of course per product. There are small rounding glitches. Lets take again the example from above. I am quite sure that even if you show 0.11 on your product page, that it is correct to show for ten products in the cart 1.07. Regardless B2B or B2C. But we also have other cases, people buying products for some hundred euros. So the right strategy differs also from your product value and the used digits of your used currency. Damn this are really a lot things to consider which are always a bit different.


So and now lets put the theoretical stuff aside and think about a vm2 solution. What we need is in the productdetail view a rule for the product, so that we have the finalprice with tax, but then to disable the product vat rule and use only the one of the carts. It should be quite easy to add it. Maybe as a kind of extra parameter for any rules.

[Milbo]

Item price inc VAT = £ 24.52
QTY purchased = 10
Final sales price = £ 245.20
VAT payable = £245.20 - (£245.20/(1+20)) = £40.86666 (round to 2 dp ) = £40.87

VM shows a TAX in the cart of £40.90

http://www.vatcalculator.co.uk/

absolutly correct. If you take the tax from the final result you get 40.87.
But the tax for one product is 4.09, and if you take 4.09 * 10 you get the 40.90 .

And you can do what you want, one of the results is always "wrong". Just make a experiement. lets assume you cannot use the quantity option. You must add the product 10 times. It is quite clear to see then, that either the intermediate results are correct and summed up. Or the intermediate results are rounded and the final result is using the unrounded values. then the result is more accurate, but the sum of the tax is not right. You cannot solve it.

[AH]

Milbo

You are exactly correct, we will always end up with a situation where a single solution does not fit the variety of B2B or B2C relationships

We  alculate the VAT on each item and aggregate these amounts (the "normal method" of accounting)

Because we deal with B2C we have to disclose the VAT inclusive price and can if we want to display nett as well. 

Customers pay the Vat inclusive price multiplied by quantity.

This never results in a rounding issue as the sales price inclusive is already rounded to 2dp.

As a consequence we end up with a line item total that is not subject to rounding variances no matter the rate or the quantity added.

Yes if we add 10 items with the same vat inclusive price we do end up with £40.90 but that is correct if line item VAT is the scheme being used and 10 separate lines are added.  This may look crazy but that is the oddities of a line item scheme, you win on some and lose on others.

So the tax is pretty easy to work out per line item =

VAT payable = Round to 2 dp ((qty*Final sales price) - ((qty*Final sales price)/(1+Vat rate)))

Nett price = (qty*Final sales price) - VAT payable

These figures are then stored in the relevant order detail and order total tables.

That is pretty much how I do it for VM1 and it gives us a compliant system that can (if required) disclose the VAT payable per line item at the time of purchase.

I am sure there are things I have not considered for VM2

Discounts spring to mind, applied after tax rather than before, that would require an horrific calculation at line item level but less so at total invoice by tax rate (some fun where multiple tax rates are applicable to an invoice!)

I hope that you can see where my concern is and that I am keen to help where possible.  Unfortunately I am not a programmer but a business consultant with a keen interest.

Many thanks again for your replies and consideration of this issue.

[jack19]

Hi,
I think it's the same problem I'm experiencing, see this trhead http://forum.virtuemart.net/index.php?topic=108743.0.
I think that Hutson has expressed the problem correctly, which is the same in Italy.
All that said Milbo is correct, but not good for us.
So we need to make the calculation according to our tax laws.
Regards

[jack19]

Hi,
I do not think is a problem of rounding. Milbo has fully explained this aspect. The approach is to use tax accounting and when to rounding.
e.g. (from cost price 20.43 not 20.43333)
In my configuration:
Cost price : 20.43
VAT (single product) = (20.43*20%) = 4.09
Item price inc VAT =  24.52

How to make a calculation in accordance with the tax laws of Italy?

QTY purchased = 1
20.43*1 *20% += 24.52
QTY purchased = 10
Final Price 20.43*10*20% += 245.16 (VAT 40.86 + 204.30)

In VM seems that the calculation is performed in this way:

QTY purchased = 1
Cost price : 20.43
VAT (single product) = (20.43*20%) = 4.09
Final Price = Cost Price*Qty + VAT*Qty = 20.43*1 + 4.09*1= 24.52

QTY purchased = 10
Final Price = Cost Price*Qty + VAT*Qty = 20.43*10 + 4.09*10= 204.30 + 40.90 = 245.20  *See note
it should be
Final Price 20.43*10 = 204.30 *20% += 245.16 (VAT 40.86 + 204.30)

* Note
This calculation should be corrected instead of starting from the final price as he said Hutson. However, it remains the problem of rounding starting from the final price, that should be:
Qty         Final P.     Cost Price                VAT
1   24,52   20,43      4,09
2   49,04   40,87      8,17
3   73,56   61,30      12,26
4   98,08   81,73      16,35
5   122,60   102,17      20,43
6   147,12   122,60      24,52
7   171,64   143,03      28,61
8   196,16   163,47      32,69
9   220,68   183,90      36,78
10   245,20   204,33      40,87


Regards