A:

The compound annual growth rate (CAGR) shows the rate of return of an investment over a certain period of time, expressed in annual percentage terms. Below is an overview of how to calculate it both by hand and by using Microsoft Excel.

What is CAGR?

But first, let's define our terms. The easiest way to think of CAGR is to recognize that over a number of years, the value of something may change – hopefully for the better – but often at an uneven rate. The CAGR provides the one rate that defines the return for the entire measurement period. For example, if we were presented with year-end prices for a stock like:

  • 2015: $100
  • 2016: $120
  • 2017: $125

From year-end 2015 to year-end 2016, the price appreciated by 20% (from $100 to 120). From year-end 2016 to year-end 2017, the price appreciated by 4.17% (from $120 to 125). On a year-over-year basis, these growth rates are different, but we can use the formula below to find a single growth rate for the whole time period.

CAGR requires three inputs: an investment’s beginning value, its ending value and the time period (expressed in years). Online tools, including Investopedia’s CAGR calculator, will give the CAGR when entering these three values. The formula is:

Plugging in the above values we get [(125 / 100)^(1/2) - 1] for a CAGR of 11.8%. Despite the fact that the stock's price increased at different rates each year, its overall growth rate can be defined as 11.8%.

Tips & Tricks for Calculating CAGR

One mistake that's easy to make in figuring CAGR is to incorrectly count the time period For instance, in the above example, there are three calendar years. But since the data is presented as year-end prices, we really we only have two completed years. That's why the equation reads 1/2, not 1/3.

Now, let's say we had a stock whose annual price data was presented in percentage, instead of dollar, terms:

  • 2015: 10%
  • 2016: 15%
  • 2017: -4%

In this case, the data is being shown from the beginning of the year, as in, the entire yearly return in 2015 (10%), the entirely yearly return in 2016 (15%), and the entire yearly return in 2017 (-4%). So when calculating CAGR, we would actually be working with a time period of three years.

We would need to convert these percentages into actual beginning and ending values. This is a good opportunity to use a spreadsheet, since it's easy to add a helper column to convert the percentages into values.

Calculating CAGR in Excel

The math formula is the same as above: you need ending values, beginning values and a length measured in years. Although Excel has a built-in formula, it is far from ideal, so we will explain that last.

Financial modeling best practices require calculations to be transparent and auditable. The trouble with piling all of the calculations into a formula is that you can't easily see what numbers go where, or what numbers are user inputs or hard-coded.

The way to set this up in Excel is to have all the data in one table, then break out the calculations line by line. For example, let's derive the compound annual growth rate of a company's sales over 10 years:

The CAGR of sales for the decade is 5.34%.

A more complex situation arises when the measurement period is not in even years. This is a near-certainty when talking about investment returns, compared to annual sales figures. The solution is to figure out the total completed years, and add them to the partial year (called the stub year).

Let's take the same figures, but have them be stock prices:

Pros and Cons of the CAGR

The CAGR is superior to other calculations, such as average returns, because it takes into account the fact that values compound over time.

On the down side, CAGR dampens the perception of volatility. For instance, let's say you have an investment that's posted these changes over three years:

  • 2015: 25% gain
  • 2016: 40% loss
  • 2017: 54% gain

That's actually a 5% CAGR, but the year-over-year volatility in those returns is huge. The reality is many investments experience significant short-term ups and downs, and by smoothing them out, so to speak, the CAGR might give a numerically accurate, but emotionally misleading, impression of performance. It's like a map that tells correctly informs you your destination is only five miles away, without indicating the bumpy condition of the road.

CAGR is also subject to manipulation depending on the measurement period, which is ultimately (and often arbitrarily) selected. A CAGR can be shifted to avoid a negative year in the stock market (such as 2008), or to include a year of strong performance (such as 2013).

The Bottom Line

The CAGR helps identify the steady rate of return of an investment over a certain period of time. It assumes the investment compounds over the period of time specified, and it is helpful for comparing investments with similar volatility characteristics.

For more information on CAGR, check out What are the main differences between compound annual growth rate (CAGR) and internal rate of return (IRR)? and Why is the compound annual growth rate (CAGR) misleading when assessing long-term growth rates?.

