Graphed LogoGraphed
Login

How to Calculate Geometric Average Return in Excel

Cody Schneider•

Calculating the average return of an investment seems simple, but using a basic average can give you a dangerously misleading picture of your actual performance. To get the real story, you need to use the geometric average, which accounts for the magic (and sometimes painful) effect of compounding. This guide will walk you through exactly how to calculate the geometric average return in Excel, step-by-step.

What is Geometric Average Return, and Why Does it Matter?

The geometric average return, sometimes called the time-weighted rate of return, is a way to calculate the average rate of return on an investment over multiple time periods. Unlike a simple arithmetic average that just adds up the returns and divides, the geometric average considers how the returns from one period compound and affect the base for the next period.

This is crucial because investment returns don't happen in a vacuum - a 10% gain in year two is calculated on the new, higher balance you had after year one. Conversely, a 10% loss is calculated on your current value, not your initial investment. The geometric average accurately reflects this reality.

Arithmetic vs. Geometric Average: A Simple Example

Let's say you invest $1,000. In Year 1, you have an amazing year and see a 100% return. In Year 2, the market corrects, and you lose 50%.

  • Your Starting Capital: $1,000

  • Year 1 Return (+100%): Your investment grows to $2,000.

  • Year 2 Return (-50%): You lose 50% of your $2,000, bringing you back down to $1,000.

At the end of two years, you’re right back where you started. Your actual average return is 0%.

Now, let's see what the two types of averages would tell you:

  • Arithmetic Average: (100% + (-50%)) / 2 = 25% per year. This calculation suggests your investment is growing at a fantastic 25% annually, which is clearly wrong.

  • Geometric Average: It will correctly calculate your return as 0% per year, reflecting the fact that you ended up right where you began.

For any investment with fluctuating returns (which is pretty much all of them), the geometric average provides a much truer measure of compound annual growth rate (CAGR).

Setting Up Your Data in Excel

Before we jump into the formulas, you need to structure your data properly. It’s simple. All you need is two columns: one for the time period and one for the return during that period. It’s best practice to express returns as decimals for easier calculations.

Let's use a 5-year investment as our example for the rest of this tutorial. Here's our data:

  • Year 1: 15%

  • Year 2: -8%

  • Year 3: 20%

  • Year 4: 5%

  • Year 5: -2%

Here’s how you would set this up in Excel:

  1. In Column A, list the periods (e.g., Year 1, Year 2, etc.).

  2. In Column B, list the returns as percentages (e.g., 15%, -8%).

  3. In Column C, convert these percentages to decimals. The formula in cell C2 would be =B2/100, which you can then drag down for the other years.

Your sheet should look like this:

(Image representing an Excel sheet with the data structured as described)

Why create this decimal column? Because all of the following Excel formulas rely on the decimal format (e.g., 0.15 instead of 15%).

Method 1: The Easiest Way with the GEOMEAN Function

Excel has a built-in function, GEOMEAN, that does most of the heavy lifting. However, it requires one small preparatory step: it can't evaluate the geometric mean on negative numbers. To work around this, we need to create a "growth factor" column.

The growth factor is simply 1 + the period's return. A 15% return is a growth factor of 1.15, while an 8% loss is a growth factor of 0.92.

Step-by-Step with GEOMEAN:

Step 1: Create a Growth Factor ColumnUsing our previous setup, create a new column (Column D) labeled "Growth Factor." In cell D2, type the formula:

=1+C2

Drag this formula down to apply it to all your returns.

Step 2: Use the GEOMEAN FunctionNow pick any empty cell where you want your result. Type the following formula:

=GEOMEAN(D2:D6)

This calculates the geometric mean of your growth factors. The result will be something like 1.0556. This is the average growth factor, but not the average return yet.

Step 3: Subtract 1 to Get the Final ReturnNow, simply modify the formula to subtract 1. This converts the average growth factor back into an average return rate.

=GEOMEAN(D2:D6)-1

Step 4: Format as PercentageThe result will be a decimal (e.g., 0.0556). To make it readable, select the cell, right-click, choose "Format Cells," and select "Percentage." Set the decimal places to two for a clean look.

You'll see your geometric average return: 5.57%.

This is the most direct and reliable way to calculate the geometric average in Excel.

