Tax Rounding issue (and proposed solution)

[alexm]

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)

[alexm]

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

[alexm]

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.

[kongondo]

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.

[alexm]

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?

[alexm]

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

??

 

[alexm]

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.

[kongondo]

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. 

[alexm]

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!

[Spinbox]

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%.

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

[alexm]

@Spinbox I'm pretty certain @kongondo is working on a revision of the tax calculation from our discussions. I'll let him clarify that though when he's free.

[kongondo]

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.

[Spinbox]

Hi @kongondo, you have any updates on the fix?

[kongondo]

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.

[alexm]

@kongondo you da man! ?

[kongondo]

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.

[alexm]

No drama! Thank you for your efforts as always mate

[Spinbox]

Any progress on this?

[kongondo]

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.

[kongondo]

Hey @Spinbox,

Sorry last week did not happen!  I had to fix some installer issues which I finished today. I then messed up with an overzealous regex cleanup of debug code which 'cleaned out' a number of valid code in different places! I have had to revert to the state before cleanup. I will cleanup again, slowly this time, tomorrow. I'll then make the release. Sorry again.

[kongondo]

@Spinbox, @alexm, Padloper 010 was released this week, in case you missed it ?.

 

[alexm]

OH EM GEEEE!! Wicked well done man