VAT and Rounding Errors between OC3 and PayPal Express

OC 3.0.2.0 (nothing in the release notes about a fix for this in any later version)

Judging by multiple forum posts, there seem to have been ongoing issues, for some years, with rounding when adding VAT. I have found nothing that helps with the issue I have encountered.

I have two products, and postage, priced including VAT (20%):
Product A £50
Product B £10
Postage C £6

OpenCart data for these products has the ex. VAT prices as £45.8333, £8.3333 and £5.0000 respectively.

Product B can be added as an option (again, priced at £8.3333) to A or it can be added to the cart as a separate item.
In both cases the OpenCart order total is correctly calculated as £71.00 inc. VAT. This is communicated to the purchaser on their invoice.

In the case when B is added as [edit] a separate item the total passed to PayPal (and charged to the customer) is £70.99
This is totally unprofessional and unacceptable.

Why does this happen? Why does OC not just send the (known) correct amount to PayPal in both cases?
Where how does the rounding happen in both cases?
How can I fix it?

£45.8333
is not a valid amount in any currency. well maybe bitcoin
Sort your pricing properly and you will find your problem goes away.

Not very helpful!

Quote me the legislation that says prices have to be in whole pence.

Well try sending that amount to your bank.
It is NOT a valid amount
E-commerce and the rest of the world work with 2 decimal places. DECIMAL

What does OC work to, when calculating VAT or doing currency conversion?

£45.8333 * 1.20 = £50.00 Which is the correct VAT inc. price.

£45.83 * 1.20 = £54.996 Will OC round that up in it's own invoice and in what it passes to PayPal?
What if the customer orders two?
£91.66 * 1.20 = £109.992 Will OC change this to £109.99 (wrong) or £110.00? What will it pass to PayPal?

Your comment "sort your pricing out" is not helpful. Pricing does not revolve around quantities that always give whole pence when VAT is added/subtracted.

Obviously the final amounts have to be whole pence but somewhere additional precision MUST be used and OC needs to be consistent.

I think the problem arises from treating a product with an option as one item with a single price and applying its own rounding rules, rather than rounding each price individually and then calculating the total.

No
The problem arises with you not understanding the fundamentals of currency in e-commerce.
The rest of us do not have the problem.

£50.00 inc vat is £41.67 plus £8.33

I don't know about your calculations

crosland wrote: ↑

Fri Mar 12, 2021 9:28 pm

OC 3.0.2.0 (nothing in the release notes about a fix for this in any later version)

Judging by multiple forum posts, there seem to have been ongoing issues, for some years, with rounding when adding VAT. I have found nothing that helps with the issue I have encountered.

I have two products, and postage, priced including VAT (20%):
Product A £50
Product B £10
Postage C £6

OpenCart data for these products has the ex. VAT prices as £45.8333, £8.3333 and £5.0000 respectively.

Product B can be added as an option (again, priced at £8.3333) to A or it can be added to the cart as a separate item.
In both cases the OpenCart order total is correctly calculated as £71.00 inc. VAT. This is communicated to the purchaser on their invoice.

In the case when B is added as [edit] a separate item the total passed to PayPal (and charged to the customer) is £70.99
This is totally unprofessional and unacceptable.

Why does this happen? Why does OC not just send the (known) correct amount to PayPal in both cases?
Where how does the rounding happen in both cases?
How can I fix it?

The problem occurs because PayPal does it's calculations to 2 decimal places.
45.83 + 8.33 + 5 + 11.83 = 70.99

Whereas OpenCart will not use rounding until the end.
45.8333 + 8.3333 + 5 + 11.833332 = 70.99992 (which will round to 71)

You could enter your ex. VAT prices to 2 decimal places. But I'm guessing you enter the price as 8.3333 so the including VAT prices look to add up correctly (55 + 10 + 6 = 71 rather then 55 + 10 + 6 = 70.99).

