There is some errors in the formula used inside the Purchase Invoice module with multi currency for sure. I have not tested with default currency.
We order machinery from our vendor in Europe and had to make a 5% down payment in 2021 at a higher exchange rate than prevailing (1.44140). We made a forward contract for the remaining 95% amount at at even higher rate (1.50300) to pay the supplier in March 2022. The current exchange rate used for this transaction is 1.450481000.
The total invoice value is € 95,750.00 which at the exchange rate considered works out to be CAD $ 138,883.56.
But we made a payment of € 91, 320.00.
To make this payment we used the original deposit of € 4787.50 at 1.44140
and remaining € 86,532.50 at the forward contract rate of 1.5030.
Total composite CAD $136,958.33 for € 91, 320.00
The Advance payment account in EUR was
So one would have expect that the draw on the Advance payment account would be as shown below:
Expected Result:
In order to get the above desired result, the GL entries should have looked as follows:
Below you will see the Advance payment applied to the Purchase Invoice:
But if you see below it does not pick up the exchange rate from the journal entry and shows a huge exchange loss. It assumes a value of 0.0 for the exchange rate and hence the huge exchange loss.
It picks up the exchange rate from the payment entry though:
Below you can see the total purchase invoice value in EUR and CAD $
But we are only paying € 91, 320.00 out of the € 95,750.00 for this invoice at this point.
Once I submit the purchase Invoice, the advance payment account gets messed up. Please see below:
This is because of wrong entries in the General Ledger. For some reason, it takes the entire purchase invoice value of € 95,750.00 at the exchange rate used for the invoice which is CAD $ 138,883.56. Instead of using € 91, 320.00 and calculated CAD $ value of $136,958.33 which is € 4787.50 * 1.44140 + € 86,532.50 * 1.5030
It then tries to compensate the exchange loss through the advance payment account. Instead, it should have done the following:
The $43.48 credit is the exchange gain for the € 4787.50 *(1.450481 - 1,4414)
and the debit of $4544 is the correct value calculated from the payment entry. which makes everything right as below:
the Purchase Invoice Advance entry looks like this:
The exchange rate is 0.00
Journal entry has the Is Advance selected as yes
So I see 4 issues with the purchase invoice advance payment entries:
- Exchange rate not coming through for journal entries
- General ledger entry uses wrong (total invoice value) instead of using total from Advance Payment table
- It also uses the Purchase invoice exchange rate instead of the exchange rate from the advance payment entries.
- The Exchange loss/Gain should not be applied to the advance payment account.
I hope i have been able to explain the issue. I thing this may be a bug and I do not know where or how solve this issue.
I can make it work by entering the correct exchange rate and exchange loss/gain through the database to get the correct loss/gain and then changing the CAD $ values as shown and deleting the second entry for the advance payment account.
I hope this can be resolved through an update and should be a quick one I hope