1

Things I know from other SO questions:

  • I must use either ints or Java's BigDecimal (Java is my case, though the question is broader), not floating point types.
  • I must store at a minimum one more decimal place than what I want to show. In my case I want to show 2 decimal places. The convention is to use decimal(19,4) in the database (in my case it's Postgres, but again this question is broader)

When I have in memory a bill with 101 items each costing $1.00004999999999999 my calculated total from adding all BigDecimals of value $1.00004999999999999 (or all integers if I were to choose that representation) would be $101.005049999999999 which is rounded to $101.01.

If I store 4 decimal places in the DB, each item will be rounded and stored as $1.0000, and when I pull them from database and add them up I get $101.0000, rounded to $101.00.

If I increase the storage precision to decimal(19,5) I need 1001 items instead of 101 for that to happen, but it is still possible. It will always be something that can happen, though one possible solution is to increase storage precision so much that this error is less likely to happen than a lightning striking the computer.

So far my solution is to never use BigDecimal, but instead always use BigDecimal.setScale(4), which sets it to 4 decimal places. Would you suggest a different approach?

Things that I know won't work:

  • Calculating the total after storage. I need to calculate it with JavaScript while the user is inputing the data.
  • Never doing calculations with rounded values. I don't know of a way to store them without rounding them
  • Never rounding up, always down. If I have a tax value of 1.005049999 it must be shown as 1.01. It's a business rule. I could challenge it, but there must be a way that doesn't impact the way the business works.

Other considerations:

  • I don't care much about execution speed or memory usage.
Blueriver
  • 3,212
  • 3
  • 16
  • 33
  • 1
    Do you intend to have your system support prices of `1.00004999999999999` precision? You seem to be contradicting yourself by specifying such a precise price, yet using `DECIMAL(19,4)` in the db. Which one is it? – Kayaman Aug 31 '16 at 18:37
  • 4
    It's a mathematical fact that round(a+b+c) is in general not equal to round(a)+round(b)+round(c). You must decide which one gives the correct value according to the business rules. – Henry Aug 31 '16 at 18:40
  • 1
    I don't write accounting software, but I would expect that if an application requires you to do rounding, it's going to require you do do rounding _in an application-specific way._ – Solomon Slow Aug 31 '16 at 18:42
  • Just wondering - does it even make sense to keep more than 2 decimal digits for currency? I mean you cannot go to the bank and request a payment for 0.0099$ it's either 1 cent or nothing .... – PKey Aug 31 '16 at 18:45
  • @Kayaman you're right, my bad. I don't want to support individual prices of `1.0000499999999`. I have no interest in supporting item totals of `1.0000499999999`, but unless I round everything up that's their value in memory. All I need is to show 2 decimals accurately. `DECIMAL(19,4)` comes from what I understand is a generally accepted solution to store currency. I can quote several SO answers on that. Doesn't mean they're right. – Blueriver Aug 31 '16 at 18:47
  • @Plirkee you can go to this company and request a product which has several separate taxes of 0.0099$ each, and they should be computed accurately and shown in a user-friendly way (2 decimals). Complicated guys I guess. – Blueriver Aug 31 '16 at 18:50
  • @Plirkee Some countries use up to 4 decimal places for their currency. See links in [this answer](http://stackoverflow.com/a/3893804/5221149) for details. – Andreas Aug 31 '16 at 18:52
  • @Blueriver They are right to say 4 decimal places if you need to store international currencies. For US only, 2 is of course enough. See my previous comment. – Andreas Aug 31 '16 at 18:54
  • @Andreas damn, that complicates some guys. I count myself lucky that I only need to support argentinian pesos, US dollars, euros and brazilian reales, all with 2 decimal places. However if I use 2 decimal places in the DB and arbitrary precision in BigDecimal in memory I can get a difference of 1 cent with just 2 items each worth 1.00499999 – Blueriver Aug 31 '16 at 18:56
  • Of course, you wouldn't use different precision in db and in your application - these should be kept the same. However I still think that there shouldn't be such a thing as item costing 1.00499 (speaking about 2 decimal digit currency @Andreas) the final cost of an item is either 1$ or 1.01$. Meaning that if your tax for the product is 0.0099$ it's actually 0.01 so you always do computations with 2 digits. – PKey Aug 31 '16 at 19:11
  • @Plirkee Taxes are *usually* applied to the bill total, not to each individual line item. However, some states/localities *might* do it on a per-item level, though I don't have any examples of ones that do. – Andreas Aug 31 '16 at 19:18
  • @Plirkee that makes sense to me. I'll see if it makes sense to the accountants. – Blueriver Aug 31 '16 at 19:19
  • @Andreas these guys apply different percentages of the same tax to different items (e.g. some items have a 21% of a tax called IVA, some a 10.5% IVA and some a 0% IVA) – Blueriver Aug 31 '16 at 19:19
  • @Blueriver So you sum up the total by tax category, and calculate the tax on those sub-totals. It's required. E.g. see the rules for [sales tax in Texas](http://comptroller.texas.gov/taxinfo/sales/faq_collect.html#collect2): *If you sell multiple taxable items on one invoice, then you **must** compute the tax on the **total sum** of the sales prices of the taxable items sold.* Though, as mentioned in previous comment, other states/localities may do it differently. I do however believe that tax calculation on invoice total is the norm, since it eliminates cumulative rounding errors. – Andreas Aug 31 '16 at 19:25

2 Answers2

6

Always use integer math for currency. Never store currency as a floating point value. Currency is a fixed point value. Store all currency values as an integer that represents the smallest denomination that matters to your system. For example, if you are using USD and the smallest denomination you care about is a penny ($0.01) then store currency values as a count of pennies. If you care about thousandths of a penny ($0.00001) then store that.

DwB
  • 37,124
  • 11
  • 56
  • 82
  • 1
    Thanks, but I don't see how this answers my question – Blueriver Aug 31 '16 at 18:51
  • 2
    @Blueriver Oh, but it does. What DwB is telling you is that you should identify what the smallest unit of currency you plan on dealing with is, and have that be the base of your calculations. For example, let's say that the smallest unit of currency you want to deal with is the tenth of a cent (i.e. $0.001). Then, the integral value of `1000` would correspond to 1000 tenths of one cent, or $1 exactly. There is an extra translation step you have to go through, but doing this mitigates the rounding problems you speak of. – nasukkin Aug 31 '16 at 19:30
0

You may want to consider always storing the BigDecimal value at full precision, doing all the calculations at full precision, and only rounding the value at the last possible moment. That way, your rounding errors don't get accumulated.

However, I must question your example. You've created a situation where the correct answer is $101.005. It's not clear how this value should be rounded. We traditionally round up on a five, but that's because, if there are any other digits, it's closer to the higher value than the lower one. But when it's all zeroes, it's equally close to each valid value, so I'm not sure there is a correct answer in this case.

MiguelMunoz
  • 4,548
  • 3
  • 34
  • 51
  • I thought exactly the same, but if I want till the last possible moment, I will do full precision calculations in memory, then round them right before I store them in the DB, I get the issue I show in my example. Calculations done before storing differ from calculations done after storing. As for the correct answer, my example's answer is $101.005049999999999, which is not equally close to each valid value, it is closer to 101.01 than to 101.00. Still you're right that I should specify my expected answer after rounding is 101.01 – Blueriver Aug 31 '16 at 20:02