Bug 300503

Created attachment 71322 [details]
Example KMyMoney Book (Uncompressed)

A simple KMyMoney book based on the example discussed in the Details section. The reports in Favorite Report can be used to reproduce the issue.

Thank you for your detailed explanation. I agree, that KMyMoney has some room for improvement on the multi-currency side (which also covers the securities with varying prices). We already have a wishlist item at https://bugs.kde.org/show_bug.cgi?id=231192 on file which points to an interesting article.

Looking at your experience in the accounting field, can you confirm that the proposed solution at http://www.mathstat.dal.ca/~selinger/accounting/tutorial.html would solve the problem you describe?

Dear Thomas:

Thanks for your reply. First of all, I’d like to thank you and your team for making such a great program free to us. My time spent on providing detailed explanation will definitely be dwarfed by the efforts you spent on developing and maintaining the software.

I’ve read the article you provided and I’m sorry that the many of the claims in the article are incorrect. For example, in section 2.3 the author claims that “such gains and losses do not occur during transactions, but rather in the time intervals between transactions.” Actually the reazlied forex gain/loss does occur during transactions. Currency is just like any other kinds of assets (e.g. stock) that when you have it and you sell it, realized gain/loss occurs. Another example is in section 3.2 that the author says “Currency gains and losses are determined … by re-valuing accounts when a change of exchange rates occurs.” In practice, realized forex gain/loss occurs during transactions (as I just said) and unrealized forex gain/loss occurs through revaluation. And revaluation is done periodically at month-end and year-end closing using month-end and year-end revaluation exchange rate (typically a market exchange rate). 

I understand that it might be difficult for non-accounting practitioner to know the prevailing practices people use in real life (I agree with the author that textbook doesn’t provide enough detail). So in the rest of my comment, I’d like to spend more time to explain in detail of the proper accounting treatment for foreign exchange and securities transaction using KMyMoney terms and concepts. Also I’ll propose my solution for KMyMoney to make such improvement. 

Section 1 Definitions
To begin, I’d like to explain some terms:
Base currency: KMyMoney already has this concept (base currency is similar to the reference currency mentioned in section 3.2 of that article). The figures in base currency are what we concern most and many of the important reports (such as balance sheet and income statement) are supposed to be produced in base currency. In the rest of this comment, I’ll use USD as base currency for examples.
Transaction exchange rate/price: The exchange rate or equity price we use for making the transaction. If we make foreign exchange with bank, the exchange rate offered by the bank is transaction exchange rate. The same is true for buying/selling securities. KMyMoney already has the function to enter transaction exchange rate. For example a pop-up window will be shown when people enter a transaction with accounts in different currencies
Bookkeeping exchange rate: For foreign currency transaction, the exchange rate used to convert the transaction amount in transaction currency to an amount in base currency. Bookkeeping exchange rate typically is a prevailing market rate updated periodically (for example, daily).
Valuation exchange rate/price: The exchange rate or securities price used for period-end valuation to produce unrealized gain/loss. The valuation exchange rate can be the same as bookkeeping exchange rate to simplify updating two exchange rates.

Section 2 Assumptions
With these concepts, I will walk you through the accounting treatment of different situations. To begin, I will assume that all income/expense accounts are in base currency. The reason why income/expense accounts are in base currency is because once incomes/expenses are incurred, they will become a fact and shouldn’t change with exchange rate fluctuation. For asset/liability accounts, both amount in account currency and amount in base currency will be kept.  In additional, we only need to ensure the total debit amount in base currency equals to the total credit amount in base currency. The amount in account currency can be viewed as our currency inventory (see the metaphor in section 3.1 of that article) and the amount in base currency can be viewed as its monetary value. 
Finally I would like to assume that the cost basis is in moving average (of course FIFO and LIFO can also be used). In practice bookkeeper must determine cost basis once he/she decides to begin bookkeeping and once the cost basis is selected, it seldom changes because a lot of adjustment work must be done if cost basis is being changed. Below is the chart of accounts I’m going to use and I assume all accounts have zero opening balance:

