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/10)) – 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?

    The compound annual growth rate (CAGR) can be very confusing for beginning investors. It has a complicated and unusual math ... Read Full Answer >>
  2. How do I calculate the debt-to-equity ratio in Excel?

    The debt to equity (D/E) ratio is an important leverage metric in corporate finance. It is a measure of the degree to which ... Read Full Answer >>
  3. What is the formula for calculating earnings per share (EPS) in Excel?

    The earnings per share (EPS) ratio is an important metric used by analysts and traders to establish the financial strength ... Read Full Answer >>
  4. What is the formula for calculating the current ratio in Excel?

    The current ratio is a metric used by the finance industry to assess a company's short-term liquidity. It reflects a company's ... Read Full Answer >>
  5. How can I calculate the acid test ratio in Excel?

    The acid test ratio, commonly called the quick ratio, is a useful accounting metric used to measure a company's ability to ... Read Full Answer >>
  6. What is the formula for calculating the quick ratio in Excel?

    The quick ratio is one of a number of measures of liquidity ratios used to determine a company's ability to pay off its short-term ... Read Full Answer >>
  7. How do you calculate operating cash flow in Excel?

    Calculating free cash flow is useful for investors and lenders to evaluate the success of a company. To create an Excel spreadsheet ... Read Full Answer >>
  8. How do you calculate r-squared in Excel?

    R-squared is a statistical relationship between two series of events. It is also a risk measure that is used in economics ... Read Full Answer >>
  9. How do you use Excel to calculate a debt service coverage ratio (DSCR)?

    There are actually three different kinds of debt service coverage ratios, or DSCR, that can be calculated in Microsoft Excel. ... Read Full Answer >>
  10. How do I calculate yield to maturity in Excel?

    To calculate a bond's yield to maturity in Microsoft Excel, you need to have the correct numeric inputs and enter them into ... Read Full Answer >>
  11. What is the formula for calculating gross profit margin in Excel?

    Gross profit margin is an important metric for evaluating a company's profitability. In short, it measures the amount of ... Read Full Answer >>
  12. Why is the compound annual growth rate (CAGR) misleading when assessing long-term ...

    The compound annual growth rate, or CAGR for short, measures the return on an investment over a certain period of time. Below ... Read Full Answer >>
  13. What are the main differences between compound annual growth rate (CAGR) and internal ...

    The compound annual growth rate, or CAGR for short, measures the return on an investment over a certain period of time. The ... Read Full Answer >>
  14. Why is return on investment (ROI) a bad measure for calculating long-term investments?

    ROI is a performance metric used to evaluate the financial efficiency of an investment, or to compare the relative efficiency ... Read Full Answer >>
Related Articles
  1. Forex Education

    Improve Your Investing With Excel

    Excel is a useful tool to assist with investment organization and evaluation. Find out how to use it.
  2. Forex

    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 ...
  3. Stock Analysis

    Performance Review: Emerging Markets Equities in 2015

    Find out why emerging markets struggled in 2015 and why a half-decade long trend of poor returns is proving optimistic growth investors wrong.
  4. Investing

    Don't Freak Out Over Black Swans; Be Prepared

    Could 2016 be a big year for black swans? Who knows? Here's what black swans are, how they can devastate the unprepared, and how the prepared can emerge unscathed.
  5. Stock Analysis

    Analyzing Sirius XM's Return on Equity (ROE) (SIRI)

    Learn more about the Sirius XM's overall 2015 performance, return on equity performance and future predictions for the company's ROE in 2016 and beyond.
  6. Stock Analysis

    Will Virtusa Corporation's Stock Keep Chugging in 2016? (VRTU)

    Read a thorough review and analysis of Virtusa Corporation's stock looking to project how well the stock is likely to perform for investors in 2016.
  7. Stock Analysis

    Analyzing Porter's Five Forces on JPMorgan Chase (JPM)

    Examine the major money-center bank holding firm, JPMorgan Chase & Company, from the perspective of Porter's five forces model for industry analysis.
  8. Stock Analysis

    Analyzing Dish Network's Return on Equity (ROE) (DISH, TWC)

    Analyze Dish Network's return on equity (ROE), understand why it has vacillated so greatly in recent years and learn what factors are influencing it.
  9. Economics

    Understanding Cost-Volume Profit Analysis

    Business managers use cost-volume profit analysis to gauge the profitability of their company’s products or services.
  10. Fundamental Analysis

    5 Must-Have Metrics For Value Investors

    Focusing on certain fundamental metrics is the best way for value investors to cash in gains. Here are the most important metrics to know.
RELATED TERMS
  1. Compound Annual Growth Rate - CAGR

    The Compound Annual Growth Rate (CAGR) is the mean annual growth ...
  2. Short-Term Debt

    An account shown in the current liabilities portion of a company's ...
  3. IRR Rule

    A measure for evaluating whether to proceed with a project or ...
  4. Profit and Loss Statement (P&L)

    A financial statement that summarizes the revenues, costs and ...
  5. Current Liabilities

    A company's debts or obligations that are due within one year. ...
  6. Percentage Change

    Percentage change is a simple mathematical concept that represents ...
Hot Definitions
  1. Liquidation Margin

    Liquidation margin refers to the value of all of the equity positions in a margin account. If an investor or trader holds ...
  2. Black Swan

    An event or occurrence that deviates beyond what is normally expected of a situation and that would be extremely difficult ...
  3. Inverted Yield Curve

    An interest rate environment in which long-term debt instruments have a lower yield than short-term debt instruments of the ...
  4. Socially Responsible Investment - SRI

    An investment that is considered socially responsible because of the nature of the business the company conducts. Common ...
  5. Presidential Election Cycle (Theory)

    A theory developed by Yale Hirsch that states that U.S. stock markets are weakest in the year following the election of a ...
Trading Center