Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Paypal / OSC Rounding Totals


Mort-lemur

Recommended Posts

Hi,

 

I have noticed a bit of an anomaly sometimes when customers use paypal to check out.

 

The order comments come back that the order value does not match the payment total - this is only ever by £0.01, but never the less its quite annoying - all I can think is that its something to do with rounding.

 

I am using stock Paypal Standard module and my checkout process as far as this is concerned is also standard osc 2.3.3.4.

 

I never encountered this on my 2.2Rc2a stores using paypal IPN

 

A screenshot is below:

post-253268-0-67711400-1391442702_thumb.png

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

This is a common problem -- applying a percentage markup/down to a price (such as tax), and rounding for display. Compare the sum of all such changed values to the sum of the original prices with the percentage applied to the sum, and it's common to see a small difference. If everything rounds in the same direction, it is possible to have a discrepancy of more than 0.01 currency unit. I don't know if anyone ever found a good solution to this. Is it possible that PayPal applies VAT to the entire order, while elsewhere it's applied to individual items?

Link to comment
Share on other sites

If you can, make the exact same order as far as checkout_confirmation. This should show 43.35 total.

 

If you look at the code of that page (view source in browser), can you please find & post the values of:

 

"shipping", "tax" and "amount".

 

Paypal standard passes the total like this:

 

'shipping' => $this->format_raw($order->info['shipping_cost']),
'tax' => $this->format_raw($order->info['tax']),

and

'amount' => $this->format_raw($order->info['total'] - $order->info['shipping_cost'] - $order->info['tax']),

 

 

Link to comment
Share on other sites

@@burt This is what I see from the source at the checkout confirmation page:

 

<tr>

<td align="right" class="main">Sub-Total:</td>

<td align="right" class="main">£37.95</td>

</tr> <tr>

<td align="right" class="main">UK Mainland (4 Kg) (Hermes 3 - 5 Day):</td>

<td align="right" class="main">£5.40</td>

</tr> <tr>

<td align="right" class="main">Total:</td>

<td align="right" class="main"><strong>£43.35</strong></td>

</tr> <tr>

<td align="right" class="main">Total Vat @ 20% Included in the Above:</td>

<td align="right" class="main">£7.23</td>

</tr>

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

@@burt OK I viewed the Hidden fields on the page after turning off encrypted payments and I can now see the problem:

 

shipping 5.40 tax 7.23 business [email protected] amount 30.73 currency_code GBP invoice 5263

 

Edited by Mort-lemur

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

..But I dont know how to fix it ....

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

Seems to be caused by rounding during the calculation of the Vat ie when there is a 3rd decimal place in the calculation of =>0.005 then the amount is rounded up.

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

No reflection on burt's analysis, but satisfy yourself that you are looking at the right instance of

 

'amount' => $this->format_raw($order->info['total'] - $order->info['shipping_cost'] - $order->info['tax']),

 

Comment the line out, to check that the transaction fails. Then try changing it to

 

'amount' => $order->info['total'] - $order->info['shipping_cost'] - $this->format_raw($order->info['tax']),

 

Suppose that 'tax' is rounded down by format_raw. In the quoted line of code, it is preceded by a minus sign. So there may be a sense in which 'amount' is rounded up. This would cause a one-penny discrepancy.

 

The two basic numbers in your screen-shot, 31.63 and 37.95, seem to be related as follows

 

31.63 * 1.2 = 37.956

 

That is, the 20% vat has been applied to the item values only. Not to the whole order value. We haven't been asked to discuss legalities, but are you sure that is the correct treatment? If you have been undercollecting vat then an occasional rounding error is the least of your problems. There is a further mystery in the line

 

Total Vat @ 20% Included in the Above £7.23

 

This figure seems to have been derived as

 

43.35 / 6 = 7.225

 

but we must divide by 5, not by 6, to split off the 20% vat. Is this 'Total Vat' consistent with the Paypal page, in the case of a successful transaction? Sorry to be just negative. But if the code is fundamentally flawed then fine-tuning, to fix a rounding error, would be premature.

Link to comment
Share on other sites

Total Vat @ 20% Included in the Above £7.23

 

This figure seems to have been derived as

 

43.35 / 6 = 7.225

 

but we must divide by 5, not by 6, to split off the 20% vat.

Um, P + 20% => P*1.2 => P*6/5 => P', P'*5/6 => P therefore T = P'/6

Link to comment
Share on other sites

@@Chris H @@MrPhil @@burt

 

The way the payment seems to be passed to paypal (from viewing the hidden fields) is as follows

 

Bearing in mind that my store displays prices with vat and there is vat applied to the shipping as well.

 

Item Net Value is £31.63

Item Absolute Gross is £37.956

Shipping net is £4.50

Shipping Gross is £5.40

 

