How to fix price and VAT calculations

[cruftex]

The problem

A collection of all problems related to price, tax and invoice calculations are in these EPICs and closely related to each other:

• [EPIC] Ecotax #14900
• [EPIC] - Rounding issues #19445
• [EPIC] Fix invoices issues #13822
• [EPIC] - Prices & Taxes #9703
• Discussion for functional requirements: Requirements for price and tax / VAT calculations for cart and invoices #36324

The solution

Because of its interrelations, I try to cover the whole area completely and in a bottom up manner. From database representations, calculations to the final invoiced price. This is meant to establish a bigger picture on the topic and help with coordinating improvements.

Disclaimer: I have yet to explore PS more and I did not see everything in the area of discounts, specific prices and currency conversion.

I found previous efforts here:

• https://github.com/PrestaShop/functional-documentation/blob/main/functional-documentation/business-rules/pricing/method-of-calculating-a-cart-total.md
• https://github.com/PrestaShop/prestashop-specs/blob/master/content/1.7/broader-topics/price-calculation-rules.md

Updates

I try to update this text every now and then to make it more concise, add more details and incorporate feedback.

• 2023-06-24: Add the update section and compatibility section, touch ups
• 2024-06-07: Wrote down a set of requirements from the merchants perspective in Requirements for price and tax / VAT calculations for cart and invoices #36324

Structure

Each topic has the structure:

• Current behaviour
• Example
• Expectations or Requirements for the "correct" behaviour
• Solution Recommendation

The expectations and solution ideas are my own, so, let me know when I am wrong or whether there is a more elegant solution.

Price Entry: Imprecision with VAT included prices

Currently, PrestaShop is storing product price as net price without VAT. The price including VAT is calculated from the net price plus the VAT rate. Since the database is storing the price with 6 digits precision, the VAT included price that the merchant has entered cannot exactly be reproduced.

Example:
I have a product that costs exactly 100 Euro, including 19% VAT. When I enter 100 Euro (8.0.4) and view the product list PS displays 100 Euro. When I edit the product, PS displays 99.999999. At some later point, the price also changed to 99.99, but unfortunately I cannot reproduce that. I assume that there was some additional floored rounding applied to the 99.999999 when the product was copied or edited.

Although PS is calculating with a price of 99.999999, the price of 100 Euro is shown.

Requirements / Expectations:

• The entered price is exactly reproduced and used for ongoing calculations
• Prices are always shown with the decimal digits that are used for calculations
• A merchant cannot enter a price with decimal digits that are not shown to the customer

The best way is that prices are stored exactly as these were entered. If the user is entering a VAT inclusive price, this is
stored in the database and the VAT excluded price is empty and vice versa.

Alternatively, I can think of a workaround, and introduce a flag that determines what price the user actually entered was the inclusive or exclusive price. That flag can be introduced backwards compatible and allows to reproduce the price entered exactly, as long as the stored price format has more decimals than the price we want to show and calculate with. However, this is error prone and might lead to code that is using an incorrect price.

Float calculations

The database stores the price as decimal. Calculations in PHP are with floats.

The float format is not able to represent fractional decimal numbers correctly. Here is a good read: https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency.

The float type has roughly a precision of 14 digits. The price has 6 fractional digits at the moment. With one digit for rounding, that leaves 7 digits to the left of the decimal point for calculations. We will hardly get product prices or orders beyond a Million. But with every calculation step the error adds up.

I recommend changing to decimal calculation (e.g. with BCMath). This also has the advantage that testing becomes easier since values must match exactly to a decimal value.

VAT setting

The VAT setting is currently at the level of the the customer group.

The invoice in 8.0.4 does show prices exclusive, while the credit slip is respecting the setting and does show prices including VAT if this is specified.

Requirements:

• Shop templates, invoices and calculations need to be aligned with that setting.

Correct calculation of order position totals

Example of current behaviour:
I set product tax inclusive price to 0.005 Euro. The shown product price is 0.01 Euro. When I order 10 products the total price is 0.05 Euro.
I set a product with tax inclusive price to 0.014 Euro. The shown product price is 0.01 Euro. When I order 10 products the total price is 0.14 Euro.

