I learned in a college course a year ago that sometimes brokers lead investors on with some mutual fund returns by providing them with the arithmetic mean of the last 10 years of returns. In reality, the geometric mean is far more accurate and should be used. This article will show you how to calculate the geometric mean of returns in excel and compare it to the arithmetic mean.
Why A Geometric Mean Exists
Geometric means exist because they are more accurate when dealing with negative percentages than arithmetic means are. Let me explain. Let’s assume that we own a stock that is worth $50 per share. The return for year one is 100%. The stock doubles in price to $100. The return for year two is -50%. The stock then drops back down to its original price of $50. Nothing has changed.
Using the arithmetic mean for this small problem we would get an average annual return of 25% when in fact we had a return of 0%. Computing these numbers using excel will give us a geometric return of 0%. This is why geometric mean returns should be used instead.
An arithmetic mean is a very standard way of calculating returns over a period of time. Let’s imagine that our stock got these returns for the past 5 years.
To calculate the arithmetic mean of these stocks, we simply add them all up and divided by the number of returns. 22 -13 + 32 -25 + 18 = 34. 34/5 = 6.8%. Using the arithmetic mean we get an average five year return of 6.8%
We will now use the same five numbers and calculate the geometric mean. We will use excel to do this calculation as it saves a huge amount of time.
In order to do this problem, we must convert the negative percentages to positive numbers. This is the only way to calculate a geometric mean. First, let’s convert these percentages into their decimal equivalents.
- 22% = .22
- -13% = -.13
- 32% = .32
- -25% = -.25
- 18% = .18
Now that we have those, add one to each number so we can get rid of the negatives while maintaining the relationship between the numbers.
- 22% = .22 + 1 = 1.22
- -13% = -.13 + 1 = .87
- 32% = .32 + 1 = 1.32
- -25% = -.25 + 1 = .75
- 18% = .18 + 1 = 1.18
These are the numbers we will plug into excel to calculate our mean. Plug in the numbers 1.22, .87, 1.32, .75 and 1.18 in a vertical column. After you have typed the five numbers in a vertical column, click on the square underneath the numbers. Type in ‘=GEOMEAN(‘. After typing this in you will use your cursor to select the boxes in which you want to compute the geometric mean of the numbers inside. After you select your five squares with our numbers in it, finish off the equation with the ending ‘)’. Your ending box should look something like this: ‘=GEOMEAN(A1:A5)
If done correctly you will get the number 1.044. Just as we had to add 1 to all of our numbers before, we must now take away 1 from the five year average to get .044. When we turn this back into a percentage we get a five year average return of 4.4%. Recall that the arithmetic mean was 6.8%. That is over 2% greater than the actual yield of whatever fund this might be.
The difference between a geometric mean and an arithmetic mean should now be apparent. In the problem we did above, we received a difference of 2.4% between the two. The geometric mean shows us a more realistic look at the average returns. You should now understand how someone can make stock returns look better than they really are. Using the geometric mean to calculate returns will give you a more realistic outlook on what you can expect in the future.
Value Investing for Long-Term Investors
Growth Stocks Vs. Value Stocks
How to Evaluate a Single Value Stock