Forex Accounts Receivable, timing difference between invoice and payment

[J. B. Rainsberger]

Hi, folks. Another Novice-level question about handling forex amounts.

My home currency is CAD. I invoice a client in EUR. They pay in EUR.

Complications: 

- the accounting standards in force include using the monthly average exchange rate for foreign currencies

- the client invoice arrived in November and the payment in December

The problem concerns the customer's Accounts Receivable balance after paying the invoice.

If I use only market values and don't specify the exchange rate on the individual postings, then we have a 0 balance in EUR, but a non-0 balance in CAD.

P 2023-11-01  EUR  1.45 CAD

P 2023-12-01  EUR  1.47 CAD

2023-11-01  Invoice

    assets:AR:customer    1000 EUR

    revenue:sales

2023-12-01  Invoice paid

    assets:bank    1000 EUR

    assets:AR:customer

The balance of assets:AR:customer will be non-0 using --value=then,CAD as long as the exchange rate changes between 2023-11-01 and 2023-12-01 (and it almost always does). The foreign exchange difference accumulates indefinitely.

If I try to periodically balance the assets:AR:customer account, such as at year-end, then I can have a balance of 0 CAD (using --value=then,CAD), but am stuck with a balance of "0 EUR, 20.00 CAD" in the "don't apply the market rates" view. This is the same problem of accumulating foreign exchange difference, but in reverse.

If I want a 0 balance in both EUR and CAD, it seems I have to specify the exchange rate on the payment posting to match the exchange rate in effect when the invoice was issued.

P 2023-11-01  EUR  1.45 CAD

P 2023-12-01  EUR  1.47 CAD

2023-11-01  Invoice

    assets:AR:customer    1000 EUR

    revenue:sales

2023-12-01  Invoice paid

    assets:bank    1000 EUR

    assets:AR:customer    -1000 EUR @ 1.45 CAD

    expenses:exchange gain or loss

Now the balance in assets:AR:customer would be 0 EUR and 0 CAD; however, now I run the risk of mistyping the exchange rate and I have virtually no way to check it with any kind of lint/test.

What am I missing? This seems like it ought to be easier than I'm making it, but maybe it isn't. :shrug:

Many thanks,

@jbrains

[Simon Michael]

Hi JB,

On Fri, Feb 23, 2024, at 05:36, J. B. Rainsberger wrote:

The balance of assets:AR:customer will be non-0 using --value=then,CAD as long as the exchange rate changes between 2023-11-01 and 2023-12-01 (and it almost always does). The foreign exchange difference accumulates indefinitely.

That's right - the settlement delay allows a foreign exchange gain or loss for you (and the inverse for the customer) to accumulate. It's correct to record that as an extra revenue or expense. Eg as you do here, giving the customer a fixed exchange rate and taking the gain/loss risk yourself:

If I want a 0 balance in both EUR and CAD, it seems I have to specify the exchange rate on the payment posting to match the exchange rate in effect when the invoice was issued.

P 2023-11-01  EUR  1.45 CAD

P 2023-12-01  EUR  1.47 CAD

2023-11-01  Invoice

    assets:AR:customer    1000 EUR

    revenue:sales

2023-12-01  Invoice paid

    assets:bank    1000 EUR

    assets:AR:customer    -1000 EUR @ 1.45 CAD

    expenses:exchange gain or loss

Now the balance in assets:AR:customer would be 0 EUR and 0 CAD; however, now I run the risk of mistyping the exchange rate and I have virtually no way to check it with any kind of lint/test.

Is it a significant risk ? A "; today's market rate" comment would help a little. You could also move or copy the P record so that it appears next to the transaction. And I suppose you could write some tool to check these agree, or to add the @ cost automatically based on today's price.

[J. B. Rainsberger]

On Fri, Feb 23, 2024 at 6:22 PM Simon Michael <si...@joyful.com> wrote:

 

On Fri, Feb 23, 2024, at 05:36, J. B. Rainsberger wrote:

The balance of assets:AR:customer will be non-0 using --value=then,CAD as long as the exchange rate changes between 2023-11-01 and 2023-12-01 (and it almost always does). The foreign exchange difference accumulates indefinitely.

