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;
- Calculate simple returns by (Xt0-Xt-1)/Xt-1
- 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.
- 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.
- 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