Rash thoughts about .NET, C#, F# and Dynamics NAV.


"Every solution will only lead to new problems."

Wednesday, 21. February 2018


Rounding is a bitch – Twitter polls, percentages, and VAT

Filed under: Diverses — Steffen Forkmann at 11:11 Uhr

A couple of days ago I came across a tweet where people made fun of a Twitter poll that essentially looked like the following:

“Which letter do you prefer?”

Letter Counts Percentages
A 406 41 %
B 348 35 %
C 246 25 %
D 0 -1 %
Sum 1000 100 %

After 12 years of work on invoicing/billing systems I can safely say: I made the same mistake as well. Any many many more. Rounding is actually a hard problem and in this post I want to show some of the strategies that can help to deal with the issues that arise from rounding.

So let’s start with this simple twitter poll. Where did it go wrong? The issue is very simple, every percentage is rounded and the rounding error is piling up to 1%.

Letter Counts Percentages Rounded Error
A 406 40,6 41 0,4
B 348 34,8 35 0,2
C 246 24,6 25 0,4
D 0 0 0 0
Sum 1000 100 101 1

So far so good, but now you remender from school that percentages should always add up to 100% – and we have 101%. So what now? Well, now it gets ugly.

Essentially you now have 3 different options:

  1. Show more digits until the error goes away.
  2. Distribute the rounding errors on the individual percentages in the lines.
  3. Ignore the 100% percent rule, since it actually only applies to percentages that were not rounded.

Strategy 1. is often not applicable since you are rounding for some reason. Later in the post we will go over to money and there are just no smaller coins than 0,01 €.

Strategy 3 is actually applicable in this case here, but has the drawback that people will still complain about the statistics since the “percentages should always add up to 100%” rule is very deeply rooted in our heads and we often forget that it only applies to non-rounded values. Later in the post when we talk about money, we will see that this strategy is not applicable for invoices and that the sum is fixed.

So we are left with strategy 2 and that’s what Twitter did here. The biggest issue with that strategy is that now at least one of the lines will be incorrrect if you view it in isolation. In this special case Twitter choose a distributation strategy that can be described as “add the error to the value of the last line, so that the sum is correct”. Unfortunatly in this edge case the strategy breaks the analysis completely. First of all: negative percentages in a poll should be considered impossible. And even if you ignore that, the rounding of a zero to something non-zero is a bit unfortunate. So what would be better?

Let’s look at a different strategy, where the rule is only slightly changed to “add the error to the value of the line with largest value, so that the sum is correct”. We get:

Letter Counts Percentages Rounded Error
A 406 40,6 40 -0,6
B 348 34,8 35 0,2
C 246 24,6 25 0,4
D 0 0 0 0
Sum 1000 100 100 0

That simple trick works well for twitter polls. Note: You still have the issue on the first line if you view it in isolation, but there is no way to avoid that if you fix the sum to 100%.

(I personally would adapt the rule a bit and check if it would change the poll from one winner to two winners that are neck and neck.)

VAT calculation

Let’s go over from twitter polls to invoice calculation and VAT. Here the (german) law has very specific rules for rounding and one is that VAT is always calculated on the sum of the individual amounts per VAT category. Let’s look at a sample why that is. Let’s consider you want to buy 100 nuts in the supermarket. The nuts have a unit price of 0,01 € and we have a VAT of 19%. So what you expect is:

Quantity Unit Price VAT % Amount Amount incl. VAT
100 0,01 19 1,00 1,19
Sum 100 1,00 1,19

But what if we trying to be clever and ask the cashier to treat every nut as a seperate invoice line:

Quantity Unit Price VAT % Amount Amount incl. VAT
1 0,01 19 0,01 0,01
1 0,01 19 0,01 0,01
1 0,01 19 0,01 0,01
Sum 100 1,00 1,00

Oups. We managed to avoid paying the VAT completely. Clearly this is not in the interest of the state 🙂

So basically the law means that we can’t choose rule 3. from above and we always have to do something else. In case of invoices a very good strategy is to just not print the amount incl. VAT for individual lines. Since the law doesn’t require you to show these amounts this is a valid strategy. Unfortunately many customers still expect you to show these.

So let’s apply our last distribution rule here:

Quantity Unit Price VAT % Amount Amount incl. VAT
1 0,01 19 0,01 0,01
1 0,01 19 0,01 0,20
1 0,01 19 0,01 0,01
Sum 100 1,00 1,19

We chose a line with maximum amount and added the rounding error to it. This still looks a bit unfortunate. So in practice we often change the rule to: “order all non-zero lines by decreasing absolute amount and start to distribute the rounding error in smallest possible unit from top to bottom until the sum is correct.”

Quantity Unit Price VAT % Amount Amount incl. VAT
1 0,01 19 0,01 0,02
1 0,01 19 0,01 0,02
1 0,01 19 0,01 0,01
Sum 100 1,00 1,19

So in this extreme edge case 19 lines would be adjusted, but only very slightly. We found this rule works very good in practice if you really need to print the “Amount incl. VAT” for the lines.

In real invoices the situation even gets worse. Now you need to deal with different VAT % categories and also with negative amounts. So you need to group lines by sign and VAT category and apply the rule for each of these groups. Happy rounding!

Addendum

There is yet another way to solve the issue. You can add a new invoice line with type “VAT rounding correction” which is VAT only and contains the error and have no amount excl. VAT . Systems like Dynamics NAV even know “External VAT correction” to deal with VAT differences that arise when two systems calculate the VAT differently.