To round or not to
Rounding values in transactions to tackle floating point errors
· 4 min read
About two months into joining the company, I started contributing to support activity. After working on server administration, where things were usually black and white, I found erpnext to be a patchwork of greyer shades. There were usually multiple ways to achieve the same result and it all came down to design decisions and experience. Every time I tried to solve a ticket, I had to dig deep into the code and at the same time, get my concepts cleared, most often from Nabin (He has an immense amount of patience).
Debit and Credit not equal for this voucher: Diff (Debit) is 0.00280000000021
Did not save
Somtimes, customers used to face this error. It would bewilder them. Where did 0.0028 come from?
They saw rounded figures in the form. But, the values stored in variables weren't rounded. When you clicked on the field to edit it, you could see the stored value. The rounding was just for display. What you saw wasn't what there was.
Similarly, there were problems with Write Off Amount in Sales Invoice. If the Grand Total was 149.60 and the Customer paid 150, the user would write off -0.40. But, the Grand Total stored was something like 149.597342. Hence, the user would get:
(Paid amount + Write Off Amount) cannot be greater than Grand Total
Did not save
I hate inconsistency. So, these issues seemed to arise just to taunt me. But, the calculation code at its core was very complex and I lacked the understanding to untangle it. The simpler and immediate solution was to tell the customer to adjust a value.
The problem escalated when we implemented tax inclusive prices. It involved calculating tax exclusive amount from the inclusive amount, given tax percentages. Once the tax exclusive amount is calculated, the code for forward calculation kicked in, and calculated the tax amounts from the percentages.
One would expect that:
tax exclusive amount + tax amount for that item = tax inclusive amount
But, this wasn't always the case. Reverse calculating tax exclusive amount involves division, while the forward calculation of tax amount from tax exclusive amount involves multiplication. Such sequential division and multiplication often caused floating point errors.
What are floating point errors?
A computer uses floating point representation of real numbers. It looks something like:
Significant digits × baseexponent
Floating point representation reduces the storage requirements when calculations are performed, thus speeding up calculations by a factor of 10 or more.
However, not all real numbers can be accurately represented using floating point system. Hence, for the computer, 150.1 is 150.09999999999999, the nearest number it can represent. Again, what you see isn't what there is.
To solve such problems for good, we decided to round-off values after each operation, so that, what you see is what there is. With our latest release, such issues were fixed. (Mostly. There might remain some edge cases, which we may discover later).
But, rounding has created a new problem for those who use tax inclusive prices. Suppose, 4.35 includes 21% VAT. So, you get:
Tax exclusive amount = 4.35 / (1 + 0.21) = 3.595 = 3.60 (rounded to 2 decimals)and,
Tax amount = 3.6 * 0.21 = 0.756 = 0.76 (rounded to 2 decimals)
Here, we can see that:
3.60 + 0.76 = 4.36, which is not equal to 4.35. A difference of 0.01 arises.
Even with a Gaussian rounding algorithm that reduces such accumulated errors in the long run, the errors aren't completely eliminated and organizations have to write-off such an accumulation at the end of a period.
It also indicates that a split up of 4.35 cannot be accurately represented using 2 decimals. From another perspective, there is no two decimal number, that will yield 4.35 when a tax of 21% is applied on it.
The immediate solution is to either:
- Increase precision of stored values, such that 3.595 + (0.21) * 3.595 = 4.35, or
- Change 4.35 to 4.36, such that 3.6 + 0.76 = 4.36
We believe that the 2nd option is the right way to go. We can automate this so that the code will correct such prices to representable ones, which in this case is to change 4.35 to 4.36 for that Item. What are your thoughts?
This one problem got me started with accounting, selling and buying modules of erpnext. Yet, I get the feeling that it hasn't been solved completely. I await the edge cases.
- Round-off error: https://en.wikipedia.org/wiki/Round-off_error
- Gaussian rounding / Banker's rounding: http://www.xbeat.net/vbspeed/i_BankersRounding.htm
Anand is the Chief Technology Officer at ERPNext. He reads fiction, dabbles in photography and is always on the watch for the best ToDo app.
Excellent, I would like to know what you think of version 12 erpnext and the calculations that are currently performed.
This problem was faced sevral times in ERP Software. There is a longish discussion about this whi