Cash-USD (asset account in USD)
Cash-EUR (asset account in EUR)
Investment (asset account in EUR)
Realized forex gain (income account)
Realized forex loss (expense account)
Unrealized forex gain (income account)
Unrealized forex loss (expense account)
Realized capial gain (income account)
Realized capial loss (expense account)
Unrealized capial gain (income account)
Unrealized capial loss (expense account)
Job income (income account)
Food expense (expense account)

Throughout my examples, the accounting equations for amount in base currency will always hold.

Section 3 Transaction in Foreign Currency
Section 3.1 Realized Foreign Exchange Gain/Loss
In algorithmic language, realized foreign exchange gain/loss may occur as long as some side of the transaction is in a currency other than base currency. The realized foreign exchange gain/loss can be calculated from the difference between the transaction amount converted into base currency using transaction exchange rate and the amount in base currency using the cost basis of selection. In absent of transaction exchange rate, the bookkeeping exchange rate will be used by default. The realized foreign exchange shall be booked to respective gain or loss account.
Example 1: (bookkeeping rate EUR/USD = 1.2) Receive wage of USD100.00 (specified by the employment contract) paid in EUR80.00.
Account		Account currency	Amt in a/c currency		Amt in base currency
Dr. Cash-EUR		EUR			80.00				96.00 (80.00 * 1.2)
Cr. Job income		USD			100.00				100.00
Dr. Realized loss	USD			4.00				4.00 (100.00 - 96.00)
In this example, the transaction exchange rate is bookkeeping rate 1.2. The cost of you receiving EUR80.00 is USD100.00. The reason why we have realized currency exchange loss here is because the EUR I received doesn’t worth of what if I receive USD with the bookkeeping exchange rate.  However if the wage is announced in euro, there won’t be exchange gain/loss because there’s no conversion of EUR80.00 cash receipt into USD denominated income. At this point, we can see that the unit cost of EUR cash is 96.00/80.00 = 1.2.

Example 2. (bookkeeping rate EUR/USD = 1.15) Exchange EUR30.00 into USD33.00 with bank
Account		Account currency	Amt. in a/c currency		Amt in base currency
Dr. Cash-USD		USD			33.00				33.00
Cr. Cash-EUR		EUR			30.00				36.00 (30.00 * 1.2)
Dr. Realized loss	USD			3.00				3.00 (36.00 - 33.00)
Because of the difference between transaction exchange rate (33.00/30 = 1.1) and our unit cost of EUR (1.2), we have realized exchange gain/loss. Now we have EUR50.00 (inventory) at hand and it is value in base currency is 60.00 (96.00 - 36.00 = 60.00). The unit cost of our euro inventory, therefore, is 60.00 / 50.00 = 1.2.

Example 3. (bookkeeping rate EUR/USD = 1.25) Spend EUR10.00 for dinner
Account		Account currency	Amt. in a/c currency		Amt in base currency
Dr. Food		USD			12.50				12.50 (10.00 * 1.25)
Cr. Cash-EUR		EUR			10.00				12.00 (10.00 * 1.2)
Cr. Realized gain	USD			0.50				0.50 (12.50 – 12.00)
In this example, you consume the EUR cash inventory and have it be exchange into USD denominated expense.  By default we use bookkeeping rate as transaction rate. Because the unit cost of EUR cash is 1.2, we have exchange rate gain in this transaction. After this transaction, we have EUR40.00 at hand valued at USD48.00 in base currency. The unit cost of our euro inventory is 48.00 / 40.00 = 1.2.

I hope the above examples are comprehensive. From these examples, I guess you may be able to notice that basically
1. If you acquire forex asset (e.g. Cash-EUR), the amount in base currency is converted using bookkeeping rate if foreign exchange is involved. The difference between this amount and the amount in base currency of the opposite of the journal entry shall be booked to realized forex gain/loss.
2. If you dispose forex asset (e.g. spend Cash-EUR or transfer out), the amount in base currency is determined by the cost of acquiring the asset (using the cost basis selected) if foreign exchange is involved. The difference between this amount and the amount in base currency of the opposite of the journal entry shall be booked to realized forex gain/loss.
3. If you move forex asset between accounts of the same account currency, there’s no foreign exchange involved and the amount in base currency of the source account in source account unit cost will also be moved. 
For liabilities, the logic is the same. Suppose we reach the fiscal year end, let’s take a look at our balance sheet and income statement at the moment.

