rounding errors

[lgarvey]

Hi,

 

An oscommerce installation I have been developing for several months has occasional orders with final total prices that are 0.01 inaccurate. It happens infrequently, but is nonetheless is annoying and is something I will have to fix.

 

I have looked up several issues with PHP's rounding functions which appear to be native to PHP. It seems that even the osc team posted to bugs.php.net complaining of an inconsistency in the way PHP's rounding functions work.

 

However, I have run the code fragment on that bug report, and other examples, on the server and it appears PHP is rounding correctly.

 

Someone else has suggested the rounding issues occur because of a loss of precision due to the fact that prices are stored in the database accurate to 4 fractional digits, ie. .0000

 

Has anyone encountered and fixed this problem? Perhaps just altering the database tables to set all price fields to something like DECIMAL(15,8) might be a solution? Or is something else causing it?

 

All help is greatly appreciated.

 

Regards,

L

[failsafe]

Has anyone encountered and fixed this problem? Perhaps just altering the database tables to set all price fields to something like DECIMAL(15,8) might be a solution? Or is something else causing it?

 

All help is greatly appreciated.

Perhaps you could try 15,8 to see?

 

If you could supply a particular case where the rounding error occurs then maybe someone will look into it. Otherwise, you're probably closing the door to folks that haven't encountered the problem (i.e. most of us) who would need to run extensive tests to try to discover a case that goes wrong, a possible futile exercise if the cause is actually a coding error in your own code.

[satish]

Yes this happens as some code part takes the order total where as the other part loops thru each product calculates taxes(rounding off the taxes).SO if there are too many products the round off effect will result in this.

 

 

Satish

[lgarvey]

What is the solution?

 

L

[failsafe]

What is the solution?

Good question. I did a little digging to understand the error. I don't have a solution, but can at least guess what's going wrong. I'm not sure there really is a solution. The problem is even suffered by SAGE accounts - I've had many an invoice produced by that containing similar rounding errors.

 

I'll take a simple example of 2 products using the UK sales tax (VAT) of 17.5% to make a stab at what's happening...

 

Say you have 2 products A and B. Prices excluding sales tax are 11.23 and 9.35 respectively. Customer orders both.

 

Invoice generated like this:

 

1 x Product A @ 11.23, sales tax (11.23 * 0.175) = 1.96525, so round this up to show 1.97, total price therefore 13.20

1 x Product B @ 9.35, sales tax (9.35 * 0.175) = 1.63625, so round this up to show 1.64, total price therefore 10.99

 

If you calculate the final total price by summing the final column (13.20 + 10.99) = 24.19

However if you instead sum the net values (11.23 + 9.35) = 20.58, then add the sales tax (20.58 * 0.175) 3.6015 you end up with 24.1815 which is rounded down to 24.18.

 

Yikes - there is 0.01 difference between the 2 answers! Obviously part of osCommerce works the total out one way, another part works out the total the other way. The problem is: which way is correct?

 

To me, the intuitive fair solution is to sum the net values, then work out the tax based on that. I suppose if there's something that isn't correct, you must say it is the rounded value shown for each of the tax calculations, which is normally shown on an invoice for individual products. Perhaps if you want to show the tax per product then the true value (without rounding) should be shown so that when the individual tax values are added together the correct combined tax is shown, which when rounded (just once) comes to the same answer as if you sum the net values and then work out the tax on that value?

 

Does this make sense? As I say, not a solution, but at least suggests how the problem manifests itself.

 

Does anyone have any light to shed on how the accounting should actually be done? (I won't look to the SAGE accounts package for help here :) ). I suspect it's a rounding problem we're just going to have to live with.

[germ]

The answer is to NOT show/calculate tax on any number except the total.

 

That eliminates any rounding/summation errors.

 

So you have:

 

1 x Product A @ 11.23

1 x Product B @ 9.35

==================

11.23 + 9.35 = 20.58 (total) x 1.175 (tax as a decimal) = 24.1815, which rounds to 24.18

 

Doesn't that sound logical?

:unsure:

 

As far as I know that's how the stores I shop in "in person" do it.

[failsafe]

The answer is to NOT show/calculate tax on any number except the total.

That eliminates any rounding/summation errors.

Doesn't that sound logical?

Yes, thanks Jim. That is very logical. At least I picked the same answer, sort of :)

 

There is one slight hassle though... when the price is stored in the database, some folks might enter the price including tax and let the system work out the net value (that gets stored). Say they want the final price to be 9.99 (including the sales tax at 0.175) then that yields a product net price of 9.99 / 1.175 = 8.50212766, rounded to 8.5021. Showing 8.5021 for the net price isn't really a good thing either, so this gets rounded to 8.50.

 

