Money is never a float

The bug in one line

Type 0.1 + 0.2 into Python or a browser console and you get 0.30000000000000004. Excel has the same behaviour underneath: Microsoft's own documentation shows =(0.5-0.4-0.1)*1 returning -2.78E-17 instead of zero.

The reason is that computers store these numbers in binary. 0.1 has no exact binary form, in the same way that one third has no exact decimal form. The machine keeps the nearest value it can, and the tiny gap shows up when you calculate.

In a physics simulation that gap is noise. In a ledger it is a cent someone has to explain.

Where the cents go: rounding

The gap rarely changes a single total. It changes the result when you round. In Python, round(2.675, 2) returns 2.67, while anyone rounding by hand writes 2.68. The value the computer actually holds for 2.675 is:

2.67499999999999982236431605997495353221893310546875

It sits just under the half cent, so it rounds down.

There is a second trap. Python's round() sends exact halves to the nearest even digit, so round(0.125, 2) gives 0.12. Most invoices expect half up, which gives 0.13. Two different rules, and nothing in the code tells you which one you got.

A test with 40,000 invoice lines

To see how often this happens, I generated 40,000 invoice lines with a fixed random seed: unit prices from 0.50 to 500.00, quantities from 1 to 50, 10% tax added, each line rounded to the cent. Then I calculated every line twice, once with floats and round(), once with Decimal and half-up rounding.

In every one of the 597 lines the float result was the lower one. The check: 279,231,833.85 − 279,231,827.88 = 5.97, and 597 lines × 0.01 = 5.97. About half of the 597 come from values stored just below the half cent, the rest from round-half-to-even.

5.97 on 279 million is small. The size is not the problem. The problem is that 597 lines no longer match the invoices printed by the other system, and an auditor's sample will find them. Explaining each one costs more than the cents.

The rule

Money is Decimal, rounded at a step you can show.

In Python, here is one of the lines from the test that disagreed, done properly:

from decimal import Decimal, ROUND_HALF_UP

CENT = Decimal("0.01")
price = Decimal("365.83")     # from text, not a float
line = price * 35 * Decimal("1.1")
line = line.quantize(CENT, rounding=ROUND_HALF_UP)
print(line)                   # 14084.46

Float and round() give 14084.45 for the same line, because 14,084.455 is stored just below the half cent.

Three details matter:

1. Create decimals from text: Decimal("0.1"), not Decimal(0.1), which copies the float's error.
2. Round where the document rounds. If the invoice shows line totals, round each line, then add the rounded lines.
3. Choose the rounding mode on purpose and write it down. Half up suits most invoices; check what your tax authority expects.

The same rule outside Python: in SQL use DECIMAL(19,2) or NUMERIC, never FLOAT or REAL. In JavaScript work in whole cents or use a decimal library; the calculators on this site use big.js. In Excel, wrap each step the invoice shows in ROUND().

Check your own files

Search your scripts for float( near anything called amount, price or total. Look for database columns typed FLOAT or REAL. Look for round( with no rounding mode stated. Each one is a place where cents can move without anyone deciding they should.

The free calculators on this site follow the rule and print every step, so you can compare their working with yours.