Balance Sheet (USD)
Asset = 81.00	(Cash-USD in base currency + Cash-EUR in base currency, which is 33.00 + 48.00)
Liability = 0.00
Equity (net worth) = 81.00

Income Statement (USD)
Income = 100.50
Expense = 19.50 (composed of food expense and realized exchange loss)
Net income = 81.00

As you can see, the balance sheet reconciles with income statement and all accounting equations hold. Noted that the above financial reports have foreign currency assets/liabilities valued at historical exchange rates. Using inventory metaphor, it means that USD48.00 is the historical cost of inventory EUR40.00) and that at month-end/year-end closing, we shall revaluate the inventory using the prevailing market price (the valuation rate), which will be discussed in the next section.
Section 3.2 Unrealized Foreign Exchange Gain/Loss
In algorithmic language, the unrealized foreign exchange gain/loss occurs when there’s difference between account balance in account currency converted into amount base currency using valuation exchange rate and account balance in base currency.
Suppose that we are closing the fiscal period 2012/03 and that our valuation rate is EUR/USD = 1.27. We only valuate those asset/liability accounts in foreign currency. The calculation is simple: EUR40.00 * 1.27 = 50.8 – 48.00 = 2.80, which is our unrealized exchange rate gain. Therefore we shall make the following journal entry:
Account		Account currency	Amt. in a/c currency		Amt in base currency
Dr. Cash-EUR		EUR			0.00				2.80
Cr. Unrealized gain	USD			2.80				2.80

Note that the debit side (Cash-EUR) has amount in account currency being zero. This is because valuation process doesn’t generate additional cash in euro. Using the inventory metaphor, our EUR inventory does not change; its value in base currency appreciates.
At this point, we are ready to finalize balance sheet and income statement as our foreign currency balances are mark-to-market.

Balance Sheet (USD)
Asset = 83.80	(Cash-USD in base currency + Cash-EUR in base currency, which is 33.00 + 50.80)
Liability = 0.00
Equity (net worth) = 83.80

Income Statement (USD)
Income = 100.50
Expense = 19.50 (composed of food expense and realized exchange loss)
Comprehensive income = 2.80 (composed of unrealized gain/loss)
Net income = 83.80

Month-end revaluation is part of the month-end closing process. It only needs to be done monthly. Because businesses review financial reports periodically, these reports are usually prepared monthly. After month-end closing, the accounting period will be closed and no transaction can be made into closed periods as the inserted transaction may impact the revaluation result. The industrial practice to change transaction in closed periods is to reverse revaluation first and then to perform revaluation again after the transaction change.
Finally, I would like to say that there’re two methods to do month-end foreign currency revaluation: one is to perform revaluation without reset (explained above) and another is to perform revaluation with reset. The later one actually is the more commonly used in practice especially by multinational corporations because it can reflect the historical cost of the foreign currency accounts. To perform revaluation with reset, we only need to add an additional journal entry on the first day of the following accounting period with debit and credit side of the month-end revaluation journal entry reversed. It means that the reset journal entry restores the account balance in base currency back to before the account is revaluated. The rationale is that not all foreign currency accounts have the same merit. Say we have a Cash-EUR and Checking-EUR both of which have EUR100.00. Their account balances in base currency are seldom the same because the euro in the two accounts is not acquired with the same exchange rate. By using the reset entry, we restore the account historical value to better reflect the merit of each account. 
Section 4 Securities Transaction
Section 4.1 Realized Capital Gain/Loss
Conceptually treatment of securities has no significant difference with that of foreign currency. Realized gain/loss occurs when securities asset or liability is being deposed (the exchange process of foreign exchange is also a kind of disposal). The inventory metaphor also holds.
In algorithmic language, realized capital gain/loss occurs when you have capital asset (e.g. long position of stock) and you sell it or when you have capital liabilities (e.g. short position of stock) and you buy it. The realized capital gain/loss is determined by the difference between the acquisition value of the capital in the cost basis selected and the disposal price (transaction price).
Note that there’s an additional complexity here. The investment account may be denominated in foreign currency if you have oversea investment account. In practice, enterprises usually keep the investment amount in account currency and in base currency in the book while the number of shares is kept in a subsidiary ledger. Therefore I will walk you through this more general case: investment account in EUR.

