5. Specification

August 29, 2024

On this page

Overview

This article describes how Erply calculates the total of a sales document. 

Input

Sales document rows. For each row, we need to know four properties. (Let us also assign a sample value to each, to illustrate the calculations.)

  • Initial unit net price                — 6.6667
  • Discount percentage             — 15%
  • Quantity                                 — 10
  • Tax rate (%) / VAT rate (%)    — 20%

Output

  • Net total
  • Tax
  • Total
  • The net total and tax (VAT) of each row

Algorithm based on net price

Version 2

  1. For every row, start with the unit net price.
  2. Round it to price_decimals decimal places. (price_decimals is set to 2 on US accounts and 4 elsewhere1). Typically, the value already is at this precision, bcause the database by default stores product net prices with 4 decimal places.
  3. Subtract discount. Round the result to price_decimals decimal places.
    6.6667 * (100 - 15) / 100 =~ 5.6667
  4. Multiply by amount to get row net total. Round the result to 2 decimal places2.
    5.6667 * 10 =~ 56.67
  5. Calculate row tax, by multiplying row net total by tax rate3.
    56.67 * 20 / 100 = 11.334
  6. Add up the net totals of all rows, and the tax totals of all rows.
  7. Round both results (invoice net total, invoice tax total) separately to 2 decimal places. 
    Invoice net = 56.67, invoice tax = 11.33

Version 3

Same as previous, but:

  • In step 3, the result of the discount is rounded to 10 decimal places.
  • In step 4, the row net total is rounded to 8 decimal places.

Version 5

  1. For every row, start with the unit net price.
  2. Round it to price_decimals decimal places.
  3. Subtract discount. Round the result to 10 decimal places.
  4. Multiply by amount to get row net total. Round the net total to 2 decimal places.
  5. Calculate row tax, by multiplying row net total by tax rate.
  6. Take all the rows on the document, and adjust the tax amounts on some of these, using the largest remainder algorithm. This ensures that on a document with small prices and many rows, each row will have a net total and a tax total with the precision of 2 decimal places (avoiding the need to store more decimals), but the roundings do not introduce a cumulative bias.
    1. Round all row tax totals DOWN (towards zero) to 2 decimal places. For example, 1.666666… becomes 1.66.
    2. Calculate the “rounding leftover”: cumulative difference caused by the rounding-down. Rounding 1.66666… to 1.66 leaves a leftover of 0.006666… If the document has five rows with identical totals, the cumulative difference is 0.0333…
    3. Round that leftover to whole “cents” (2 decimal places), using ordinary rounding. 0.03333 =~ 0.03.
    4. Distribute the leftover cents between rows that were most affected by the rounding-down. If several rows were adjusted by the same amount, pick the first one(s). In this example, the cents must be added to the first three rows.
    5. As a result, the tax totals on the five rows will be:
      1. 1.67
      2. 1.67
      3. 1.67
      4. 1.66
      5. 1.66
  7. Add up the net totals and tax totals of all rows, to get invoice net total and tax total.

Algorithm based on price with tax

Version 2

  1. Calculate the initial price with tax and round it to 2 decimal places.
  2. Subtract discount. Round the result to 2 decimal places.
  3. Multiply by amount to get row total. Round it to 2 decimal places.
  4. From row total, calculate the net total by subtracting tax (and round the result to 2 decimal places) and the tax amount (which is the difference between the first two)
  5. Add up the net totals of all rows, and the tax totals of all rows.
  6. Round both results (invoice net total, invoice tax total) separately to 2 decimal places.

Version 3

Same as version 2, but:

  • In step 2, the discounted price with tax is rounded to 10 decimal places.
  • In step 4, the row net total is rounded to 8 decimal places.

Version 4

Same as version 2, but:

  • In step 1, initial price with tax is rounded to 10 decimal places.
  • In step 2, the discounted price with tax is rounded to 10 decimal places.
  • In step 4, the row net total is rounded to 8 decimal places.

Version 5

 

Footnotes

1: And 0 in Vietnam. The precision of prices can be overridden with configuration parameter overwrite_price_decimals (for example overwrite_price_decimals = 6), but values above 4 do not work unless the account database has also been modified accordingly, because product price fields are defined in the database as DECIMAL(15, 4)

2: The precision of amounts of money can be overridden with configuration parameter overwrite_money_decimals (for example overwrite_money_decimals = 0) 

3: Some customers sell refurbished products where a part of the item's net price is non-taxable (specifically, the cost at which the item was obtained for resale from an individual) and only the remainder is taxed. Example: net price 100 €, of which 80 € is non-taxable. If the VAT rate is 10%, the remainder of 20 € is taxed at 10%, yielding a tax of 2 €, and the total price with tax is 102 €.

Please note that Erply product catalog only stores net prices, not the price with VAT. (Price with VAT is always derived on  the fly when needed.). Therefore, if the store has a specific final price with VAT in mind, it might be necessary to run the same calculation “backwards” to figure out a net price to set to this item. (This is not in the scope of this specification, but to illustrate the full workflow.)

Example: the final price with tax must be 100 €, the purchase cost of the refurbished item was 80 €, and tax rate is 10%. In this situation, the remainder 20 € splits into net = 20 / 1.1 = 18.1818 €, tax is 1.8182 € and the total net (including the non-taxable amount) is 98.1818 €. This is the value that the sales document row will have stored in the “net price” field.