And for more about using Microsoft's spreadsheet software, check out our Guide to Excel for Finance.

RELATED FAQS
  1. Why is the compound annual growth rate (CAGR) misleading when assessing long-term ...

    The compound annual growth rate (CAGR) measures the return on an investment over a certain period of time. Below is an overview ... Read Answer >>
  2. How do you calculate CAGR?

    Find out how to understand and use the compound annual growth rate formula, and find out why it helps put an uneven performance ... Read Answer >>
  3. What are the main differences between compound annual growth rate (CAGR) and internal ...

    The compound annual growth rate (CAGR), measures the return on an investment over a certain period of time. The internal ... Read Answer >>
  4. How do I calculate compound interest using Excel?

    Learn what compound interest is, the formula used to calculate it, and how to calculate it using three different techniques ... Read Answer >>
  5. What is the formula for calculating net present value (NPV) in Excel?

    Understand how net present value is used to estimate the anticipated profitability of projects or investments, and how to ... Read Answer >>
Related Articles
  1. Investing

    The Most Accurate Way To Gauge Returns: The Compound Annual Growth Rate

    The compound annual growth rate, or CAGR for short, represents one of the most accurate ways to calculate and determine returns for individual assets, investment portfolios and anything that ...
  2. Financial Advisor

    The 15 Fastest-Growing RIAs

    Registered investment advisors are consolidating their business and assets. Here are 15 of the fastest-growing RIAs.
  3. Investing

    Learn Simple and Compound Interest

    Interest is defined as the cost of borrowing money, and depending on how it is calculated, it can be classified as simple interest or compound interest.
  4. Financial Advisor

    Compound Annual Growth Rate (CAGR)

    The compound annual growth rate is an important tool for measuring investment performance and comparing it across asset classes. Discover how it is calculated and how it can inform your investment ...
  5. Managing Wealth

    Morgan Stanley On the Trends in Asset Management (MS)

    Learn about trends in the asset management industry and future prospects for its growth, according to a May 2016 Morgan Stanley report.
  6. Investing

    How to Calculate Your Investment Return

    How much are your investments actually returning? Find out why the method of calculation matters.
  7. Investing

    Why NVIDIA's Valuation May Be A Giant Bubble

    Nvidia's Dizzying Ascent: Its rise in market value has far outpaced its revenue growth
  8. Investing

    Calculating Future Value

    Future value is the value of an asset or cash at a specified date in the future that is equivalent in value to a specified sum today.
  9. Retirement

    The Magic Numbers Of Retirement

    Don't forget these numbers when planning for your golden years.
  10. Personal Finance

    Schedule Loan Repayments with Excel Formulas

    Calculate all the particulars of a loan using Excel, and set up a schedule of repayment for a mortgage or any other loan.
RELATED TERMS
  1. Compound Annual Growth Rate - CAGR

    The Compound Annual Growth Rate (CAGR) is the mean annual growth ...
  2. Average Annual Growth Rate - AAGR

    The average increase in the value of an individual investment ...
  3. Compound Interest

    Compound Interest is interest calculated on the initial principal ...
  4. Compound Return

    The rate of return, usually expressed as a percentage, that represents ...
  5. Effective Annual Interest Rate

    Effective Annual Interest Rate is an investment's annual rate ...
  6. Periodic Interest Rate

    The interest rate charged on a loan or realized on an investment ...
Hot Definitions
  1. Salvage Value

    The estimated value that an asset will realize upon its sale at the end of its useful life. The value is used in accounting ...
  2. Cryptocurrency

    A digital or virtual currency that uses cryptography for security. A cryptocurrency is difficult to counterfeit because of ...
  3. Promissory Note

    A financial instrument that contains a written promise by one party to pay another party a definite sum of money either on ...
  4. SEC Form 13F

    A filing with the Securities and Exchange Commission (SEC), also known as the Information Required of Institutional Investment ...
  5. Fixed Asset

    A long-term tangible piece of property that a firm owns and uses in the production of its income and is not expected to be ...
  6. Absolute Advantage

    The ability of a country, individual, company or region to produce a good or service at a lower cost per unit than the cost ...
Trading Center