You ran the profit and loss for March in April, and it said one thing. You ran the same report for the same month in September, and it said another. Nobody edited an invoice. Nobody touched an expense. The number moved anyway.
This is not a bug in your arithmetic. It is a decision somebody made about when to convert currency, and almost every small retail system makes it the same wrong way.
The mistake
Say you keep your books in dollars and you buy from a European supplier. In March you agreed a purchase of €4,462. The euro was at 1.08, so that purchase cost you $4,819. You sold the goods, you made your margin, you reported your March profit.
By September the euro is at 1.14.
Now: what did that March purchase cost?
The honest answer is $4,819. That is what left your account. But if your system stores only the euro figure and converts it whenever you ask, it will tell you $5,087 — because it is applying today’s rate to a document from six months ago. Your March cost of goods just went up by $268, and your March profit went down, and nothing about March changed.
Run the report again in December and you will get a third answer.
Why it happens
It happens because storing one number is easier than storing three.
A system that keeps only amount and currency has to convert on the way out. It has
no memory of what the rate was, so it uses the only rate it has: the current one. Every
report becomes a snapshot of today’s rate applied to history’s transactions, which
is a combination that describes nothing that ever happened.
It is a seductive shortcut because it looks right at first. On the day you enter the document, today’s rate is the right rate. The error only appears later, quietly, after the rate has moved and after you have already told somebody what your margin was.
What the fix actually is
Every record that holds money needs to store three things, not one:
- the amount in the currency it was agreed in
- the rate on the day it was agreed
- the base value — the two multiplied together, frozen
That third field is the one that matters. Once it exists, reports never convert anything. They sum a column. The March purchase contributes $4,819 to March’s cost of goods in April, in September, in December, and in five years.
Changing today’s rate then does exactly what a shopkeeper expects: it changes what new documents are worth, and nothing else.
It sounds almost too simple, and the implementation is simple. What is not simple is the discipline: every money-bearing table needs the three fields, and every path that writes money has to go through one place that stamps them. Invoices, payments, credit notes, refunds, purchases, bills, supplier payments, expenses, bank transactions. Miss one and you have a table that still drifts, and you will find it eighteen months later when two reports disagree.
The details that catch people out
A payment settles at the invoice’s rate, not today’s. If you invoice a customer €1,000 in March and they pay in June, that payment is worth what the invoice said it was worth. The customer agreed a price, not a currency exposure. Settle it at June’s rate and the invoice will never quite close — it will sit there owing $40 that nobody can explain.
A credit note inherits the invoice’s rate too. Return goods from a March invoice in July and the credit has to be worth exactly what was charged, or a full return leaves a residue behind. The same applies to the refund the credit note generates.
Editing a document must not re-stamp it. This is the one people get wrong even after they have added the three fields. Somebody corrects a typo in the notes field of an old invoice, the system saves the record, and a naive implementation re-reads the current rate on save. The document has now silently re-priced itself because someone fixed a spelling mistake. Only an actual change to the money — the amount, or a deliberate change of currency — should ever recalculate.
Cost of goods needs the same treatment. It is not enough to freeze the sale. Each invoice line has to record what that unit cost at the moment it was sold, because the product’s cost price will change with the next delivery. Otherwise your historical margins move every time you restock.
How to check your own system
You do not need to read anyone’s code. Three tests, ten minutes:
- Run a profit and loss for a month at least six months back. Write the net profit down.
- Go into settings and change your secondary currency’s rate by ten per cent. Save it.
- Run the same report again.
If the number moved, your history is not stored — it is being recalculated. Change the rate back and consider what else that means: every figure you have ever reported was only true on the day you ran it.
A fourth test, for the subtle version: open an old foreign-currency invoice, change something harmless like the notes, save it, and check whether the total in your base currency is still the same.
What it is worth
This is not an accounting nicety. In a market where the local currency moves against the dollar several times a year, a system that recalculates history means you cannot answer basic questions: did we do better this March than last March? Is this supplier cheaper than that one? Was the December promotion worth running?
You can have opinions about those things, but you cannot have figures, because the figures change under you.
A saved document that keeps its own rate is not a feature anyone puts on a banner. It is the difference between a system that records what happened and one that keeps producing new versions of it.