0

I support a financial .net application. There is a lot of advice to use the decimal datatype for financial stuff.

Now I am stuck with this:

decimal price = 1.0m/12.0m;
decimal quantity = 2637.18m;
decimal result = price * quantity;  //results in 219.76499999999999999999999991

The problem is that the correct value to charge our customer is 219.77 (round function, MidpointRounding.AwayFromZero) and not 219.76.

If I change everything to double, it seems to work:

double price = 1.0/12.0;
double quantity = 2637.18;
double result = price * quantity;  //results in 219.765

Shall I change everything to double? Will there be other problems with fractions?

I think this question is different from Difference between decimal, float and double in .NET? because it does not really explain to me why the result with a more precise datatype decimal is less accurate (in the sample above) than the result with the double datatype which uses fewer bytes.

Community
  • 1
  • 1
yonexbat
  • 2,902
  • 2
  • 32
  • 43
  • 2
    Possible duplicate of [Difference between decimal, float and double in .NET?](http://stackoverflow.com/questions/618535/difference-between-decimal-float-and-double-in-net) – Heretic Monkey Feb 21 '17 at 16:59
  • 7
    Just a basic flaw in the computation, 1/12 never has a perfect result unless you count with 12 fingers. What the heck kind of company charges their customers a twelfth of a dollar for an item? You'll have to get the unit price correct first. – Hans Passant Feb 21 '17 at 17:05
  • 1
    "Shall I change everything to double" - why? You've found one example where your expectations weren't met (although, as Hans points out, you've got an odd starting point where your per-unit price isn't easy to represent, and then I'd point out that it's not a per-unit price since you apparently can subdivide your units anyway). It would be better to pick better *representations* of your quantities. As likely as not, it's possible to find equal and opposite examples where `decimal` fits and `double` produces the wrong results. – Damien_The_Unbeliever Feb 21 '17 at 17:26
  • 1
    Put division last: `1m*2637.18m/12m` instead of `1m/12m*2637.18m`. – user4003407 Feb 21 '17 at 17:28
  • @PetSerAI: Putting division last changes the result indeed: 219.765M. Tnx for that. – yonexbat Feb 21 '17 at 18:52
  • @Hans Passant: 12 is for number for months. – yonexbat Feb 21 '17 at 18:53

2 Answers2

6

The reason decimal is recommended is that all numbers that can be represented as non-repeating decimals can be accurately represented in a decimal type. Units of money in the real world are always non-repeating decimals. Your problem as others have said is that your price is, for some reason, not representable as a non-repeating decimal. That is it is 0.083333333.... Using a double doesn't actually help in terms of accuracy - a double can not accurately represent 1/12 either. In this case the lack of accuracy is not causing a problem but in others it might.

Also more importantly the choice to use a double will mean there are many more numbers that you couldn't represent completely accurately. For example 0.01, 0.02, 0.03... Yeah, quite a lot of numbers you are likely to care about can't be accurately represented as a double.

In this case the question of where the price comes from is really the important one. Wherever you are storing that price almost certainly isn't storing 1/12 exactly. Either you are storing an approximation already or that price is actually the result of a calculations (or you are using a very unusual number storage system where you are storing rational numbers but this seems wildly unlikely).

What you really want is a price that can be represented as a double. If that is what you have but then you modify it (eg by dividing by 12 to get a monthly cost from an annual) then you need to do that division as late as possible. And quite possibly you also need to calculate the monthly cost as a division of the outstanding balance. What I mean by this last part is that if you are paying $10 a year in monthly instalments you might charge $0.83 for the first month. Then the second month you charge ($10-0.83)/11. This would be 0.83 again. On the fifth month you charge (10-0.83*4)/8 which now is 0.84 (once rounded). Then next month its (10-0.83*4-0.84)/7 and so on. This way you guarantee that the total charge is correct and don't worry about compounded errors.

At the end of the day you are the only one to judge whether you can re-architect your system to remove all rounding errors like this or whether you have to mitigate them in some way as I've suggested. Your best bet though is to read up on everything you can about floating point numbers, both decimal and binary, so that you fully understand the implications of choosing one over the other.

Chris
  • 27,210
  • 6
  • 71
  • 92
1

Usually, in financial calculations, multiplications and divisions are expected to be rounded to a certain number of decimal places and in a certain way. (Most currency systems use only base-10 amounts of money; in these systems, non-base-10 amounts of money are rare, if they ever occur.) Dividing a price by 12 without more is not always expected to result in a base 10 number; the business logic will dictate how that price will be rounded, including the number of decimal places the result will have. Depending on the business logic, such a result as 0.083333333333333333 might not be the appropriate one.

Peter O.
  • 32,158
  • 14
  • 82
  • 96