Foreign currency revaluation – Accounts payable

The basic idea of foreign currency revaluation in any accounting standards like IFRS, IndAS etc. is to adjust open foreign currency transactions which include open invoices, debit notes, credit notes and advance payments. This simply means that if I have to pay the open invoice at the period end what amount of gain/loss I make in terms of accounting/reporting currencies.

Let me explain in detail how Foreign currency revaluation is handled by Microsoft Dynamics 365 for Finance. To demonstrate Foreign currency revaluation, I have created a new vendor account having currency set to EUR. The transactions are being performed in standard Contoso environment within the USMF legal entity. All the transaction are performed on Microsoft Dynamics 365 for Finance version 10.0.13 Platform update 37.

Setup accounts for currency revaluation

The prerequisite setup required for executing foreign currency revaluation is setting up Main accounts for Realized gain, Realized loss, Unrealized gain and Unrealized loss.

If Main accounts for Realized gain, Realized loss, Unrealized gain and Unrealized loss needed at Legal entity level then setup using the following steps and refer below screenshot:

Accounts for currency revaluation – Ledger

• Navigate to General ledger >> Ledger setup >> Ledger
• Assign Main accounts for Realized gain, Realized loss, Unrealized gain and Unrealized loss posting types.

If Realized gain/loss and Unrealized gain/loss tracking is required currency wise than setup the Main accounts using the following steps and screenshot.

Currency revaluation accounts – Currency wise

• Navigate to General ledger >> Currencies >> Currency revaluation accounts
• Filter the currency code for which Main account setup is required e.g. EUR.
• Select the ledger for which currency revaluation accounts setup is required for selected currency code.
• Assign Main accounts for Realized gain, Realized loss, Unrealized gain and Unrealized loss posting types.

We saw that Currency revaluation accounts can be defined at two places and hence now the question is which accounts are used for posting realized/unrealized gain or realized/unrealized loss. The answer to this question is as below:

When Foreign currency revaluation for Account payable, Accounts receivable, Cash/Bank or General ledger is processed Microsoft Dynamics 365 Finance gives first preference to the Currency revaluation accounts defined for each currency, if accounts are found at currency level then they are used, else Currency revaluation accounts from Ledger are used for posting foreign currency realized/unrealized gain or realized/unrealized loss.

Setup currency exchange rate

• Navigate to General ledger >> Currencies >> Currency exchange rates or simply enter “Currency exchange rates” in the search box.
• Select Default from Exchange rate type drop-down field
• Filter the desired currency pair from the grid
• Click Add button, then enter the Start date and Exchange rate value

Post vendor invoice

• Setup currency exchange rate for USD-EUR pair for the date in which the vendor invoice is accounted (100 USD = 85 EURO)
• Navigate to Accounts payable >> Invoices >> Invoice journal and then click New button
• Select journal name and click Lines button
• Enter line as per below table. Set Offset account type to Ledger and select any expense account in the Offset account field.

Date	Account type	Account	Invoice date	Invoice	Debit	Credit	Currency	Exchange rate
05-10-2020	Vendor	GT-FX-V-001	05-10-2020	INV-001	0.00	1,000.00	EUR	117.64706

Voucher	Date	Ledger account	Account name	Currency	Amount in transaction currency	Amount	Amount in reporting currency	Posting type
APIN000050	05-10-2020	410110	Freight expenses	EUR	1,000.00	1,176.47	1,176.47	Ledger journal
APIN000050	05-10-2020	200100	Accounts Payable	EUR	-1,000.00	-1,176.47	-1,176.47	Vendor balance

Voucher	Date	Invoice	Description	Amount in transaction currency	Balance in transaction currency	Currency	Amount	Balance	Amount in reporting currency	Balance in reporting currency
APIN000050	05-10-2020	INV-001		-1,000.00	-1,000.00	EUR	-1,176.47	-1,176.47	-1,176.47	-1,176.47

Process Foreign currency revaluation

At each period end before Foreign currency revaluation process is executed make sure that following entries are accounted.