So the total Vat amount is 37.956 - 31.63 +0.90 = £7.226 (rounded up to £7.23 on the screenshot) - So the Vat amount charged and declared is correct.

 

However, the way the payment is sent to paypal is that the vat amount of £7.226 is deducted from the Gross price of the items (37.95 - 7.226 = 30.724) which is rounded up to £30.73 by OSC.

 

Then the Gross shipping amount of £5.40 is added to the amount 30.73 + 5.40 = £36.13 then the Vat is added back in 36.13 + 7.23 = £43.26

 

It is this rounding when the Vat calculation goes into a third decimal place that is causing an incorrect amount to be passed to paypal.

 

Am I the only one who is seeing this problem on their site?

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

So, are you required by law to list the price as "including VAT", but PayPal needs it separated out as a tax? You're going to run into these complications when you build in a tax and have to remove it to display separately on the invoice. If the above is true, I'm wondering if it wouldn't be better to modify osC to store the price without VAT and when displaying a page or the shopping cart contents, to show price+VAT? What do you do for cases where VAT is not to be charged (sales to the U.S., for example?)? Is it possible to not tell PayPal that it's a tax, and just treat it as part of the base price ("including VAT")? There's got to be a better way than getting our undies tied in a knot over how to cleanly pull VAT out of a price and then putting it back in, accurate to the penny.

Link to comment
Share on other sites

@@MrPhil Not required by law to show prices inc vat - but it stops the shock factor at checkout when the order value increases by 20% when Vat is added on.

 

Some of my items are zero vat rated - so the problem does not apply to them.

 

And I don't export to the US or outside Europe so that problem doesn't apply.

 

This must be the same for any country though where a tax rate is applied to the item and the owner elects to show prices including tax in the store and MUST be specific to 2.3.3.4 + Paypal - as I mentioned above, I never saw this message in my 2.2 stores (maybe the message was masked?)

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

Hi

 

I *think* that you used to be able to send aggegated totals to PayPal with osC ??

 

