Translating a List of Monthly Payments from One Currency to Another

Question

I have a list of monthly payments in Excel that looks like this:

01/18/2005  340.00
07/25/2005  270.00
05/10/2006  500.00
03/30/2007  730.00
10/01/2007  120.00

If the payment amounts in the second column to the right are in Euros, and I want to add a column to the right of it that shows the amounts in USD, what is the best method to do the conversion from the first currency into the second?

Options I can imagine:

1. Use the historical EUR/USD rate for each date in the list
2. Use the average historical EUR/USD rate for each year indicated in the list

Questions:

Which of these approaches is "standard"?
Are there other ways to do it?
What are the advantages and disadvantages of each approach?

Thanks!

Answer 1

If the company primarily operates in USD, and the above transactions are results from foreign operations in Europe, then By IAS 21.21:

A foreign currency transaction should be recorded initially at the rate of exchange at the date of the transaction (use of averages is permitted if they are a reasonable approximation of actual).

Since the payments are "uneven" with respect to time intervals and amounts, using yearly average is not acceptable. You should look up the daily closing rates of each day.

Answer 2

Sorry for a shameless plug of a tool I wrote, but since it's free and is highly relevant to the question I feel I should mention it here: http://currency.foxjump.ca is designed to do exactly the type of multi-date currency conversion that you'd need here. You copy a list of payments from a spreadsheet (in exactly the format that you listed in the question, Excel and Google Spreadsheets are both supported), paste the list into the tool, and it gives you the converted currency column (which you can paste back into your spreadsheet).