Foreign Currency Transactions

An Example Transaction

Transactions between base currency account and base currency account are as trivial as transactions between foreign currency accounts of the same currency. The most complex process is how to overstep the border.

The best way is to get a look onto a voucher I've got by changing some money. I beg your pardon for the bad quality but is was meant to be for private use only. That was no business money.

This voucher tells us: I have changed € 30.00 into £ 21.82. The exchange rate was 1.3752 and there was a fee of £ 2.50. I'm coming from Germany and so let us assume that the base currency of my accounting system is Euro.

In order to run this accounting problem properly open petra provides an account named "Internal Transfer" and the account number is 9800.

Let us assume that we have two accounts

1. 6000 Petty Cash Euro
2. 6001 Petty Cash British Pound

Then we have to create a batch containing a journal in EUR and a journal in GBP. Then we have to account:

1. 6000 Petty Cash € - Credit: € 30.00
2. 9800 Internal Transfer € - Debit: € 30.00

This moves the € 30.00 to the Transfer-Account and then

1. 9800 Internal Transfer € - Credit: £ 21.82
2. 6001 Petty Cash £ - Debit: £ 21.82

moves the "pound" to the £ Petty Cash Account.

The internal currency exchange rate 1.3752 is the value for the other direction because I've changed the money in England. So the correct value is 0.727167. Actually the precision which is used for that value is 4 if USD, EUR or CHF are exchanged but I've found a 7 digit value for the exchange of USD in JPY (1.00 JPY[Japan Yen] = 0.0121800 USD).

The maximal precision defined by the openpetra data base is 10 digits and so I prefer to use this precision as default. I the future this value may be transcribeable by currency dependent values but not now.

Let us start only by the exchange itself where no exchange fees are accounted. This will result in the following data base entries.

In this cases it is important to store the value of a_transaction_amount_n in the precision defined by the currency. This is done automatically. In case of switzerland the smalles coin is CHF 0.05 and so you cannot get only a multiple of it. No internal roundings are necessary.

Requirement: Do not round transaction amount values in any way

The next step is to calculate the accounting value base on the exchange rate and in our case this is the value of EUR 30.0068622476. Using the correct mathematical rounding rules I should have to pay EUR 30.01 for the GBP 21.82.

Now GBP 21.82 is somewhere in a petty cash and EUR 30.01 is the value which is used by the accounting system to rate this money in base currency value. So the value of € 0.01 has to be accounted additionally. The best way is to interpret this as the very first revaluation.

So the complete accounting set in the EUR-Journal are the following transactions:

1. 6000 Petty Cash € - Credit: € 30.00
2. 9800 Internal Transfer € - Debit: € 30.00
3. 5003 Currency Revaluation - Credit: € 0.01
4. 9800 Internal Transfer € - Debit: € 0.01

and replying the GBP-Journal

1. 9800 Internal Transfer € - Credit: £ 21.82
2. 6001 Petty Cash £ - Debit: £ 21.82

Rounding the calculated base currency values

The next question is how to round the value of a_amount_in_base_currency_n. Referring to different literature in the web, the first preliminary answer is: Do not round base amount values in any way.

But this is wrong: Let us assume, that three persons have changed some money. For simplicity let us assume that they have got exact the same exchange conditions only on three different days. This will result in three identical batches - only different dates and different cost centres. The cost centre numbers are marked with c.

1. Edith - c9000 - EUR 30,-
2. Charly - c9001 - EUR 30,-
3. Mary - c9002 - EUR 30,-

If you have to produce a detailed report, then you have to produce the output ...

Person	Cost Centre	Foreign currency	Base currency	Exact Base currency
Edith	c9000	21.82	30.01	30.0068622476
Charlie	c9001	21.82	30.01	30.0068622476
Mary	c9002	21.82	30.01	30.0068622476
All	.	65.46	90.02	90.0205867428

This means: The correct base currency value of GBP 21.82 is EUR 30.01, but if this has been accounted three times, the value of the sum is not EUR 90.03 but EUR 90.02.

Furthermore: The problem arises after the second accounting process, not the third. So if you store precise data (e.g. 10 digits) you have the problem that an accidental need of a revaluation will get necessary after the accounting has been done.

The only way to leave this trap is to store the data in the precision which is defined by the base currency and this means 2 digits for USD, GBP and EUR and 0 digits for JPY.

In this case you have to calculate the balance sheets by adding the base currency and the foreign currency values independently of the each others and you must not run any transaction calculates using the sums. In our case the accounting value of GBP 65.46 based on one accounting process is valued by EUR 90.02 and Edith, Charlie and Mary (three accounting processes) may have EUR 30.01 each and the sum in a balance report is EUR 30.03.

