Almost every multi-currency problem we are asked to fix has the same cause: the system stored one amount per transaction and converted on the way to a report. That works until a rate moves, and then last month’s report quietly disagrees with the invoice you sent.
Decide the functional currency once, and write it down
One currency is the one your accounts are presented in. Everything else is a transaction currency that gets translated into it. This is a decision with accounting consequences, so make it with your accountant, record it, and stop revisiting it — a business that changes its mind here every year has no comparable numbers at all.
Store three things on every line, not one
journal_lines
currency text not null -- 'USD' | 'ZWG'
amount_txn_cents bigint not null -- as transacted. integer. never a float
rate_used numeric(18,8) not null -- transaction currency -> base
rate_date date not null
rate_source text not null -- who published it, so you can defend it
amount_base_cents bigint not null -- computed once, then immutable- Money is integer minor units. A float will lose you cents and there is no version of that conversation you enjoy.
- `amount_base_cents` is written once. Recomputing it from today’s rate is what makes historical reports move.
- The rate is a fact about the transaction, so it lives on the transaction. Not in a settings table you overwrite each morning.
- Record the source of the rate. When someone asks why a figure is what it is, "the rate we used on the 14th, from this source" is an answer. "The system rate" is not.
Where the rate comes from, and who may change it
- One published source, named in your accounting policy, loaded daily.
- A rate for a date, never a single current rate. Back-dated entries need the rate for their own date.
- Manual override allowed, restricted to named roles, and logged with a reason. Somebody will need it; nobody should be able to do it quietly.
- A rate table that is append-only. Correcting yesterday by editing the row is how a reconciliation becomes impossible.
Invoice in one currency, paid in another
- 01Raise the invoice in the currency you agreedThe customer’s obligation is that amount in that currency. Store the base equivalent at the invoice date rate for reporting, and leave the invoice alone from then on.
- 02Receipt the payment at the payment date rateConvert what actually arrived, on the day it arrived, at that day’s rate.
- 03Post the difference as an exchange differenceThe gap between the base value of the invoice and the base value of the receipt is a realised exchange gain or loss. It goes to its own account. It is not extra revenue, it is not a discount, and it never adjusts the original sale.
- 04Close the invoice on the transaction currency amountThe invoice is settled when the agreed currency amount has been paid, not when the base amounts happen to agree.
What gets revalued at period end
| Balance | Revalued at period end? | Where the difference goes |
|---|---|---|
| Bank and wallet balances in a foreign currency | Yes | Unrealised exchange difference |
| Receivables and payables in a foreign currency | Yes | Unrealised exchange difference |
| Inventory bought in a foreign currency | No | Held at the base cost recorded when you bought it |
| Fixed assets | No | Held at the base cost on acquisition |
| Revenue and expenses already posted | No | Translated once, at the transaction date rate |
Reporting that people can trust
- Every total is labelled with its currency. An unlabelled total is a support ticket waiting to be raised.
- Revenue reports available by transaction currency and in base currency, and the two are reconcilable.
- The rate used shown on any report that translated something. Hiding it does not make the question go away.
- Comparatives translated the same way every period, or your trend line is measuring the rate rather than the business.
Pricing and rounding
- Keep a price list per currency rather than converting a base price at display time. Converted prices produce numbers no salesperson wants to read out.
- Round once, at the line, then sum. Summing unrounded values and rounding the total makes your document disagree with its own lines.
- Decide the rounding rule for each currency in advance and apply it in one place in the code.
Get the three columns and the append-only rate table in place and multi-currency stops being a monthly argument. Skip them and no amount of reporting work afterwards will make the numbers agree — the information needed to make them agree was never stored. Fiscalisation has its own currency requirements: see what fiscalisation requires of your system.