There are a few options to look at which may be suitable in some situations.

• Adjust your prices so it doesn't happen. Might be impossible for some prices.
• Add and adjustment item to items sent to PayPal when the total doesn't match.
• Only send the total price of the order to PayPal.
• Enter you prices including VAT and don't calculate the VAT at checkout.
• Change the way OpenCart calculates the totals.

Where are you all getting 45.8333 from for £50 inc vat
This is not even a problem when you do the sums right etc etc etc

It £41.67 + vat £8.33 = £50.00

Making a problem where there is none

Sorry, typo, it should have been £55 inc. VAT, but exactly the same issues arise when the calculation does not result in whole pence.

As you can see it is confirmed that OC works to 4 dp.

Thank you for your considered reply It confirms by suspicions.

ADD Creative wrote: ↑

Fri Mar 12, 2021 11:34 pm

The problem occurs because PayPal does it's calculations to 2 decimal places.
45.83 + 8.33 + 5 + 11.83 = 70.99

Whereas OpenCart will not use rounding until the end.
45.8333 + 8.3333 + 5 + 11.833332 = 70.99992 (which will round to 71)

In the case of a product option it seems OC adds the product and option then rounds before sending to PayPal
so 45.8333 + 8.3333 = 54.1666 which is rounded to 54.17
PayPal does it's calculations to 2 decimal places.
54.17 + 5 + 11.83 = 71

Whereas OpenCart will not use rounding until the end.
54.1666 + 5 + 11.833332 = 70.99992 (which will round to 71)

It's the inconsistency in where the rounding is applied that is the issue.

You could enter your ex. VAT prices to 2 decimal places. But I'm guessing you enter the price as 8.3333 so the including VAT prices look to add up correctly (55 + 10 + 6 = 71 rather then 55 + 10 + 6 = 70.99).

There are a few options to look at which may be suitable in some situations.

• Adjust your prices so it doesn't happen. Might be impossible for some prices.

Unfortunately not an option.

[*]Add and adjustment item to items sent to PayPal when the total doesn't match.
[*]Only send the total price of the order to PayPal.
[*]Enter you prices including VAT and don't calculate the VAT at checkout.
[*]Change the way OpenCart calculates the totals.[/list]

The problem is most of those involve some element of coding. I can code, but it would take too long to understand the structure of OC before I could start.

Using only VAT inclusive prices would not be legal for orders above £250. where the VAT must be shown.

I would expect OC to behave "correctly", or at least consistently, "out of the box".

Well your still wrong because £55.00 inc VAT
£45.83 + VAT £9.17

You are fundamentally getting this wrong and ADD you should know that too
Just STOP putting more than TWO decimal places.
Opencart will deal with it fine and I have an idea paypal might know what they are doing also

mikeinterserv wrote: ↑

Sat Mar 13, 2021 12:14 am

Well your still wrong because £55.00 inc VAT
£45.83 + VAT £9.17

Correct, and no one said otherwise. In ADD's example

45.83 + 8.33 + 5 + 11.83 = 70.99

the total VAT is (correctly) £11.83

You are fundamentally getting this wrong and ADD you should know that too

If I exclusively use 2 dp for prices, e.g. 45.83, then OC will round the VAT inc. price 45.83 * 1.2 for one item to £55 - correct
Now add 5 of them to the basket 45.83 * 5 * 1.2 = 274.98, off by 2p - unnacceptable.

This is because OC works to 4 dp.

You are fundamentally misunderstanding the issues.

But £274.98 is the correct amount :-) that is what your BANK would calculate the amount as.
You are expecting 55 x 5 to be 275 - seems logical I know
BUT you are doing 5 x 45.83 + VAT
VAT calculated backwards gives you £229.17 not £229.15 from £275 total
You miss that the single 55 is rounded up but in the long calc the rounding changes depending on the amounts encountered

In this example the £274.98 is ACTUALLY more accurate than the single £55.00

