1
$\begingroup$

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}

$\endgroup$
4
$\begingroup$

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
$\endgroup$
  • $\begingroup$ 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 $\endgroup$ – Jason Guevara Jan 16 '15 at 23:35
  • $\begingroup$ The 2 makes it annualized. $\endgroup$ – John Jan 16 '15 at 23:36
  • $\begingroup$ Wait $\frac{P_t}{P_{t-1}}$ is not equal to $exp(log(P_t-P_{t-1}))$ as you stated $\endgroup$ – Kamster Jan 17 '15 at 4:24
  • $\begingroup$ I think you meant $log(frac{P_t}{P_{t-1}})$ since that is the log growth rate $\endgroup$ – Kamster Jan 17 '15 at 4:30
  • 1
    $\begingroup$ @Kamster Thanks for the correction. I mean the log differences, but didn't do the Latex right. $\endgroup$ – John Jan 17 '15 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.