• Make sure that all invoices of Foreign vendors are entered and posted.
• Make sure that all Payments of Foreign vendors are entered, posted and settled against the invoices.

Follow below steps for processing Foreign currency revaluation process:

• Setup exchange rates for USD-EUR pair for the period end date. In our example it is 10/31/2020 (mm/dd/yyyy format). Let’s keep the exchange rate to 86 EUR = 100 USD.
• Navigate to Accounts payable >> Periodic tasks >> Foreign currency revaluation
• Click Foreign currency revaluation button to open Foreign currency revaluation dialog box

• Explaination of parameters available on Foreign currency revaluation dialog box
  • Method: Select Standard
  • Considered date: Considered date is an important parameter. Enter period end date here. In our example put 10/31/2020 (mm/dd/yyyy format). Microsoft Dynamics 365 for Finance will consider all open vendor transactions as on Considered date and the resulting accounting voucher are posted in Considered date.
  • Date of rate: To calculate foreign currency gain/loss system needs exchange rate according to which all open vendor transactions will be adjusted. This is the date for which Microsoft Dynamics 365 for Finance searches for the exchange rate from the Currency exchange rates setup table. This should be set to the period end date 10/31/2020 (mm/dd/yyyy format).
  • Use posting profile from: When foreign currency revaluation adjustments are posted system fetches gain/loss accounts from either Currency revaluation accounts setup or from Ledger setup which forms one side of the accounting voucher and the account on second side of the accounting voucher is taken as per setup in this field. If you set this field to “Posting” then Microsoft Dynamics 365 for Finance will take account from each posted transactions, but if you set this field to “Select” then the accounts are taken from vendor posting profile selected in next parameter called “Posting profile”
  • Posting profile: This field is only relevant if “Use posting profile from” is set to select. The dropdown shows all the available Vendor posting profiles here. For posting foreign currency adjustments on Vendor side the accounts are taken from selected vendor posting profile in this field.
  • Dimension: Three options None, Table and Posting are available. If None is selected no financial dimensions are used for posting foreign currency adjustments (this is not at all recommended). Selection of Table means financial dimensions from Vendor record are used for posting foreign currency revaluations. The best recommended setup here is Posting, this means dimensions are taken from each posted vendor transaction and used for posting Foreign currency revaluation adjustments.
  • Print: If you want to print Foreign currency revaluation reports at the end of process then select this check box.
• Click Ok button to begin the foreign currency revaluation process.
• When Foreign currency revaluation process is completed system will prompt for printing Foreign currency revaluation report. Click Ok button to generate the Vendor – foreign currency revaluation report.

Vendor – foreign currency revaluation report

What does Vendor – foreign currency revaluation report represent? Let me explain:

1. It is a currency wise and Vendor wise report showing current open balance, new open balance after foreign currency adjustment and the difference amount realized as unrealized gain/loss both in Accounting currency and Reporting currency.
2. Balances in accounting and reporting currencies are the same if Accounting currency and Reporting currency on the ledger are the same else they will also differ.
3. Currency wise subtotal row which also included exchange rate considered for adjustment for both accounting currency and reporting currency.
4. Grand total row for showing the total adjustment amount in both accounting currency and reporting currency.
5. The summary section shows all the Main accounts impacted during the foreign currency revaluation process. The report currently is not showing the amount in reporting currencies in the summary section which looks like a bug.
6. The unfortunate thing is this report can only be printed while processing Foreign currency revaluation. After posting foreign currency revaluation there is no option available to print it (we have customized it for one of our clients so that it can be printed any time).

Let’s examine foreign currency revaluation voucher

Now let’s have a look at foreign currency revaluation voucher. The voucher is as shown in below table.

• The invoice amount in accounting and transaction currency before revaluation was 1176.47 (= 1000 * 100 / 85).
• After foreign currency revaluation invoice amount is revised to 1162.79 (= 1000 * 100 / 86) due to fall in exchange rate. At time of Invoice it was 1 EUR = 1.17647 USD and now at the period end it is 1 EUR = 1.16279 USD.
• The voucher doesn’t contain any amount in the transaction currency. The adjustment only happens for accounting currency and reporting currency, this is because even after revaluation due amount to vendor remains 1000.00 EUR. First two lines are for accounting currency and the last two are for reporting currency.
• The accounts used for posting unrealized gain/loss (second line in voucher) in accounting currency are taken from Currency revaluation accounts form. But for posting unrealized gain/loss in reporting currency the accounts (fourth line in voucher) from Ledger are used, this looks like a bug (the issue has been reported to Microsoft. You can monitor the and subscribe for notification at this link. The fix shall be most probably provided with version Microsoft Dynamics 365 for Finance release 10.0.17).

Voucher	Date	Ledger account	Account name	Currency	Amount in transaction currency	Amount	Amount in reporting currency	Posting type
130000000	31-10-2020	200100–	Accounts payable	EUR	0.00	13.68	0.00	Vendor balance
130000000	31-10-2020	310101–	Unrealized gain for EUR	EUR	0.00	-13.68	0.00	Exchange rate gain
130000000	31-10-2020	200100–	Accounts payable	EUR	0.00	0.00	13.68	Vendor balance
130000000	31-10-2020	801600—-	Currency Adjustment Profits – Unrealized	EUR	0.00	0.00	-13.68	Exchange rate gain

What’s the impact on vendor transactions?

A new vendor transaction line with Transaction type foreign currency revaluation is posted during the Foreign currency revaluation process (see second line). This additional foreign currency revaluation transaction gets settled with the open invoice in terms of accounting currency and reporting currency, due to which the net outstanding balance in accounting currency and reporting currency becomes 1162.79 (-1162.79 = -1176.47 + 13.68).

Voucher	Date	Invoice	Description	Amount in transaction currency	Balance in transaction currency	Currency	Amount	Balance	Amount in reporting currency	Balance in reporting currency
APIN000050	05-10-2020	INV-001		-1,000.00	-1,000.00	EUR	-1,176.47	-1,162.79	-1,176.47	-1,162.79
130000000	31-10-2020	INV-001	Foreign currency revaluation on 31-10-2020	0.00	0.00	EUR	13.68	0.00	13.68	0.00

What happens when I pay the Invoice?

Let’s understand what happens when I pay the invoice during the next month on 5-11-2020. Setup currency exchange rate of 100 USD = 84 EUR and then post the payment journal after settling it with the open vendor invoice of 1000.00 EUR. When payment against the invoice is posted following activities happen:

• The voucher for payment journal is posted with amount in transaction currency equal to 1000 EUR.
• Amount posted in accounting currency and reporting currency 1190.48 (1000 * 100 / 84). If accounting and reporting currencies are different then the amount posted in accounting and reporting currency might differ.

Voucher	Date	Ledger account	Account name	Currency	Amount in transaction currency	Amount	Amount in reporting currency	Posting type
APPM001210	05-11-2020	110110-001-	Bank Account – USD	EUR	-1,000.00	-1,190.48	-1,190.48	Bank
APPM001210	05-11-2020	200100–	Accounts Payable – Domestic	EUR	1,000.00	1,190.48	1,190.48	Vendor balance

• A related voucher is posted as shown below. Last four lines of the voucher is the reversal of unrealized gain posted during the last foreign currency revaluation process.
• The invoice amount in accounting and reporting currency is 1176.47 USD (1000 * 100 / 85). The payment amount in accounting and reporting currency is 1190.48 USD (1000 * 100 / 84). As a result the difference amount of 14.01 is posted to the Realized exchange rate loss account.

