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 turned out to be practical;
- An ERP (Enterprise Resource Planning) system cannot handle all kinds of country-specific regulations.
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 invoice level.
How to stabilize without country-specific 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.