Method 2: Using the PRODUCT and COUNT Functions

This method doesn't use the dedicated GEOMEAN function but instead reconstructs the underlying mathematical formula in Excel. It's a great way to understand what's actually happening behind the scenes.

The manual formula for geometric mean is: [(1 + R1) * (1 + R2) * ... * (1 + Rn)]^(1/n) - 1

Let's build this with Excel functions.

Step-by-Step with PRODUCT:

Step 1: Create the Growth Factor Column (If You Haven't Already)Just like in the first method, you need that helper column where each cell is =1+C2, =1+C3, etc.

Step 2: Multiply the Growth Factors Together with PRODUCTThe PRODUCT function multiplies all numbers in a given range. In a blank cell, this formula will handle the (1+R1)*(1+R2)*... part:

=PRODUCT(D2:D6)

Step 3: Find the Number of Periods (n) with COUNTWe need to take the "nth root" of the product, where n is the number of years. The COUNT function can find this for us:

=COUNT(C2:C6)

Step 4: Combine Them to Calculate the RootTo take the nth root, you raise a number to the power of (1/n). So, we combine the PRODUCT and COUNT functions like this:

=PRODUCT(D2:D6)^(1/COUNT(C2:C6))

Step 5: Subtract 1 for the Final ResultJust like before, we subtract 1 to get our final average return.

=PRODUCT(D2:D6)^(1/COUNT(C2:C6))-1

Format the cell as a percentage, and you will get the same answer: 5.57%. This method confirms your understanding of the formula by letting you build it piece by piece.

Method 3: The Advanced Single-Cell Array Formula

Want to look like an Excel expert? You can calculate the geometric average return in a single cell without any helper columns by using an array formula. An array formula performs multiple calculations on a range of cells at once.

Here’s the formula:

=GEOMEAN(1+C2:C6)-1

Look familiar? It’s almost identical to our first method, but here we’re adding 1 to the entire range C2:C6 inside the formula itself. To make this work, you can't just press Enter.

How to Enter an Array Formula:

  1. Type the formula into a blank cell.

  2. Instead of pressing Enter, press Ctrl + Shift + Enter (on Mac, it's Cmd + Shift + Return).

Excel will automatically add curly braces { } around your formula, indicating it has been successfully entered as an array. The formula in the formula bar will look like this:

{=GEOMEAN(1+C2:C6)-1}

This tells Excel to first create a temporary array in its memory where it calculates 1 + return for every cell in your range (C2:C6). Then, it performs the GEOMEAN function on that invisible, temporary array. It's a clean, efficient way to get your answer without cluttering your spreadsheet with extra columns.

You can do the same with the PRODUCT formula:

{=PRODUCT(1+C2:C6)^(1/COUNT(C2:C6))-1}

Remember to format the final result as a percentage!

Common Mistakes to Avoid

  • Mixing Up Periods: Make sure all your returns correspond to consistent time periods. Don't mix monthly returns with annual returns in the same calculation, as the result won't be meaningful.

  • Forgetting to Add 1: The GEOMEAN formula requires positive numbers. Adding 1 to your returns (1+R) converts them into growth factors (e.g., -0.08 becomes 0.92), solving this problem. Forgetting this step will cause a #NUM! error if you have any negative returns.

  • Not Subtracting 1 at the End: Remember that the geometric mean of your growth factors is not your final answer. You must subtract 1 at the end to convert the average growth factor back into an average rate of return.

Final Thoughts

Understanding your investment's true performance is crucial for making smart decisions, and the geometric average return is the right tool for that job. By correctly accounting for compounding, it gives you an accurate annual growth rate that a simple arithmetic average just can’t provide. Using Excel's GEOMEAN, PRODUCT, or array formulas, you can easily pull this powerful metric from your data.

While mastering formulas in Excel is a great skill, sometimes the bigger challenge is constantly gathering, cleaning, and updating the data from different platforms - like ad accounts, sales tools, and e-commerce platforms - just to run these analyses. We built Graphed to solve that exact problem. It connects directly to your marketing and sales data sources, automatically keeping dashboards and reports updated in real-time. Instead of spending hours in spreadsheets, you can ask questions in plain English and get back to focusing on the insights, not the manual calculations.