How to Calculate CAGR in Excel: Formula, RRI Function and Examples

shape
shape
shape
shape
shape
shape
shape
shape
How to Calculate CAGR in Excel: Formula, RRI Function and Examples - featured image

CAGR stands for compound annual growth rate. It is the constant yearly rate at which an investment, a sales figure or a population would have to grow to get from its starting value to its ending value over a number of years, assuming the growth was compounded each year. It smooths out the ups and downs of real life into a single, easy to compare percentage. Investors use it to compare funds, and managers use it to describe the growth of revenue or users. This guide shows the formula, three ways to calculate it in Excel, worked examples with numbers that you can verify, the difference between CAGR and a simple average of growth rates, and how to use the result to project the future.

Updated November 2026

In this guide

  1. The CAGR formula
  2. Example 1: a simple investment
  3. Method 1: the power formula
  4. Method 2: the RRI function
  5. Method 3: the RATE function
  6. Example 2: uneven yearly values
  7. CAGR versus the average growth rate
  8. Build a CAGR table in Excel
  9. Project future values
  10. Find the years needed to reach a target
  11. Where CAGR is used
  12. Common mistakes
  13. Practice problems
  14. Quick recap

The CAGR formula

The formula is CAGR equals the ending value divided by the starting value, raised to the power of one divided by the number of years, minus 1. In symbols, CAGR = (End / Start)^(1 / n) – 1. The number n is the number of periods between the start and the end, which is the number of years, not the number of data points. For example, values for the years 2021 to 2026 are six data points, but only five years of growth. Using the wrong n is the most common mistake in this calculation.

Example 1: a simple investment

Suppose you invested 10,000 and five years later it is worth 16,105. The ratio is 16,105 divided by 10,000, which is 1.6105. Raise it to the power of one fifth, which is 0.2, to get 1.10, and subtract 1 to get 0.10. The CAGR is 10 percent. You can check it by growing 10,000 by 10 percent each year: 11,000, 12,100, 13,310, 14,641 and 16,105. Each year is the previous year times 1.10, which confirms that the steady rate of 10 percent produces the final value.

Method 1: the power formula

Put the starting value in B2 and the ending value in B3, and the number of years in B4. Then use =(B3/B2)^(1/B4)-1. For 10,000, 16,105 and 5, Excel returns 0.1000, which you format as a percentage to see 10.00%. If you have years in a column, you can calculate n with the years themselves, such as =(B7/B2)^(1/(A7-A2))-1, where A2 and A7 hold the first and last year. This formula works in every version of Excel and needs no special function.

Method 2: the RRI function

Excel has a function made for this. RRI(nper, pv, fv) returns the equivalent interest rate for the growth of an investment. For our example, =RRI(5,10000,16105) returns 0.1000, or 10 percent. The arguments are the number of periods, the present value and the future value. RRI is available in Excel 2013 and later. It is shorter and easier to read than the power formula, and it makes the intent clear to anyone who opens your file. Use whichever your team understands better.

Method 3: the RATE function

RATE is a more general function for loans and annuities. For CAGR, set the payment to zero and make the present value negative: =RATE(5,0,-10000,16105). It returns the same 10 percent. The sign matters, because RATE treats money you pay out as negative and money you receive as positive. If both values have the same sign, Excel returns an error. RATE is useful when you also have regular payments, but for a plain growth rate, RRI is simpler.

Example 2: uneven yearly values

Revenue was 100 in year 0, 120 in year 1, 114 in year 2 and 150 in year 3. The CAGR uses only the first and last values and the number of periods, which is 3. The ratio is 150 divided by 100, which is 1.5. The cube root of 1.5 is about 1.1447, so the CAGR is 14.47 percent. The yearly changes were plus 20 percent, minus 5 percent and plus 31.58 percent, so growth was uneven, but the CAGR describes the steady rate that gives the same result. Check: 100 times 1.1447 three times is about 150.

CAGR versus the average growth rate

