# Geometric Returns values less than -100%

I am trying to find the geometric return for semi-annual log-returns in Excel. However, I do not know how to handle values less than -100%....

Dates   RETURNS
6/29/00 17:00   -3.29%
12/28/00 16:00  26.05%
6/28/01 17:00   -13.26%
12/30/01 16:00  -3.42%
6/27/02 17:00   0.00%
12/30/02 16:00  -5.69%
6/29/03 17:00   5.75%
12/30/03 16:00  3.04%
6/29/04 17:00   -0.01%
12/30/04 16:00  -6.02%
6/29/05 17:00   -23.06%
12/29/05 16:00  -15.01%
6/29/06 17:00   9.18%
12/28/06 16:00  0.00%
6/28/07 17:00   0.44%
12/30/07 16:00  0.00%
6/29/08 17:00   11.34%
12/30/08 16:00  -2.21%
6/29/09 17:00   0.00%
12/30/09 16:00  0.00%
6/29/10 17:00   17.45%
12/30/10 16:00  -160.06%
6/29/11 17:00   0.90%
12/29/11 16:00  9.34%
6/28/12 17:00   6.74%
12/30/12 16:00  2.29%
6/27/13 17:00   6.05%
12/30/13 16:00  -2.50%
6/29/14 17:00   0.03%
12/8/14 16:00   1.05%


Assuming this code is pasted in Excel in A1, then I calculate the Geometric Return in B34 by: {=PRODUCT(1+B2:B31)^(1/COUNT(B2:B31))-1}

You are calculating the geometric mean as if these are arithmetic returns. If you let $$L_{t}\equiv \frac{P_{t}}{P_{t-1}}-1$$ and $$C_{t}\equiv log(P_{t})-log(P_{t-1})$$ then $$L_{t}=exp\left(C_{t}\right)-1$$

Thus, to calculate the geometric return on log returns, you would recognize that $$\prod\left(1+L_{t}\right)=exp\left(\sum C_{t}\right)$$

The equivalent formula (correcting for the fact that you have semi-annual returns) for log returns would be

EXP(SUM(B2:B31))^(2/COUNT(B2:B31))-1

• excellent! thanks for explaining... However does the output in EXP(SUM(B2:B31))^(2/COUNT(B2:B31))-1 return the semi-annual % or annual % ? thanks – Jason Guevara Jan 16 '15 at 23:35
• The 2 makes it annualized. – John Jan 16 '15 at 23:36
• Wait $\frac{P_t}{P_{t-1}}$ is not equal to $exp(log(P_t-P_{t-1}))$ as you stated – Kamster Jan 17 '15 at 4:24
• I think you meant $log(frac{P_t}{P_{t-1}})$ since that is the log growth rate – Kamster Jan 17 '15 at 4:30
• @Kamster Thanks for the correction. I mean the log differences, but didn't do the Latex right. – John Jan 17 '15 at 7:44