[Kmymoney-devel] [Bug 300503] Net Worth Report Does Not Reconcile with Income/Expense Report
James Zhixin Zhang
reachjames at reachjames.net
Mon May 28 12:33:58 UTC 2012
https://bugs.kde.org/show_bug.cgi?id=300503
--- Comment #3 from James Zhixin Zhang <reachjames at reachjames.net> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
More information about the KMyMoney-devel
mailing list