Requirement:
The position total needs to be calculated based on the advertised (shown) unit price to the user.

Precision

As shown above, the prices shown to the user, differ with the price that is used for calculations.

In the currency setup, the number of decimal digits is defined.

Requirements:

• In a wholesale scenario with inexpensive products, there is the need that prices have more decimal digits
• Price input, display and calculations need to be aligned
• The final, invoiced price fractional digits needs to be aligned with what is used for money transfers in this country or currency

However, since the exclusive/inclusive VAT is per customer group, it makes sense to define it separately on the shop level depending on the VAT setting.

So the idea would be two settings per shop for precision:

• Offer precision VAT exclusive price: decimal digits used when product prices are displayed (hence offered)
• Offer precision VAT inclusive price

The offer precision is used to round calculation results, if needed.

If unset, the currency precision is used. If offer and currency precision differ, the precision in the currency is used to round the invoice total

With multi store, one store could have the offer precision of two digits, while the other has the precision of three digits, allowing for a B2B shop with a "finer" pricing.

The merchant cannot enter a more precise price then the offer precision setting.

TODO:
More thoughts are needed on how this correlates with B2B/wholesale features.

Tax calculation

The tax calculation needs to be done with minimal error possible according to accounting standards.

Depending whether the prices are tax inclusive (B2C) or tax exclusive (B2B), either the tax including position totals
or tax excluding position totals are summed up to the final total.

More formal in pseudo code:

for each distinctive VAT-rate:
  unit_price = inclusiveVat ? displayedVatInclusiveUnitPrice : displayedVatExclusiveUnitPrice
  position_total = qty * unit_price
  product_total = position_price1 + position_price2 + ...
  if (inclusveVat) {
     vat_inclusive_sum = product_total
     included_vat = round(product_total / (1 + vat_rate) * vat_rate)
    vat_exclusive_sum = vat_inclusive_sum - included_vat
  } else {
    vat_exclusive_sum = product_total
     included_vat =  round(product_total * vat_rate)
     vat_inclusive_price = vat_exclusive + included_vat
  } 

My recommendation: remove the tax_excl and tax_incl prices on the position level and likewise in the order details. There is only one price which needs to be displayed and calculated with on the level of a position. This price is either tax inclusive or tax exclusive.

The shipping costs need to be included as well in the total.

See:

• [Epic][Pricing][MOC][B to B] : Method Of Calculating a cart total without discount #32227

TODO: ecotax?
https://github.com/PrestaShop/prestashop-specs/blob/master/content/1.7/broader-topics/ecotax.md

Discount

As said above, I did not have seen everything in PS regarding this.

There is no accounting requirement on how the discount is to be applied. This is up to the merchant or mutual agreement.

Requirements:

• A global discount should be applied on the sum total and not on position level to minimise rounding errors
• If quantity discount (or is that a reduction?) or other calculations is applied per order position, the rounded result is shown as position total and used for ongoing calculations
• Base prices, unit prices and position totals may have more fractional digits than the invoiced final

Rounding settings

PS has too different rounding settings. It is not documented where these settings are actually applied (tax, discounts, currency conversion, etc.).

Requirements:

• Each fundamental option should be needed and have some automated test
• In general mercantile rounding is to be used for tax purposes

I think it is best to remove the rounding settings altogether and use mercantile rounding if rounding is needed.

There might be still a wish for different rounding for certain purposes. Maybe for currency conversion?
This could be covered by a hook instead.

Obvious bugs

The above conceptual changes, should cover all issues that report unexpected calculation differences. Besides that, there are issues which also imply the solution already.

• Bad recalculation of price tax excl field after entering an ecotax for a product with VAT #9855
• Recalculate totals on order change #17769

Compatibility

This might be realised for PS 9 or 10. The new major version would allow breaking changes.

One major step would be to change the representation of a price from float to a decimal format.
However, changing the representation of prices without backwards compatibility would break a lot of third party code.

Idea:
Be backwards compatible and use floats at interface boundaries for code that consumes prices, e.g. payment modules and templates. Don't be backwards compatible for code that is "hooking" into the price calculations.

TODO: think/check more about the hook interface changes

Implementation steps

The first steps could look like this:

