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 yearend prices for a stock like:
 2015: $100
 2016: $120
 2017: $125
From yearend 2015 to yearend 2016, the price appreciated by 20% (from $100 to 120). From yearend 2016 to yearend 2017, the price appreciated by 4.17% (from $120 to 125). On a yearoveryear 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 yearend 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 builtin 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 hardcoded.
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 nearcertainty 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 yearoveryear volatility in those returns is huge. The reality is many investments experience significant shortterm 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 longterm growth rates?.
And for more about using Microsoft's spreadsheet software, check out our Guide to Excel for Finance.

What is the formula for calculating net present value (NPV) in Excel?
Net present value is used to estimate the profitability of projects or investments. Here's how to calculate NPV using Microsoft ... Read Answer >> 
How do I use the rule of 72 to calculate continuous compounding?
Find out why the rule of 72 does not accurately reflect the growth caused by continuous compounding, and which number can ... Read Answer >> 
Calculate your portfolio's investment returns
Learn the basic principles underlying the data and calculations used to perform personal rates of return on investment portfolios. Read Answer >> 
What is a good annual return for a mutual fund?
Before investing in mutual funds, it's important to understand individual goals for the investment over a specified time ... Read Answer >> 
How do I calculate a forward rate in Excel?
Learn how to calculate the forward rate using Microsoft Excel if you have access to a zerocoupon spot rate curve for the ... Read Answer >>

Investing
Learn simple and compound interest
Interest is defined as the cost of borrowing money or the rate paid on a deposit to an investor. Interest can be classified as simple interest or compound interest. 
Investing
Read This Before You Sell Southwest Airlines Stock (LUV)
Southwest Airlines (NYSE: LUV) continues to defy expectations. Driven by operational excellence and betterthanexpected results, the company's stock has rocketed nearly 90% in the past year, ... 
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. 
Tech
Blockchain Solutions to Grow 75% Through 2022: IDC
According to International Data Corp.’s forecast, blockchain solutions will hit $11.7B by 2022. 
Investing
How to calculate your investment return
How much are your investments actually returning? The method of calculation can make a significant difference in your true rate of return. 
Investing
Why Amazon's Stock May Continue To Outperform Alibaba's
Amazon's stock will probably outperform Alibaba over the next few years. Here's why. 
Investing
Overcoming Compounding's Dark Side
Understanding how money is made and lost over time can help you improve your returns. 
Personal Finance
Schedule Loan Repayments With Excel Formulas
Learn how to calculate all the particulars of a loan using Excel, and find out how to set up a schedule of repayment for a mortgage or any other loan. 
Retirement
Using Compounding to Boost Retirement Savings
Allowing growth on your investments to compound over time gives you immense returns when saving for retirement. 
Trading
Improve your investing with Excel
Find out how to use Excel, a useful tool for assisting with investment organizations and evaluations.

Compound Annual Growth Rate  CAGR
The Compound Annual Growth Rate (CAGR) is the mean annual growth ... 
Compound Interest
Compound interest is interest calculated on the initial principal ... 
Average Annual Growth Rate (AAGR)
Average annual growth rate is the average increase in the value ... 
Compound Return
The compound return is the rate of return that represents the ... 
Annual Return
Annual return is the compound average rate of return for a stock, ... 
Periodic Interest Rate
The periodic interest rate is the interest rate charged on a ...