I’m an active user of KMyMoney and have been using KMyMoney on daily basis for almost two years. I think that I’ve found a serious logic flaw in the Income/Expense Report that Net Worth Report did not reconcile with the Income/Expense Report. Based on my observation, it seems that the reason might because of the Income/Expense Report does not consider the fair value adjustment of the balance sheet accounts. Below is a simple scenario that the current Income/Expense Report fails to reconcile with Net Worth Report: ----------Sample Start---------- Suppose that we have only two asset accounts in our book (base currency is USD): Cash-USD, open date 01/01/2011, currency USD, opening balance 1000.00 Cash-EUR, open date 01/01/2011, currency EUR, opening balance 0.00 And there’s neither liability account nor income/expense account. Then we make one entry for foreign exchange from USD to EUR with bank: 05/01/2011, exchange from USD130.00 to EUR100.00 On 12/31/2011, we update the exchange rate EUR/USD to be 1.50 and the Net Worth Report to be viewed for the calendar year 2011 shall be like this: Cash-USD USD870.00 Cash-EUR USD100.00 * 1.50 = EUR150.00 Balance USD870.00 + USD150.00 = USD1020.00 ----------Sample End---------- In the above example, we have opening balance of USD1000.00 and ending balance of USD1020.00 in calendar year 2011. It is supposed that we shall have income of USD20.00 for the year because of the difference between the opening balance and ending balance. Such income of USD20.00 comes from the unrealized exchange rate gain because we enjoyed lower exchange rate for the foreign exchange transaction made on May 01, 2011. However in Income/Expense report, we see grand total of zero for calendar year 2011 because in the above example, we make no income/expense-related journal entry. Although the USD20.00 gain is not realized and in most countries it is also not taxable, such gain is part of the income because of the asset appreciation and it shall be shown in the Income/Expense report. Beside the above scenario, I think that the Income/Expense report may fail in any book in which there are balances in asset/liability accounts whose price fluctuates. More specifically if there’s foreign currency account or investment current in KMyMoney and such account has balance, the Income/Expense report will not reconcile with Net Worth Report. As a consultant specialized in finance and accounting, I can tell you that it is incorrect and non-GAAP not to include such kind of unrealized gain/loss. In practice, all of our clients (many of which are Fortune 500 companies) have unrealized gain/loss accounts for different categories of asset/liability account to carry the gain/loss resulting from period end revaluation. The unrealized gain/loss amounts will be included in income statement under Comprehensive Income, for instance. Therefore income statement can reconcile with balance sheet. This article provides simple explanation on this: http://www.investopedia.com/articles/04/022504.asp (see section Comprehensive Income). Based on my knowledge of KMyMoney, I think there are two ways that you can fix this bug: 1. The easy way is to add report entries like Unrealized Gain and Unrealized Loss to collect the gain/loss resulting from exchange rate fluctuation or equity price fluctuation for each asset/liability account when generating the Income/Expense report. 2. The difficult way is to add revaluation function in KMyMoney to make unrealized gain/loss journal entries automatically based on fiscal period end exchange rates and equity prices. To do this, you might need to add two fields for the asset/liability account so that user can maintain the income/expense accounts to which the unrealized gain/loss are adjusted to. Then using this automatic revaluation function, KMyMoney will make journal entries by calculating the different between the transaction price and period end price. Therefore there’ll be no need to revise the Income/Expense Report. I believe the most important reason why people and businesses maintain bookkeeping is for reporting based on which decision-making can be made. If we allow this kind of flaw exist in KMyMoney, it will be misleading for decision-making. For example, people won’t find out that why their net worth is so low even if they have high income and low expenditure when their books carry huge amount of (unrealized) investment loss. I myself found that I had thousands of dollars Net Worth gain not shown in the Income/Expense report. It took me a while to find out where it came from and even I’ve found out the reason, I don’t know whether it comes from foreign currency gain/loss or investment gain/loss. I hope this bug can be fixed in KMyMoney. Thank you. Reproducible: Always Steps to Reproduce: 1. Create a new book and enter the transaction specified in the Details section 2. Review Net Worth Report balance for 2010 and then review Net Worth Report balance for 2011. 3. Review Income/Expense Report for 2011. Actual Results: Net Worth Balance in 2010 = USD1000.00 Net Worth Balance in 2011 = USD1020.00 Income/Expense Grand Total = USD0.00 Expected Results: Net Worth Balance in 2010 = USD1000.00 Net Worth Balance in 2011 = USD1020.00 Income/Expense Grand Total = USD20.00 See http://www.investopedia.com/articles/04/022504.asp for brief explanation on the subject matter
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 ***