• write some failing, automated tests
• introduce a flag next to the product price, whether the tax included or tax excluded price was entered
• introduce an offer precision for VAT included and VAT excluded prices in the shop setting, next to the default currency selection
• in the product listing in BO show prices with the correct precision as entered by the merchant AND mark prices as rounded in case these are not exact, basically meaning, the merchant sees whether the product price is based on the VAT exclusive or inclusive price
• allow prices only to be entered as precise as the offer precision and make the problem that 100 Euro becomes 99,999999 disappear

[jf-viguier]

thanks @cruftex for your work on this huge subject !

[Hlavtox]

Hi @cruftex, I like your initiative, these are some points that need more thought. :-)

The best way is that prices are stored exactly as these were entered. If the user is entering a VAT inclusive price, this is
stored in the database and the VAT excluded price is empty and vice versa.

You cannot store the prices with tax. Tax is could be different for every country you are selling in and you need to calculate it from the base price.

I recommend changing to decimal calculation (e.g. with BCMath). This also has the advantage that testing becomes easier since values must match exactly to a decimal value.

There already is our own Decimal library if I am right, that should handle all number calculations precisely.

I set product tax inclusive price to 0.005 Euro. The shown product price is 0.01 Euro. When I order 10 products the total price is 0.05 Euro.

There is a problem with this. Imagine you display both prices per product.

• Imagine you calculate the price without VAT from the top.
• With tax - 0,15 EUR, without tax - 0,12 EUR (0,123966942 EUR)
• You order 10 pieces
• With tax - 1,5 EUR, without tax - 1,2 EUR or 1,24 EUR. What value without tax is correct?

[cruftex]

@Hlavtox

Hi @cruftex, I like your initiative, these are some points that need more thought. :-)

You are welcome.

You cannot store the prices with tax. Tax is could be different for every country you are selling in and you need to calculate it from the base price.

At the moment, you can enter the tax inclusive price and the tax rate in the normal product entry. So there is only one tax rate, the one that was entered, which defines the rate for the tax inclusive price that was entered.

Store the price exactly as entered, e.g.: (incl = 15, excl = null, rate = 19%) and then convert the price only if it is actually needed without tax. So for an end user shop in Germany, you would never need to calculate or display the tax exclusive price.

If your prices are typically VAT exclusive, then you enter those and calculate with them.

My first ERP system that I started using in 1994 was doing it that way and every other ERP system I saw meanwhile likewise. Fun fact, you can read about the history of this ERP system here: https://www.gandke.de/gshistorie.html

There is the feature of specific prices. I have not looked into that in particular, but I assume that you can enter prices for a specific country.

With tax - 0,15 EUR, without tax - 0,12 EUR (0,123966942 EUR)

As I said. You need to calculate exactly with the price you are offering the product. Let's take your example, the price was entered with tax, but then it is offered without tax. With 2 digits precision, you offered the product for a price of 0,12, which is 0,12000000000 Euros and not a single centi-cent more ;)

If you want to calculate with fractional cents, then this needs to be able to reproduced by the customer. So you need to show more precision on the product price, invoice positions and then round the final invoiced to something that actually could be transferred.

In that example, the tax inclusive price of 0,15 Euro would be never displayed to the customer, since you show prices tax exclusive. That price is therefor not relevant for the transaction itself.

Only if we need to charge the customer with the VAT, the invoice needs to have a tax inclusive amount for the final total sum.

Imagine you display both prices per product.

You calculate either with tax exclusive or tax inclusive prices. If you want to display both prices, the other price would only be informative and imprecise and MUST not be used for any calculation.

For template variables, I recommend, that there is only one price on product level and position level. If the other price, or the included tax is needed, e.g. for informative purposes, that could be calculated in that template. 99% it is not needed and just having one price makes things easier.

Example as it is now from order-slip.product-tab.tpl:

...

<th class="product header-right small" width="15%">{l s='Unit price' d='Shop.Pdf' pdf='true'}<br />{if $tax_excluded_display}{l s='(Tax Excl.)' d='Shop.Pdf' pdf='true'}{else}{l s='(Tax Incl.)' d='Shop.Pdf' pdf='true'}{/if}</th>
<th class="product header-right small" width="15%">{l s='Price' d='Shop.Pdf' pdf='true'}<br />{if $tax_excluded_display}{l s='(Tax Excl.)' d='Shop.Pdf' pdf='true'}{else}{l s='(Tax Incl.)' d='Shop.Pdf' pdf='true'}{/if}</th>

