Compound Annual Growth Rate: Comparing Investments with the Excel CAGR Formula

Does your company offer several types of IRA accounts? Are you unsure of your current IRA strategy? How do you know if the choices you make for your future are the best available to you?

Say you review your IRA returns and consider changing your mix of investments. Five years ago, you invested $10,000 in a mutual fund that is now valued at $16,183. That’s a gain of over $6,000, but it took five years. Is that good?

Now, another company advertises a fund with a five-year annualized growth rate of 12%. Surely it can’t mean that the fund earns 12% every year, for aren’t these investments iffy with frequent ups and downs? How can you compare your existing investment to the fund’s 12% five-year percentage?
The good news is that you can do these calculations yourself, using Excel to find the Compound Annual Growth Rate, or CAGR, of your current investment.

An Example of Compound Growth

Compound growth means that, because your investment’s value gains a little bit each year (you hope!), you have more to invest in the following year. Consider this example:

If you invested $10,000 at the beginning of 2009, you might have been concerned at the end of the year when your $10,000 had fallen to only $9,600, but you would have been elated a year later when it climbed to $12,723. By the end of five years, your $10,000 investment had grown to $16,183.

To calculate the net gain, simply subtract the initial value from the ending value. C9 – C2 = $6,183. This shows the absolute number of dollars gained, but it tells you nothing about the rate of return. Is this gain good? The answer is, “It depends.” Think of it this way: If you invested $75 and gained $6,183, you’d be thrilled; but if you invested several million dollars and had a gain of only $6,183, you’d fire your financial advisor. So is this return on an investment of $10,000 good?

Line 12 adds more information. The return on investment is the net gain divided by the initial investment. This is a 61.83% return. Generally speaking, a higher number is better, but it still doesn’t tell us anything about the timeframe.

The returns each year are volatile in this example, and simply averaging the values in Column B will not produce a useful metric.

The Answer Is in the Compound Annual Growth Rate

The CAGR is an estimate of the rate of return per year. The CAGR is the imaginary rate of growth that, if compounded annually, is equivalent to what your investment achieved over a period of time. It is important to understand that a variable investment does not actually grow this much every year—some years it’s higher, and some year’s it’s lower, as in the example above—but it provides a useful means of comparing one investment to another.

To calculate CAGR, use the formula:

In the example above:

The CAGR for your investment is 10.11% per year. In other words, if you could have invested your $10,000 in a fund that returned exactly 10.11% per year, then at the end of five years, you’d have the same $16,183 that your investment produced. That’s what it means to say that the CAGR is equivalent to the rate of return your investment.

Note that, although this is a useful measure for comparing investments, it is far from the only thing you’ll need to consider. For example, CAGR does not measure volatility. The example above had wild swings from year to year, and some investors don’t have the stomach for it. A savings account or CD returns exactly the same amount every year, just like the imaginary alternative investment, although it’s rare to find a fixed investment that pays as well as stocks and mutual funds. These are some factors to consider when finding your investment style and making your investment choices, and none of these factors are covered by the CAGR formula.

Next Steps

While the CAGR formula is a good start for comparing investments, you’ll want to examine additional formulas and methods to evaluate more complicated investments. For example, you’ll need the Standard Deviation (STDEV.S() or STDEV()) to measure volatility. If you’re adding to your investments periodically, you might need to calculate the Internal Rate of Return using IRR() or XIRR().

The good news is that Excel has an entire section filled with financial calculations. Arm yourself with Excel, continue to increase your knowledge, and perhaps you’ll be the next Warren Buffet.