Calculating Geometric mean

I need to annualize daily returns for about 120 firms for over a period of 10 years. I chose to calculate the geometric return because 1) it is the actual return 2) to avoid the asymmetric effect of negative and positive returns.

My problem is that the geometric return that i have calculated doesn't match the actual yearly rate of return. Here is what I've done;

1. Calculate simple returns by (Xt0-Xt-1)/Xt-1
2. Check how many days the stock was being traded for, because the rule of thumb 252 is to ambiguous. This is because some stocks get a listing in e.g. March and would therefore be unfair to multiply by 252.
3. Use the formula =sumproduct(geomean(aa1:zz36+1)-1) to come up with daily geomean. This should do the trick regarding the negative values, but maybe my result are biased because of the amount of 'zero' returns due to holiday/(un)listing. I also thought of this by using the count.if'0' and subtract this number by the total days in a year.
4. annualize the daily geomean by multiplying with counted days traded.

I thought all was well until i checked my first observation. This fund got listed 2-march-2010 for 100 and the year end was 161.6. Using the above methodology yield a daily geomean of 0.009915 and trading days of 201. Multiplying by 201 this results in annualized geomean of 1.9931. This is obvious incorrect.

I could also use the formula ((Xt0/Xt-1)^(1/tds)-1)*tds - which result in the correct answer of .4805- but is devious due to irregularities of dataset..

Can somebody see what I am doing wrong here. As I mentioned above, I do have have some zero value because I download daily prices per year. I then select 1-Jan to 31-Dec. How can I overcome the problem with the zero values? I tried empty space, but than the formula doesn't work.

-

I'm currently also using daily returns which I want to annualize. This is my approach:

1. For every month, I calculate the simple return using the formula: (end-of-month closing price / beginning-of-month closing price) - 1.

2. I use the Excel formula somproduct(geomean(A1:A12+1)-1) to find the monthly compounded return.

3. Finally, I annualize the result of step 2 by 12 (months).

The reason for cutting up the year into months is that when I use the formula (31 December closing price / 1 January closing price) ^(1/252) * 252, the result doesn't represent the situation because of the high price swings.

Hope this helps, but I'm not sure if it's correct. Maybe someone can verify this approach.

-

When you say the return on firms, I take it you mean the change in the stock price of firms. If you were talking about the return of firms' investment strategies, then you would have to deal with cash inflows, which makes the answer more complicated.

If you are having problems, there are two equivalent approaches that should give you the correct answer. In the first, only focus on the price of the stock at the end points to compute the geometric return. In the second, take the average of the log returns over the appropriate horizon and frequency, multiply by whatever constant to annualize the log returns (252 in your case), and then convert from log by $exp(x)-1$, where $x$ is the adjusted log return.

-
What I meant is indeed the change in stock price of the firms whereby I make use of return index (RI) of the relevant firm. But isn't it, from a processing point of view, not easier to use the formula 'sumproduct(geomean(aa1:zz36+1)-1)' wherby making use of the simple returns than to check when each firm individually started/stopped trading for each year. The main stumbling block when applying the first method are the zero values. The second approach is very devious. Any advice how to cope with the zeros? Rgds –  Gekke Henkie Apr 24 '13 at 9:31
You could convert the return index into a total return index. –  John Apr 24 '13 at 14:08
Thnx for the reply. What about David's approach? It does take the disadvantages away of the amount of zeros. But what is the added value of using daily returns when applying his approach? and should I still use the daily simple returns or monthly simple returns to calculate st. dev? –  Gekke Henkie Apr 25 '13 at 13:02