Jump to content

Tax Rounding issue (and proposed solution)


alexm
 Share

Recommended Posts

Hi @kongondo

I'm just testing the tax calculations and the rounding doesn't seem to be handled quite right. 

The product is £3.50 and charge taxes on this product is enabled.
The store also has 'All taxes are included in stated prices' checked under tax settings and tax is set at 20%.

On cart page the total for 10 of this item is £35.

Once I get to the checkout confirmation the total after the tax calculation is £34.92 (see screenshot)

Screenshot 2024-05-16 at 23.25.11.png

Link to comment
Share on other sites

Ok I've found the issue. This is due to the tax being calculated for each item then multiplied by 10 giving you the lower figure that doesn't round to £35.

The more accurate way to calculate taxes for each line of items and arrive back at £35 (inline with the true product price) is to calculate the tax on the total figure for that line of items, not on one item then multiplying that.

By my testing and judgment (though I'll happily be told my logic is wrong ?) is as below:

$price_per_item = 3.50;
$quantity = 10;
$vat_rate = 20; // in percentage

// Calculate the total price including tax
$total_price_incl_tax = $price_per_item * $quantity;

// Calculate the total price excluding tax
$total_price_excl_tax = round($total_price_incl_tax / (1 + $vat_rate / 100), 2);

// Calculate the VAT amount
$vat_amount = round($total_price_incl_tax - $total_price_excl_tax, 2);

echo "Total Price Excluding Tax: £" . number_format($total_price_excl_tax, 2) . "\n";
echo "VAT Amount (20%): £" . number_format($vat_amount, 2) . "\n";
echo "Total Price Including Tax: £" . number_format($total_price_incl_tax, 2) . "\n";

This will output:

Total Price Excluding Tax: £29.17
VAT Amount (20%): £5.83
Total Price Including Tax: £35.00

Which is accurate

  • Like 1
Link to comment
Share on other sites

And using bcmath inline with Padloper: 

$price_per_item = '3.50';
$quantity = '10';
$vat_rate = '20'; // in percentage

// Calculate the total price including tax
$total_price_incl_tax = bcmul($price_per_item, $quantity, 2);

// Calculate the total price excluding tax
$divisor = bcadd('1', bcdiv($vat_rate, '100', 2), 2);
$total_price_excl_tax = bcdiv($total_price_incl_tax, $divisor, 2);

// Calculate the VAT amount
$vat_amount = bcsub($total_price_incl_tax, $total_price_excl_tax, 2);

// Output the results
echo "Total Price Excluding Tax: £" . number_format($total_price_excl_tax, 2) . "\n";
echo "VAT Amount (20%): £" . number_format($vat_amount, 2) . "\n";
echo "Total Price Including Tax: £" . number_format($total_price_incl_tax, 2) . "\n";

This will output:

Total Price Excluding Tax: £29.16
VAT Amount (20%): £5.84
Total Price Including Tax: £35.00

Which again is accurate and gives the true total.

  • Like 1
Link to comment
Share on other sites

  • alexm changed the title to Tax Rounding issue (and proposed solution)

Thanks @alexm,

You have no idea how much grief rounding gave me! (https://www.martinfowler.com/eaaCatalog/money.html). I thought I had this nailed down! I did nearly 500K random tests and I thought I had sorted it out! WooCommerce had issues as well, btw. I even looked at HMRC and EU Regulation, e.g. https://www.gov.uk/hmrc-internal-manuals/vat-trader-records/vatrec12030.

I had one mind to make this configurable. There are 3-rd party options for handling money but wasn't keen going that route. If bcmath works fine, I'll take that, otherwise might be forced to use Money or Brick Money. 

Thanks for the code. I'll have a play.

  • Like 1
Link to comment
Share on other sites

5 hours ago, kongondo said:

You have no idea how much grief rounding gave me! (https://www.martinfowler.com/eaaCatalog/money.html). I thought I had this nailed down! I did nearly 500K random tests and I thought I had sorted it out! WooCommerce had issues as well, btw. I even looked at HMRC and EU Regulation, e.g. https://www.gov.uk/hmrc-internal-manuals/vat-trader-records/vatrec12030.

 

Interesting!! I think bcmath is ideal for the job as you've opted for, for sure. And I'm pretty certain (from my understanding) that calculating the tax from the total of each line item is the best way to avoid discrepancies. Of course, I see your pain, because if you then wanted to display the unit price excl. tax for that line item you would indeed arrive at £2.92. And then if you ((2.92 x 10) * 1.2) you arrive at 35.04.

But looping back round. For the total and accurate tax calculation of the product price which includes tax, then I think it better and more accurate to display the tax based on the line total and then a small note can always be added explaining that 'taxes are calculated on the total of each line item'. So then if we do show the price excl. tax per item and it's marginally different, the methodology is explained?

  • Like 1
Link to comment
Share on other sites

Or disregard displaying the unit price exclusive of tax it tax is enabled altogether and instead simply show the tax amount for all items as above:

Total Price Excluding Tax: £29.16
VAT Amount (20%): £5.84
Total Price Including Tax: £35.00

??

 

Link to comment
Share on other sites

Just taking this one step further again.

If it is an instance where people want to display their prices for products on the site NET (e.g. B2B or American standard I believe) then you would show a product that is listed on the backend of the website at £3.50 with prices configured as inclusive of VAT, on the frontend as £2.92. Then at checkout you'd simply change the logic to calculate taxes for that case as so (2.92 x 10) x 1.2 = £35.04
That would be the true expectation of the price based on what the customer sees on those kind of stores where product prices should be displayed net of VAT

I'll leave it there. Haha.

  • Like 1
Link to comment
Share on other sites

2 hours ago, alexm said:

I think bcmath is ideal for the job as you've opted for, for sure.

Should be. Incidentally, under the hood, the money libraries also use bcmath. 

  • Like 1
Link to comment
Share on other sites

Yeah I suspected as much from your original list of Requirements php settings wise.

Well I've asked about and that would be my logic to remedy said totalling matter boss!

  • Like 1
Link to comment
Share on other sites

  • 2 weeks later...

I'm having an issue where the total price with tax differs from the unit price with tax for a product.

The store also has 'All taxes are included in stated prices' checked under tax settings and tax is set at 21%.

image.png.1625f72b996f04abf1d70932ec9fb9cd.png

Is there a fix planned for this or can I hotfix this for now?

  • Like 1
Link to comment
Share on other sites

Hi @Spinbox,

1 hour ago, Spinbox said:

Is there a fix planned for this or can I hotfix this for now?

Yes. What @alexmsaid. There is a fix already, thanks to @alexm proposals above. In summary:

  1. Both 'calculating tax on a unit item and multiplying by quantity' versus 'totaling quantities first then applying tax' are acceptable approaches. One approach gives the taxman a few more pennies and the other saves the customer a few more pennies in taxation.
  2. I might make this configurable in the future but for now, I have adopted the latter per @alexm's suggestion (using bcmath).
  3. The rounding error mainly occurs in cases where tax is included in the price. 

I run around 10 million tests and all passed. There's two bits remaining. I need to implement this approach in Padloper discounts and do final test before releasing.

I hope to have this ready by tomorrow.

Thanks.

  • Like 2
Link to comment
Share on other sites

  • 3 weeks later...

Hi @Spinbox,

Sorry for my slowness! I should have updated you earlier. I ran into some issues. In the end, I decided to stop trying to reinvent the wheel and use a well-tested library. I have now implemented the Money for PHP library. It is a great little library. I wish I had done this sooner. The delay is due to the many places in Padloper code that need changing. The bulk of these are in order line items and I have finished that bit. The rest is in whole order values (totals, subtotals, etc.) and discounts. I am hoping to be done by the weekend. 

Thanks.

  • Like 3
Link to comment
Share on other sites

  • 2 weeks later...

Hi all,

Sorry for the delay. I found a bug that related to prices include tax, specifically in relation to shipping and discounts. Still sorting this out. 

Thanks.

  • Like 1
Link to comment
Share on other sites

  • 4 weeks later...
On 7/18/2024 at 9:43 AM, Spinbox said:

Any progress on this?

Yes. Version 010 will be out this week (hopefully on Tuesday). Sorry for the delay. I had to take a detour to fix the code from a maintenance point of view as it was descending into maintenance hell 🙂. As a quick by the way (I'll explain in the release thread), I had to, unfortunately, deprecate some of the API. However, this simplifies things for me and for developers. For instance, instead of $checkout = $padloper->checkout; $checkout->render(), we now have everything directly under $padloper. E.g., $padloper->renderCheckout();

Thanks.

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...