Rounding Rules That Survive Audit in Spreadsheets and SQL

The auditor asks a simple question: how do you round? And three people in the finance team give three answers. Per line, said one. At the invoice total, said another. Whatever Excel does, said the third. All three describe something real, and they produce figures that differ by a cent here and there, which over a year of invoices becomes a difference that someone must explain. A rounding rule that survives audit is not clever. It is written down, applied in one place, tested on the awkward cases and visible in the report. This article shows how to write the policy, how each tool behaves and how to test it. It supports Excel reports that finance teams rely on and extends rounding and formatting numbers for finance readers, which covers the display side.

The policy, in a paragraph

A rounding policy has four parts. Write all four.

Level. Where does rounding happen? On each line, on each document, on a period total, or only on display? A policy might say: "Line amounts are rounded to two decimals. Tax is calculated per line and rounded to two decimals. Document totals are the sum of the rounded lines."

Precision. How many decimals? Two for most currencies; zero for yen; three for dinar. Intermediate calculations carry more precision than the final figure.

Tie rule. What happens at exactly half? Round half away from zero (2.345 becomes 2.35, and -2.345 becomes -2.35) is the common commercial rule. Round half to even (banker's rounding) is used in some statistical and accounting contexts to avoid bias. Round half up (toward positive infinity) treats negatives differently from half away from zero: -2.345 becomes -2.34.

Remainder. When a total must be split into parts that add to it (an allocation of a discount, a split payment), who gets the leftover cent?

Put the paragraph in the README of every report and in the code comment of the function that implements it. If the rule comes from a law or a standard, cite it. If an invoicing system already applies a rule, the report follows that system, and your job is to reproduce it faithfully.

Why the level matters

Take an invoice with three lines of 0.33 at 20% tax. Each line's tax is 0.066, which rounds to 0.07, so three lines give a tax total of 0.21. Rounded on the document instead: net 0.99, tax 0.198, which rounds to 0.20. Same data, 0.21 against 0.20. Neither is wrong; they are different rules. Scale that across 40,000 invoices and the difference is real money, and the choice has to match the one used on the issued documents, or the report does not agree with the customer's copy.

The same applies to currency conversion (convert each line or the total), to discounts (percent off each line or off the total) and to prorations. Whichever you pick, apply it identically in every place that computes the figure.

How each tool rounds

PostgreSQL round(numeric, n) rounds half away from zero: round(2.345, 2) is 2.35 and round(-2.345, 2) is -2.35. On double precision, round(x) rounds half to even, and the representation error of binary floats makes round(2.345::double precision, 2) an unreliable ground for rules. Use numeric for money.

Excel ROUND(2.345, 2) returns 2.35 and ROUND(-2.345, 2) returns -2.35: half away from zero. ROUNDUP and ROUNDDOWN round away from and toward zero. MROUND, CEILING and FLOOR round to multiples. Excel works in binary floating point, so 2.345 may be stored as 2.34499999999999997, and Excel's ROUND applies some tolerance so that it usually behaves as if the decimal were exact; the result at the edge is not guaranteed to match exact decimal arithmetic.

Python round(2.345, 2) returns 2.35 or 2.34 depending on the binary representation; round(2.5) returns 2 and round(3.5) returns 4, because the built-in uses half to even. For money use decimal:

from decimal import Decimal, ROUND_HALF_UP

def money(x, places="0.01"):
    return Decimal(x).quantize(Decimal(places), rounding=ROUND_HALF_UP)

money("2.345")     # Decimal('2.35')
money("-2.345")    # Decimal('-2.35')

Note the names: ROUND_HALF_UP in Python's decimal module rounds ties away from zero, so -2.345 becomes -2.35, which matches PostgreSQL and Excel. ROUND_HALF_EVEN is banker's rounding, and ROUND_CEILING rounds toward positive infinity. Pass strings or integers to Decimal, never floats, or the representation error comes along.

pandas Series.round(2) uses NumPy's round half to even on floats. For money in a data frame, either round in SQL before loading, convert to Decimal objects, or work in integer cents.

Implement once

Rounding in several places multiplies chances for disagreement. Put the rule in one place, preferably the database, and let everything else read the result.

CREATE FUNCTION money_round(x numeric) RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$ SELECT round(x, 2) $$;

A named function makes the rule searchable and changeable in one spot, and the policy sentence can sit in a comment on it. The report queries call it. The workbook receives rounded values and does not round again; a ROUND in a cell formula on top of rounded values is harmless but is a second implementation to keep in step.

Where the sheet owns a calculation (a budget workbook where planners enter a rate and the sheet computes amounts), use ROUND in the formulas at the level the policy states, and note it:

=ROUND(D2*E2, 2)               line amount, rounded per line
=SUM(F2:F20)                   document total as the sum of rounded lines

Never rely on number formats to round; they change display only.

Allocation: when parts must add to the whole

Splitting 100.00 three ways gives 33.333... Rounded parts of 33.33 add to 99.99. The missing cent has to go somewhere. The largest remainder method allocates it fairly: round every part down, then give the leftover cents, one each, to the parts with the largest fractional remainders.

from decimal import Decimal, ROUND_DOWN

def allocate(total, weights):
    total = Decimal(total)
    w = [Decimal(x) for x in weights]
    s = sum(w)
    raw = [total * x / s for x in w]
    parts = [r.quantize(Decimal("0.01"), rounding=ROUND_DOWN) for r in raw]
    left = int((total - sum(parts)) * 100)
    order = sorted(range(len(raw)), key=lambda i: raw[i] - parts[i], reverse=True)
    for i in order[:left]:
        parts[i] += Decimal("0.01")
    return parts

allocate("100.00", [1, 1, 1])   # [Decimal('33.34'), Decimal('33.33'), Decimal('33.33')]

Ties in the remainders need a deterministic order (by index, as above, or by an agreed key). The rule must be stated, because "who gets the extra cent" is exactly what a reviewer will ask. Invoicing systems that split a discount across lines use a method like this to keep the total exact.

Test the rule

A rounding rule is a function with edge cases, and the tests are the proof. Build a table of cases with the expected result, and run it in every tool that rounds.

The row for 1.005 is the one that exposes floats: as a binary double, 1.005 is stored as 1.00499999999999989, and a naive floating-point round gives 1.00. A decimal or numeric implementation gives 1.01. Run the table through the SQL function, the Excel formula and the Python function, and fix any that disagree. Add a case for three equal lines and for an allocation, matching the examples in the policy.

Keep the test cases in the repository as a CSV and compare tool outputs against it in CI, with the Excel column filled by a small script that opens the workbook in LibreOffice and reads the results (testing a generated Excel report covers workbook-level tests).

Document what auditors read

When the auditor arrives, they want: the policy paragraph, the code or formula that implements it, the test cases and a sample showing the result on real data. Keep them together on one page of the process documentation. If the policy changed during the year, keep both versions with the dates they applied, and make sure the evidence for a given period shows which was in force.

When no single rounding rule is correct

Some situations need different rules in different places: tax rounded per line because the law says so, invoice totals rounded on the document because the customer's system does, and management figures shown in thousands. That is fine, provided each is named and applied where it belongs. What is not fine is applying a convenient rule in a place that has a prescribed one. Statistical reporting sometimes prefers half to even to avoid bias in aggregates. Currency with no minor unit rounds to whole units. Check the requirement of the document or the jurisdiction, and when in doubt ask the accountant who signs the figures. The PostgreSQL manual describes mathematical functions and round, and Python documents the decimal module's rounding modes.