Should one really round the net prices to 2 decimal places when storing it in the database? Otherwise, might the same rounding error occur in reverse where, say, someone buying 5 items priced at 8.5021 would get a total net price of 42.5106 rather than the expected 42.50?

 

Grrr, these rounding problems are hard on the head. :(

[germ]

someone buying 5 items priced at 8.5021 would get a total net price of 42.5106 rather than the expected 42.50?

The prices in the DB should have no more decimal places that the final answer (two).

 

5 x 8.50 = 42.50

 

Then no problem!

:lol:

 

But, I'm not an accountant.

 

To me the whole is equal to the sum of the parts, as in:

 

Part A + Part B + Round( Tax on the sum of A + B ) = Whole price

 

And not:

 

Round(Part A x tax) + Round(Part B x Tax) = Whole price

 

Or:

 

(Part A x tax) + (Part B x Tax) = Round(Whole price)

 

I went over this in another thread a while back, but I don't remember exactly how osC does the invoice / total.

[lgarvey]

> Grrr, these rounding problems are hard on the head.

 

Agreed! =)

 

Surely it shouldn't be that much trouble to modify some of the order totals modules: Sales Tax & Totals so that they do calculations based on the net value, instead of individual items?

 

We're going to have to make some modifications. I just want to make sure that what we're doing is correct. Based on other posts I suspect that increasing the precision of prices in the database (000000000000000.00000000) will not solve the problem, but getting osc to calculate tax on net values will.

 

L

[failsafe]

The prices in the DB should have no more decimal places that the final answer (two).

To me the whole is equal to the sum of the parts, as in:

Part A + Part B + Round( Tax on the sum of A + B ) = Whole price

Thanks Jim, very logical and I agree that's a good solution. I haven't looked in detail what osC does and I'm not an accountant either, but my guess is that osC probably gets it correct (so far as this is possible)!

 

Lyndon, I've done some more digging and can see that just about everywhere I look from Google checkout to Intuit Business software, SAGE accounting to Zen Cart... they all encounter this same rounding problem. You might be on a hiding to nowhere trying to 'fix' this.

 

I can see Jim's suggestion works well if you were to quote all your prices exclusive of sales tax, then sum the values in the cart before calculating the sales tax on the final total figure and rounding appropriately. That is indeed a very accurate and fair way of accounting for the tax.

 

However, in the UK for a normal retail shop (and probably elsewhere too), it's necessary to show the gross value to the customer for each product, so there are no hidden extras like sales tax at the checkout stage. This ties in with how many retailers price their products with attractive values like 9.99 (rather than 10.00) for example, where the 9.99 includes the sales tax.

 

When you take your items to the checkout on the High Street, the shop simply sums the individual gross values together and presents the total as the amount to pay. This works out as an exact gross figure because it is made up of exact gross values of individual items. The rounding problem would show its ugly head if you tried to dissect the bill on a per item basis, to show each item's net and tax values and round each one of them appropriately to 2 decimal places.

 

In the osC admin back office, when you enter the price of a product you can either input the net or gross value, leaving osC to calculate the other, which it happily does to 4 decimal places. With UK sales tax being 17.5%, entering a 9.99 gross value yields a 8.5021 net value (when rounded to 4 d.p.). Perhaps that's wrong, but that's how osC does it at present.

 

So in summary, I suppose the correct intuitive solution (if there is one) is that if you show all the prices without sales tax in the shop then the final total should simply be the sum of their values, then add the single rounded sales tax figure on the result (as Jim suggests). But if you show all your product prices in the shop inclusive of sales tax, then the final total should be worked out as the simple sum of the values you have shown (as in the 5 x 9.99 gross value ending up as 49.95). To avoid the ugly rounding errors on invoices, perhaps you should never list individual products with both their net and tax values; you can either list all the 'accurate' net values (if your products are priced in your shop excl. tax) or list all the 'accurate' gross values (if your products are priced in your shop incl. tax). From the total price you can then easily show the total net and total tax parts.

 

To do it any other way, you quite simply can't avoid the rounding problems... I think it's a mathematical impossibility, but I'll be very happy to stand corrected on that if someone can show me otherwise.

 

Now, how does osC work this out exactly... I'll leave that one for now! :)

[lgarvey]

As far as I know osc stores the pretax value and the final value for each product. Then in the order totals modules it adds these up.

 

I think that the system may need reworking so that it calculates individual sales tax on products when sales tax is needed, such as on checkout_confirmation, etc. but then when calculating the total it takes the net value and adds to the total net value. This would be a solution, I think. And, thinking about it... it might only mean modifying the sales tax order totals module. I think it would get tricky, though, if CCGV and other order totals modules are used because then we'd have to consider how tax is processed on those items and adjust accordingly.

 