That's right - the settlement delay allows a foreign exchange gain or loss for you (and the inverse for the customer) to accumulate. It's correct to record that as an extra revenue or expense. Eg as you do here, giving the customer a fixed exchange rate and taking the gain/loss risk yourself:

Thanks for confirming what I suspected. That helps a lot.

I had trouble with this mostly due to not having an intuition around this. Now I think I do. How does this sound?

The exchange difference represents a change in value of the money flowing through Customer's A/R account. Now I can interpret the "native currency view" as an accurate description of how much money Customer owes me and the "market value in my currency view" as a reflexion of the value of the flow of money through the account. Using the example below, Customer doesn't owe anything (balance of 0 EUR), but they have had a value of 100 CAD to us by virtue of the currency fluctuation.

My primary concern lay in being able to interpret these numbers accurately. It always seemed strange to see CAD balances for customers who didn't owe us anything! Now I think I get it.

A Customer balance of (0 EUR, -275.50 CAD) means "Customer owes us nothing, but we've made 275.50 CAD from exchange difference". The only annoyance comes when the same Customer pays us both in their currency and ours. (Sometimes this happens.) Worst case, I could split that into two accounts to retain clarity of the meaning of the current balance in the account.

If I want a 0 balance in both EUR and CAD, it seems I have to specify the exchange rate on the payment posting to match the exchange rate in effect when the invoice was issued.

P 2023-11-01  EUR  1.45 CAD

P 2023-12-01  EUR  1.47 CAD

2023-11-01  Invoice

    assets:AR:customer    1000 EUR

    revenue:sales

2023-12-01  Invoice paid

    assets:bank    1000 EUR

    assets:AR:customer    -1000 EUR @ 1.45 CAD

    expenses:exchange gain or loss

Now the balance in assets:AR:customer would be 0 EUR and 0 CAD; however, now I run the risk of mistyping the exchange rate and I have virtually no way to check it with any kind of lint/test.

Is it a significant risk ? A "; today's market rate" comment would help a little. You could also move or copy the P record so that it appears next to the transaction. And I suppose you could write some tool to check these agree, or to add the @ cost automatically based on today's price.

Significant? I'm not sure. Mostly, I'm concerned about either handcrafting or generating the wrong value and having no real way to notice.

A helpful feature would be something like "-1000 EUR @ EURCAD rate as of 2022-12-01". I don't know anything about hledger's internal structure. How feasible a feature would this be? I assume it's already in the inbox somewhere. :)

I thought about generating the "@ cost" as part of what I've built in jq to process my CSV files. I would need to verify that I'm doing that sensibly before I go too far. :) I'm clinging as long as I can to a model where I don't need to worry about reapplying hardcrafted changes to the journal files that my scripts produce.

If I cross that bridge, I'll come back begging for more guidance.

Thank you,

--

J. B. (Joe) Rainsberger :: tdd.training :: jbrains.ca ::
blog.thecodewhisperer.com

Replies from this account routinely take a few days, which allows me to reply thoughtfully. I reply more quickly to messages that clearly require answers urgently. If you need something from me and are on a deadline, then let me know how soon you need a reply so that I can better help you to get what you need. Thank you for your consideration.

[J. B. Rainsberger]

On Sun, Feb 25, 2024 at 6:56 AM J. B. Rainsberger <m...@jbrains.ca> wrote:

On Fri, Feb 23, 2024 at 6:22 PM Simon Michael <si...@joyful.com> wrote:

 

On Fri, Feb 23, 2024, at 05:36, J. B. Rainsberger wrote:

The balance of assets:AR:customer will be non-0 using --value=then,CAD as long as the exchange rate changes between 2023-11-01 and 2023-12-01 (and it almost always does). The foreign exchange difference accumulates indefinitely.

That's right - the settlement delay allows a foreign exchange gain or loss for you (and the inverse for the customer) to accumulate. It's correct to record that as an extra revenue or expense. Eg as you do here, giving the customer a fixed exchange rate and taking the gain/loss risk yourself:

Now a new wrinkle. When I do this for my particular set of accounts, the sub-penny amounts accumulate and result in a trial unbalance:

$ hledger --file ledger/main.journal --value=then,CAD --end="2023-11-01" balance | tail -n 1

            0.01 CAD 

