Floating Point Numbers & Currency Rounding Errors

A reminder to those who learned this lesson long ago, and a warning to those that have not learned it for themselves: *Don’t use floating point numbers for money*.

When doing any kind of calculation with currency, accuracy is extremely important. And floating point numbers (floats and doubles) don’t have an accurate enough representation to prevent rounding errors from accumulating when doing arithmetic with monetary values.

An excellent “StackOverflow response”:https://stackoverflow.com/a/3730040 that explains the issue states:

bq. Representing money as a double or float will probably look good at first as the software rounds off the tiny errors, but as you perform more additions, subtractions, multiplications and divisions on inexact numbers, you’ll lose more and more precision as the errors add up. This makes floats and doubles inadequate for dealing with money, where perfect accuracy for multiples of base 10 powers is required.

h2. Ways it Can Go Wrong

When it comes to dealing with money in a computer program, a developer needs to stay ever-vigilant. Just because you, or a previous developer, made the right call by using a @DECIMAL@ column when creating the database schema, doesn’t mean that accuracy can’t be lost before the result of some calculation is displayed to an end user. It’s all too easy to slip up and lose the necessary accuracy without even realizing it.

h3. 1. During Calculations

In order to maintain the required accuracy for financial calculations, the best option (in my experience) is to use a built-in decimal type if your language provides one (e.g. Java and Ruby both have a @BigDecimal@ class, C# has a @decimal@ type).

In Ruby:

amount = BigDecimal.new("5.99")

h3. 2. During Storage

To ensure an accurate representation of the currency amount in the database, use something like MySQL’s @DECIMAL@ column. One “StackOverflow response”:https://stackoverflow.com/a/13030389 indicates that @DECIMAL(15,2)@ is the way to go. PostgreSQL even has a “money”:https://www.postgresql.org/docs/9.1/static/datatype-money.html#DATATYPE-MONEY-TABLE numeric type that can be used for a column.

If you are creating your schema using a Rails migration, you can specify a decimal column like this:

create_table :entry do |t|
  t.decimal :amount, precision: 15, scale: 2
end

As this “Decimal numbers in Rails and MySQL”:https://torontoprogrammer.ca/2010/05/decimal-numbers-in-rails-and-mysql/ post points out, if you forget to specify the precision and scale, Rails will default (for a MySQL database) to a precision of 10 and a scale of 0. As the post points out, this means @15.37@ will be stored in the database as @15@!

h3. 3. During Retrieval

Now that the database is storing the monetary values accurately, you don’t want to do anything to lose that accuracy when retrieving values from it. In a Rails app you don’t need to worry about it because ActiveRecord automatically uses @BigDecimal@ for @DECIMAL@ type columns. But if you are using an alternative ORM, like “DataMapper”:https://datamapper.org you need to make sure you are using @Decimal@ for your properties, and not @Float@.

The same opportunity for error arises in ORMs in most languages. For example, when using Java’s “Hibernate”:https://hibernate.org/ ORM, you need to specify the variable as a @BigDecimal@ not a @Float@ or @Double@.

The same vigilance is needed when not using an ORM at all. I recently discovered a problem in some Java reporting code that was using straight JDBC calls, and extracting a price value from the database with @rset.getDouble(“price”)@ instead of @rset.getBigDecimal(“price”)@.

h3. 4. Before Display

Just because a set of monetary values has been saved in the database accurately, and then retrieved from the database accurately, doesn’t mean you get to relax. Any time you are doing a calculation with a set of monetary values, you need to be careful to not accidentally lose the accuracy you’ve worked so hard to maintain by introducing a floating point error just before displaying a value to the end user.

Here is a seemingly correct totaling up of BigDecimal values from a Ruby program:

  subtotals.inject(0.0) do |total, val|
    total += val
  end

But by providing that initial value of @0.0@, all of the BigDecimals are converted to floats, and errors can be introduced. Instead it should look like:

  subtotals.inject(BigDecimal.new(0)) do |total, val|
    total += val
  end

Any language that will silently convert from a decimal type to a float/double is susceptible to this easy-to-make mistake.

h2. Resources

For some good in-depth explanation/analysis of why floating point numbers are not acceptable for representing money, give these a read:

* “Why not use Double or Float to represent currency”:https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency