In this blog, I will take you through the steps to create a simple foreign currency sales invoice transaction to demonstrate how Microsoft Dynamics NAV 2017 converts FCY to LCY amounts and how it accounts for unrealised/realised gains and losses.
I will also show how to run the adjusted exchange rates batch job in NAV.
NAV calculates the foreign currency gains and losses based on the fluctuation of the effective exchange rate between the posting date of a sales/purchase invoice and the date of a payment or any applying entry.
Under Financial Management > Periodic Activities > Currency > Currencies, you will be able to set the corresponding posting G/L Account No. for Realised and Unrealised Gains and Losses entries by editing the Currency Card.
I will be using USD as the foreign currency (FCY) with the below sample setup on the Currency Exchange Rates table.
On the first line of the above table, it indicates that on or after 01/08/16, USD amount transactions will be converted to LCY using the relationship of 2 USD = 1 AUD. This is effective until there is another entry with the latest starting date (i.e., 01/09/16 where 2.5 USD = 1 AUD).
1. Create and post a Foreign Currency Sales Invoice
• Amount: USD $10,000
• Posting date: 1/8/2016
• Exchange Rate: 2 USD = 1 AUD
On the invoice below, I have not included GST and discounts so that it will post simpler entries that will be easier to review. I have also made sure the currency code is selected on the Foreign Trade FastTab of the sales invoice.
If you click on the ellipsis button beside the currency code, it will show you the Exchange Rate amount that the transaction used to convert to LCY.
Note: If the Fix Exchange Rate Amount field on the Currency Exchange Rates Table are set to “Currency” or “Relational Currency”, you will be able to change the Exchange Rate or the Relational Exch. Rate Amount at this page. If you do not want these to be modified at transaction level, you will need to set the value to “Both”.
On the Sales Invoice Statistics, you will see that this has already converted to the Local Currency correctly using the exchange rate defined on the setup (10,000 USD / 2.0).
1.1 Foreign Currency Sales Invoice – Entries
Once the sales invoice is posted, it will create below G/L entries in LCY amount.
Also take note of the LCY amounts posted in the Customer Ledger Entry.
2. Run the Adjust Exchange Rate Batch Job
• Posting Date = 31/8/2016
• Exchange Rate: 1.25 USD = 1 AUD
This function is typically run on Month End. It will calculate differences for the individual currency balances for each open foreign currency transaction and post it to the unrealised gains/losses account. This will also adjust the sub-ledger entries (i.e., Vendor, Customer, Bank Ledger Entries). You can run this by clicking Adjust Exchange Rate on the Currencies Home Ribbon tab as shown below:
This will open a request page which will enable you to select the date range and as well as other filters to process the adjustment.
You can leave the Starting Date to blank so it will include any open entry prior to the End Date. The Posting Date is important as this will determine the effective exchange rate that will be the basis of calculating the currency fluctuations. In my sample, I have used 31/8/2016 which is the same with the End Date. I have also filtered the Currency Code so it will only adjust USD transactions.
Note: Make sure Adjust Customer, Vendor and Bank Accounts tick box is checked so it will also adjust the sub-ledger entries and tally with the G/L entries.
2.1 Adjust Exchange Rate – Entries
Once the batch job processing is completed, it will be posting the entries to reflect the unrealised gains/loss.
In my sample, the sales invoice posted on 1/8/16 has recorded $5,000 AUD in the receivables account. With the new exchange rate on 31/8/16, your receivables for the unpaid sales invoice must be $8,000 AUD. NAV posts an Unrealised Foreign Currency Gain and increased the receivables account by $3,000 AUD to achieve this.
Notice that it also adjusted the Remaining LCY Amount in the Customer Ledger Balance to reflect the current LCY conversion.
3. Receive payment for the Sales Invoice
• Posting Date = 1/9/2016
• Effective exchange rate= 1 AUD = 2.5 USD
3.1 Foreign Currency Payment – Entries
In the payment entries below, the system posted a $4,000 AUD payment in the bank using the effective rate on 1/9/16. It has also reversed the Unrealised Foreign Currency Gain recorded during the Adjust Exchange rate job run. Then, as the LCY conversion of the applied payment is less than the original LCY of the invoice, NAV posted a Realised FX Loss of $1,000 AUD ($5,000 – $4,000).
The receivable also was reversed and the invoice in the customer ledger entry has been fully applied.
It is also worth mentioning that Microsoft Dynamics NAV 2016 and 2017 have the ability to connect to a currency service, such as Yahoo, to automatically update the exchange rates.