Calculating Geometric mean

Question

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.

Thanks in advance

Answer 1

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.

Answer 2

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.