A:

The compound annual growth rate, or CAGR for short, measures the return on an investment over a certain period of time. Below is an overview of how to calculate it by hand, and in Excel.

Check out our full list of FAQs on calculating key financial measures in Microsoft Excel.

CAGR Defined

The concept of CAGR is relatively straightforward and requires only three primary inputs: an investment’s beginning value, ending value, and the time period. Online tools, including Investopedia’s CAGR calculator, will give the CAGR when entering these three values. The CAGR represents the growth rate of an initial investment assuming it is compounding by the period of time specified. Specifically, the formula is:

CAGR in Excel

The CAGR formula can be recreated in Excel. The formula to use is:

= ((FV/PV)^(1/n)) – 1

Where FV is the investment’s ending value, PV is its ending value, and n is the # of years.

The XIRR function in Excel also calculates an internal rate of return (IRR) that can also be used to calculate the CAGR. The XIRR function is:

XIRR(values,dates, guess)

Again, what is needed are the beginning and ending investing values, and date periods. This function is more flexible as it can include multiple values and dates beyond just the ending and starting value. The one rub to this approach is the user must enter an estimated CAGR value, but this also helps intuitively understand the return values.

A Brief Example of the CAGR Calculation

Assume an investment’s beginning value is $1,000 and it grows to $5,000 in 10 years. The CAGR calculation is as follows:

= ((5,000/1,000)^(1/5)) – 1

The CAGR is 17.4% and details that the investment grew at this annual rate for a decade.

Limitations of the CAGR

The CAGR is superior to average returns because it considers the fact that investment returns compound over time. One limitation is that it assumes a smoothed return over the time period measured. In reality, investments experience significant short-term ups and downs. CAGR is also subject to manipulation as the time period used can be controlled by the user. For instance, a five-year return period can be shifted by a year to avoid a negative period (such as 2008), or to include a period of strong performance (such as 2013).

The Bottom Line

The CAGR helps frame 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 different returns across periods, as well as for comparing investments in different asset classes.

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?

RELATED FAQS

  1. 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 ...
  2. What is the formula for calculating earnings per share (EPS) in Excel?

    Understand the basics of the earnings per share ratio and how this important financial metric is calculated in Excel and ...
  3. How do I calculate the debt-to-equity ratio in Excel?

    Understand the basics of the debt to equity ratio, how it is interpreted as a measure of financial stability and how it is ...
  4. What is the formula for calculating the current ratio in Excel?

    Understand the basics of the current ratio, including its use and interpretation as a financial metric and how it is calculated ...
RELATED TERMS
  1. Compound Annual Growth Rate - CAGR

    The year-over-year growth rate of an investment over a specified ...
  2. Enterprise Value (EV)

    A measure of a company's value, often used as an alternative ...
  3. Nonadmitted Balance

    An item on an insurer’s balance sheet that represents reinsured ...
  4. Best's Capital Adequacy Relativity (BCAR)

    A rating of an insurance company’s balance sheet strength. Best’s ...
  5. Deferred Tax Asset

    A deferred tax asset is an asset on a company's balance sheet ...
  6. Earnings Per Share - EPS

    The portion of a company's profit allocated to each outstanding ...

You May Also Like

Related Articles
  1. Fundamental Analysis

    How do you calculate CAGR?

  2. Fundamental Analysis

    What is the formula for calculating ...

  3. Fundamental Analysis

    How do I calculate the debt-to-equity ...

  4. Fundamental Analysis

    How can I calculate the acid test ratio ...

  5. Fundamental Analysis

    What is the formula for calculating ...

Trading Center
×

You are using adblocking software

Want access to all of Investopedia? Add us to your “whitelist”
so you'll never miss a feature!