Implementing Multi-Currency Support in a Custom ERP: Exchange Rates, Revaluation, and Reporting

For Indonesian businesses that trade internationally — importing raw materials, exporting products, or billing clients in USD or SGD — multi-currency handling in an ERP is not optional. Manufacturers implementing multi-currency ERP capabilities typically see a 60-80% reduction in financial close time through automated consolidation, according to Bizowie. In practice, I've seen Indonesian trading companies spend 3-4 days per month manually converting foreign currency transactions into Rupiah for their books. At Commsult, I built a multi-currency module that handles the full cycle: transaction entry in any currency, automatic IDR equivalent calculation, period-end revaluation, and consolidated Rupiah reporting.

The Currency Architecture

Our system has a single functional currency (IDR — Indonesian Rupiah) as the base. All financial reports are in IDR. Foreign currency transactions are stored with both the original foreign currency amount and the IDR equivalent at the transaction date. This dual-amount approach is standard in accounting systems and simplifies reporting: the IDR amounts flow directly into the journal and require no conversion at report time.

Exchange Rate Storage and Updates

We maintain an exchange_rates table with columns: currency_pair (e.g., 'USD/IDR'), rate_date, buy_rate, sell_rate, and mid_rate. Rates are fetched daily from Bank Indonesia's API (the official source for Indonesian businesses) via a NestJS cron job at 8 AM WIB. The business can also enter custom rates manually for specific transactions when they use a rate negotiated with their bank — common for large USD transactions. All historical rates are retained (never overwritten) so we can reconstruct the exact IDR amount for any past transaction.

Multi-Currency ERP Architecture (IDR as Functional Currency)

  Exchange Rate Source
  ├── Bank Indonesia API (daily, 08:00 WIB)   ← official source
  │   └── exchange_rates table (historical, never overwritten)
  └── Manual override (for negotiated bank rates)

  Foreign Currency Transaction Flow:
  ┌─────────────────────────────────────────────────────────┐
  │  AP Invoice: USD 10,000                                 │
  │                                                         │
  │  ERP looks up USD/IDR rate: 15,850 (from BI API)       │
  │                                                         │
  │  Journal Entry posted:                                  │
  │  DEBIT  Expense Account     IDR 158,500,000            │
  │  CREDIT AP Payable — USD    USD 10,000 / IDR 158,500K  │
  └──────────────────────────┬──────────────────────────────┘
                             │  Payment date: rate 15,920
                             ▼
  ┌─────────────────────────────────────────────────────────┐
  │  Payment: USD 10,000 × 15,920 = IDR 159,200,000        │
  │                                                         │
  │  DEBIT  AP Payable — USD    IDR 158,500,000             │
  │  DEBIT  Forex Loss Account  IDR     700,000   ← diff   │
  │  CREDIT Bank — USD          IDR 159,200,000             │
  └─────────────────────────────────────────────────────────┘

  Month-end Revaluation:
  Open FC balances × closing rate − original IDR = unrealized P/L
  Posted as reversing journal (reverses on day 1 of next period)

Handling Foreign Currency Transactions

When an AP invoice arrives in USD, the entry screen shows USD amount, and the system looks up the current USD/IDR rate to compute the IDR equivalent. The journal entry records: debit Expense (IDR equivalent), credit AP Payable — USD (original USD amount + IDR equivalent). When the payment is made, if the IDR/USD rate has changed, the payment generates a foreign exchange gain or loss entry for the difference. This realized gain/loss is posted to a dedicated Forex Gain/Forex Loss account and appears on the P&L.

Period-End Revaluation

At month-end, open foreign currency balances (unpaid invoices, bank accounts in foreign currency) must be revalued at the closing rate. The revaluation job reads all open FC balances, computes the difference between the original IDR equivalent and the current IDR equivalent at the closing rate, and posts unrealized gain/loss journal entries. These entries reverse at the start of the next period (they're marked with a reverse_on_date). This is a standard accounting practice per PSAK 10 and IAS 21, but it's completely manual without ERP support — I've seen companies skip it and produce materially inaccurate balance sheets as a result.

// NestJS: Bank Indonesia exchange rate fetcher
@Cron('0 8 * * 1-5', { timeZone: 'Asia/Jakarta' })
async fetchBankIndonesiaRates() {
  const pairs = ['USD/IDR', 'EUR/IDR', 'SGD/IDR', 'CNY/IDR'];
  const today = new Date().toISOString().split('T')[0];

  const res = await fetch(
    `https://www.bi.go.id/biwebservice/wskursbi.asmx/...`
  );
  // Parse BI XML response and insert rates
  for (const { pair, buy, sell, mid } of parsedRates) {
    await this.exchangeRateRepo.upsert({
      currencyPair: pair,
      rateDate:    today,
      buyRate:     buy,
      sellRate:    sell,
      midRate:     mid,
      source:      'bank_indonesia',
    }, ['currencyPair', 'rateDate']);
  }
  this.logger.log(`Updated ${pairs.length} exchange rates from BI`);
}

// PostgreSQL: transaction schema with dual amounts
ALTER TABLE journal_lines ADD COLUMN foreign_currency  VARCHAR(3);
ALTER TABLE journal_lines ADD COLUMN foreign_amount    NUMERIC(15,2);
ALTER TABLE journal_lines ADD COLUMN exchange_rate     NUMERIC(12,6);

-- Computed IDR amount check (6 decimal precision)
ALTER TABLE journal_lines ADD CONSTRAINT chk_idr_consistency
  CHECK (
    foreign_amount IS NULL
    OR ABS((debit - credit) - (foreign_amount * exchange_rate)) < 1
  );

How We Built This at Commsult

The multi-currency implementation adds three fields to every financial transaction: foreign_currency, foreign_amount, and exchange_rate. The IDR amount is always computed as foreign_amount × exchange_rate. A PostgreSQL function validates that the IDR amount on the journal line matches the stored computation, catching any rounding issues. We use 6 decimal places for exchange rates (IDR rates for USD are in the range 15,000–16,000 so we need precision for small USD amounts).

Multi-Currency Bank Reconciliation

Bank reconciliation is more complex with multiple currencies: you're reconciling a USD bank account statement against USD transactions in the ERP, then also reconciling the IDR equivalents. We provide a reconciliation screen per bank account per currency, showing outstanding transactions in the foreign currency sorted by amount and date. The reconciliation marks which bank statement entries match which ERP transactions. Unmatched items flag as reconciliation exceptions for the finance team to investigate.

Management Reporting in IDR

All management reports are presented in IDR, with an optional secondary column showing the original foreign currency amounts for FC-heavy businesses. The P&L shows total revenue and costs in IDR, with a Forex line for net realized and unrealized gains/losses. This is important for management — a good operating month can show a poor bottom line if the Rupiah depreciated sharply against USD liabilities. Surfacing the Forex component separately helps management understand the true operating performance versus currency impact.