Programming rounding of amounts

Why explanation about rounding? Program script requires current knowledge to stabilize rounding of amounts:
  • The Dutch tax authority gives a partial explanation for two ways of rounding;
  • From a technical point of view, intermediate values must be stored without rounding;
  • In accounting terms, values must be stored in a table rounded (two, three or four decimal places are common);
  • For accounting software, rounding with two additional decimal places has turned out to be practical;
  • An ERP (Enterprise Resource Planning) system cannot handle all kinds of country-dependent regulation.
Which problems and ways out exist when rounding sales tax? A tax total based on invoice lines can be rounded up or down too much:
  • Entering a purchase invoice in accounting software requires intervention of the user;
  • A customer does not have to pay an invoice if it is too high;
  • A tax authority can complain about a declaration if it is too low;
  • In case of multiple currencies, rounding may become really excessive, eg plus or minus 0.70;
  • If an invoice total contains more than one child line, the tax calculation must not lose the information required;
  • Customers’ accounting software can calculate correctly on the basis of two additional decimal places at child line level in the billing process;
  • Rounding itself is sensitive in case of three decimal places: net 0.07 + tax 21% = 0.0847. If this tax is in at least four decimal places, and an intermediate total uses three decimal places, then this total changes from really 0.08 to wrongly 0.09;
  • A problem is about net + tax -> gross. Net and tax at invoice level need rounding to two decimal places before they totalize to gross;
  • A formatting function in PHP needs rounding too: sprintf(“%.2f”, round($inputamount, 2)).

Gross 2 * 18.29 = 36.58 -> net 30.23 plus tax 6.35 happens to be wrongly processed:
XS4ALL Internet B.V. 2018-03-22: gross 2 * 18.30 = 36.60 (net 30.24 plus tax 6.36)
XS4ALL Internet B.V. 2018-04-22: gross 2 * 18.29 = 36.58 (net 30.24 plus tax 6.34)
This situation is most likely the result of changing a calculated net price from 15.12 to 15.1157.

A total from net of 0.09 instead of 0.08 happens to be wrongly processed:
KPN B.V. (business) 2018-08-03: net 0.07 (0.0700) plus 21% tax 0.01 (0.0147) -> total 0.09
or like: net 0.07 (0.0710) plus 21% tax 0.01 (0.0149) -> 0.0859 -> total 0.09

Examples from net:
1st invoice line 15.12 * 0.21 = 3.1752 -> 3.18
2nd invoice line 15.12 * 0.21 = 3.1752 -> 3.18 -> tax too high: 6.36
——————-
1st invoice line 15.1157 * 0.21 = 3.174297 -> 3.17
2nd invoice line 15.1157 * 0.21 = 3.174297 -> 3.17 -> tax too low: 6.34
——————
1st invoice line 15.12 * 0.21 = 3.1752 -> 3.1752
2nd invoice line 15.12 * 0.21 = 3.1752 -> 3.1752 -> coincidentally correct: 6.35
——————-
1st invoice line 15.1157 * 0.21 = 3.174297 -> 3.1743
2nd invoice line 15.1157 * 0.21 = 3.174297 -> 3.1743 -> correct: 6.35

Examples from gross:
1st invoice line 18.29 / 1.21 = calculated net 15.12 -> 3.18 
2nd invoice line 18.29 / 1.21 = calculated net 15.12 -> 3.18 -> tax too high: 6.36
——————-
1st invoice line 18.29 / 1.21 = calculated net 15.1157 -> 3.17 
2nd invoice line 18.29 / 1.21 = calculated net 15.1157 -> 3.17 -> tax too low: 6.34
——————-
1st invoice line 18.29 / 1.21 = calc. net 15.1157 -> 3.1743 
2nd invoice line 18.29 / 1.21 = calc. net 15.1157 -> 3.1743 -> coincidentally correct: 6.35
——————-
1st invoice line 18.29 * 0.21 / 1.21 = 3.17429752066116 -> 3.1743
2nd invoice line 18.29 * 0.21/ 1.21 = 3.17429752066116 -> 3.1743 -> correct: 6.35

How to write program script for sales tax? 
  • Calculate and store tax amounts at delivery / invoice line level, to exactly four decimal places;
  • Change a table definition for field properties if technically necessary;
  • Round an amount properly before storing, avoid ‘-/- 0.00’ after e.g. subtraction of rounded amounts;
  • Initialize a variable value in script like 0.00 prevents from possible interpretation as integer;
  • Provide a report to compare periodic tax payment versus the percentage of net;
  • Achieve a minimal percentage of differences at invoice level using two more decimal places;
  • Have the functional, process or product owner view test scenarios.
What to display of tax on an invoice?
  • Display of tax at invoice line level is usually irrelevant and not a standard;
  • Two decimal places may not have been summed correctly to match the invoice level.
How to stabilize using country-independent guidelines?
  • Calculate tax amounts to exactly two extra decimal places;
  • Round eg net 0.07 + 21% = 0.0847 right away to 0.08, not first to 0.085 and secondly to 0.09;
  • From net, eg 21%, such as: 2 * 15.1157 + 2 * 3.1743 -> 30.23 + 6.35 -> 36.59;
  • From gross, eg 21%, such as: 2 * 18.29 -> 2 * 15.1157 + 2 * 3.1743 -> 30.23 + 6.35 -> 36.59;
  • Display the two additional decimal places if the tax is displayed at invoice line level;
  • Calculate the net price up to exactly two additional decimal places if from gross.