Example 1. (bookkeeping rate EUR/USD = 1.22) Buy 10 shares of HSBC @ EUR1.2 per share
Account		Account currency	Amt. in a/c currency		Amt in base currency
Dr. Investment		EUR			12.00				14.40 (12.00 * 1.2)
Cr. Cash-EUR		EUR			12.00				14.40 (12.00 * 1.2)
In this example, we have no investment initially and buying 10 shares of HSBC doesn’t generated realized capital gain/loss per my claim. Also there’s no foreign exchange gain/loss because you just transfer the money in EUR from cash to investment and the associated amount in base currency is also transferred.

Example 2. (bookkeeping rate EUR/USD = 1.24) Buy 20 shares of HSBC @ EUR1.05 per share
Account		Account currency	Amt. in a/c currency		Amt in base currency
Dr. Investment		EUR			21.00				25.20 (21.00 * 1.2)
Cr. Cash-EUR		EUR			21.00				25.20 (21.00 * 1.2)
In this example, additional shares are bought at lower price. After this transaction, the unit cost of HSBC is (12.00 + 21.00) / (10 + 20) = EUR1.1 per share.

Example 3. (bookkeeping rate EUR/USD = 1.3) Sell 15 shares of HSBC @ EUR1.3 per share.
Account		Account currency	Amt. in a/c currency		Amt in base currency
Dr. Cash-EUR		EUR			19.50				23.40 (19.50 * 1.2)
Cr. Investment		EUR			16.50				19.80 (16.50 * 1.2)
Cr. Realized capital gain	USD			3.90				3.90 ((19.50 – 16.50) * 1.3)
Dr. Realized forex loss	USD			0.30				0.30 (23.40 – 19.80 – 3.90)
In this example, we have realized capital gain of EUR3.00 (19.50 – 16.50) and we post it to realized capital gain using bookkeeping rate of 1.3, which means that we have realized capital gain of USD3.90. Also you may notice that we have a foreign exchange loss in this transaction because the capital gain is now worth more than when we disposed the investment. Finally, if brokerage charges are involved, they shall be booked to a separate expense account after realized capital gain/loss being calculated.
After these examples, let’s take a look at our balance sheet and income statement after these investment transactions:

Balance Sheet (USD)
Asset = 84.60	(add base currency balances of Cash-EUR: 31.80, Investment: 19.80, and Cash-USD: 33)
Liability = 0.00
Equity (net worth) = 84.60

Income Statement (USD)
Income = 104.40
Expense = 19.80
Net income = 84.60

Section 4.2 Unrealized Capital Gain/Loss
Simply put, unrealized capital gain/loss occurs at each period-end. It is determined by the difference between the market price and the cost of acquiring the securities asset/liability. The unrealized capital gain/loss in investment account currency will then be converted into amount in base currency using valuation exchange rate.
Note that there’s an additional step in valuating securities. Since securities may be denominated in foreign currency, after valuating unrealized capital gain/loss, the unrealized foreign exchange gain/loss must also be produced. 
Before doing the month-end valuation, let’s first take a look at our account balances:
Account		Account currency	Balance in a/c currency		Balance in base currency
Cash-USD		USD			33.00				33.00
Cash-EUR		EUR			26.50				31.80
Investment		EUR			16.50				19.80
In the investment subsidiary ledger, we know that we still have 15 shares of HSBC valued at EUR16.50 or USD19.80. To perform month-end revaluation, the first step is to revaluate securities. Suppose the HSBC’s market price is EUR1.25 per share and valuation exchange rate EUR/USD is still 1.27. We have a unrealized capital gain of 15 * 1.25 – 16.50 = EUR2.25 or 2.25 * 1.27 = USD2.8575. So we make the following journal entry:

Account		Account currency	Amt. in a/c currency		Amt in base currency
Dr. Investment		EUR			2.25				2.8575 (2.25 * 1.27)
Cr. Unreazlied capital gain	USD			2.8575			2.8575 (2.25 * 1.27)

After securities valuation, our account balances are as follows:

