On VAT Calculation / Rounding Monetary Values

Yesterday API design was hard, today creating invoice line items with VAT on them is hard.

When calculating taxes on items, a very careful approach must be taken on rounding. Line items of an invoice need to be stand-alone units and special care must be taken when calculating sum of rounded values. For unaware developer, this could be trickier than expected.

Products

I'll demonstrate the problem with a ficticous invoice. Let's say I have five different products to sell with following unit prices:

• Product 1    1.24 €
• Product 2    2.77 €
• Product 3    3.31 €
• Product 4    4.01 €
• Product 5    5.12 €

Example invoice #1

A happy customer bought ten of Product 1. Following invoice could be generated from the transaction:

Rather trivial arithmetic with value added tax being 24% can be made. 10 units costing 1,24€ each will total 12,40€. Adding 24% tax on them will result VAT amount of 2,976€. As €-cents really needs to be between 0 and 99 rounding is needed on total amount. Minor numeric cruelty will be needed to make result feasible as money. That's ok. The end result is correct and everybody are happy.

Example invoice #2

Second happy customer bought same as the first customer and additionally 14 of Product 2. Following invoice could be generated from the transaction:

Looking at columns total and total rounded reveal the unpleasant surprise. If rounding is done once, after sum, the rounded value isn't 63,47 as expected. When rounding the total sum into two decimals 63,4632 will result 63,46. There is a cent missing! Where did it go? Or ... alternatively, where did the extra cent appear into total rounded column?

Example invoice #3

Let's escalate. A really good customer bought everything. Lots of everything. Invoice will look like this:

Whoooo! It escalates. Now we're missing two cents already. Total sum of 342,3144€ will round down to 342,31€.

Lesson Learned

Doing one rounding at the end is mathematically sound. Arithmetic really works like that. However, when working with money, there are additional constraints. Each line needs to be real and could be a separate invoice. Because this constraint, we're rounding on each line. Calculating sum of rounding stops making sense mathematically.

Please note, these numbers were semi-randomly selected to demonstrate the problem. In real world scenario roundings can easily cancel each other and detecting this problem will be much more difficult.

Modeling real-world into computer software is surprisingly tricky.

- Anonymous