I presume the customer balances look like 0, but are instead not quite 0 CAD. I infer this comes from hledger not rounding the exchange difference when posting the payment for each invoice.

Can I force hledger to round when it computes the exchange difference or did you very expressly decide not to allow that for good reasons? I tried to find the relevant section in the guide, but failed.

Thanks again.

[Simon Michael]

On Sun, Feb 25, 2024, at 04:56, J. B. Rainsberger wrote:

On Fri, Feb 23, 2024 at 6:22 PM Simon Michael <si...@joyful.com> wrote:

A helpful feature would be something like "-1000 EUR @ EURCAD rate as of 2022-12-01". I don't know anything about hledger's internal structure. How feasible a feature would this be? I assume it's already in the inbox somewhere. :)

Just a simple matter of programming I'm sure. It's not in my queue. In one sense it would be less explicit, causing that entry to rely (more strongly than before) on something unseen. A variation could be to write the cost but add a tag (call it a price assertion) requiring hledger to check that it matches the contemporaneous market price. Though in practice I think it's pretty rare that the transacted price exactly matches the market price (and real world market price is a somewhat fluid idea already).

$ hledger --file ledger/main.journal --value=then,CAD --end="2023-11-01" balance | tail -n 1

            0.01 CAD 

I presume the customer balances look like 0, but are instead not quite 0 CAD. I infer this comes from hledger not rounding the exchange difference when posting the payment for each invoice.

Can I force hledger to round when it computes the exchange difference or did you very expressly decide not to allow that for good reasons? I tried to find the relevant section in the guide, but failed.

A minimal repro would be helpful. I think most likely your entries need to be more precise.  

[J. B. Rainsberger]

On Sun, Feb 25, 2024 at 8:26 AM Simon Michael <si...@joyful.com> wrote:

On Sun, Feb 25, 2024, at 04:56, J. B. Rainsberger wrote:

On Fri, Feb 23, 2024 at 6:22 PM Simon Michael <si...@joyful.com> wrote:

A helpful feature would be something like "-1000 EUR @ EURCAD rate as of 2022-12-01". I don't know anything about hledger's internal structure. How feasible a feature would this be? I assume it's already in the inbox somewhere. :)

Just a simple matter of programming I'm sure. It's not in my queue. In one sense it would be less explicit, causing that entry to rely (more strongly than before) on something unseen. A variation could be to write the cost but add a tag (call it a price assertion) requiring hledger to check that it matches the contemporaneous market price. Though in practice I think it's pretty rare that the transacted price exactly matches the market price (and real world market price is a somewhat fluid idea already).

It's beginning to feel like this would move in the direction of Quickbooks, with an explicit forex rate on each transaction as needed. That just seems like a potential nightmare to me if anything went wrong with generating entries. Of course, version control _does_ limit the damage....

$ hledger --file ledger/main.journal --value=then,CAD --end="2023-11-01" balance | tail -n 1

            0.01 CAD 

I presume the customer balances look like 0, but are instead not quite 0 CAD. I infer this comes from hledger not rounding the exchange difference when posting the payment for each invoice.

Can I force hledger to round when it computes the exchange difference or did you very expressly decide not to allow that for good reasons? I tried to find the relevant section in the guide, but failed.

A minimal repro would be helpful. I think most likely your entries need to be more precise.  

I think I know what you mean, but if I created a posting with 10 significant digits (or whatever the actual precision is), I think my accountant might yell at me. Is this truly the only way?

I can work on providing a minimal example, but when I play around with `print "Exchange Gain or Loss", I run into the well-understood "print might not produce balanced transactions" issue, which I imagine matches the off-by-1-cent problem. Perhaps I need to run all my posting through the `print` command so that those postings are all rounded and have explicit amounts.

[Simon Michael]

There'll be a simple solution - you'll probably see one yourself - but I can't say what it is at the moment, I'd have to see what's going on.

[J. B. Rainsberger]

On Mon, Feb 26, 2024 at 8:15 PM Simon Michael <si...@joyful.com> wrote:

 

There'll be a simple solution - you'll probably see one yourself - but I can't say what it is at the moment, I'd have to see what's going on.

In the meantime, I upgraded to 1.32.3 and the behavior seems to have changed:

$ hledger --file /home/jbrains/Workspaces/Recordkeeping/DiasparSoftwareServices/Financial/Bookkeeping/ledger/main.journal --value=then,CAD --end="2023-11-01" balance | tail -n 1
                   0 

I think, however, that this is a coincidence based on banker's rounding, because...

$ hledger --file /home/jbrains/Workspaces/Recordkeeping/DiasparSoftwareServices/Financial/Bookkeeping/ledger/main.journal --value=then,CAD --end="2023-11-01" balance "Accounts Receivable"

                   0  Assets:Accounts Receivable:Customer 1
                   0  Assets:Accounts Receivable:Customer 2
        3,850.00 CAD  Assets:Accounts Receivable:Customer 3
--------------------
        3,849.99 CAD 

$ hledger --file /home/jbrains/Workspaces/Recordkeeping/DiasparSoftwareServices/Financial/Bookkeeping/ledger/main.journal --value=then,CAD --end="2023-11-01" balance "Accounts Receivable" -c "1000000000.000000 CAD"
       -0.005000 CAD  Assets:Accounts Receivable:Customer 1
       -0.005000 CAD  Assets:Accounts Receivable:Customer 2
     3850.000000 CAD  Assets:Accounts Receivable:Customer 3
--------------------
     3849.990000 CAD  

Banker's rounding means that both balances of -0.005 CAD round to 0. (And these A/R balances are, of course, balanced on the other side by Exchange Gain or Loss, so the full trial balance, in fact, balances.)

WAIT.

Maybe I'm being stupid here.

My concern revolves around the "Clear foreign A/R balances" transaction and having thousandths of a penny in there to balance the accounts. But the accountants will not _see_ those extra decimal places! They'll simply see balanced accounts, nicely rounded, because they'll consume hledger reports and not directly use hledger.

So... I guess I should actually post all those annoying decimal places in the "Clear foreign A/R balances" posting and just let things be as they are. I'll run reports. In spots, postings will be off by a penny, the accountants will chuckle, and handle it. Their books and my books might be out a tiny amount, but it'll probably even out over time.

Brilliant? or Wishful thinking? (And I know it's in your best interests to call it "Brilliant", but please don't do that just to make me feel better or go away. :) )

Thanks,

[Simon Michael]

Brilliantly pragmatic and what all sensible people would do!

Meanwhile... :) I'm still happy to explain and discuss the behaviour in any complete example.

1.32 introduced more careful precision preserving in a number of places.

--

You received this message because you are subscribed to the Google Groups "hledger" group.

To unsubscribe from this group and stop receiving emails from it, send an email to hledger+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/hledger/CAA85kdVwZSgDdc0qsbX-qjNtgp%2BovEQsB2TkRNGpTTt7DJj%2B3w%40mail.gmail.com.

[J. B. Rainsberger]

On Tue, Feb 27, 2024 at 4:15 PM Simon Michael <si...@joyful.com> wrote:

 

Brilliantly pragmatic and what all sensible people would do!

https://i.imgflip.com/8h9sji.jpg

Meanwhile... :) I'm still happy to explain and discuss the behaviour in any complete example.

I have an anonymized example from my actual books. It's by no means minimal. If you're willing to look at it, I'm willing to share it.

1.32 introduced more careful precision preserving in a number of places.

I attest to the difference it has made!

[Simon Michael]

I'm willing, I always like to investigate anomalies and see if there's a good explanation. Feel free to email me or open an encrypted chat with me eg from the hledger room on matrix.

--

You received this message because you are subscribed to the Google Groups "hledger" group.

To unsubscribe from this group and stop receiving emails from it, send an email to hledger+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/hledger/CAA85kdWLDv6su3Tage0xqtmcq6gT3w-SGJ4NAFu5NUmNkgaChg%40mail.gmail.com.

[J. B. Rainsberger]

On Tue, Feb 27, 2024 at 6:48 PM Simon Michael <si...@joyful.com> wrote:

 

I'm willing, I always like to investigate anomalies and see if there's a good explanation. Feel free to email me or open an encrypted chat with me eg from the hledger room on matrix.

Thank you for the invitation. I joined the Matrix room, I like the idea of a Discourse forum, and I won't hesitate when I run into the next roadblock.