mikeinterserv wrote: ↑

Sat Mar 13, 2021 12:54 am

But £274.98 is the correct amount :-) that is what your BANK would calculate the amount as.
You are expecting 55 x 5 to be 275 - seems logical I know
BUT you are doing 5 x 45.83 + VAT
VAT calculated backwards gives you £229.17 not £229.15 from £275 total

No. Consumers, in general, are only interested in VAT inc. pricing. If you go into a high street shop you expect the VAT inc. total for 5 items to be the total for 5 items, not 2p less. If you buy 5 items at £55 you expect to pay £275.

That's why ex. VAT prices have to be entered to 4 dp (which still doesn't work when you get to 1000s of items).

There are three fundamental issues
OC works from the ex. VAT price, so we have to use higher precision to get the correct VAT inc. price shown to the consumer. From my previous forum search in this issue, this is a well know aspect of pricing in OC.
OC calculates totals and rounds inconsistently when individual products are added to the cart v. when options are involved. This result in in different totals for the same products purchased. This results in differences between the OC invoice and the PayPal payment.

HMRC allows VAT calculations to be on a line-by-line basis or on the total. OC is using some hybrid approach and adding product + option and rounding this as some kind of sub-total.

If OC were consistent then the OC invoice and the PayPal payment would match in all cases (I think) and I would have no issue. The odd penny could be explained, but OC is not consistent.

Go and speak to an accountant
I have only been doing e-commerce for 20 odd years so what do I know.

If consumers are only interested in VAT inc prices then just display prices INC vat remove taxable goods option and it will work how you expect
5 x 45.83 is always going to be 229.15 + 20% vat is always going to be 45.83 - now - guessing you can add up.

mikeinterserv wrote: ↑

Sat Mar 13, 2021 12:54 am

You miss that the single 55 is rounded up but in the long calc the rounding changes depending on the amounts encountered

In this example the £274.98 is ACTUALLY more accurate than the single £55.00

You miss that £55.0000... is the consumer price, end of.

OC works on ex. VAT and requires more than 2dp to be used so that the consumer sees the correct figure. ADD understands that in his reply.

You also seem to be missing that I would not have an issue if OC were consistent in its treatment of individual products v. options.

mikeinterserv wrote: ↑

Sat Mar 13, 2021 1:34 am

Go and speak to an accountant

Unfortunately I doubt most accountants would be able to fix the OC code.

I have only been doing e-commerce for 20 odd years so what do I know.

If consumers are only interested in VAT inc prices then just display prices INC vat remove taxable goods option and it will work how you expect

If you really have been in e-commerce for so long then you would know that calculating and showing VAT is, as I have already said, a legal requirement for retail orders over £250 See https://www.gov.uk/vat-record-keeping/vat-invoices

As you know more than me I shall let you continue to explain it to others.
Then all the thousands of people who come here everyday with the problem will have a nice answer.

crosland wrote: ↑

Sat Mar 13, 2021 12:13 am

The problem is most of those involve some element of coding. I can code, but it would take too long to understand the structure of OC before I could start.

Using only VAT inclusive prices would not be legal for orders above £250. where the VAT must be shown.

I would expect OC to behave "correctly", or at least consistently, "out of the box".

crosland wrote: ↑

Sat Mar 13, 2021 1:16 am

There are three fundamental issues
OC works from the ex. VAT price, so we have to use higher precision to get the correct VAT inc. price shown to the consumer. From my previous forum search in this issue, this is a well know aspect of pricing in OC.
OC calculates totals and rounds inconsistently when individual products are added to the cart v. when options are involved. This result in in different totals for the same products purchased. This results in differences between the OC invoice and the PayPal payment.

HMRC allows VAT calculations to be on a line-by-line basis or on the total. OC is using some hybrid approach and adding product + option and rounding this as some kind of sub-total.

