VAT Tax Rounding Problem - Impossible Prices

And you aren't inputting prices including VAT in this case? Is there any reason why? Also, the point of rounding is configurable; perhaps adjusting the rounding option for your tax rate can yield a better result? At the end of the day, increasing a value by an odd percentage is often going to result in an odd final value. Including VAT in the price itself is the only reliable way to make sure it is the desired number.

What do you mean by "display vat separately"?

What you're describing should be the default behavior. Can you take a screenshot of it where it's not working as you expect?

Ok, on the product edit form, there should be a select list beside the price field that lets you input the product price including VAT. This will make it work as you're expecting, with the order totals area showing the VAT split out from the product price for a grand total of 100€.

See for example:

Ahh, ok, I was wondering why you weren't seeing this. The screenshots I showed above are working right now; those are from a clean installation of Commerce. The calculation has to happen differently in code because it happens when the product is loaded and performs an inverse calculation at that point in time to separate the tax out of the base price. This is different from normal pricing; entering a price including VAT essentially functions as if there were a tax calculation Rule with no conditions always being executed first. It could be that we just need an action to set a price including VAT, which could solve this entirely for you, no?

Sure, but you might actually want to evaluate other issues that deal with removing price components from a price. The problem is that if your tax calculation doesn't happen, the amount entered in the database is going to still be the full amount including VAT. So, whether you do the inverse tax calculation or not, the price of the product will be the price as entered (i.e. 10 €). I think what you really want is to have access to the price excluding VAT, right? In that case, we'd first need to do the inverse calculation so we know how much to remove.

Going to close this now unless there are follow-up things to pursue. I don't know of any actual tax rounding problems, just better support that we need for VAT, which is coalescing around the http://drupal.org/project/commerce_eu_vat project.

I mused have missed this issue being closed and I am re opening it as the bug still exists.

The Commerce EU VAT module suffers from this issue exactly as described.

* a net price of 13,50 € results in 14,44 € (incl. 7% vat)
* a net price of 13,51 € results in 14,46 € (incl. 7% vat)

You can't have a gross price of 14,45 €.

I can't find a separate issue about increasing the accuracy for storing and calculating price data but still displaying in the standard accuracy

There is no issue (for 1.x at least) to increase the computational precision while retaining a smaller display precision. However, what I find odd about this example is that 13.5 * 1.07 rounds up to 14.45. I don't know how you're getting to 14.44 unless you've opted to round the half down. (And fwiw, I just confirmed that it goes to 14.45 on a clean install of Kickstart 1.x.)

That said, this issue has been discussed elsewhere, which is why it was undoubtedly closed here. (There was about a year's lack of activity on this at the time I closed it.) The summary if I recall is that there isn't a bug here, just a desire for different functionality. Right now we support either 1) entering a product price including VAT (which gets you any precise amount but makes it harder to discount / show the price w/o tax - there are other issues for those feature requests) or 2) adding the VAT via Rules (which makes it impossible to arrive at certain desired prices depending on the tax rate).

As far as I can tell, the code is working fine as is - i.e. there's no bug in what we offer, it just doesn't do what some users want - conditional VAT but the ability to input a price with a desired "final purchase price" including VAT. My recommendation in the past has been for a contributed module to be developed to handle that in 1.x, and then in 2.x when we up our storage / computational precision and separate that from our display precision, it may become a non-issue.

Such a contributed module basically needs to know how to accept a desired target price, associate that with a tax rate, store in the price field the actual product price excl. VAT, and then know that when a VAT is applied to a price that it should use the target price and reverse calculate the base_price / VAT price components instead of trying to calculate it with the typical multiplication. It's non-trivial, which is why we haven't pursued adding such a feature to core.

> 1) entering a product price including VAT (which gets you any precise amount but makes it harder to discount / show the price w/o tax - there are other issues for those feature requests) or 2) adding the VAT via Rules (which makes it impossible to arrive at certain desired prices depending on the tax rate).

It's a while since I was working on this, but IIRC, neither of those are viable if your store needs to support different VAT rates. Which in the UK at least, happens as soon as you want to sell children's clothes, for instance -- so not that rare a use case surely.

I think you'd need to tease that out a little further - different VAT rates... for different products? different customers? Either way, though, as I mentioned after this, the as of yet unsupported feature would be "conditional tax rates where store owners won't to specify pricing including VAT" (acknowledging that Commerce must still store price excluding VAT in the event that taxes are conditionally applied to products).

Ryan, I was trying to help someone on IRC today with this problem.

The maths in commerce is not as simple, and here is a correct example:

