Exchange differences from bank statements: the calculation offices still do in Excel

One Invoice, Three Rates

Take a client who imports from Germany. A supplier invoice for 10 000 EUR is dated 2 September. On 25 September the client pays it from their PLN account, and the bank converts the money.

Here's what the accountant has to establish for that one payment. I'll use round illustrative rates, not real ones from those days:

• Cost: average NBP rate from the last working day before the cost date, say 4.2700. The cost is 42 700 zł.
• Payment: the client paid from a PLN account, so the rate is the one the bank actually applied, its selling rate, say 4.2950. The client paid 42 950 zł.
• Difference: 10 000 × (4.2950 − 4.2700) = 250 zł of negative exchange difference, a tax-deductible cost in September.

Now change one thing: the client pays from their EUR account, using euros received from a customer in August, when the receipt was valued at 4.2500. The same invoice now gives 200 zł of positive difference, taxable income. Same invoice, same day, a 450 zł swing in the result. The only thing that changed was which account the money left from.

That's the whole problem. The arithmetic is easy. What's hard is knowing which rate belongs on each side, where it came from, and having that for every foreign-currency payment of every client.

Where the Rules Sit

Briefly, because the detail is in the statutes:

• Income tax, booking: revenue and costs in foreign currency are converted at the average NBP rate from the last working day before the day the revenue arises or the cost is incurred.
• Income tax, payment: art. 15a CIT and art. 24c PIT calculate exchange differences from the rate actually applied on the day of receipt or payment. Only when the actual rate can't be determined does the average NBP rate from the last working day before that day apply. The tax office can question an actual rate that differs from the NBP average by more than 5%.
• VAT: art. 31a of the VAT Act converts the tax base at the average NBP rate from the last working day before the tax point (or the ECB rate, if the taxpayer chooses). VAT doesn't produce exchange differences. The VAT amount in PLN is fixed when the invoice is booked.
• Year end: balance-sheet valuation under art. 30 of the Accounting Act produces unrealised differences. Under the default tax method they stay out of the tax base. A client can elect to follow the accounting rules for tax instead (art. 9b CIT, art. 14b PIT), and that choice binds them for at least three years.

The last point matters more than it looks. If you don't know which method a client chose, every number below is a guess.

Where Offices Get It Wrong

I've looked at enough month-end spreadsheets to know the same five mistakes keep coming back:

1. NBP rate on a converted payment. The client paid from a PLN account, the bank converted at its own rate, and the spreadsheet uses the NBP average for the payment date anyway. Every line is off by the bank's spread. Pennies on one invoice; real money over a year.
2. The currency account without a queue. Euros come in at one rate and go out to pay suppliers later. Without a FIFO queue (or whatever method the client agreed with their advisor) kept across months, nobody can say at what rate a given payment's euros entered the account. Usually someone rebuilds it in December.
3. Partial payments. An invoice paid in three instalments produces three exchange differences, each on the paid portion. Spreadsheets often handle the first instalment and then lose track.
4. Advances. Money paid before the invoice is issued changes which rate applies to which part of the invoice. This needs a person, not a formula.
5. Netting. A compensation note between a euro receivable and a euro payable is a settlement too, and it produces differences on both sides. It doesn't show up on the bank statement at all, so a statement-driven process misses it.

None of these is exotic. A client with three EU suppliers and a couple of foreign customers hits all five within a quarter.

The Volume Problem

This is the arithmetic that makes it a process problem, not a knowledge one. A typical import-heavy client in a 40-client portfolio has around 40 foreign-currency payments a month. For each one, the accountant looks up the invoice, finds the rate it was booked at, finds the payment on the statement, works out the payment rate and its source, calculates and books.

At four or five minutes per line, that's three hours a month for one client. Most of it is lookup, not judgement. And it all lands in the same week as the close. I've described that crunch in month-end close in an accounting office: the exchange differences have to be in before the income tax advance on the 20th.

What the Data Already Has

Most of what the calculation needs already exists in machine-readable form:

• Invoices: since KSeF, purchase and sales invoices arrive as structured XML with currency, amounts and dates. A foreign-currency invoice also states the VAT in PLN.
• NBP rates: the NBP publishes table A through a free public API, by currency and date. There's no reason for anyone to copy rates out of a browser.
• Payments: MT940 statements from both the PLN and the currency accounts. Most banks put the conversion rate in the transaction description for converted payments. Some don't, and those lines need a flag rather than a guess. I covered statement import in enova365 bank statements and MT940.
• Matching: linking a payment to an invoice is the same step as in ordinary bank reconciliation, the one I wrote about in automating bank statement booking. Once a payment is matched, the exchange difference is a calculation on two known rows.

What an Agent Prepares

This is the monthly output I'd want on the accountant's desk around the 10th, for each client:

• every foreign-currency settlement in the month, matched to its invoice: amount settled, invoice rate, payment rate;
• the source of each payment rate: bank rate from the statement, FIFO layer from the currency account, or NBP average because nothing better was available;
• the calculated difference per line and the monthly totals, positive and negative;
• the FIFO queue for each currency account, carried over from last month and closed for this one;
• flags: a converted payment with no rate in the statement, partial payments, advances, payments with no matching invoice, rates more than 5% off the NBP average.

The accountant goes through the flags and books. In a normal month that's around 15 minutes per client instead of three hours. The agent doesn't pick the method for a currency account, doesn't decide how an advance is allocated, and posts nothing. I explained why I keep that line in AI agents and liability.

Where to Start

Before automating anything, answer one question for each client with a currency account: what method do we use for payments out of it, and where is that written down? If the answer is "whatever Kasia did last year", that's the first fix, and it costs nothing.

If you'd like to see how an agent builds the monthly exchange-difference list from KSeF and your clients' statements, write to me. I'll go through it on one real client and tell you honestly whether it's worth automating at your volume.