Tax amount is not rounded causing total price mismatch in cart

I have exactly the same problem of mismatch in between unit price by quantity, subtotal and tax amount when using a product having a price excluding tax and managing taxes through rules (which adds more flexibility in tax management). But it seems to me that the issue is not related to rounding on tax, but rather on the unit price stored at the product level.

Let say for an example that we have a product with a price including tax of 12$ with a tax of 19.6%, we will store a net price of 10.03$ at the product level, when in fact it should be something equal to 10,0334448 (to keep precision in further calculation).

The only way I see to fix it, is to determine what is the actual price including tax before any calculation. But as in this case we don't know in advance which tax rate is applicable it's impossible to get it before we reach some function that assigns the tax rate to the order line.

As soon as the tax rate is known it is quite easy to get the difference generated by the initial rounding and to apply it both to the the order line amount AND to the subtotal.

At this point, I don't know where is the best place to perform this, so I started some tests adding the following code inside the function commerce_tax_rate_calculate() :

...
if (!commerce_price_component_load($line_item_wrapper->commerce_unit_price->value(), $tax_rate['price_component'])) { 
    // First we round the unit price including tax to the penny 
    $new_unit_price = round(($line_item_wrapper->commerce_unit_price->amount->value() * (1 + $tax_rate['rate']))/100,2)*100;
    // We calculate the net price with all precision 
    $new_unit_price = $new_unit_price / (1 + $tax_rate['rate']);
    // we get the difference due to early rounding at the product level
    $tax_balance = $new_unit_price - $line_item_wrapper->commerce_unit_price->amount->value();

    // for testing purposes I applied the difference to the tax amount in order to balance the different amounts in   
    // the cart, which is bad because it's resulting in a wrong tax amount based on the subtotal of the order, but     
    // I have no clue at this point on how to do otherway to update the subtotal calculation ??????
    return array(
      'amount' => $new_unit_price * $tax_rate['rate'] + $tax_balance,
    ...   

I attached a snapshot of the cart both before and after this fix.

Any help to achieve this in a none-dirty way like it is at this point, would be greatly appreciated.

I finally came up with a solution by implementing hook_commerce_line_item_presave in a custom module. The main idea is to recalculate all prices and tax amounts (unit and total) with all the precision needed before the line item is saved.
In case someone would be interested in some solution to fix this rounding at checkout issue, here is the hook I setted up :

/**
* Implements hook_commerce_line_item_presave.
* Fixes the rounding issue in line-items and total when inclusive tax
*/
function custom_commerce_line_item_presave($line_item) {
    $line_item_wrapper = entity_metadata_wrapper('commerce_line_item', $line_item);
	$unit_price_data = $line_item_wrapper->commerce_unit_price->data->raw();
	$total_data = $line_item_wrapper->commerce_total->data->raw();
		
	// if inclusive tax rate, recalculate a rounded unit price  and exact tax amount 
	if ($unit_price_data['components'][0]['included']) {
	  for($i = 0; $i < sizeof($unit_price_data['components']); ++$i) {
        if (!empty($unit_price_data['components'][$i]['price']['data']['tax_rate']['rate'])) { 
	      $tax_rate =  $unit_price_data['components'][$i]['price']['data']['tax_rate']['rate'];
	      $unit_price_inclusive = round($line_item_wrapper->commerce_unit_price->amount->value()/100,2);                   
	      $unit_tax = $unit_price_inclusive - ($unit_price_inclusive / (1 + $tax_rate ));
	      $line_item_wrapper->commerce_unit_price->amount = $unit_price_inclusive * 100;
	      $line_item_wrapper->commerce_total->amount = $line_item_wrapper->commerce_unit_price->amount->value() * $line_item_wrapper->quantity->value();
	  	  
	      // update unit tax amount in component data
	      $unit_price_data['components'][$i]['price']['amount'] = $unit_tax * 100;
	      $line_item_wrapper->commerce_unit_price->data = $unit_price_data;
	  
	      // update total tax and total base_price in component data
	      $total_data['components'][$i]['price']['amount'] = $unit_tax * 100 * $line_item_wrapper->quantity->value() ;
	      $total_data[ 'components'][0]['price']['amount'] = $line_item_wrapper->commerce_total->amount->value() - $total_data['components'][$i]['price']['amount'];
	      $line_item_wrapper->commerce_total->data = $total_data;
		  break;
		};
	  };
	};
	return $line_item;
}

Attached a snapshot of the result

Any suggestion to improve this code is welcomed.

Oops ! wrong file attachment...

subscribe. maybe could be a checkbox for the tax rate definition to be rounded or not.

Bumping this to critical. This is a known issue affecting European-style VAT (US sales tax works as intended). We need a rounding option here.

Attached is a patch that I think addresses this sufficiently. Here's what it does:

1. Defines some rounding constants similar to those defined by PHP 5.3+ for round().
2. Default tax types to no rounding (i.e. it continues to function as is). Notice I'm doing this at the tax type level, much like the display inclusive option. I was originally going to put this at the rate level, but usability and practicality made me switch it to types.
3. Adds support to the tax type edit form to adjust the rounding mode of the type. This is PHP version sensitive, so additional options are available on PHP 5.3+. I don't see any reason to make our own backwards compatible rounding function here.
4. Adds an update function to add the new round_mode database column. I update the default VAT tax type to round the half up.
5. Gets rid of a TODO comment that was actually backwards. The comment indicated we needed to make taxes function how they already were functioning. The inverse was actually true, so it should've been a TODO comment for this patch. Either way, this patch nails it.
6. Implements the rounding on normal tax calculation, when product prices are loaded that include VAT, and when rebasing the unit price components of line items when their unit prices change.
7. You can ignore the comment change to commerce_cart_product_add(). I just noticed that someone in another issue was saving the line item unnecessarily before adding it to the cart.

I intend to commit this as is; I've tested it, and as I've described it above, it's working. Let me know if you think I did something wrong. : )

Alrighty, talked this over with Damien, and on his recommendation I did go ahead and change our implementation to support the four rounding methods regardless of your PHP version. It wasn't horribly difficult, and it gave me my first ever excuse to use floor() and ceil() (with a couple of modulus operations thrown in as a bonus). See the commit diff for the final patch. I tested this and it appears to be working for all rounding methods for regular ol' VAT, for VAT inclusive prices, and for VAT inclusive line items whose unit prices are adjusted in the order edit form.

Perhaps I can get some confirmation on this, too. I'm pretty sure this is the expected behavior, but I wanna make sure I'm not crazy, because it could look a little weird in the totals. I have a product that is 60 cents including a 10% VAT. This equates to a base price of 54.54 cents (or 55 cents rounded) and the tax 5.46 cents (or 5 cents rounded). So my 10% VAT rounds out to an actual rate of 8.333% thanks to rounding. I don't see any way around this, but I'm also not sure it's really a problem. Glad I've never had to report VAT. Thoughts?

Commit: http://drupalcode.org/project/commerce.git/commitdiff/df02ca3

have this been included in drupal commerce? i have this problem and i think that it was closed without it. because that, i reopen it. i have 7.x-1.7

Please look at the timestamp and commits. This is over 1.5 years old, and it ended with the patch being committed. You must be experiencing some other issue. My recommendation would be to post a question at http://www.drupalcommerce.org/questions with as much information as you can possibly provide about your setup and your problem.

@Devline thanks for your module. I was getting crazy looking all around to find why my vat included was not rounding well. After trying many things, the solution was to not apply any rounding in the VAT, in the tax types configure form and using your module. thanks thanks!!!

I am using commerce 7.x-1.7 without any discounts or any thing that could influence. I reused the sample-french-vat-tax included. I changed it to be the 21% that we have in Spain. I disabled the Calculate taxes: Sales tax also. I put here my trying for debugging purpose.

Correct calculation
product 13,50 € x 4 products = 54,00 €
Subtotal 44,63 €
VAT 21% 9,37 €
Order total 54,00 €

Results using Tax amount rounding mode (without Devline's module):

Do not round at all

product 13,50 € x 4 products = 54,00 €
Subtotal 45,00 €
VAT 21% 9,00 €
Order total 54,00 €

Round the half up

product 13,50 € x 4 products = 54,00 €
Subtotal 44,64 €
VAT 21% 9,36 €
Order total 54,00 €

Round the half down

product 13,50 € x 4 products = 54,00 €
Subtotal 44,64 €
VAT 21% 9,36 €
Order total 54,00 €

Round the half to the nearest even number

product 13,50 € x 4 products = 54,00 €
Subtotal 44,64 €
VAT 21% 9,36 €
Order total 54,00 €

Round the half to the nearest odd number

product 13,50 € x 4 products = 54,00 €
Subtotal 44,64 €
VAT 21% 9,36 €
Order total 54,00 €

@rszrama THANKS FOR YOUR GREAT WORK :)
I reopen, with better documentation, the issue for you to be able to check it. If you need any other information, i will be pleased to provide it.

Edit: I am using PHP Version 5.3.10-1ubuntu3.6 and Apache/2.2.22 (Ubuntu)

Devline hasn't posted in here for over 2 years. This issue just ain't coming back from the dead. : P

That said, I think you're wrong. VAT is rounded at the unit price level, so you can't have a unit price that gets multiplied by an even number end up an odd number. Your "correct subtotal" of 44,63 € just doesn't make sense to me. It looks to me like the correct calculation is as rounded, which this issue was closed fixed as supporting.

If you think I'm incorrect about VAT calculation, please refer to https://drupal.org/project/commerce_eu_vat for a solution for Spain. : )

Thanks :)
I'll try it.

Hi rszrama,

I understand why it doesn't make sense for you, but most accounting softwares (at least in France) validate the VAT based on total incl. tax and the tax rate, therefore if you have a total amount of 54 with a tax rate of 21% it will validate only for a net price of 44,63 (54 / 1.21 = 44,628 -> 44,63).

Ok, that sounds like something to take up with dwkitchen in the commerce_eu_vat queue - he's our resident EU tax genius and would know how that works within the system as is. fwiw, I've never heard of a VAT that isn't calculated at the unit price level, but if that is the case then you should still be able to accomplish it - just not by entering prices including VAT.

Hi rszrama,

Many thanks for your responsiveness, as usual.... I totally agree with you on using prices not including VAT, and that is how accounting systems are actually doing, but when you enter your net price it let you enter the number of decimal you need to keep precision in tax calculation at the unit price level (generally between 3 To 5 decimals are enough for most business types), therefore the final validation on total, taxes and sub-total will pass.

We also have to keep in mind that when you are exporting sales in an accounting or when the customer records his invoice or when you fill your tax statement, it's at the total level without any knowledge of detail lines, that's why the validation is set on totals. It's acceptable here to display prices on invoices with more than 2 decimals or to have small discrepencies on invoice's detail lines, but never on totals.

I think the long time discussion on EU Tax is all around the number of decimal stored in prices... I can see only two ways to go, either at the price level, either to have some kind of mechanism able to restore at some point a unit price with enough precision.

Sorry to disturb again in this thread... :)

Yeah, in Commerce 2.x we'll actually be separating out the storage / computational precision from the display precision so we can solve these sorts of scenarios for good. dwkitchen is the mastermind behind that plan, so I expect to see him heavily involved in the process.

Wonderful Ryan ! this should remove a big pain in the neck for many europeans :)... I saw that dwkitchen has pinpointed the actual issue back in september 2011. So we'll be patient and wait for Commerce 2.x...

@candelas I'm glad to see that [#2] was of some help for you in this issue.