....

<td class="product right">
	{if $tax_excluded_display}
            - {displayPrice currency=$order->id_currency price=$order_detail.unit_price_tax_excl}
	{else}
	    - {displayPrice currency=$order->id_currency price=$order_detail.unit_price_tax_incl}
	{/if}
</td>
<td class="product right">
	{if $tax_excluded_display}
	   - {displayPrice currency=$order->id_currency price=$order_detail.total_price_tax_excl}
         {else}
	  - {displayPrice currency=$order->id_currency price=$order_detail.total_price_tax_incl}
	{/if}
</td>

[RosaBenouamer]

@cruftex the more I work on the subject the more I understand your pertinent remarks.
Let's talk about it one after another.
Let's begin with

Price Entry: Imprecision with VAT included prices

Currently, PrestaShop is storing product price as net price without VAT. The price including VAT is calculated from the net price plus the VAT rate. Since the database is storing the price with 6 digits precision, the VAT included price that the merchant has entered cannot exactly be reproduced.

Example: I have a product that costs exactly 100 Euro, including 19% VAT. When I enter 100 Euro (8.0.4) and view the product list PS displays 100 Euro. When I edit the product, PS displays 99.999999. At some later point, the price also changed to 99.99, but unfortunately I cannot reproduce that. I assume that there was some additional floored rounding applied to the 99.999999 when the product was copied or edited.

Although PS is calculating with a price of 99.999999, the price of 100 Euro is shown.

Requirements / Expectations:

• The entered price is exactly reproduced and used for ongoing calculations
• Prices are always shown with the decimal digits that are used for calculations

I totally agree with this point

• A merchant cannot enter a price with decimal digits that are not shown to the customer

Considering the switch of currency possible and the number of decimals is different by currency. IMO what is important is the price that the user sees on the FO is used for the calculations. So the price on the will be rounding
according to the number of decimals of the currency and this rounding will be used for the following calculation.

The best way is that prices are stored exactly as these were entered. If the user is entering a VAT inclusive price, this is stored in the database and the VAT excluded price is empty and vice versa.

Alternatively, I can think of a workaround, and introduce a flag that determines what price the user actually entered was the inclusive or exclusive price. That flag can be introduced backwards compatible and allows to reproduce the price entered exactly, as long as the stored price format has more decimals than the price we want to show and calculate with. However, this is error prone and might lead to code that is using an incorrect price.

If you are available let me know I have a spreadsheet which simulate the futur behavior. It could be interesting to test it with you :-)

I will be on holidays a few days. If you are available. I will send you my calendar to position a point :-) to test around this point first and point after point we could validate weeks after weeks :-)

[cruftex]

@RosaBenouamer

IMO what is important is the price that the user sees on the FO is used for the calculations. So the price on the will be rounding
according to the number of decimals of the currency and this rounding will be used for the following calculation.

That is correct, if you round to the number of displayed decimals, that is solving the problem that the price is a bot off, like 99.999999 in the above example. You are calculating with the same price that is displayed. But there could be merchants that display unit prices with higher precision, lite in the gas stations the litre price would be 1.895 Euros, for example. So I think that we need two settings for the decimals, a separate one for the precision of displayed prices.

The requirement would be:

• Unit prices can have more decimals than the currency, to support small unit prices which need more precision. In this case, invoice line totals are rounded to the precision of the currency.

[Hlavtox]

@cruftex @RosaBenouamer The more I think about it, I think that the topic would not actually be THAT difficult.

• We can already achieve “calculating with displayed price” by setting the rounding in BO to “Round each item”. If that option works of course.
• The problem would be for B2B scenarios if we want to use the tax excluded price as primary for calculations. Then we would need to apply the tax in the final step of the calculation somehow.

[cruftex]

@Hlavtox

We can already achieve “calculating with displayed price” by setting the rounding in BO to “Round each item”. If that option works of course.

