Multi-Currency Accounting in ERP: Rates, FX Gains, IDR Rounding

The first time a client asked me to add USD invoices to an IDR-based ERP, I estimated two weeks. The schema change took two days. The other three months of calendar time went into everything around it: which exchange rate applies on which date, what happens when a USD invoice is paid at a different rate, how month-end revaluation works, and why the AR aging report suddenly showed a customer owing minus 37 rupiah. Multi-currency is not a field on a form — it is an accounting subsystem.

This post is the design I wish I had on day one, shaped by building AP/AR for ANCoraPRO on NestJS and PostgreSQL: the dual-currency data model, the exchange rate pipeline, realized versus unrealized gains, and the rounding realities of a functional currency that has no decimal places in practice.

The Data Model: Every Line Carries Two Currencies

The non-negotiable rule from IAS 21, the accounting standard governing foreign exchange effects: an entity keeps its books in its functional currency — for an Indonesian company, almost always IDR — and foreign currency transactions are translated into it at the rate on the transaction date. Your schema must reflect that duality on every monetary line: the document amount in transaction currency, and the booked amount in functional currency, converted once at posting time and then frozen.

Frozen is the key word. The biggest multi-currency bug class I have seen in custom ERPs is storing only the foreign amount and converting on read, using whatever rate is current. Reports then change retroactively every time the rate table updates, and two prints of the same ledger disagree. Store the rate you used, store the converted amount, and never recompute either:

-- Every monetary line stores BOTH currencies, always
CREATE TABLE journal_line (
  id                  BIGSERIAL PRIMARY KEY,
  journal_entry_id    BIGINT NOT NULL REFERENCES journal_entry(id),
  account_id          BIGINT NOT NULL REFERENCES account(id),
  -- transaction (document) currency
  currency_code       CHAR(3) NOT NULL,        -- 'USD', 'SGD', ...
  amount_txn          NUMERIC(18,2) NOT NULL,  -- signed
  -- functional currency (IDR), converted at posting time
  exchange_rate       NUMERIC(18,6) NOT NULL,  -- rate USED, frozen
  amount_functional   NUMERIC(18,0) NOT NULL   -- IDR has no cents
);

-- The rate table keeps history; never overwrite a rate
CREATE TABLE exchange_rate (
  currency_code  CHAR(3) NOT NULL,
  rate_date      DATE NOT NULL,
  rate           NUMERIC(18,6) NOT NULL,  -- 1 unit -> IDR
  source         TEXT NOT NULL,           -- 'BI_JISDOR' | 'MANUAL'
  PRIMARY KEY (currency_code, rate_date)
);

Notice amount_functional is NUMERIC with zero decimal places. Modern IDR has no usable subunit — sen exist in law but not in payments — so booking functional amounts in whole rupiah from the start saves you from phantom fractional balances later. The transaction currency side keeps two decimals because USD, SGD, and EUR genuinely use them.

Exchange Rates: One Pipeline, Three Rate Types

The second design decision is which rate applies when. In Indonesian practice you will deal with up to three rate families, and your rate table needs to know which is which:

• Transaction rate — the rate on the document date, used to book invoices and bills. Most clients use Bank Indonesia's JISDOR reference rate for USD; it is published per business day and is the least arguable choice in an audit.
• Tax rate (kurs pajak) — the Ministry of Finance publishes a weekly rate that must be used for tax documents. Your invoice can legitimately carry one rate for accounting and another for the VAT calculation; design for both fields, do not assume they are equal.
• Closing rate — the rate on the balance sheet date, used to revalue open monetary balances at month-end per IAS 21. This one drives unrealized gains and losses.

Operationally I treat rates as append-only reference data with a source column: an automated daily fetch writes BI_JISDOR rows, finance can insert MANUAL overrides for currencies the feed misses, and nothing ever updates an existing row. If a rate was wrong, you insert a corrected rate and re-post affected documents deliberately — silent retroactive fixes are how books stop reconciling.

Realized vs Unrealized FX Gains: Two Different Events

Exchange differences come in exactly two flavors, and conflating them is the most common conceptual bug in homegrown multi-currency modules:

The settlement logic deserves a worked example, because the subledger invariant it protects is what keeps AR and AP honest:

Worked example — realized FX gain on a USD invoice

Day 1   Invoice issued      USD 10,000  @ 16,200  = IDR 162,000,000
            DR  Accounts Receivable   162,000,000
            CR  Revenue               162,000,000

Day 35  Payment received    USD 10,000  @ 16,450  = IDR 164,500,000
            DR  Bank                  164,500,000
            CR  Accounts Receivable   162,000,000
            CR  FX Gain (realized)      2,500,000

The AR subledger must close to EXACTLY zero in BOTH currencies:
USD 10,000 - 10,000 = 0   and   IDR 162,000,000 - 162,000,000 = 0
The 2,500,000 difference NEVER touches AR — it goes to FX gain.

That invariant — open items must close to zero in both the transaction currency and the functional currency — is the thing to test relentlessly. Every partial payment, every credit note, every write-off must preserve it. Odoo's accounting engine does the same thing with its automatic exchange difference journal: the difference posts to a dedicated FX account, never to the receivable itself.

Month-End Revaluation Without Tears

Unrealized FX is a batch job, and the cleanest way I have found to run it in a custom ERP is as a four-step, fully reversible procedure:

1. Select all open monetary items in foreign currency: unpaid AR and AP lines, foreign-currency bank accounts, advances and deposits.
2. Revalue each at the closing rate for the period end date and compute the delta against its current functional-currency book value.
3. Post one revaluation journal entry per currency, dated the last day of the period, against the unrealized FX gain and loss accounts.
4. Post the automatic reversal dated the first day of the next period. Settlements then compute realized differences against original booking rates, untouched by revaluation noise.

IDR Rounding Realities

Rounding sounds like a footnote until your trial balance is off by 1 rupiah and an auditor wants to know why. The rules I now build in from the start:

• Round at the line level, once, at posting time — half-up to whole rupiah for functional amounts. Summing rounded lines is stable; rounding sums is not reproducible from the parts.
• Force journal balance after rounding: if debits and credits differ by a few rupiah from per-line rounding, post the difference to a dedicated rounding account automatically. Never let the posting fail or, worse, fudge a real line.
• Keep rates at six decimals. JISDOR-style quotes carry more precision than two decimals, and truncating rates is a silent source of cumulative drift on high-volume accounts.
• Settle tiny residuals automatically: a payment that leaves an open balance under a configurable threshold, say 100 rupiah, should auto-write-off to the rounding account instead of haunting AR aging forever.

None of this is sophisticated computer science. It is just deciding the policy explicitly, encoding it in one place — the posting service — and refusing to let individual screens do their own arithmetic.

The Takeaway

Multi-currency accounting in an ERP stands on four legs: dual-currency storage with frozen rates, a disciplined append-only rate pipeline, strict separation of realized and unrealized differences, and explicit rounding policy enforced at the posting layer. Get those right and the rest — reports, dashboards, aging — falls out naturally, because the underlying numbers can be trusted.

And before writing any code, sit with the client's accountant for one hour and agree which rates apply to which documents. That conversation is cheaper than any refactor you will ever do.