L

[♥Gyakutsuki]

Hi,

 

I found a solution for resolve this problem, I don't if it's perfectly.

 

The problem come in classes /shopping cart with this function : calculate_price in currencies.php

I think it's a new function introduced in the latest oscommerce

 

To resolve

replace in classes/shopping cart this

// $this->total += $currencies->calculate_price($products_price, $products_tax, $qty);

$this->total += tep_add_tax($products_price, $products_tax) * $qty;

 

And the calcul is ok

[Guest]

I've been working on this problem too ... the solution i've been looking at it related to how the rounding takes place in FUNCTION CART

 

Where the order needs to have tax applied you need to add a round.

 

'final_price' => round($products[$i]['price'] + $cart->attributes_price($products[$i]['id']),2),

 

And you work out if you need tax by looking at the geo_zone_id for the order.

 

	
$results = tep_db_fetch_array( tep_db_query("SELECT	geo_zone_id FROM zones_to_geo_zones WHERE zone_country_id =  '". $this->delivery['country_id'] ."' ") );
$tax_tf	= $results['geo_zone_id'];

 

I'm just in the process of testing this ... but it looks like a solution on all the test orders that have been placed so far. The trick it to work out the correct line item price early enough ... then all the other prices/calculations fall correctly into place. The solution was originaly worked out by a guy i work with ... then i just needed to work out how make osCommerce code work!!!! :blink:

[MrPhil]