I quickly checked with this setting and a product with the price of 0.01 Euro. It behaves as I had expected. The invoice says:

The tax section is omitted, because the tax total is 0. Tax excl. total and total is the same. This is because the tax on each item is less than 0.005 Euro. The "Round each item" is actually the worst and produces the most tax calculation errors. However, at least it gives consistent results and the tax total does not vary when you have 100 lines with one product or one line with 100 products.

Fun fact: The PayPal module requires to set the rounding to "Round each item".
202ecommerce/paypal#92

The problem would be for B2B scenarios if we want to use the tax excluded price as primary for calculations. Then we would need to apply the tax in the final step of the calculation somehow.

You always need to apply the tax on the total.

[Hlavtox]

@cruftex Fun fact 2 - rounding tax to zero was a way that many companies (including our gangster ex PM) cheated tax when buying state debt bonds. They bought state bonds 1 CZK per bond, so tax was zero. Cheated the state for 3,5m EUR on taxes. 😁

[RosaBenouamer]

@RosaBenouamer

IMO what is important is the price that the user sees on the FO is used for the calculations. So the price on the will be rounding
according to the number of decimals of the currency and this rounding will be used for the following calculation.

That is correct, if you round to the number of displayed decimals, that is solving the problem that the price is a bot off, like 99.999999 in the above example. You are calculating with the same price that is displayed. But there could be merchants that display unit prices with higher precision, lite in the gas stations the litre price would be 1.895 Euros, for example. So I think that we need two settings for the decimals, a separate one for the precision of displayed prices.

The requirement would be:

• Unit prices can have more decimals than the currency, to support small unit prices which need more precision. In this case, invoice line
  totals are rounded to the precision of the currency.

@cruftex could you please give an example with the two settings and with where each settings will influence the calculation ?
For instance
Setting 1 : this one of the currency so 2 for euros
Setting 2 : 3 (considering 1.895 Euros)
Quantity : 100
Rounding mode :
Tax rating : 20%

Steps for calculation and invoice :-) Thank you !

[RosaBenouamer]

If you are available. I will send you my calendar to position a point :-) to test around this point first and point after point we could validate weeks after weeks :-) What do you think about this ?

[cruftex]

@RosaBenouamer

If you are available. I will send you my calendar to position a point :-) to test around this point first and point after point we could validate weeks after weeks :-) What do you think about this ?

I will be in Paris next week for the conference. Do you have time for 1-2 hours on Friday, 1st December?

[RosaBenouamer]

Cool @cruftex ! Are you on slack PrestaShop Open Source ? To choose an hour ? You could come in our office :-)

[wzzly]

@RosaBenouamer @cruftex any news on this issue? I have a client that has the same problems with tax calculations. Because he uses alot of % discounts, we use rounding on every item, then the total ex vat is corrext, but vat calculations seem to be based on not rounded prices and not on the total ex vat. Very problematic.

[cruftex]

@wzzly
I was meeting with Rosa and we were discussing requirements, needed changes and some transitioning strategies end of November.

AFIAK the idea is to improve things in PS 9. It will not be possible to do a fix for PS 8, because changes need to be quite fundamental for everything about prices, which is not possible to introduce without breaking existing interfaces. So this can only be done with a major release. I will ping Rosa.

[Hlavtox]

@wzzly @cruftex Well, given that apparently Rosa is no longer on the subject, I don't think we can expect any changes from Prestashop SA side. 😄

[wzzly]

@cruftex @Hlavtox is it just me, or isn't it so strange that such huge bugs/issues are still in Prestashop? These are fundamental features for any webshop software right? How can any webshop platform have problems with calculating prices/taxes? The last couple of years I came a cross multiple basic features that are just not working and I wonder how it's possible these things don't get priority from prestashop SA instead of releasing new features...

[Hlavtox]

@matks I know you will say that "there is so much critical problems important to keep the project alive", that SA can't invest into things that matter.

But category redirect type, product publication date, admin api, autoupgrade rework are not those. 👍

[matks]