It is tempting to average the yearly percentages. For the uneven example, the average of 20, minus 5 and 31.58 percent is 15.53 percent, which is higher than the CAGR of 14.47 percent. The simple average overstates the growth, because it ignores compounding. If you grew 100 at 15.53 percent for three years, you would get about 154, which is more than the real 150. The CAGR is the correct measure of the compound result. Always use CAGR when you want to describe the growth of a value over several periods. Our percent change guide explains the single-period case.

Build a CAGR table in Excel

Make a small table with the years in column A and the values in column B. Add a column C for the yearly growth with =B3/B2-1 filled down. At the bottom, calculate the CAGR with the first and last rows. Show the average of column C next to it, so readers can see the difference. Format the growth cells as percentages with one or two decimals. Add a line chart of the values, as described in our line graph guide, to show the trend. A table plus a chart gives a complete picture in one view.

Project future values

Once you have a CAGR, you can project the future, with the caution that growth rarely stays constant. The future value after n years is the current value times one plus the rate, raised to the power of n. With 16,105 now and a rate of 10 percent, the value in two years is 16,105 times 1.21, which is 19,487.05. In Excel: =B3*(1+B5)^2, where B5 holds the CAGR. Use scenarios with a lower and a higher rate, such as 8 and 12 percent, to show a range of results rather than a single prediction.

Find the years needed to reach a target

You can also turn the formula around. To find how long it takes to double at 10 percent, solve 1.10 to the power of n equals 2. In Excel use =NPER(0.1,0,-1,2) or =LN(2)/LN(1.1). Both return about 7.27 years. A quick rule of thumb is the rule of 72: divide 72 by the rate in percent, which gives 7.2 years. The exact and the estimate are close. This is helpful when you want to know how soon a target will be reached at a given growth rate.

Where CAGR is used

Investors use CAGR to compare funds with different holding periods, such as a fund that grew 50 percent in four years and another that grew 80 percent in six. Companies use it for revenue, profit and customers. Economists use it for population and GDP. It is easy to understand and to compare, but it hides the path. Two investments with the same CAGR can have very different risk, because one may have been volatile and the other steady. Use CAGR together with other measures, and do not treat it as a promise of future returns. This is general information, not financial advice.

Common mistakes

The most common mistake is using the number of data points instead of the number of periods, which changes the rate. Another is to use the average of yearly growth rates. A third is to mix up the start and the end values, which gives a negative rate for growth. A fourth is to have a zero or negative starting value, since the formula cannot handle it. A fifth is to forget to format the result as a percentage. A sixth is to compare CAGRs of periods with different lengths without saying so. State the start year and the end year with the result.

Practice problems

Try these. Problem 1: a value grows from 200 to 400 in 5 years, so the CAGR is 2 to the power of 0.2, minus 1, which is about 14.87 percent. Problem 2: sales go from 50 to 80 in 4 years, so the CAGR is 1.6 to the power of 0.25 minus 1, which is about 12.47 percent. Problem 3: use RRI for problem 1 and check the result. Problem 4: project 400 forward 3 years at 14.87 percent, which gives about 606. Problem 5: calculate how long it takes to triple at 10 percent, which is LN(3)/LN(1.1), or about 11.53 years.

Quick recap

CAGR is the steady yearly rate that links a starting value to an ending value. Use the formula (End/Start)^(1/n)-1 or the RRI function, where n is the number of years between the two values. Do not average the yearly growth rates, because that ignores compounding. Use the CAGR to compare investments, describe growth and project simple scenarios, and remember that it hides volatility and does not predict the future. State the period every time you quote a CAGR, so readers know what it covers.

Frequently asked questions

What is CAGR?

It is the constant yearly growth rate that takes a starting value to an ending value over a period.

What is the CAGR formula in Excel?

=(End/Start)^(1/n)-1 or =RRI(n,Start,End).

What is the difference between CAGR and average growth?

CAGR accounts for compounding, and a simple average of yearly rates does not.

How many periods should I use?

Use the number of years between the first and the last value.

Can CAGR be negative?

Yes, if the end value is lower than the start value.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *