How to maintain correct account balance in multiple currencies in case of foreign bank account?

The Problem:

Let’s say we have a foreign bank account “Paypal USD” in USD currency and the company’s base currency is INR. Currently, we face the below problem where the account with zero USD balance has some balance in INR.

You can see in the above example, we have an INR balance of 4, even though the USD value is zero. Here, the exchange rate is set based on the exchange rate on the posting date of the transaction.

The Workaround:

Currently, we suggest the users the following workaround to create a Journal Entry to make the INR balance zero.

As you can see, here we are manipulating Exchange Rate to make the INR balance zero. But I don’t know if this workaround is acceptable or not by the auditors. We need to find out the actual solutions to handle this issue.

The Solution(?):

The probable solution (which can solve it at least mathematically) is considering the moving average of incoming (deposit) exchange rates while the account is getting credited (withdrawn). It will ensure the account balance is always zero in both currencies.

But it comes with a problem too. We need to repost all future entries in case of any back-dated entries or pass an adjustment entry booking the difference as exchange gain/loss. Again, I don’t know if this approach is acceptable to the accounting principles or not.

If anyone knows how it should be handled as per the accounting guidelines, please post it here.

2 Likes

Repost is not a good idea. Better to allow GL Entry without account currency amount = 0 amount and allow the GL entry to have only company currency amount

This is how Tally does the transaction handling.

1 Like

Doing moving average will create floating point differences as well.

1 Like

My auditors (US and South Asia) would be really unhappy with me if I tried to do this.

Wouldn’t the correct answer be to post a Exchange Gain/Loss entry to the bank account every time a new exchange rate is posted? At my company, we have a “Bank Revaluation” account on our income statement for this purpose.

6 Likes

I support @aakvatech that Moving Average method would not be good idea as while withdrawing or paying you will need to Debit the Expense or Supplier account with todays rate and not moving average rate.

In Tally, they allow to pass entry in base currency to accounts of Foreign Currency. So, if you see the ledger in account currency, you will NOT see that Journal Entry passed in Base Currency and when you view the account ledger in Base Currency then the Journal Entry will be Visible.

If we allow to pass entry in Base Currency in Foreign Currency Account then it might help solving the problem.

2 Likes

A quick, simple video for an introduction to IAS 21.

Summary

  • All transactions concerning P&L should be at spot rates (as it works currently).
  • Monetary Items from BS (like Paypal USD) should be valued at closing rates.

The proposed solution could be incorrect as transactions would not be booked at spot rates.

The current workaround seems correct from an accounting perspective (and can be automated). Say the difference (INR 4 in your example) gets booked to exchange the gain/loss account.

How to determine exchange differences (where the USD account still has a balance)?

The actual moving average would be overkill (with repost). This may not be needed as the Paypal account balance in INR need not have an accurate balance as of the date (since it should be reported at the closing rate with the difference in exchange gain/loss account).

The simple average for the balance as of date (say Balance in USD / Balance in INR) should be good enough to automatically calculate exchange gain/loss. This is just so that exchange gain/loss is updated as and when Forex is consumed (a simple reversal for cancellation of entry is good enough).

This goes in addition to what @aakvatech & @VimalRathod82 suggested:

So, the automated difference entry would be just in base currency.

Edit:

Example:
Say I have a balance of USD 100 (INR 8100) as on the date in Paypal.

If I purchase a service worth USD 50 (INR 4145) today, Journal Entry can go like

Expense Dr – USD 50 (INR 4145)
Paypal CR – USD 50 (INR 4050)
Ex Gain CR – USD 0 (INR 95)

  • This can have a similar treatment for all transactions with any posting date (without repost) as the accuracy of the Paypal balance in INR is not essential.
  • Also, as on the date, the Paypal balance would be valued at the Closing Rate, with the difference in the Exchange gain account ensuring the correct balance in the Exchange gain account.
  • A simple reversal for cancellation would be good enough for the same reason.
5 Likes

We have struggled due to this issue and can share the following insights:

  • For bank accounts - that is a realized gain loss.
  • The longer you wait to post that entry - the balances will be off by a lot and also the exchange rate used in journal entry to resync the balances could be 1000 times different - attracting the attention of auditor.
  • In some instances, when you checkmark bank balance should be debit only - ERPNext will not allow us to post any further transactions to that account as the company currency debit/credit may not have adequate balance.

Some rules we follow:

  • GL entries must always match the applicable exchange rate. For example purchase of USD - credit in local currency account and debit USD account will not match if we use any other rate - etc.
  • Realized gain loss must be booked during fiscal year.

Some suggestions:

  • Allow an account to be designated as REALIZED gain / loss. Check mark allow semi automated / calculated gain loss for that account at time of posting a transaction to that account - so no separate journal entry is needed. The reference exchange rate should be the one last posted in system / and used by ERP to display co currency balances. This will keep the balances in sync.
  • Allow semi automated / calculated gain loss for same designated accounts at time of posting a new exchange rate. We manually post exchange rate revaluation and delete unrealized gain loss accounts from the journal entry (I think since V14 that system created journal entry has stopped working for us).

This should keep the balances in sync and also allow for year end closing with final exchange rate. Just some thoughts… Thanks for looking into this. In a way ERPNext actually does good - it shows you a pending gain loss when the balances are off…

1 Like

As others have said, it sounds like this is roughly the right approach in terms of end results – periodic revaluations against an expense account – but as you point out the exchange rate manipulations are a bit confusing and may raise concerns even if they get to the right place in the end.

Is it possible right now to produce GL Entries that look like this with Journal Entries?

Thanks, everyone for the responses. I think we got a fair idea of how to handle it.

And I think we should go with this approach, booking difference entries automatically while withdrawing any amount from the bank. We will provide an option to pass zero debit/credit in account currency in GL Entry.
Also, in the Exchange Rate Revaluation document, we need to fetch those accounts which have zero balance in account currency but have a balance in company currency.

5 Likes

I have tried the above workaround and it works perfectly as far as accounting of DR and CR is concern but it has its side effect in ERPNext.

  1. Because the entry is passed in Journal Entry, in payment reconciliation the Journal Entry only shows at the payment side and not Invoice side. So now you have an entry hanging which cannot be reconciled.

  2. Above problem affects the Account Payable or Account Receivable report and summary reports also as the reconciliation is not happening.

so above are the observation which i want to share with the team. which need a solution if the above workaround is proposed.