Voucher	Date	Ledger account	Account name	Currency	Amount in transaction currency	Amount	Amount in reporting currency	Posting type
140000656	05-11-2020	200100	Accounts Payable – Domestic	EUR	0.00	-14.01	0.00	Vendor balance
140000656	05-11-2020	310202	Realized loss for EUR	EUR	0.00	14.01	0.00	Exchange rate loss
140000656	05-11-2020	200100	Accounts Payable – Domestic	EUR	0.00	0.00	-14.01	Vendor balance
140000656	05-11-2020	801300	Currency Adjustment Loss – Realized	EUR	0.00	0.00	14.01	Exchange rate loss
140000656	05-11-2020	200100	Accounts Payable – Domestic	EUR	0.00	-13.68	0.00	Vendor balance
140000656	05-11-2020	310101	Unrealized gain for EUR	EUR	0.00	13.68	0.00	Exchange rate gain
140000656	05-11-2020	200100	Accounts Payable – Domestic	EUR	0.00	0.00	-13.68	Vendor balance
140000656	05-11-2020	801600	Currency Adjustment Profits – Unrealized	EUR	0.00	0.00	13.68	Exchange rate gain

Let’s examine vendor transactions

The foreign currency revaluation line posted earlier during the last foreign currency revaluation gets reversed when the payment is settled with the invoice

A new vendor transaction with transaction type Foreign currency revaluation is added to the vendor transactions. This new transaction having amount -14.01 (1000 * 100 / 85 – 1000 * 100 / 84) in only posted in accounting and reporting currency. It is settled with original invoice having the amount of -1176.47 and hence the net balance in accounting and reporting currency becomes 1190.48, which eventually gets settled with the payment transaction with the amount of 1190.48 = 1000 * 100 / 84. So, finally, the outstanding balance in all three currencies become zero now.

Voucher	Date	Invoice	Description	Amount in transaction currency	Balance in transaction currency	Currency	Amount	Balance	Amount in reporting currency	Balance in reporting currency
APPM001210	05-11-2020			1,000.00	0.00	EUR	1,190.48	0.00	1,190.48	0.00
140000656	05-11-2020	INV-001		0.00	0.00	EUR	-13.68	0.00	-13.68	0.00
140000656	05-11-2020	INV-001		0.00	0.00	EUR	-14.01	0.00	-14.01	0.00
130000000	31-10-2020	INV-001	Foreign currency revaluation on 31-10-2020	0.00	0.00	EUR	13.68	0.00	13.68	0.00
APIN000050	05-10-2020	INV-001		-1,000.00	0.00	EUR	-1,176.47	0.00	-1,176.47	0.00

A few more scenarios

The foreign currency revaluation scenario discussed above is a standard one where the vendor invoice is open for a month, revalued and in next month it is paid. But in real life, there are a few more variations to the process or sequence. I will try to list out most of them here:

Invoice remains open for more than a month

• Unrealized gain/loss is posted during the foreign currency revaluation process run at the first-month end.
• The consecutive foreign currency revaluation process reversed the unrealized gain/loss posted during the last foreign currency revaluation run. As a result of the current foreign currency revaluation process, new unrealized gain/loss are posted.

Partial payment against revalued invoice

When partial payment is processed against an revalued open vendor invoice Microsoft Dynamics 365 for Finance performs following activities:

• Unrealized gain/loss is reversed in proportion to the amount settled with the invoice.
• Realized gain/loss is posted in proportion to the amount settled with the invoice.
• The remaining outstanding amount against the invoice gets revalued during the next foreign currency revaluation process.

Invoice paid in next month and the revalued at current month end

Firstly, let me explain this case in detail. Here the invoice is posted in the current month, which is then paid (settled) in the next month before foreign currency revaluation for current month-end is processed.

• When the invoice is paid (settled) the realized gain/loss is posted in the next period.
• Foreign currency revaluation for the current period end is processed which results of posting of unrealized gain/loss.
• The fact is that the invoice is already paid and as a result of this realized gain/loss is already posted.
• To avoid posting of both realized gain/loss and unrealized gain/loss for the same invoice what Microsoft Dynamics 365 for Finance does is it reverses the unrealized gain/loss posted in the same date when the Invoice was paid. As a result, it is ensured that only the realized gain/loss gets accounted and unrealized gain/loss gets reversed.

---

The excel workbook for test case can be downloaded from below link: