Calculating Geometric Returns from Log Returns

Summary

The document addresses a geometric-return calculation that appears to fail when a dataset includes a reported return below negative one hundred percent. The key issue is that the spreadsheet formula treats the observations as arithmetic simple returns, even though the data are described as log returns. Applying the simple-return product formula directly can therefore produce an invalid result.

The explanation defines simple returns from price ratios and log returns from differences in log prices, then uses the exponential relationship between them. Summing log returns and exponentiating gives the compounded growth factor; annualizing or converting to a per-period figure requires accounting for the semiannual frequency. The provided calculation uses the number of observations to adjust the aggregate log return to an annual rate. The method depends on the inputs truly being log returns and on the stated sampling frequency; it is not interchangeable with compounding arithmetic returns.

Key ideas

• Simple returns and log returns use different representations and cannot be compounded with the same direct formula.
• A log return equals the logarithm of the corresponding price ratio.
• Summing log returns and exponentiating recovers the compounded growth factor.
• Adjust the aggregate log return for the sampling frequency when expressing a periodicized geometric return.
• Verify that observations are log returns before applying a log-return aggregation method.

Tags

# Geometric Returns values less than -100%


# 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}`

## Answer by John (score 5, accepted)

https://quant.stackexchange.com/a/16244

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
```

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.