• 20.82 + (20.82 * .2 = 4.16) = 24.98
• 20.83 + (20.83 * .2 = 4.17) = 25.00

Thus you cannot calculate a sales price of 24.99 with a VAT rate of 20%.

As joachim says (1) entering a product price including VAT, is not viable if you also need to support selling outside of the EU or Intra-community reverse charge.

The way this is calculated is prices are rounded up and VAT is rounded down so:

• 20.825 + (20.855 * .2 = 4.165) = 20.83 + 4.16 = 24.99

I suppose there could be a tax fudge tick box on the product that deducts 0.01 from the VAT amount when required. Looks like this will need to be a custom tax rate calculation callback.

#1970882: Fix for imposible prices including tax

How about something like this:

/**
 * Implements hook_commerce_currency_info_alter().
 */
function mymodule_commerce_currency_info_alter(&$info) {
  $info['EUR']['decimals'] = 4;
  $info['EUR']['display_decimals'] = 2;
  $info['EUR']['format_callback'] = 'mymodule_format_currency';
}

function mymodule_format_currency($amount, $currency, $object) {
  // The rest copied verbatim from @see commerce_currency_format().
  $price = number_format(commerce_currency_round(abs($amount), $currency), $currency['display_decimals'], $currency['decimal_separator'], $currency['thousands_separator']);

  // Establish the replacement values to format this price for its currency.
  $replacements = array(
    '@code_before' => $currency['code_placement'] == 'before' ? $currency['code'] : '', 
    '@symbol_before' => $currency['symbol_placement'] == 'before' ? $currency['symbol'] : '', 
    '@price' => $price, 
    '@symbol_after' => $currency['symbol_placement'] == 'after' ? $currency['symbol'] : '', 
    '@code_after' => $currency['code_placement'] == 'after' ? $currency['code'] : '', 
    '@negative' => $amount < 0 ? '-' : '', 
    '@symbol_spacer' => $currency['symbol_spacer'], 
    '@code_spacer' => $currency['code_spacer'],
  );

  return trim(t('@code_before@code_spacer@negative@symbol_before@price@symbol_spacer@symbol_after@code_spacer@code_after', $replacements));
}

This would allow you to still have multi currency and use the same callback.

This could still lead to some odd rounding issues when adding up the line items to get the order total.

I will test as this could be a much better solution than my current one in Commerce VAT.

So giving this some testing this comes back to all the lots of issues around external APIs. Particularly payment gateways who expect an amount at the correct number of decimal places.

Having checked some would cope with this as one I have worked on requires two decimal places on all currencies it supports.

commerce_currency_amount_to_decimal($transaction->amount, $transaction->currency_code)

Could result in 15.5 so I use number_format($amount, 2, '.', '') to get 15.50. This would also cover the 15.551 problem, but wouldn't respect commerce currency rounding settings.

There are many other issues that this has with calculation of totals when adding up 100s of orders as the rounding will be off when they are aggregated.

This does give further thoughts on what we need from price storage and calculation in Commerce 2.x though. Have a look at #1125706: Price Implementation for Commerce 2.x

I shared a solution similar to #23 and #24 at #2125611-4: VAT rounding flawed in some countries to overcome VAT rounding differences. axel.rutz pointed me at #25 of this issue that other contribs (especially payment gateways) might not handle a different number of decimals very well.

@dwkitchen: Could you elaborate on which other modules did not cope well with different amounts of decimals?

We use the Payment Commerce module which converts the integer amount to a float and sends this float (with any amount of decimals) to a payment processing module (in our case the commercial Rabobank Omnikassa, also by Xano) which formats the amount according to the payment provider's requirements. So in our case a custom amount of decimals fortunately appeared to work out of the box, as probably with all other Payment modules will. I suppose the problem lies within payment gateways written especially for Commerce which assume integers based on certain amounts of decimals.

This could be circumvented if a payment gateway offers a hook to alter payment data right before they get sent to the payment provider (as ie. the Rabobank Omnikassa and Ogone modules do offer). We could add two custom options to ie. the Euro currency which has 2 decimals by default:

• decimals (default option): amount of decimals used for storage and calculations, ie. 4 for more precise calculations.
• decimals_display (custom option): amount of decimals to display using a custom implementation of commerce_currency_format(), ie. 3 for products that are ordered in large quantities.
• decimals_official (custom option): used by alter hooks to format amounts before they get sent to the payment provider to meet their requirements, for Euro's this is 2.

As long as payment gateways (start) offer(ing) alter hooks, I think a contrib module could handle this.