After the bubble burst in home prices (along with the rest of the U.S. economy), I was wondering what happened to the European investors that were buying up million dollar condos in Florida at the highs. They had two major risks if they were not hedged: housing prices and currency fluctuations.
I used the same analysis I used for home prices to develop the workbook that can be downloaded, called “Currency”. It shows the formula for measuring net return of unhedged foreign investments.
The data sheet is made up of a table with daily prices (2004 to 2014) for the S&P 500, the SX5E (Eurozone Stock Index) and the EUR/USD FX quote. The StartDate and EndDate on the table are for the vertical lines in the two charts. For a better net return dividend payments should be included, but I had a hard enough time finding daily prices for the two stock indices.
User input is easy. There are only three decisions to make:
- What is the starting currency, euro or the dollar (option buttons)
- What date did the initial transaction take place (drop down box)
- What date was the transaction reversed (drop down box)
By selecting the option button that determines the starting currency “Dollar”, it is assumed that a U.S. investor converted U.S. dollars to 100,000 euros on the starting date to invest in the SX5E (Eurozone Stock Index). At the ending date, the stock is sold and converted back from euros to dollars.
By selecting the option button that determines the starting currency “Euro”, it is assumed that a European investors converted euros to 100,000 U.S. dollars on the starting date to invest in the S&P 500 (U.S. stock market index). At the ending date the stock is sold and converted back from dollars to euros.
Everything after that is automated and the net return is calculated showing return worked out on the sheet and the formula. There are also two charts with vertical lines showing the start and end dates of the transactions.
The default input is a European (starting currency euros). On 1/22/2007 the investor converted €77,178.36 into $100,000 and purchased the S&P 500 at a closing price of 1422.95. On 8/10/2009 the investor sold the stock index and converted the dollars back to €50,042.78.
As you can see the investor lost 29.22% in the S&P stock index, plus the dollar fell over that period and there was a currency loss of 8.39%. In this workout the math is ((1+% change in investment) X (1+% change in currency value))-1 for a net return of -35.16% or -15.62% annually (actual/365 basis).
Next, there is another formula that is basically the same, but possibly simpler.
The two charts have vertical lines showing the starting and ending period.
Download workbook “Currency” from:
Downloads Written in Excel 2013