Is this a bug in how QuickBooks Desktop handles FX realized gains/losses?

tkelly_uk · ‎March 08, 2019

[This is a multi-currency question, so if you're not into that you may want to just skip.]

I've stumbled onto a possible bug in the way QuickBooks Desktop (I'm using Premier 2018) handles realized gains/losses in a multi currency environment. I'll describe the details using a simplified example. In the following (and in reality) our QuickBooks Home Currency is USD.

In January, we receive a bill from our vendor BritVendor for GBP 10,000.00. We create a bill in QuickBooks, posted to our GBP Accounts Payable account, and with the prevailing exchange rate. Let's say it was: 1.00 GBP = 2.00 USD
In February, we get another bill from that same vendor, this time for GBP 20,000.00. We post as before, but this time the exchange rate is: 1.00 GBP = 1.75 USD
In March, we then pay both bills with a single payment check of GBP 30,000.00, drawn on our GBP bank account. We record the payment in QB, again noting the exchange rate which has changed again and is now: 1.00 GBP = 1.50 USD

OK, so it's easy to see that in USD terms, we have incurred some kind of overall gain or loss since the exchange rate moved, and moved in the same direction. So in USD terms -- under the QuickBooks FX hood[1] as it were -- it went something like this:

January: bill for USD 20,000.00 (i.e. GBP 10,000.00 x 2.00)
February: bill for USD 35,000.00 (i.e. GBP 20,000.00 x 1.75)
so, total of bills is USD 55,000.00
March: payment of USD 45,000.00 (i.e. GBP 30,000.00 x 1.5)

So in USD terms, we've experienced an FX-induced gain of USD 15,000.00

The problem is that sometimes, but only sometimes, QuickBooks Desktop is simply not reporting that FX gain/loss. I *think* I know what is going on, and I've tested my theory and it seems to work. But I wanted to check with any other multi-currency users.

The error occurs if I post that GBP 30,000.00 two-bill payment check directly between our GBP bank account and our GBP Accounts Payable account (and then pay the bill's themselves using the resulting credit). However, if I post the payment from bank to a clearing account I create just for that purpose, then the FX effect is handled correctly.

NOTE: in both mechanisms, we do record the exchange rate for the GBP 30,000.00 payment (1.50 in my example). But QB only appears to pay attention to that rate when we pass the payment through the clearing account. If we just go directly into our A/P account, as a credit to be used later, then it's as if QB completely ignores the March FX rate, and instead acts as if the bills were paid with FX rates exactly as they were on the day the bills were issued.

[Edit: I tested the following, what I thought might be an alternative explanation, and it's not.]

But there is an alternative explanation. It may have nothing to do with whether we use a clearing account or not. Instead it may be due to the fact that with the method that works, we pay the bills as if we had received two separate payments, whereas with the broken method we pay the bills from a single large payment from which we then extract the required amount as a credit. I guess it's possible that when the single amount is posted to A/P and only split as different credits, that the FX information on the payment is being lost. (Although if that happened, and so QB had no explicit rate for the date in question, it would use the most recent earlier rate, not go back and pick the very rate that was used for each specific bill.)

As far as I can see this is a program bug, and could be a serious one in that it could affect tax liability. If I'm understanding what's going on, then this could result in someone paying tax they didn't actually owe, or in not paying tax they did owe.

Can anyone shed any light?

thanks!

Thomas

[1] Or "bonnet" as they say in the UK :-)

Rose-A · ‎March 08, 2019

Welcome to the Community, tkelly.

Allow me to lend a hand and provide you with some information about multi-currency in QuickBooks Desktop.

I appreciate the detailed information you've provided. Unrealized foreign exchange gains or losses are profits or losses that have occurred on paper, due to changes in exchange rates. These gains or losses are only realized after the transactions have been completed when money has actually been collected or paid.

That is why QuickBooks shows the effect of a Home currency adjustment on Accounts Payable or Accounts Receivable as an unrealized gain or loss, and the effect on account types such as bank accounts as a realized foreign exchange gain or loss. Unrealized gains or losses are also not reflected in the general ledger or the trial balance.

In order to correct this, you can make an adjustment for your home currency, here's how:

Click Company at the top menu bar.
On the drop-down list, click Manage Currency.
Choose Home Currency Adjustment.

You may find these articles helpful:

You know where to find me if you need more help with multi-currency in QuickBooks Desktop. Have a great day!

tkelly_uk · ‎March 09, 2019

Thanks @Rose-A . I thought I understood HCAs but maybe not as much as I'd thought. Maybe you can help me check?

I've reduced the situation to a really simple one that goes like this. I receive a foreign currency bill -- in my case GBP -- on a date when the GBP:USD exchange rate is one value. And I then pay that bill, in GBP, at some later date when the GBP:USD has changed to a different value. So far, so simple.

What I am then seeing as a problem is that depending on how I post the payment in QuickBooks, I may or may not see the resulting FX gain/loss appear in my P&L. Here are the two posting scenarios -- and note that they differ only in how the payments are posted. The bills themselves are entered in the same way in each. Also, in none of this do I run any Home Currency Adjustments (of course whether or not that is my problem is partly what I'm trying to find out!) So, here are the two ways of paying the bill. SCENARIO 1 has no problems. SCENARIO 2 is that one that seems broken.

SCENARIO 1: I typically start from my Vendor Center, and after choosing the Vendor in question:

I open the bill
I click "Pay Bill"
In the resulting window I then make sure all the various details are correct, and in particular that my GBP Bank account is selected in the "Account" field, and that the all-important EXCHANGE RATE field is what it should be
And then I click "Pay Selected Bills"
A few clicks and whirs and we're done and the bill is paid

The overall effect is that I am shown to have a zero balance with that Vendor and that that specific bill is shown as paid. Cool. And, crucially, if I run a P&L after doing this scenario, I DO SEE A REALIZED FX GAIN/LOSS. All hunky dory.

SCENARIO 2: Here I first create an explicit journal entry to post the bill amount from my GBP Bank account to my GBP A/P account. In that journal entry I do provide the prevailing FX rate -- i.e. the one that is different from the one that is on the bill. And I also provide the Vendor's name in the appropriate field so that QB knows it is meant for that particular vendor. But of course at this point that's all it knows -- who the Vendor is. It does not yet know which specific bill is being paid. So I need to make that happen. To do that, it's somewhat similar to Scenario 1. I pop over to my Vendor Center, find the bill I want and:

I open the bill
I click "Pay Bill"
In the resulting window I do check all the relevant details but crucially I click "Set Credits" (which if I've done the journal entry correctly will have a message near it telling me that I do indeed have some credits available)
In the resulting dialog box I select the amount I journaled in earlier and then return to the main bill payment screen where it's pretty much back to the same as for Scenario 1 in that
I click "Pay Selected Bills"
A few clicks and whirs and we're done and the bill is paid

And, as with Scenario 1, the overall effect is that I am shown to have a zero balance with that Vendor and that that specific bill is shown as paid. Cool. But, crucially, if I run a P&L with this scenario, I DO NOT SEE A REALIZED FX GAIN/LOSS. Not so hunky dory.

OK, so is that difference supposed to be what happens? Is it related to me not running HCAs?

FWIW, I actually did also try all of the above with HCAs. I did one at the end of the month of the bill, and then another at the end of the month of the payment. It made no difference but it's perfectly possible I'm just not doing them right. Still, I can't see why HCAs would matter here given that I am dealing with a discrepancy in realized gain/loss, not in its unrealized cousin.

thanks,

Thomas

Malcolm Ziman · ‎March 09, 2019

@tkelly_uk

You post a Home Currency Adjustment at year-end ,or month-end if you publish monthly reports, so that is irrelevant

If SCENARIO 1 works where you use the proper method, why would you even want to use a journal entry to pay a bill?

tkelly_uk · ‎March 10, 2019

Hey Malcolm,

Thanks for your input.

> If SCENARIO 1 works where you use the proper method, why would you even

> want to use a journal entry to pay a bill?

Well the quick answer is that it's kinda irrelevant how I would do things, because someone else has already done it. This is all coming out of a clean-up I'm doing of some work done by prior book-keepers/accountants. In that past work I have found a fair number of these problematic transactions, so I need to know how to fix them. And that means I want to know why they're happening.[1]

However, that might sound like I'm saying, "Yes, exactly! Why not just pay the bills directly?" (i.e. why not use what you referred to as the "proper method"?) But in fact I think I know why those past book-keepers did use their (improper? ;-) ) approach, and I probably would do the same (were it not for this issue I've bumped into).

The thing is, both scenarios in situation you're asking about (i.e. the most recent one, not the more fully described one I opened this thread with) are simplified for illustration purposes. In my original description, the payment in question covers not one but two bills.[2] Given that, I reckon they paid via a journal entry to A/P method in order to show a single check leaving the bank register, but then have it split between two different bills. And that doesn't seem unreasonable to me. So in practice, which I described originally, it's not a question of:

Pay via a Journal Entry to A/P VERSUS Pay Directly

Rather it's a question of:

Pay via a Journal Entry to A/P VERSUS Pay via a Journal Entry to a Clearing account?

To repeat, the problem is not Journal versus Direct. The problem is Accounts Payable versus Clearing (i.e. a suspense account of type "Bank"). That is: it's not the use of a journal entry per se that's at stake. What is at stake it's the type of account chosen to journal to. Type = Accounts Payable => BAD; it doesn't (appear to) handle realized FX gains/losses properly. Type = Bank => GOOD; it seems to handle them fine.

Thomas

[1] The whole thing has come to light as we attempt to move to QuickBooks Online. When we import our data from Desktop to Online, it converts over pretty accurately, except for a very big difference in the FX realized gains/losses account. At first I thought it was a problem with QuickBooks Online, but then I realized the problem was with the Desktop version. QB Online is actually getting it right. But QB Desktop, it turns out, has been wrong for a year or three now and we hadn't spotted it.

[2] I did it like that because RoseMarjorie had already suggested, in response to my fuller description, that the problem might be something to do with Home Currency Adjustment use. So my second description stripped away everything I could and still cause the issue, in order to let her help me decide if HCA use really was at stake.

@Malcolm Ziman @Rose-A

tkelly_uk · ‎March 10, 2019

Hi again Malcolm,

You'd made another point:

> You post a Home Currency Adjustment at year-end ,or month-end if

> you publish monthly reports, so that is irrelevant

Exactly! I don't see why HCAs are relevant here at all either.

But someone else suggested they might be, and her user says she's part of the "QuickBooks Team", so ahma listenin' to her! :-) I'm still asking her advice on this, and it may end up that I've confused things in my description such that she concludes that HCA is a red herring after all. But that's still an ongoing discussion.

But put it this way. If it's not HCAs, then what is it? I have a reproducible situation where QuickBooks' reporting of tax-impacting amounts appears to be incorrect. I'm in software test, and if I were running this particular project then in the absence of a credible explanation, I'd be reporting a bug.

Thomas

Topics

Training

Community

Resources

Is this a bug in how QuickBooks Desktop handles FX realized gains/losses?