If OC were consistent then the OC invoice and the PayPal payment would match in all cases (I think) and I would have no issue. The odd penny could be explained, but OC is not consistent.

OpenCart is consistent. It does all calculations before currency conversion and format. It's just the results can look inconsistent. Take your example of a product with an option vs two products. OpenCart comes to the exact same total internally. As two products 45.8333 + 8.3333 + 5 + 11.83332 = 70.99992. As an option 54.1666 + 5 + 11.83332 = 70.99992. Both come to exactly the same total of 70.99992 which will round to 71. You could actually test this by changing the decimal places of your currency. Also note OpenCart doesn't work to 4 decimal places it is just the prices are stored in the database to this.

The inconsistent problem arises when these are passed to PayPal as 2 decimal places. "Item 1: 4.83" and "Item 2: 8.33" in one case, "Item 1: 54.17" in the other. Which is where the 0.01 difference comes from. If only the total was to passed to PayPal, as happens in a lot of the other payment modules, you wouldn't see an issue.

If you are using a payment module the sends item and not just the total, you basically have the choice of the final total not matching (or needing an adjustment) or the cart page looking like the prices don't add up.

The issues caused with doing the calculations before currency conversion and format has been brought up many times for more than a decade. The latest being https://github.com/opencart/opencart/issues/9413. Sadly I can't see the developer ever changing this and I guess whatever method use will always have it issues in some way or another. It would also require some quite significant changes for the core and many extensions.

Not that any of this helps you much, but may make it clearer to what is actually going on.

By the way, the showing VAT on orders over £250 only applies to VAT invoices not to the prices you show on your website. You also only have to send a VAT invoice when asked if your sales are 'retail'. I've have VAT invoices from large online UK retailers sent in a spreadsheet files. Where they have to manually produce it because their website doesn't deal with VAT. Of course going VAT inclusive only doesn't help if you are shipping to the rest of the world and want to zero-rate.

Nobody in their right mind tries to use more than 2 decimal places.
You are thinking of some parallel universe THAT is why the developer is NOT going to fix it. It does not need fixing.
Over 20 years this was never an issue. ADD you are being kind trying to explain But man not happening.
Fixation of trying to work backwards from a fixed price ie £55.00 is futile as many have found out. Its a rookie mistake.

ADD has made some good suggestions of what to do about it if you MUST be different to the rest of the world of commerce and accounting

mikeinterserv wrote: ↑

Sat Mar 13, 2021 6:56 am

Nobody in their right mind tries to use more than 2 decimal places.
You are thinking of some parallel universe THAT is why the developer is NOT going to fix it. It does not need fixing.
Over 20 years this was never an issue. ADD you are being kind trying to explain But man not happening.
Fixation of trying to work backwards from a fixed price ie £55.00 is futile as many have found out. Its a rookie mistake.

ADD has made some good suggestions of what to do about it if you MUST be different to the rest of the world of commerce and accounting

I agree that trying to work backwards can be futile, but it does seem common that some retailers want to do this. A lot of store owners end up entering more than 2 decimal places, as if you don't you can end up with the situation where the lines and/or totals don't add up when displaying prices including VAT. In this case selling a product in the UK for £55 another for £10 and charging £6 for delivery. That's £71 in total. Now, if you enter the prices excluding VAT, as you are selling outside the UK and need to zero-rate or you just want to display the ex. VAT price for your business customers. If you do that to 2 decimal places you will get a total of £70.99. Which to a lot of people doesn't make sense when they see it at checkout.

Then there's the 9.99 problem. How do you enter a price excluding VAT in order to sell at 9.99 including 20% VAT. Enter 8.32 and you get 9.98 when adding the 20%. Enter 8.33 and you get 10.00.

And then the the issue of selling in other currencies. Even if you enter prices to 2 decimal places, once the exchange rate is applied, you only have to say add 10 of an item to the cart and the unit price * 10 won't match the total for that line. Again doesn't make sense to a lot of people when they see it at checkout.