DESKTOP EDITION CLOUD EDITION SERVER EDITION GUIDES FORUM

Multi-currency; rounding issue


#1

On the profit and loss report a 0.01 profit shows due to exchange rates. When selecting this value and looking at the detailed report the profit/loss is actually zero (because I’ve entered the right exchange rate using settings). It still shows up in the profit/loss report as being 0.01…

Some screenshots to support:



#2

Obviously this would be a bug. Is this happening in the latest version though? What version are you currently using?


#3

One of the recent versions; Manager 16.3.8.

Just updated to the most recent one for a different issue, but this rounding issue is still present.


#4

OK, this is one of those bugs I’d need to see an accounting file showing this issue.

If you can send me your accounting file to lubos@manager.io, I will look into it.


#5

Hi @lubos ubos,

I’ve send you the file in 2016, you replied by email the following:

I was able to narrow down the issue but the problem is quite deep. I will need to re-write some portion of multi-currency.
Discrepancy is due to rounding between currencies. For example, when you have a transaction in foreign currency which has one debit and multiple credits (e.g. sales invoice), every debit and every credit is converted, then rounded to base currency individually. This can cause debits to not equal credits in base currency. The way I deal with this right now is to simply put this rounding discrepancy to currency gains/losses.

Any update on fixing it?


#6

I’ll put it under ideas. Completely forgot about this still being an issue.


#7

Thanks, To me, but I can imagine also to others, its an issue as it results in fictional currency gains/losses that cannot be account for, as the amount on e.g. an invoice and the bank statement actually do match in terms of currency conversion.


#8

Any progress on this, or perhaps anyone able to suggest to correct for these fictional gains/losses in my cases as I am not sure how to set this account to zero without offsetting another account…


#9

OK, I’m still stuck with this. I can understand this is not easily solved but it does result now in discrepancies that are unreal and I have no idea how to solve this, as these fictional gains/losses appear without actually existing and I am supposed to finish the books of 2018 soon…

Maybe @Tut can suggest a way to solve this in an accounting approved way?


#10

Sorry, @patrickdewit, I cannot.


#11

Rounding errors are real and must be explicitly accounted for, such as with a visible offsetting account. As they are real for all users supporting it at the program level would be nice but not essential.

Apparently an early white collar computer crime involved a bank programmer putting the rounding errors in a private account. Resulted in the theft of millions of dollars and was undetectable until he owned up.


#12

In this case they are, at least in my opinion, unreal. It would be better if I could use a journal entry to fix this by putting them on the proper account (e.g. the account where they originate from) instead of them showing separately on a P&L statement.

I mean; I work in EUR:

I book 100 USD on an expenses account ‘A’, I pay 120 EUR from my bank account. Due to currency rounding this might show up as 121 EUR Expenses and 1 EUR Currency Profit. I should be able to balance this manually to show a more real representation of what has happened?


#13

@patrickdewit can you check the latest version? I think this issue has been fixed in version 19.2.22.


#14

It seems to be solved, thanks!


#15

@lubos

It does not seem to be completely resolved, see for example this one:

Possible the result from internal rounding, but in this case the following happened:

I work in EUR, with an EUR bank account. I have an invoice in USD. We pay the invoice and a certain amount in EUR is deducted from the bank account. In my case, no forex gains/losses are supposed to appear if I set the date of the invoice and payment to the same date. Also, by using the number deducted from the bank account and the invoice I manually compute the exchange rate, which I add under settings under the same date.

Maybe I am making an error in my way of thinking, but this method should not result in forex gains/losses, right?


#16

I’ve tried to reproduce this in my test file.

I don’t understand how your report shows 332.50 EUR. It should be 332.49 EUR.

Because: 370 / 1.1128 = 332.49460819554277498202731847592 ~= 332.49

My test file shows 332.49 EUR

Now, as you can see, I still have foreign exchange gain of 0.01 EUR.

The reason for this is that exchange rate for payment is not really 1.1128 (even though the report implies it).

It’s actually: 370 / 332.48 ~= 1.11284. This is because on payment transaction, you are not entering exchange rate. You are simply entering amount deducted from your bank account in EUR (332.48) and then how much that should be in USD (370.00) and out of these two amounts, Manager works out the exchange rate.

The report presentation needs to improve to reflect that. It currently rounds payment exchange rate to 4 decimal places because that’s how many decimal places have you used when setting up exchange rates under Settings tab. This makes it look like both transactions are using exactly the same exchange rate but that’s not the case. The first one is using exchange rate of 1.1128 and the second one is using 1.11284.

Anyway, before I get to making this right, I’m more concerned about how your report is showing 332.50 EUR when it should show 332.49 EUR.


#17

I also do not know why it shows like this. I made a blank accounting file that contains only this transaction. I must say, the 370 is made up out of various items.

All is set up in similar fashion as in my real accounting file, but now it shows:

Minimal working example:

test (2019-03-12).manager (28 KB)

Real accounting file

I’ve uploaded the minimal working example accounting file. I would be ok with sharing the actual accounting file but not publically on the forum (e.g .by email would be ok)

I am currently working with version 19.2.23 on windows.

edit
OK, this is kinda weird, i’ve just updated to the latest version 19.2.42 and now it does show this:

Minimal working example

Real accounting file (v.19.2.42)


#18

OK, I can see what’s happening now.

When you enter purchase invoice in USD, it will be converted to base currency.

Let’s say you receive purchase invoice for 10 USD. Assuming exchange rate is 1.1128, the base currency equivalent will be 8.99 EUR.

The issue is when invoice has multiple line items, each line item needs to be converted to base currency on its own first and the overall transaction must balance in base currency too (debits must equal credits).

Let’s say you have received invoice for 10 USD which is equally split between two expense accounts.

Therefore 5 USD will be converted to 4.49 EUR and since there are two line items of 5 USD, the total of base currencies for all line items will be 4.49 EUR * 2 = 8.98 EUR. So expense accounts will be debited 8.98 EUR but we can’t credit supplier account 8.99 EUR. We need to credit supplier account 8.98 EUR so the books are always balanced.

So even without changing the overall total for purchase invoice (10 USD), due to rounding, you can end up with different total in base currency just by having purchase invoice split to 2 or more lines.

The confusing part is that the report doesn’t show the exact exchange rate used. Even if you set 1.1128 under Settings, it might not be what you will see on the report. This is because 1.1128 is used for each line item but due to rounding, overall exchange rate for entire invoice could be higher or lower.

The latest version (19.2.48) shows the actual exchange rate used on the report which should avoid at least some confusion in future.


#19

Hi Lubos,

I understand that this is the result of rounding, which is inevitable. The adjustment to show the real exchange rate used is a good modification. Thanks.

Perhaps one suggestion is to not call these ‘Forex gains/losses’ but call these ‘Rounding gains/losses’. Or to have the ability to put these rounding gains/losses on a different account using the journal. At least that would generate insight in a P/L statement, as I now have to ‘explain’ why we have foreign exchange gains/losses without actually dealing with those.