Account		Account currency	Balance in a/c currency		Balance in base currency
Cash-USD		USD			33.00				33.00
Cash-EUR		EUR			26.50				31.80
Investment		EUR			18.70				22.6575

Now we are ready to do the foreign currency revaluation. Using the valuation exchange rate, our EUR26.50 + EUR18.70 = EUR45.2 now is valued at EUR45.2 * 1.27 = USD57.404. Our inventory cost is USD31.80 + USD22.6575 = USD54.4575. Therefore we have foreign currency gain of 57.404 – 54.4575 = 2.9465. We shall make the following journal entry for this:

Account		Account currency	Amt. in a/c currency		Amt in base currency
Dr. Cash-USD		EUR			0.00				1.8550
Dr. Investment		EUR			0.00				1.0915
Cr. Unreazlied forex gain	USD			2.9465			2.9465

Like foreign currency valuation, it is suggested to make reset journal entry on the first day of the following month. This will restore the investment cost back to when the investment is not valuated. 

Section 4.3 Additional Comments on Currency Trading
Contrary to what the author of that article claims, I don’t think there’s any difference between currency trading and securities trading. Typically when you do currency trading, you will open an account denominated in one currency (usually in USD). The account is usually a margin account which enables you to trade far more amount than the money deposited in the account (the typical leverage level ranges from 50 to 300). Also you don’t need to have balance in a currency to trade it. It means that if you have USD10000.00 and nothing else in your account, you can still buy GBP by selling EUR. 

Conceptually I think you can visualize currency trading as if you are buying/selling securities whose quotation symbol is GBPEUR in general ledger. You can keep you GBP and EUR positions in a subsidiary ledger similar to securities. There’s just an additional complexity that when you trade currency pairs, you long one currency and short the other currency at the same time. For instance, if you buy GBPEUR, you will have GBP asset and EUR liability at the same time after the transaction. 

Section 5 Solution for KMyMoney
From my experience with KMyMoney, I think it already has sound architecture. For example, I noticed that currency and securities were treated the same in your program and I think it’s a clever idea. If you decide to enhance the multi-currency and securities function, you may consider making modifications for the following points:

1. Change fiscal year setting to a property of the book, not a configuration of the program. Currently once I configured fiscal year, all books will have the same settings. Suppose I have my personal book is in calendar year while a small business I owned is in fiscal year starting on February 10. I have to change the KMyMoney configuration each time I produce financial reports.
2. Add settings for realized gain/loss and unrealized gain/loss accounts for each asset/liability account. For each account, let user select which income/expense account to book to for realized forex gain/ loss and unrealized forex gain/loss. For investment accounts, also let user select which income/expense account to book to for realized/unrealized gain/loss.
3. Add function to make automatic realized gain/loss entries for foreign exchange and securities transaction. The realized capital gain/loss and foreign exchange gain/loss will be booked to the account set by user. It is better to allow user to change the realized gain/loss because there might be rounding difference between the one produced by KMyMoney and the one produced by bank/broker.
4. Add month-end investment and foreign currency valuation function. At the end of each month, allow user to run this function to generate unrealized gain/loss entries and their reset entries. In practice, this is part of the month-end closing process for enterprises (they will also do depreciation and accrual etc. during month-end closing). 
5. Add function for fiscal period control. After running the month-end valuation function, the fiscal period will be marked closed by KMyMoney and no transactions can be made to that fiscal period. If the user wants to add/change transaction(s) in closed fiscal period, all month-end valuations following that fiscal period must be reversed first to reopen the fiscal period. 

I hope my explanation provides enough hints for you to make enhancement for the next upgrade of KMyMoney. As many people and businesses hold investments and foreign currency, I believe that this enhancement is pretty urgent. If you have any question, feel free to contact me and I’m willing to spend time to make KMyMoney more robust in foreign currency and investment treatment. I don’t know how much time I can spend on helping your team (writing this comment alone has taken me a couple of hours and I hope it can be helpful). Anyway if you decide to make this enhancement, I would like to help verify the logic and test the program.

From what I understood this thread is about improving multiple currencies support just like this wish BUG 231192, so I'll mark this as a duplicate of the older report with a note that this report contains the more detailed description of the feature. I don't think that this fits in the scope of a bugfix.

*** This bug has been marked as a duplicate of bug 231192 ***