The most common way to find an average of returns on an investment is to add all of the values and then divide by the number of values. That’s the arithmetic average. You can also calculate the growth rate that would lead from the initial value to the ending value over the same number of periods. That measure is the geometric mean.
- [Instructor] The most common way to find an average of returns on an investment is to add all the values, and then divide by the number of values. That is the arithmetic average. You can also calculate the growth rate. That would lead from the initial value to the ending value over the same number of periods. That measure is the geometric mean. In this movie, I'll show you how to calculate both measures. My sample file is the Means workbook, and you can find it in the chapter one folder of the exercise files collection. This workbook contains a list of growth rates for an investment fund for the years 2009 through 2016.
If I want to find the arithmetic average, I can just use the average function, which you're probably familiar with. I'll click in cell F3, then type in equal sign, and then average. Then, after a left parentheses, I will select cells B2 through B9, type a right parentheses to close out, and press Enter. There, I get my average growth of 8.75%. That's a pretty good rate. The problem, of course, is that there's wide variation.
If we look at the values over in my data set, I'll see that I have positive values from 49%, and another with a positive growth rate of 41%, down to negative 29, or negative 26%. Because of the variation, I should also calculate the geometric mean, which would give the equivalent compound annual growth rate. There are two ways to calculate the geometric mean. The wrong way, which gives an error, and the right way, which will give you the answer you want.
I'll start by showing you the mistake that you can make, and then show you the right way to make the calculation. I'll create my formula in cell F5. I'll click there, type equal, and the function I'll use is GEOMEAN, G-E-O-M-E-A-N, and then I'll select cells B2 through B9, type a right parentheses to close out the argument list, and press Enter. And, I get a number error. The reason that I got the error is because GEOMEAN cannot handle negative numbers.
You'll see that in cells B2 through B9, we have several negative values. The way you can get around that problem is to add one to each of the growth rates. Because an investment can't go down more than 100%, that is lose all it's value, you can add one and get a list of positive values. In cell C2, I will type equal B2 plus one, and Enter. There's my adjusted growth rate of 95%, and I'll click cell C2, and double-click the fill handle at the bottom-right corner to get my list of adjusted growth rates.
Now, I can go over to cell F5, click there, type an equal sign, and, again, I'll use GEOMEAN. Then, in the parentheses, I will select cells C2 through C9, which have my adjusted values, all positive, followed by a right parentheses, and now I will subtract one. Because I added one to each of the growth rates, I can just subtract one from the average and that will adjust the value to where I need it. When I press Enter, I get a geometric mean of 4.62%.
Again, that is the compound annual growth rate that would take this investment to the same end-value as the raw growth rates that we see in column B. The choice of whether to use the arithmetic mean or geometric mean often comes down to a rhetorical choice. If you have an investment that varies widely in return across the years, then the arithmetic mean will often be larger than the geometric mean. In all cases, knowing both the arithmetic and geometric means, as well as other measures, such as variance, standard deviation, and standard error, will all inform your analysis of the data.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart