Jump to content

Sales Tax Calculation wrong after 6.2.9 upgrade


Recommended Posts

A client in California has alerted me that his store is not correctly calculating sales tax.  I checked it and he is correct.  He said it started happening after the upgrade to 6.2.9.  I checked his tax rate setup and his zones and all appears to be done correctly.  He has it to calculate on items and shipping.  CA sales tax is 9% and it appears that it is calculating a fraction of a percent over that.  The amount of error seems to depend upon the size of the total order.  Anyone else experience this or have any idea where I should start looking?  It all should be pretty straightforward so I am not sure how this can happen.  I thought maybe it was because some products are discounted but when I do the math without the discount it is still off.  Example is attached that shows an incorrect tax, it should be $25.15.  Thanks for any help or ideas of where to go to try to track this down.

Screen Shot 2020-03-24 at 9.31.39 AM.png

Edited by KirkM
Link to post
Share on other sites

Having examined the changes between CC628 and CC629, there is nothing that would explain why the tax would start to be calculated erroneously.

Please mention what version the store was upgraded from.

 

Link to post
Share on other sites

So, the product prices... Are these single unit prices? I can understand $X.96 and $X.97 prices, but $X.27 and $X.36? Would these be  +$X.30 and +$X.40 option costs?

 

Edited by bsmither
Link to post
Share on other sites
Posted (edited)

Store was upgraded from 6.1.13

2 hours ago, bsmither said:

Are these single unit prices?

Yes.  Those cents you find odd are the result of a percentage discount off of the regular price.  Everything in the store is $X.95 at regular price.  For example, the item that is $58.36 is normally $72.95 and is discounted 20% to $58.36, which they have entered in the product's pricing tab under sale price.  No products in this store have options.

2 hours ago, bsmither said:

And what are we looking at? An email? The admin Order Overview?

Order summary in the admin area.

Edited by KirkM
Link to post
Share on other sites

So, "sale prices" at 20% off -- per product or global sale mode? -- as well as a fixed discount of $20? Or did the $20 just happen to add up to the individual 20% off amounts?

If possible, please let us know the full retail price of the items.

 

Edited by bsmither
Link to post
Share on other sites

It appears that the discounts are manually calculated by them and each sale price is entered in each individual product's "sale price".  The $20 off is a coupon discount for the entire order in addition to the individual sale prices on each item.  I just checked the one item quickly and saw that the sale price was exactly 20% off the regular price so I assumed that they are doing that with all of the products on sale.  However, I did a spot check on a couple of others and they are all different percentages off so maybe they are discounting based upon cost or something.  Anyway, it shouldn't matter since they are manually entering a sale price on each individual product.  It could be anything and the math should still work on the tax calculation.  There is not any kind of global discount, they are all product-specific.  See the screen shot for an example of how each sale product price is entered.

1944573102_ScreenShot2020-03-25at7_47_33AM.jpg.d554eab2f9613da655da36f6503995cc.jpg

Here are the numbers in regular price -> sale price:

$99.95 -> $83.96
$72.95 -> $58.36
$19.95 -> $11.97
$49.95 -> $33.27
$79.95 -> $66.96
$59.95 -> $36.97

And here is the tax setup, it is pretty straightforward:

619607826_ScreenShot2020-03-25at8_09_27AM.jpg.a19257761c01b63a1756fc989a394896.jpg

Link to post
Share on other sites

I have recreated the above order locally, and I get the same figures.

I can trace through the code and will determine where the figures are summing to (incorrect) $25.23 instead of (correct) $25.15.

 

Link to post
Share on other sites

This is a case of rounding error creep.

It starts with a calculation at:

/classes/tax.class.php, near line 453 (also 443 for percent):

$amount	= sprintf('%.2F', $price*($tax['percent']/100));

Try:
$amount	= $price*($tax['percent']/100);

The $amount, limited to two decimal digits (i.e., 7.56 vs 7.5564), is inserted into the product data array that is kept in the shopping basket.

