Magento

**UPDATED TO CRITICAL BUG - AS OF 3-26-09: Magento according to Client is not accurately calculating tax and not compliant with tax law

This is affecting currently all versions of Magento (1.0 thru 1.2.x)

I think this hasn’t gotten much attention because its somewhat hard to understand and many online store owners aren’t familiar with how traditional cash register’s / tax calculation works. .. Let me clarify this bug which again applies to all versions of Magento:

Bottomline:

WHAT IS HAPPENING NOW: Magento is taxing products individually, rounding the tax and then adding the tax and making it the Total Tax

Item price * Tax Rate = rounded(Tax)
Item price * Tax Rate = rounded(Tax)
Item price * Tax Rate = rounded(Tax)
Item price * Tax Rate = rounded(Tax)

Total tax is the sum total of all the rounded taxes.. BUT rounding shouldn’t happen per item

IT SHOULD BE: Calculating tax based on the subtotal (of taxable products), then rounding the total tax and adding that to the grand total

[[ Begin Taxable good items ]]
Item Price
Item Price
Item Price
Item Price
Item Price
[[End Taxable good items
* Tax Rate
= rounded(Total Tax for the Order)

SO HOW DOES IT AFFECT MY STORE?: Result is your customers are being charged more/less tax than they should be charged.

OTHER RELATED ISSUES: This is also what is happening with discounts.. stay tuned for another post on this.. but it appears this issue is related to how discounts are being calculated.

REPORTED BUG: http://www.magentocommerce.com/bug-tracking/issue?issue=5616

...

More on this see a post from our project manager: http://www.magentocommerce.com/boards/viewthread/35994/

A client of ours has noticed that Magento may have an issue with the way that it is calculating SALES TAX and although the miscalculation is only a difference of a couple pennies, their bookkeeper says it would be alarming to the State Board of Equalization and could result in many headaches. They have an online store, powered by Magento, as well as a physical retail store located in California.

Roy/Yoav any confirmation of this? Will 1.3 have a fix? If not, is anyone interested in working with our dev team to fix this critical bug as it affects ALL Magento stores

********************************************

This one I’m going to have to ask the census here because I am no sales tax expert but I do know there is a realistic and possible major discrepancy. Unlike other Clients she actually has a popular physical location in the greater Los Angeles area. So, she compared the receipt from a “test” instore purchase to a test purchase her Magento instance and came up with different numbers . Both are configured with the same tax rate but applied in different ways…

So here is the issue… Magento appears to be calculating sales tax the following way according to 1.1.6 and previous versions

[[ Begin Taxable good items ]]
Item price * Tax Rate = rounded(Tax)
Item price * Tax Rate = rounded(Tax)
Item price * Tax Rate = rounded(Tax)
Item price * Tax Rate = rounded(Tax)
Item price * Tax Rate = rounded(Tax)
Item price * Tax Rate = rounded(Tax)
[[ End Taxable good items ]]

Total order tax is the sum total of the rounded tax for each item

vs

How our Client’s physical store calculates tax (and other cart platforms)

[[ Begin Taxable good items ]]
Item Price
Item Price
Item Price
Item Price
Item Price
[[End Taxable good items
* Tax Rate
= rounded(Total Tax for the Order)

Here’s an example of how this can be an issue:

How Magento tax appears to be working by default items shipped to lets say California at a 8.25% tax rate…

Magento’s Apparent tax method (viewable in Order receipt)

Item Price Tax Rate Rounded Tax Per Item
7.95 * 0.0825 $0.66
7.95 * 0.0825 $0.66
7.95 * 0.0825 $0.66
7.50 * 0.0825 $0.62
7.50 * 0.0825 $0.62
5.00 * 0.0825 $0.41
5.00 * 0.0825 $0.41
30.00 * 0.0825 $2.48
16.50 * 0.0825 $1.36
8.00 * 0.0825 $0.66
22.00 * 0.0825 $1.82

Subtotal $125.35
Total Tax $10.36

VERSUS

Traditional (our Clients method) and The Usual Subtotal based calculation

Item Price
$7.95
$7.95
$7.95
$7.50
$7.50
$5.00
$5.00
$30.00
$16.50
$8.00
$22.00

Subtotal $125.35 * .0825 Tax Rate (8.25%)
Tax Total $10.34

Tax method 1 - $10.36 vs Tax Method 2 - $10.34

You’ll notice the different tax totals .. Yes they appear to be a few pennies different per method but could make a huge difference for store processing lots of orders.. They might find either they are collecting excess tax dollars or less sale tax revenue.

From what I know and what people have told me the traditional method is what most people use… It appears Zen cart also has this bug and people have mentioned this..

http://www.zen-cart.com/forum/showthread.php?t=96174

I think the solution for Magento would be to remove the rounding function for each items tax calculation and just apply rounding at the end

because

(X+X +X+X) * Y = Z

but ...

(rounded X + rounded X + rounded X + rounded X )* Y does not always equal the Z from the equation above.. and if a company is placing lots of orders it could add up to alot of + or - when it comes to sales tax.. the last thing you want to have happen is you owe your State tax board $$$$$$$ of non-collected sales tax due to a flaw…

Any one else notice this? Is tax supposed to be calculated like the Magento method (per item rounded) ? Does our Client have an incorrectly configured cash register?

To answer your question first. Your client is correct, you should not round sub-totals always round the total number. Why? Well you showed that part This is basic math actually (no offense) and has nothing to do with sales tax, although governments can make it difficult, this not one of those things.

However this leads to the conclusion that there must be a flaw in the Magento system on this point. I know there are several Issues reported and those are assigned. My guess is it has everything to do with the rounding out or rounding out and not passing along enough digits.

In order to make that clear I have made a small table using your values.

Hopefully this comes across. If it is a bit messed up, look at the last row (SUM). The first amount is the Total order and the second amount is the total tax calculated from the Total order. The others are Sums from the x-digits formats.
As you can see only the 5-digit sum gets the closed to the tax calculated from the total order.

The solution is simple. Pass along all values with 5-digits. You could also do it with four, that is the minimum, but then you still will have differences from time to time. With five it is almost not existent.
If this is easy to implement is a second

I hope that cleared up some of the mystery.

Hello, i have the same problem. I am using Magento 1.1.7.

Is there a fix by now?

Furthermore i have the following curios case:

I’ve created a product with the price 2,86 € (excl. tax). The product-price is announced in the frontend incl. tax (19%). So far so good. But if i choosed two articles of the same product, the cart shows me instead 3,40 € the wrong price 3,41 €. This appears only when i choose two articles.

Have this the same reason (round-function) like above?

Does anyone made a bug-track?

if yes, pls post issue# i will treat a varien-crewmember to focus on that issue!

thnx
rico

I did not make a bug-report.

http://www.magentocommerce.com/bug-tracking/issue?issue=3043

edit
that is #6858

I believe we are close to fixing this… Gui i made a new rounding function in /app/code/core/Mage/Core/Model/Store.php and changed the way tax is being rounded (per item) in app/code/core/Mage/Sales/Model/Quote/Item/Abstract.php but we are testing it in a real scenario on our dev site.. I’ll let you know our results shortly..

actually we have plans to change the rounding methods in future magento versions (not only taxes)

@bytestorm
Good to hear.  Not before 2.0 I presume?

@dakman
I do believe that is a good start solution but I think the problem runs much deeper. However I am looking forward to see what you come up with.

Gui - 26 November 2008 03:27 AM

http://www.magentocommerce.com/bug-tracking/issue?issue=3043

edit
that is #6858

How and where do you actually change the rounding from four to five?

I am having the same problem with GST calculation in New Zealand....

Are there any developments on this issue. I only noticed it when I set up a test product with a value of £0.01. When you purchase a quantity of this item the tax is still calculated on at an individual level so no tax gets added to the row.

I had hoped the upgrade to 1.2.1 would resolve this but it still hasnt.

Can this please be looked at as its becoming quite critical to a lot of our clients.

Been sooo swamped havent had a chance to dive into this. This has been on my mind lately and I want to get around to fixing it but I really need the community/magento’s help on this one.. This is a complicated fix because it doesnt just effect the way taxes are calculated but discounts as well.. but YES THIS IS a critical issue..

He! Any updates on this issue? I’ve seen some updates but still doesn’t work when I order two of the same products.

Which version are you on?  1.2.1.1 states:

Fixed Bugs

* Fixed shopping cart row total including tax

Can anyone confirm that this corrects our problem here?

I have upgraded from 1.2 to 1.2.1.1 with the diff files. I can confirm that it doesn’t correct my problem.
When ordering two of the same products the rounding with tax is gives me trouble with the iDEAL payment that the total amount is not correct. (one cent short)