Which currency rounding algorithm to use in .Net?

Question

Is there a best practice on which rounding algorithm to use in .Net for decimal currency rounding operations that takes into account back-end systems? Real world experience appreciated.

---

.Net uses Banker’s Rounding by default. (MidpointRounding.ToEven) This is at odds with the SQL Server back-end that I’ll be using because SQL Server uses arithmetic rounding (MidpointRounding.AwayFromZero) and has no built in function to mimic Banker's Rounding.

Note: I'm using decimal(18, 4) in SQL Server, decimal in .Net

Here's an example of .Net's default Banker's Rounding to two decimal places vs. SQL Server's rounding to two decimal places:

| Value | .Net  | SQL Server  |
|-------|-------|-------------|
| 2.445 | 2.44  | 2.45        |
| 2.455 | 2.46  | 2.46        |
| 2.465 | 2.46  | 2.47        |
| 3.445 | 3.44  | 3.45        |
| 3.455 | 3.46  | 3.46        |
| 3.465 | 3.46  | 3.47        |

// t-sql
declare @decimalPlaces int
set @decimalPlaces = 2

select round(convert(decimal(18, 4), 2.445), @decimalPlaces) -- 2.45
select round(convert(decimal(18, 4), 2.455), @decimalPlaces) -- 2.46
select round(convert(decimal(18, 4), 2.465), @decimalPlaces) -- 2.47
select round(convert(decimal(18, 4), 3.445), @decimalPlaces) -- 3.45
select round(convert(decimal(18, 4), 3.455), @decimalPlaces) -- 3.46
select round(convert(decimal(18, 4), 3.465), @decimalPlaces) -- 3.47

// .Net
var algorithm = MidpointRounding.ToEven;
var decimalPlaces = 2;
Console.WriteLine(decimal.Round(2.445M, decimalPlaces, algorithm).ToString()); // 2.44
Console.WriteLine(decimal.Round(2.455M, decimalPlaces, algorithm).ToString()); // 2.46
Console.WriteLine(decimal.Round(2.465M, decimalPlaces, algorithm).ToString()); // 2.46
Console.WriteLine(decimal.Round(3.445M, decimalPlaces, algorithm).ToString()); // 3.44
Console.WriteLine(decimal.Round(3.455M, decimalPlaces, algorithm).ToString()); // 3.46
Console.WriteLine(decimal.Round(3.465M, decimalPlaces, algorithm).ToString()); // 3.46

If I ever retrieve a value from SQL Server and let it handle the rounding I'll wind up with pennies off here and there as compared to what .Net were to tell me because of it's default Banker's Rounding.

It seems that I should forge ahead and use arithmetic rounding throughout my .Net code base but I see an open source project (nopCommerce) using the default Banker's Rounding so it makes me wonder what the best approach is.

---

Perhaps a better question is: Is there any reason not to use arithmetic rounding (MidpointRounding.AwayFromZero) for currencies in .Net?

Answer 1

From the documentation:

Rounding away from zero is the most widely known form of rounding, while rounding to nearest even is the standard in financial and statistical operations. It conforms to IEEE Standard 754, section 4. When used in multiple rounding operations, rounding to nearest even reduces the rounding error that is caused by consistently rounding midpoint values in a single direction. In some cases, this rounding error can be significant.

Banker's rounding rarely makes sense in the real world. I used to work in the banking industry and they once sent me on a mission to stamp out "rounding errors" in reports that were due to this culprit.

In addition to banking, calculating shipping, income tax, and sales tax always uses "away from zero" rounding.

The only real world use I can think of for banker's rounding is for perhaps calculating interest and splitting the difference or perhaps paying out commissions.

Microsoft did not choose to make the default MidpointRounding.ToEven for good reason. They did so to maintain backward compatibility with Visual Basic (prior to .NET). It was not done so because it is a reasonable default, or best default of any kind. If they had to make the decision again today, it would most likely be MidpointRounding.AwayFromZero.

Keep in mind, when someone checks your work they will be using the "away from zero" method that we all learned in grade school. In my opinion, "because Microsoft made it the default" is not a good enough excuse for the program to be using it. There should be a valid business reason to use banker's rounding if and when it is necessary. If it is not explicitly called out in the application requirements, you should use MidpointRounding.AwayFromZero. It works best to change the default in your application framework to MidpointRounding.AwayFromZero.

Answer 2

When you truly need to round, do so in Only one environment. Failing to do so will cause you to rely on defaults and configurations which will eventually bite you. All other places keep the precision you were handed to avoid any strange issues.

Which algorithm you use is Dependant on the use case for your application. But regardless, pick one and make sure you have it centralized and changeable as needed.

Also the. Net default is the most common rounding for money values, so unless you have a sound, business reason to not use it, I would stick with that. The uneven distribution can otherwise cause real monetary consequences over time.

Answer 3

Use decimal rather than float / double because floats and doubles need to be stored in base 2, and decimal gets stored in base 10. If you map your SQL Server type to decimal in .NET your rounding issues will go away.

RE: the algorithm, dealing with monetary values, you have to either

• Make sure you store enough decimal places in each value/calculation step as to make sure any rounding errors are so small that they can be dismissed
• Use bankers rounding, to try and mitigate rounding errors

Imagine the following:

        Real Value  Standard    Bankers
        0.25        0.3         0.2
        0.75        0.8         0.8
-----------------------------------------
Sum     1           1.1         1

Basically, using bankers rounding, whenever you have a

x.5  (e.g. 6.5)

Using standard rounding, you will always round up, meaning for every midpoint rounding, you are adding 0.5 to the total value, but using bankers rounding, when x is even, you round down (-0.5) and when x is odd, you round up (+0.5) so there is a higher chance that with random values, the +0.5 and -0.5 cancel each other out.

Ofcourse, if you can store each value with a large enough percision, these rounding error are so miniscule they can be discarded.

Personally for calculations I always use Bankers, but for display to UI I use AwayFromZero (as its more natural)