What specifications are out there for the precision required to store money?

Question

SQL uses a notation of NUMERIC(precision, scale)

I've been googling on what precision and scale is required to store money and I see numerous people referencing things like the European Union (EU), and GAAP standardizing on numeric(x,6) or numeric(x,3) or something like that. I'm just wondering where this is specified. Can anyone quote the spec, provide a link, and date that this was issued?

Answer 1

Federal Accounting Standards Advisory Board (FASAB)

FASAB as a slighltly murky relationship with GAAP, but it's "the body that establishes GAAP for federal reporting entities." I believe it publishes what is referred to as GAAP in the document, "Statement of Federal Financial Accounting Standards (SFFAS) 34, The Hierarchy of Generally Accepted Accounting Principles for Federal Entities, Including the Application of Standards Issued by the Financial Accounting Standards Board."

Their FASAB Handbook mentions rounding to three decimal places when required.

The agency should determine whether the proper dollar scale (e.g., whole dollars, hundreds, thousands, etc.) for the cash flow spreadsheets was used. Some program subsidy rates, particularly those for programs disbursing over several years, may be influenced slightly with the scale of the program. Therefore, management should determine whether rounding to three decimal places has no significant effect on the cash flow spreadsheet values and the subsidy rate.

EU Economic and Financial Affairs

The only document that I've found that cites an EU standard is this document entitled Converting to the euro. It says,

National law can bring more detail to rules on rounding as long as this leads to a higher degree of accuracy. For example, some groups of services that are sold in units, such as the telephone, electricity or fuel, may require greater precision. In this case, their unit price could be expressed in three or four decimal places and rounding to the nearest cent may only take place on the total amount.

So I would guess that in the case of utilities to be safe in the EU, pricing to a scale of four decimal places is appropriate and you can only be sure if you round at the end of the transaction.

Summary

Use what works for your use case. There is no general rule here. Both are fine:

• store as NUMERIC(precision,scale)
• store as int or bigint with cents or dollars.

Answer 2

I doubt there is a spec that applies to any storage of currencies.
Of course you do not use data types that have rounding errors (like floats), you use a type that can exactly represent your amounts.

Examples with the US dollar:

• If you only need to store dollars, use integers.
• If you need to store cents, use numeric(precision,2). But you might as well store cents in an integer.
• If for any reason you need to store fractional cents, determine the number of decimals D you need, and use numeric(precision,D)

Of course the type will be further (precision) determined by the maximum amount you need to store, the data types available in your database, and occasionally the architecture of the operating system (That defines the size of an integer; but often your database will serve as intermediary so that you don't have to bother with that).

Answer 3

Wikipedia hosts this page where world currencies and therir fractions are collected. There are ony 6 currencies which need 3 fractional digits and the rest needs 2 fractional digits. So, it would work perfectly if your precision is 13 and decimals is 4 - i.e. in MySQL DECIMAL(13,4) which stores data in 6 bytes and also aligns with GAAP.