Currencies, Exchange Rates, and Multi-Currency Accounting

Multi-currency is where homemade systems break first. Get this model right and a lot of hard problems become easy.

The currency types

Every posting is stored in at least two, usually three or more currencies simultaneously.

Currency type Also called Meaning
Transaction currency Document currency The currency the deal was actually done in
Local currency Company code currency, functional currency The legal entity’s reporting currency — what the statutory accounts are in
Group currency Global currency The parent’s currency, for consolidation
Hard currency   A stable currency used in high-inflation countries
Index-based currency   For hyperinflation reporting

Example: a Bangladeshi subsidiary of a US group sells in EUR.

Invoice line:
  Transaction currency  EUR   10,000.00
  Local currency        BDT 1,320,000.00   @ 132.00
  Group currency        USD    10,800.00   @ 1.08

All three are stored on the journal line. Not computed on read — stored, because the rate at posting time is the legally correct one and rate tables get corrected retroactively.

Table shape 

CREATE TABLE journal_line (
  ...
  currency_code        CHAR(3) NOT NULL,
  amount               NUMERIC(19,4) NOT NULL,
  local_currency_code  CHAR(3) NOT NULL,
  amount_local         NUMERIC(19,4) NOT NULL,
  group_currency_code  CHAR(3),
  amount_group         NUMERIC(19,4),
  exchange_rate        NUMERIC(19,9),
  exchange_rate_date   DATE,
  exchange_rate_type   VARCHAR(10)
);

Exchange rate types

You need more than one rate for the same currency pair on the same day:

Type Used for
Spot / daily Actual transactions
Buying rate (bid) Converting foreign receipts
Selling rate (ask) Paying foreign suppliers
Average rate P&L translation for the period
Closing rate Balance sheet translation at period end
Historical rate Equity items, fixed assets in some GAAPs
Planning / budget rate Fixed for the year so budget variance isn’t FX noise
Customs rate Set by the customs authority, mandatory for import valuation

Customs/statutory rates are a real thing: many countries (India, Bangladesh, Brazil) publish an official rate that must be used for import duty calculation regardless of the market rate.

Rate storage: direct vs indirect quotation 

Direct quotation:   1 USD = 121.50 BDT   (units of local per 1 foreign)
Indirect quotation: 1 BDT = 0.00823 USD  (units of foreign per 1 local)

Both exist in the wild. Store which convention you used, or you will silently invert rates. Also store a ratio/factor for currencies with large magnitude differences:

from=IDR, to=USD, ratio_from=100000, ratio_to=1, rate=6.30

Rounding — where the money actually gets lost

Rules:

  1. Store amounts as exact decimals (NUMERIC/DECIMAL), never floats. Never. This is the #1 rule of financial code.
  2. Currency decimal places vary: JPY and KRW have 0, most have 2, BHD/KWD/OMR/TND/JOD have 3, and some crypto/pricing contexts use more.
  3. Round at the right level. Common convention: calculate at line level, round at line level, then sum. Summing unrounded and rounding at the end gives different totals from what’s printed on the lines — and customers reconcile line by line.
  4. Total rounding differences must post somewhere. Every ERP has a “rounding difference” GL account, because sum(rounded lines) ≠ rounded(sum) and the journal must still balance to zero.
  5. Price precision ≠ amount precision. A unit price may need 5 decimals (fuel, chemicals, electricity) while the amount has 2.
unit_price     NUMERIC(19,6),   -- more precision
amount         NUMERIC(19,4),   -- storage precision
-- display/print rounded to currency decimals

FX gains and losses

Realised

When a foreign-currency receivable is settled at a different rate than when it was booked:

1 Mar: Invoice EUR 10,000 @ 132 BDT   → AR 1,320,000
1 May: Customer pays EUR 10,000 @ 135 → Bank 1,350,000

Bank                 Dr 1,350,000
  AR                    Cr 1,320,000
  Realised FX gain      Cr    30,000

Unrealised (FX revaluation)

At each period end, all open foreign-currency items are revalued at the closing rate. The difference goes to unrealised FX gain/loss. Under IFRS this is required for monetary items.

Key subtlety: the revaluation is usually reversed on the first day of the next period, so it doesn’t interfere with the eventual realised calculation. If your system doesn’t reverse it, you double-count.

Translation (consolidation)

Translating a subsidiary’s whole balance sheet into group currency:

  • Assets/liabilities at closing rate
  • Income/expenses at average rate
  • Equity at historical rate
  • The plug goes to Cumulative Translation Adjustment (CTA) in equity, not to P&L

This is IAS 21 / ASC 830. Getting CTA right is a consolidation-engine problem; know it exists.

Hyperinflation

IAS 29: when cumulative 3-year inflation exceeds ~100%, financial statements must be restated using a general price index before translation. Affects entities in Argentina, Turkey, Venezuela, Zimbabwe, Lebanon and others at various times. Real ERPs have a specific hyperinflation accounting function; don’t try to hand-roll it.

Practical rules

  1. Never use floating point for money. Use NUMERIC/DECIMAL in the DB, BigDecimal/decimal.Decimal/integer-minor-units in code.
  2. Never store an amount without its currency code. A bare amount column is a bug.
  3. Store the rate used on the document, don’t re-look-it-up. Rate tables get corrected retroactively; historical documents must not change.
  4. Rate lookup is: (from, to, rate_type, valid_from ≤ date) ORDER BY valid_from DESC LIMIT 1 — rates are effective-dated, and you take the latest one on or before the date, not an exact match.
  5. A missing rate must be a hard error, never a silent 1.0 or 0. A silent default here can misstate an entire period.
  6. Rate tables need a daily automated load with a monitoring alert when it fails, because month-end close blocks on it.
  7. Test with JPY (0 decimals), KWD (3 decimals), and a high-magnitude currency like IDR or VND. Two-decimal assumptions are everywhere.

Common bugs

Bug Symptom
Inverted quotation Amounts off by rate²
Missing ratio/factor Amounts off by 1,000 or 100,000
Float arithmetic Journals off by 0.01, refuse to balance
Rounding sum vs sum of rounded Invoice total ≠ sum of printed lines
Rate looked up at read time Historical reports change every day
No rate-type distinction Balance sheet translated at average rate
Unrealised revaluation not reversed Double-counted FX in the next period
Assuming 2 decimals JPY amounts inflated 100×

Next