Later, an average tax amount is calculated that includes the product tax amounts (26.24/291.49=9.002024%), the shipping tax (0.72/7.95=9.0566037%), and any other taxable amount at the appropriate tax rate.

It adds up. The rounding creep will add up to about $0.07 for an order of about $300.00.

You can certainly try a test shopping basket (you don't need to be logged in) after editing the above statement as suggested.

If you go this route, be sure to watch for tax amounts being expressed with high precision. Editing the skin templates could be the solution to this, which may close the difference between what is shown versus what it should be.

Tax rounding issues have been reported:
https://github.com/cubecart/v6/issues/515
https://github.com/cubecart/v6/issues/1801
https://github.com/cubecart/v6/issues/2135
https://github.com/cubecart/v6/issues/2147
https://github.com/cubecart/v6/issues/2150
https://github.com/cubecart/v6/issues/2210

 

Edited by bsmither
Link to post
Share on other sites

Thanks bsmither.  I see that the final conclusion of issue 2210 is that the tax calculation of the store really needs to be rewritten to solve all of the issues.  I am looking into sprintf vs round discussions to try to expand my knowledge of working with floats in php.

Forgive me if this is the dumbest question ever asked, but why can't the order carry two (non-displayed) subtotals: one for taxable items (& shipping, if specified as taxable) and one with non-taxable and tax-included items?  At the calculation / display endpoint, tax is calculated on the taxable item sum rounded to 2 dp (for western currencies).  Since tax always applies to subtotals per order and not individual items per order in normal commerce transactions, wouldn't this eliminate the "creep"?  You would only be rounding once at the end instead of creating a chain of rounding that increases the error with each additional line item.

It just seems that the tax work is being unnecessarily done on product line items where it isn't needed and should be moved to a taxable subtotal.  Are there accounting reasons for needing a sales tax breakdown for every item sold? 

Again, forgive me if this is incredibly naive.

Link to post
Share on other sites

I have no answer for any accounting rules.

My thought is to eliminate any rounding in the core code. Make the precision be as high as the computer system allows.

Finally, code the Smarty skins to make the last adjustment to the displayed figures. Or, exactly when assigning values to Smarty template variables, make adjustments there. That affects only the display, not the actual values. (CubeCart does this for alternate currencies. All values are in the store's default currency, but converted for show as needed.)

Everything is treated similarly. It saves on redundant coding for specific uses. Non-taxable and tax-exempt gets zero tax. An order could contain tax-exempt or tax-reduced items. CubeCart allows for the creation of arbitrary tax classes. (But cannot, as yet, apply fixed amount excise taxes.)

Link to post
Share on other sites

My wife did some accounting early in her career and tells me that line-item tax calculation is the way she was taught for various reasons, such as returns and refunds.  You have to be able to pinpoint the amount of tax paid for each item so you can refund the item cost AND the tax paid for it from a multiple-item order.

Therefore, your proposal seems like a reasonable theoretical solution.

However, right now, my clients need the tax to be accurate and can't wait for a possible fix in the distant future.  Tax authorities frown on taking excess tax from consumers.  Too little is fine because they just make you take it out of your profits to pay your sales taxes.  Too much can be considered defrauding the consumer since you end up with the excess that was taken under the guise of tax after you pay the correct amount to the government tax authority.  If you are going to rip people off, they want you to use the "Shipping and Handling" scam.  😉

I will try to use your fix idea above and see if that works for now.

Thanks again for all of your help and expertise.

Link to post
Share on other sites

So I tried the mod you suggested and it appears to work and give the correct tax based on my loading a basket with the same items that were in the original example.  As far as the customer's view is concerned, the display is still rounded to 2 dp.  I took it up to the hosted checkout so it would register in the admin as a pending order and that seems to display correctly also.  I don't think any template mods are necessary unless I am missing something.

Is there any reason this can't be in the next version as the actual bug fix?  It seems that the rounding on each taxable line item before subtotaling isn't necessary for the entire tax routine to work accurately.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...