The reason why this problem has not been fixed yet because the work to fix is abysmal. PrestaShop SA has already invested months on exploring this topic, exploring the laws that must apply in that case, exploring the consequences of changing the current system. Probably thousand of euros have already been invested in retro-engineering the current flawed system and exploring possibilities to replace it with something without breaking everything.

There are multiple massive problems with the current system, one of them being that it is deeply connected to quite everything in PrestaShop. So basically we touch it, we touch everything. We modify it and we have a good chance of introducing bugs everywhere in the software.

So fixing these pricing issues is going to be a gigantic work where we need to do surgical changes to only fix things, and not make them worse.

If we have to stay in the medical metaphors imagine you have a patient who suffers from a deadly disease which is impacting his heart. You need to perform surgery to save him. But performing heart surgery is also a big risk and any wrong move could kill the heart and kill the patient.

So what do you do? Well as the surgeon, you carefully study the situation, assess it, explore how you could it and prepare for the day where you can say confidently "I have a plan I can carry out and I feel confident that if I follow it I will cure the disease without killing the patient"

So we're still in the "carefully study the situation, assess it, explore how you could it" situation that is why no work has started yet. And it could be long before we work because our resources are spread thin to take care of multiple topics of this software.

In the meantime, while we have people working on the "carefully study the situation, assess it, explore how you could it" we have some available time to work on category redirect type, product publication date, admin api, autoupgrade rework. It's useless to have twenty people working on a single subject, it's not a topic you can parallelize.

[Hlavtox]

It's very good that the subject is studied and your company took on the subject, but the study is ongoing for several years and the result is something that nobody will understand. Not even talking about the work of the previous employee. 😄 I don't think it's a good comparsion, with this lax attitude, the patient would be already in the grave long ago.

[matks]

Don't hesitate to send us lot of money so we can hire more people to do it faster 😝

[Hlavtox]

Well I think it's just the matter of attitude. If it was your own money to invest, you would want to see results much more. :-)

[cruftex]

@matks wrote:

Well as the surgeon, you carefully study the situation, assess it, explore how you could it and prepare for the day where you can say confidently "I have a plan I can carry out and I feel confident that if I follow it I will cure the disease without killing the patient"

Well, I did exactly that. I already outlined implementation steps.

@matks wrote:

Probably thousand of euros have already been invested in retro-engineering

That is correct. I spend roughly 4000 Euros on the topic so far.

@matks wrote:

Don't hesitate to send us lot of money so we can hire more people to do it faster 😝

Mind the contradiction: "hire more people", "cannot parallelize".

I am an experienced software engineer, wrote a complete eCommerce shop by myself in the year 2000. That shop was specifically designed to sell theatre and concert tickets, so not a general shop like PS.

Now, I do a startup selling travel stuff and I choose PS for several reasons. Our total shop revenue, at this point, is far less than the money I spend fixing bugs and sending PRs for PS.

On the PS website, I can read:

We build a truly inclusive solution for commerce to fully express its potential.

We believe that commerce is more than a platform. We believe that commerce is a human activity. To respect the business reality of each of our clients, to express the singularity and creativity of each of our users, we build the most inclusive solution.

This was appealing to me.

So far, I did not see any developer involvement on the topic from the PS side.

I don't see that my very fundamental business reality is respected.

I did send money, by using some services PrestaShop SA is providing. I spend time worth a lot of money.

I don't like being sold a "great car" and then asked for extra money to put in round tires instead of square ones.

So fixing these pricing issues is going to be a gigantic work where we need to do surgical changes to only fix things, and not make them worse. You need to perform surgery to save him. But performing heart surgery is also a big risk and any wrong move could kill the heart and kill the patient.

Prices and dealing with money is a cross-cutting concern in PS. It is not a heart surgery. You need to replace it anyway.

You cannot just analyse for years. At some point, you need to start hacking. You will break things. That is usual.

When doing a new major version, it is okay to break with existing interfaces. While it is desirable to be compatible, I have the feeling that it is actually a good idea to intentionally break everything dealing with prices to ensure correctness.

@matks
All what I can read from your answer is:
This is too complex, no developer is touching this

Or shorter:
PrestaShop SA does not want to own the product

Maybe I made the wrong choice.

@matks
Will this be fixed in PS 9 and is PrestaShop SA willing to put some dev resources in or not?