The paypal_standard now splits out the costs with the rounding error (which you'll also get in Magento, Drupal & others) you could try sending an aggregated total by editing in paypal_standard

 

 $parameters = array('cmd' => '_xclick',
					 'item_name' => STORE_NAME,
					 'shipping' => $this->format_raw($order->info['shipping_cost']),
					 'tax' => $this->format_raw($order->info['tax']),
					 'business' => MODULE_PAYMENT_PAYPAL_STANDARD_ID,
					 'amount' => $this->format_raw($order->info['total'] - $order->info['shipping_cost'] - $order->info['tax']),
					 'currency_code' => $currency,
					 'invoice' => substr($cart_PayPal_Standard_ID, strpos($cart_PayPal_Standard_ID, '-')+1),
					 'custom' => $customer_id,
					 'no_note' => '1',
					 'notify_url' => tep_href_link('ext/modules/payment/paypal/standard_ipn.php', '', 'SSL', false, false),
					 'return' => tep_href_link(FILENAME_CHECKOUT_PROCESS, '', 'SSL'),
					 'cancel_return' => tep_href_link(FILENAME_CHECKOUT_PAYMENT, '', 'SSL'),
					 'bn' => 'osCommerce22_Default_ST',
					 'paymentaction' => ((MODULE_PAYMENT_PAYPAL_STANDARD_TRANSACTION_METHOD == 'Sale') ? 'sale' : 'authorization'));

 

change to:

 

 $parameters = array('cmd' => '_xclick',
					 'item_name' => STORE_NAME,
					 'business' => MODULE_PAYMENT_PAYPAL_STANDARD_ID,
					 'amount' => $this->format_raw($order->info['total']),
					 'currency_code' => $currency,
					 'invoice' => substr($cart_PayPal_Standard_ID, strpos($cart_PayPal_Standard_ID, '-')+1),
					 'custom' => $customer_id,
					 'no_note' => '1',
					 'notify_url' => tep_href_link('ext/modules/payment/paypal/standard_ipn.php', '', 'SSL', false, false),
					 'return' => tep_href_link(FILENAME_CHECKOUT_PROCESS, '', 'SSL'),
					 'cancel_return' => tep_href_link(FILENAME_CHECKOUT_PAYMENT, '', 'SSL'),
					 'bn' => 'osCommerce22_Default_ST',
					 'paymentaction' => ((MODULE_PAYMENT_PAYPAL_STANDARD_TRANSACTION_METHOD == 'Sale') ? 'sale' : 'authorization'));

Edited by Bob Terveuren
Link to comment
Share on other sites

I should mention that if VAT is separate (and just added to the displayed price), we still have the problem of a later step (such as PayPal) adding up all the base prices and calculating a percentage tax (such as VAT). That amount can easily end up a penny or two off from the sum of individual VAT amounts. Is it legal to calculate the VAT (or sales tax) separately for each item, and announce at checkout the sum of these amounts as the amount collected and remitted for tax, rather than adding up the item prices and recalculating VAT? They are likely to give two slightly different amounts. The same holds for any discount/tax/add-on calculated as a percentage -- sum of individual entries often will not equal the percentage applied to the total price, due to rounding for display. Certainly, customers will be confused if individual amounts don't add up to exactly what they are charged in the end. You can carry to discounts/add-ons/taxes along (rounded or unrounded) and sum them up, but then they may not exactly equal any amount calculated from summed prices. Assume it's no problem that some items have different percentages applied. What we have here is a chronic problem with consumer expectations, consumer protection laws, and tax laws not necessarily converging to the same numbers.

Link to comment
Share on other sites

@@Bob Terveuren Thanks - Ill give that a try tomorrow and report back

 

@@MrPhil If I understood what you are saying I think I would probably agree ..... (w00t)

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

I see that the 2.2 Paypal IPN mod includes several "fixes" annotated with

Tax pre item fix by AlexStudio
and
BOF Tax pre item fix by AlexStudio
and
BOF shipping & handling fix by AlexStudio

 

Im wondering if some of these need to be applied to the paypal standard module that ships with 2.3.3.4?

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

So is this an osc 2.3.3.4 Bug ?

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

Could someone confirm that this is how it works - or if it is something specific to my setup?

 

Many Thanks

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

  • 1 month later...

hello,

the solution to this problem is in file:

/includes/modules/payment/paypal_standard.php

line 643 from:

return number_format(tep_round($number * $currency_value, $currencies->currencies[$currency_code]['decimal_places']), $currencies->currencies[$currency_code]['decimal_places'], '.', '');

to:

return number_format($number * $currency_value, $currencies->currencies[$currency_code]['decimal_places'], '.', '');

 

regards,

Emmanuel

Link to comment
Share on other sites

  • 4 weeks later...
  • 10 months later...

This is a common problem with various shopping cart software, not just OSCommerce. Google "Paypal VAT rounding" and you'll find a whole heap of examples.

 

It generally occurs if you are setting exact figures for the Gross (inc VAT) price, and letting the system calculate the Net (ex VAT) price. This can result in a Net price with more than 2 decimal places. Within the e-commerce software, the net total is summed from the exact net prices, but when the prices are sent to Paypal, they round each price to 2dp and sum the result of that.
 

For example, say you have a product whose gross price is 9.99. The system will calculate the net price as 8.325. But Paypal will round this to 2dp before applying the VAT. They will probably round it up to 8.33, which results in a total of 10.00. But even if they were to round it down to 8.32, then the total would be 9.98. You simply cannot get a gross price of 9.99 from a 2dp net price. This is the crux of the problem.

 

There are various ways around it:

  • Set your products to all have 2dp net prices. This means that certain gross prices will be impossible, such as 9.99.
  • Send gross prices to Paypal and tell it not to add VAT. I don't know if this is legally ok.
  • Don't send individual items to Paypal, only the net total, and pre-round it in such a way to ensure that the gross total will be the same after adding VAT. But you will still have problems if your gross total can't be obtained from a 2dp net.
  • Alter the price of one of the items to account for the discrepancy, as seen this example for Magento. That's an interesting idea, but may result in the user seeming to be charged more than they should have for that item.

Out of all of these, I would be minded to keep it simple and go for the first one.

It's very easy to achieve in MySQL, assuming that your products table contains net prices as it should:

UPDATE products SET products_price = ROUND(products_price, 2);

Of course this will result in some of your gross prices changing by a penny, but will at least ensure that the Paypal total matches the OSCommerce total.

You can also ensure that this remains the case by changing the field type so that it only has 2dp:

ALTER TABLE products CHANGE products_price products_price DECIMAL(15,2) NOT NULL;

This will ensure that any price entered with more than 2 decimal places gets rounded to 2dp.

 

You might also like to change the javascript that calculates the net from the gross and vice versa, so that it does it to 2dp. In admin/categories.php, there are two calls to doRound() with 4 as the second argument - change that to 2.

 

If your shipping prices are set including VAT then you may need to change them as well.

Edited by Ben23
Link to comment
Share on other sites

  • 3 years later...

It's a long time after this thread closed, but I just had this problem.  A solution is to change a line in the relevant file in /includes/modules/payment (in my case paypal_pro_hs.php but I have seen this before in other paypal modules). A large array called $params is built at some point. Find the line that reads

'subtotal' => $this->format_raw($order->info['total'] - $order->info['shipping_cost'] - $order->info['tax']),

and change it to

'subtotal' => $this->format_raw($order->info['total']) - $order->info['shipping_cost'] - $this->format_raw($order->info['tax']),

That forces the rounded value of subtotal reported to Paypal to be consistent with the total value and tax.

(If your shipping charge might have decimal places that need rounding, make the corresponding change to that is probably necessary.)

Link to comment
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
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
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...