I think the key to this is that there should never be hidden digits involved in any algebraic operations (+.-,*,/). The numbers you see displayed should be exactly what are added together (for example). You should never be adding numbers with "hidden" extra digits (beyond what's displayed). You should never be displaying values supposedly to be added (e.g., base price + tax for each item) and then calculating a sum in a different way (such as adding up the base prices and figuring the total tax on that sum).

 

Rounding is certainly necessary when the result of a multiplication or division results in more decimal digits than your currency supports. However, that rounded value is the one that should be used going forward, not an internal value with more decimal digits, and not a pseudo-value which is later recalculated in a different way. I don't know what the accounting standards say about an undisplayed internal value to be further multiplied (say, (base price + tax %)*quantity -> "extended" price per item), that is, whether you would round the price-plus-tax before multiplying by quantity, or carry the whole thing through and rounding at the end. You should be able to justify whatever numbers show in the shopping cart and the invoice, and a customer following along with a calculator should be able to duplicate your results. The same goes for selling items in non-unit quantities (e.g., 3.75 pounds of truffles), possibly for a price that has more decimal digits than usual (3705.24 kWh * 0.1365 dollars/kWh). That is, you need to state where you are rounding values in the process, and to how many decimal places.

 

If you have to show price + tax on a product page, that value (rounded) should be the one in the shopping cart and the one (multiplied by quantity) on the checkout page and invoice. If you need to show total base price and total tax separately, somewhere along the line, you don't carry both totals along... you carry the total base price, and subtract it from the grand total to get the tax figure. That way, all the numbers the customer sees add up correctly.

 

What you don't want to do is arbitrarily increase price decimal places (or whatever) in hopes of making rounding errors go away. You need to understand what values are being used and how they are rounded (not just for display, but for further use going forward), per applicable accounting standards and laws. In computers, all floating point operations have hardware limits on precision, something that you have to learn to deal with in a numerical analysis course. Add to this rounding to a fixed number of decimal places for currency purposes, and you have a potential mess. Note that some accounting packages do their calculations in integer values (e.g., pennies instead of dollars), which helps to enforce the use of rounded values, but leaves open the question of just how and when to round after a floating point operation (e.g., applying a tax rate). Maybe that's something that osC should look at?

 

To complicate matters, different countries have different rules on displaying prices. Someone mentioned that in the UK you have to show price + tax (no "hidden" surprises at checkout), while in the US you only have to show the base price (state sales tax, which can vary by locality, added at the end). So, you need to be able to configure which (if any) taxes are shown shown already added in, and carry that rounded value forward.

[websissy]

As a 45 year IT pro who has been around since the days of punched cards and paper tapes, here are grandpa methusalah's views on this issue...

 

There's a basic IT principle (rule) involved here that we old-timers worked out when we were building the first generation of software systems 5 decades ago. Sadly, each new generation of software systems is built by a team of newly-minted wet-behind-the-ears programmers and youngsters tend to make the same foolish mistakes the generations before them made. It's ironic, actually. If we built aircraft the same way, each new generation of aircraft would begin their life cycle by having their wings fall off in flight and end it by being crashproof but too costly to fly! Then again, isn't that what happens? :-) For some reason in IT we can't seem to learn from past mistakes.

 

The basic principle involved here is so fundamental it's hard to believe that in a fifth generation software system we're still debating it. The operating rule of thumb is: Calculate any value involving multiplication or division in just one place. Round the result to the number of decimals used in the currency and STORE it. Never do that calculation again. Always sum the details.

 

If this was being done in osCommerce, we wouldn't have this problem at all. But that's not the way OSC does it. In fact it violates that basic priciple in the most flagrant of ways. OSC captures and records the sales tax rate at the order-item/product level and (presumably because in some jurisdictions some items may be taxable -- or be taxed at different rates -- while others may not) they also calculate the tax at that level; but they don't bother to round and store the computed tax amount in the order detail records. Instead, OSC stores only the tax rateat that level. In doing that they save storage space (the same way their forbears saved space 50 years ago by storing only 2-digit years in dates). Instead OSC invites every programmer who ever needs the sales tax in the future to recalculate it!

 

Holy, moly, Batman! What a naive decision that was! It's a guaranteed way to always have rounding errors that can never be resolved. Like others here, I too looked at this code and several reports that reflect the dollar totals shown for accumulated orders and quickly concluded there's NO way to ever fix this problem downstream. The only way to fix it is to go all the way back to those order detail rows and round and store the calculated tax there (rounded to the nearest penny -- maximum of 2 decimal places) and then require all reporting programs from invoices to monthly and annual summaries that need sales tax later to always take the sum of those details.

 

There's no other way to do it correctly and no way to solve the problem after the fact.

 

Let's face it. OSC has a fundamental flaw here. That's the truth and nothing we do now will change it except to go back to the basic principle:

 

Calculate any value involving multiplication or division in just one place. Round the result to the number of decimals used in the currency and STORE it. Never do that calculation again. Always sum the details.

[MrPhil]

Calculate any value involving multiplication or division in just one place. Round the result to the number of decimals used in the currency and STORE it. Never do that calculation again. Always sum the details.

Yep. Basically what I said a year ago. To clarify and elaborate, any time you multiply by a non-integer value, you may introduce more decimal places into the result. If you round off for display purposes, you run the risk of having a mismatch when you perform the multiply on the summed base prices. You can add up the rounded values to get your total (in which case the total will match the total of what's shown on the individual product pages, but might not match the sum of the base prices multiplied by the tax rate, on the checkout summary). Or you can wait to apply taxes until the checkout page (which is not legal in some places). Either way, you need to clearly state how you're arriving at your totals, and to explain away any discrepancies in advance. There's just no way to consistently get the same results when showing price+tax on product pages and when adding up the base prices and then applying tax on the summary page. If you're going to show price+tax on product pages, you can't show base prices on your summary -- only the same (rounded) price+tax as on the product pages. Adding them up should then give a consistent result.

 

item 1: $6.99 + 8.125% tax = $7.5579375 to infinite precision ($7.56 rounded for display)

item 2: $11.89 + 8.125% tax = $12.8560625 ($12.86 rounded for display)

sum of rounded prices: $7.56+$12.86 = $20.42 (vs. $20.414 infinite precision)

checkout page sum of prices: $6.99+$11.89 = $18.88. With 8.125% tax: ($20.414 infinite precision) $20.41

 

Off by a penny. If you want to display prices with tax ($7.56 and $12.86), you have to use those numbers on the checkout page summary: total $20.42. If you display the base prices and then apply tax to the sum, you get $20.41. Or, you fudge the result by adding the rounded "with tax" values, but then the math doesn't add up. With a large number of items, the random drifts due to rounding up and down tend to mostly cancel out, but no guarantees that the roundoff errors won't go the same way and reinforce. In doing this example, I first tried $11.99 for item 2. That rounded down, so the total (after rounding) came out the same, either way.

[mghay]

That seems to be correct.

 

I have just that problem and I am just waiting for a customer to point it out to me and demand their 6 cents back!!!

 

 

and I would be most grateful if anyone can point me in the right direction to resolve it in the most simple way!

 

Meanwhile, I'm considering changing the db stored prices (i.e net) to include tax and then disable the net and tax displays!

[mr.vince]

I have been checking this over and over again...

 

Taking all the above into account, I did make an interesting observation. I do happen to have a problem with the following:

 

Shop base currency is Euros; we do offer GBP as well.

 

The shop has a product with a base price of EUR 94.95; this shows up as GPB 84.29. The original shipping in Euros is EUR 6.90, which becomes GBP 6.13. Now the total should be GBP 90.41, but shows up as GBP 90.42.

 

Sending the order to PayPal, PayPal shows a total of GBP 90.42, the correct amount. This amount is sent by the shop system, so there is a different way of calculating the total in the codebase.

 

I know that the total is calculated in the cart class, but I am currently unsure about which would be the most elegant way to fix this...