Furthermore let us assume that Edith will change EUR 30 three times at three different days. This will result in GBP 3*21.82 and EUR 3*30.01. This problem easily can be resolved in the revaluation at the end of the actual period as shown below.

Getting the correct number of digits

Actually there is one efficient way to get the information about the maximal precision of a currency. The table a_leder provides the column a_country_code_c. The value in this column shall be used to create a .net CultureInfo class. Actually the values are two character codes for example "DE".

               try
               {
                   numberFormatInfo =
                       new System.Globalization.CultureInfo(
                           ledger.CountryCode, false).NumberFormat;
               }
               catch (System.NotSupportedException)
               {
                   numberFormatInfo =
                       new System.Globalization.CultureInfo(
                           String.Empty, false).NumberFormat;
               }

If the countryCode is "DE" then the NotSupportedException will be thrown. You really need a code in the syntax <languagecode2>-<country/regioncode2> where the two digits for the language code are based on ISO 639-1 and the regicode is based on ISO 3166. Then numberFormatInfo.NumberDecimalDigits holds the correct value.

TODO: We have to investigate if an automatic conversion of this values may be necessary.

The CHF 0.05 Problem

In Switzerland the smallest coin is CHF 0.05. This my yield to the idea that the base currency values shall be rounded up to a multiply of CHF 0.05 and not CHF 0.01. But the real money which is physical available is GBP 21.82. The EUR value or in case of Switzerland the CHF value is only a valuation of the money.

The Revaluation at the End of the Month

Revaluation Accounts

In Openpetra (or better in all types of accounting software) there exists the account types

1. Income
2. Expense
3. Asset
4. Equity and
5. Liability

Here accounts of the type Income and Expense will never be revaluated. This is money which was earned or spend and no one will change the value of a paid and accounted bill because an exchange rate has changed.

The only accounts for revaluation are Asset, Equity and Liability.

Asset is the case of petty cash and this is clear. Money in the currency of GBP change its value if the value shall be defined in EUR or USD. So Equity and Liability are left. Equity for example can hold a set of stocks based in GBP and so the value may change to from month to month. But in German you have to run a revaluation only after you have sold it. So depending of different laws there may exist an enhances ruleset for the revaluation. I've asked for in the the forum and as long as we get no answer and as long nobody cries this accounts will be revaluated in the same process.

So the revaluation shall be done on accounts of the types (of course only the foreign currency types):

1. Asset
2. Equity and
3. Liability

Revaluation Cost Centres

The revaluation is done for each cost centre individually.

The Revaluation Process

Let us begin with Edith and let us assume that she has changed three times on three different das the amount of EUR 30. So she has a petty cash of GBP 65.46.

If you will transfer this value directly to "9800 Internal Transfer" and if you run a currency exchange then you will miss EUR 0.01. Much better is to create three transfers

1. 6001 Petty Cash £ - Credit: £ 21.82 (EUR 30.01)
2. 9800 Internal Transfer € - Debit: £ 21.82 (EUR 30.01)
3. 6001 Petty Cash £ - Credit: £ 21.82 (EUR 30.01)
4. 9800 Internal Transfer € - Debit: £ 21.82 (EUR 30.01)
5. 6001 Petty Cash £ - Credit: £ 21.82 (EUR 30.01)
6. 9800 Internal Transfer € - Debit: £ 21.82 (EUR 30.01)

That means: Each account in the month shall be balanced by a transfer account in the revaluation. There is no need for any currecy recalculation. The result in "Internal transfer" is the sum of both colums: GBP 65.46 and EUR 90.03. Now the sum - EUR 90.03 - will be revaluated. Let us take the same exchange rate 0.727167 and the result is EUR 90.02.

Then we have to account

1. 6001 Petty Cash £ - Debit: £ 65.46 (EUR 90.02)
2. 9800 Internal Transfer € - Credit: £ 65.46 (EUR 90.02)
3. 5003 Currency Revaluation - Debit: € 0.01
4. 9800 Internal Transfer € - Credit: € 0.01

So this type of revaluation rejects the "rounding errors of the month" and sums up all activities. It is one of the very last accouting sets of a month and the very first of the next month. So the time span for the accountings which have to be revaluated is allways the span beginning with the last revaluation itself (the sum only) and all accontings from this time up to the date of the actual revaluation.

Requirement: In every month a revaluation shall be done even if no currency exchange rate is changed.