Say I'm a US entity with USD as my reporting currency, but I order
widgets from a Japanese supplier and receive an invoice in JPY; later
I pay via wire transfer at a bank that does the JPY/USD exchange for
me. How can I correctly report the cost of the widgets separately
from the foreign currency exchange loss/gain?
For example, here's a ledger, from the above information sources:
2019-01-02 Opening Balances
Assets:Bank 4000.00 USD
Equity:Opening Balances
2019-02-01 Acme Widgets, Inc.
Expenses:Widgets 1000 JPY
Liabilities:Payable
2019-10-01 Acme Widgets, Inc.
Liabilities:Payable 1000 JPY @@ 9.75 USD
Assets:Bank
Separately, I consult an exchange like openexchangerates.org for the
exchange rates over time, and I get a price database[*]:
P 2019-01-01 23:59:58 JPY 0.009118097600116712 USD
P 2019-08-25 23:59:59 JPY 0.009537502652617925 USD
This is enough information to report expenses in USD according to US
GAAP as prescribed in ASC 830:
- At the time of the expense, 1000 JPY = 9.12 USD, so the expense
report should say
Expenses:Widgets 9.12 USD
- At the time of the payment, 1000 JPY = 9.53 USD, and I had to pay
the bank 9.75 USD. So I lost 0.41 USD owing to the change in
exchange rate, and had to pay 0.22 USD overhead for the exchange,
for a total of 0.63 USD in foreign currency exchange expenses:
Expenses:Currency exchange 0.63 USD
(Perhaps I might even want to report the currenty exchange loss
separately from the overhead of my bank's exchange, but I'll be
happy for today even if I can't make that distinction.)
So I would like to see the following report:
% ledger <MAGIC OPTIONS HERE> balance
3990.25 USD Assets:Bank
-4000.00 USD Equity:Opening Balances
9.75 USD Expenses
0.63 USD Currency exchange
9.12 USD Widgets
--------------------
0
How can I get this? (Finding a way to patch ledger and submitting it
upstream is an acceptable answer if it's not actually feasible in
ledger today -- guidance on the relevant logic would be appreciated!)
Here are some approaches I tried:
1. Run `ledger --exchange USD balance' or `ledger --market balance'.
But these give:
3990.25 USD Assets:Bank
-4000.00 USD Equity:Opening Balances
9.75 USD Expenses:Widgets
--------------------
0
That is, they fail to distinguish the cost of the widget from the
cost of waiting a few months to make the payment or the cost
imposed by the bank on exchanging currencies.
2. Run `ledger --historical balance'. But this gives:
3990.25 USD Assets:Bank
-4000.00 USD Equity:Opening Balances
9.12 USD Expenses:Widgets
0.63 USD Liabilities:Payable
--------------------
0
That is, although it correctly splits the widget and exchange
costs, it leaves the exchange cost in Liabilities:Payable rather
than in an expense account.
In this simplified example perhaps I could just move everything
left in Liabilities:Payable to Expenses:Currency exchange, but
in the actual ledger there's one or two separate liability accounts
per invoice, so that's not practical.
Further, --historical will do the wrong thing if I hold any JPY
monetary assets and want to know their current value in USD. In
particular, while --historical is appropriate for displaying
expenses and income, and perhaps for displaying nonmonetary assets,
it is wrong for displaying monetary assets and liabilities.
3. Use the VALUE tag to specify different valuation rules for
different accounts, as recommended by the manual in the section
`Commodity pricing'. This appears to be broken, and there are
essentially no automatic tests for it, which makes me wonder
whether it ever worked and has ever been used in practice.
The manual says that everything works by default as if there were a
global automated transaction
= expr true
; VALUE:: market(amount, date, exchange)
but if I add this explicitly, amounts that arise from exchanges
become wildly unreasonable:
% ledger -X USD balance
3990.25 USD Assets:Bank
-4000.00 USD Equity:Opening Balances
9120.00 USD Expenses:Widgets
18870.00 USD Liabilities:Payable
--------------------
27980.25 USD
It seems that there is a spurious multiplication; things look more
reasonable if I also divide by amount,
= expr true
; VALUE:: market(amount, date, exchange)/amount
then I get:
% ledger -X USD balance
3990.25 USD Assets:Bank
-4000.00 USD Equity:Opening Balances
10.00 USD Expenses:Widgets
--------------------
0.25 USD
The numbers are still unreasonable, though, because apparently
something got rounded far too much on the way. If I explicitly
unround, as in
= expr true
; VALUE:: unrounded(market(unrounded(amount), date, exchange)/amount)
then it looks somewhat more reasonable:
% ledger -X USD balance
3990.2500000 USD Assets:Bank
-4000.0000000 USD Equity:Opening Balances
9.1181000 USD Expenses:Widgets
0.6319000 USD Liabilities:Payable
--------------------
0
There seems to be a little more precision if I unround and use
`commodity' rather than `amount' -- not sure why:
= expr true
; VALUE:: unrounded(market(commodity, date, exchange))
% ledger -X USD balance
3990.250000000000000000 USD Assets:Bank
-4000.000000000000000000 USD Equity:Opening Balances
9.118097600116712000 USD Expenses:Widgets
0.631902399883288000 USD Liabilities:Payable
--------------------
0
But again the amount went into Liabilities:Payable rather than
into Expenses:Currency exchange.
So, although I can set the valuation function differently for
different account subtrees in order to value expenses and income at
the historical rate yet value assets and liabilities at today's
rate, as in
= /^Equity:/ or /^Expenses:/ or /^Income:/
; Value:: unrounded(market(commodity, post.date, exchange))
= /^Assets:/ or /^Liabilities:/
; Value:: unrounded(market(commodity, date, exchange))
it still doesn't help to report foreign exchange expenses.
4. Manually edit the transactions to add explicit lot prices based on
the exchange rate from the price database, _and_ add an explicit
Expenses:Currency exchange account posting:
2019-01-02 Opening Balances
Assets:Bank 4000.00 USD
Equity:Opening Balances
2019-02-01 Acme Widgets, Inc.
Expenses:Widgets 1000 JPY {=0.009118097600116712 USD}
Liabilities:Payable
2019-10-01 Acme Widgets, Inc.
Liabilities:Payable 1000 JPY {=0.009118097600116712 USD} @@ 9.75 USD
Assets:Bank -9.75 USD
Expenses:Currency exchange
This does give the report I want:
% ledger -f /tmp/riastradh/test.ledger -X USD balance
3990.25 USD Assets:Bank
-4000.00 USD Equity:Opening Balances
9.75 USD Expenses
0.63 USD Currency exchange
9.12 USD Widgets
--------------------
0
However, this is not an option for several reasons:
(a) every exchange rate tag {=0.009118097600116712 USD} has to be
correct for the date, opening a large amount of data entry
error;
(b) many of the lots pass through several intermediate accounts in
separate transactions imported automatically from statements:
i. PayPal currency conversion from Assets:PayPal to Assets:PayPal
ii. PayPal payment from Assets:PayPal to Liabilities:PayPal:<txid>
iii. invoice payment from Liabilities:PayPal:<txid> to
Liabilities:Payable:<invoiceno>
iv. invoiced expense from Liabilities:Payable:<invoiceno> to
Expenses:Widgets.
Here (i) and (ii) are generated by a script applied to PayPal
CSV export, without reference to other transactions in the
journal. Editing them by hand is out of the question.
I realize that this may expose ledger to FIFO/LIFO questions,
which ledger doesn't address -- e.g., if I receive two 10000 JPY
invoices at different exchange rates, and then pay out 15000 JPY
at a third exchange rate, how much gain/loss should I report? But
at the moment I think that this scenario doesn't actually come up
in my records, so I would be satisfied without finding a way to
talk ledger into understanding FIFO or LIFO rules.
[*] Actually it's more like `USD 109.672 JPY' and `USD 104.84925 JPY';
I manually computed the reciprocal so that ledger treats USD as the
primary commodity. It would be nice if I didn't have to do that. But
that's a separate issue.
--
---
You received this message because you are subscribed to the Google Groups
"Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/ledger-cli/20200328060039.21DE260A4B%40jupiter.mumble.net.
