Super Sale WeekClaude Skills — 20% OFF
Tips

How to Calculate CAGR in Excel: 3 Easy Ways in 2026

Powerdrill Bloom·
How to Calculate CAGR in Excel: 3 Easy Ways in 2026

To calculate CAGR in Excel, divide the ending value by the starting value. Raise the result to the power of 1 divided by the number of years, then subtract 1. In a cell, that looks like =(B6/B2)^(1/4)-1. Excel's RRI and RATE functions give the same answer in one step.

This guide covers the formula, three ways to calculate it in Excel, and how to avoid the most common mistakes. It also walks through a full example and a faster way to get growth rates from your data. Function details come from Microsoft's support pages, read on November 11, 2026.

What CAGR is

CAGR stands for compound annual growth rate. It is the steady yearly rate that would take a value from its starting point to its ending point over a set number of years.

Real growth is rarely steady. Revenue might rise 15% one year and 5% the next. CAGR smooths those swings into one rate, so you can compare growth across products, companies, or investments.

People use it for many kinds of values:

  • Revenue and profit over several years.
  • Customer counts or users.
  • Investment value, such as a fund or portfolio.
  • Market size in research reports.

CAGR only looks at the first and last values. It says nothing about the path in between. That makes it simple, and also easy to misread, as later sections show.

You will often see CAGR in market research. A report might say a market "will grow at a CAGR of 9% through 2030." That is a projection, built on assumptions. When you calculate CAGR in Excel from your own figures, you get a historical rate instead. Keep the two apart in any report, and label which one you are showing.

The CAGR formula

The formula has three inputs:

CAGR = (Ending value / Starting value)^(1 / Number of years) − 1

  • Ending value is the value at the end of the period.
  • Starting value is the value at the start.
  • Number of years is the number of periods between them, not the number of data points.

That last point causes most errors. Five years of data, from 2021 to 2025, covers four periods of growth. So the number of years is 4, not 5.

The result is a decimal. Format the cell as a percentage to show it as a rate, such as 11.43%.

Set up your data in Excel

A clean layout makes every method easier. Put years in column A and values in column B.

A B
1 Year Revenue
2 2021 1,200,000
3 2022 1,380,000
4 2023 1,450,000
5 2024 1,610,000
6 2025 1,850,000

Keep the values as numbers, not text. If a value shows a small green triangle, it may be stored as text and will break the formula.

Use a separate cell for the number of years. For example, put =A6-A2 in cell D2. That returns 4 and updates if you add another year.

If you track several series, such as products or regions, give each one its own column. Then one CAGR formula per column can be filled across. Absolute references, such as $D$2 for the number of years, keep the formula pointing at the right cell as you copy it.

Way 1: Use the CAGR formula with the ^ operator

This is the most common way to calculate CAGR in Excel, and it works in every version.

  1. Click an empty cell, such as D4.
  2. Type =(B6/B2)^(1/D2)-1 and press Enter.
  3. With the cell selected, go to Home > Number Format and choose Percentage.

The ^ sign raises a number to a power. Microsoft's POWER function page confirms that the caret operator can be used in place of POWER.

So you can also write the same formula with POWER:

=POWER(B6/B2,1/D2)-1

Microsoft describes POWER as a function that "Returns the result of a number raised to a power." Both forms give the same result. Pick whichever reads more clearly to you and your team.

Way 2: Use the RRI function

RRI is a built-in function that returns the growth rate directly. Microsoft says it "Returns an equivalent interest rate for the growth of an investment."

The syntax is RRI(nper, pv, fv):

  • nper is "the number of periods for the investment."
  • pv is "the present value of the investment," which here is your starting value.
  • fv is "the future value of the investment," which here is your ending value.

To calculate CAGR with RRI:

  1. Click an empty cell.
  2. Type =RRI(D2,B2,B6) and press Enter.
  3. Format the cell as a percentage.

In the Harbor Supply example below, =RRI(4,1200000,1850000) returns 0.1143. Formatted as a percentage, that is 11.43%, the same as the formula in Way 1.

RRI is the shortest way to calculate CAGR in Excel. It is available in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, per Microsoft's support page.

Watch the error values. Microsoft says "If argument values are not valid, RRI returns the #NUM! error value." A zero or negative starting value is a common cause.

Way 3: Use the RATE function

RATE is built for loans and annuities, but it can calculate CAGR too. Microsoft says RATE "Returns the interest rate per period of an annuity."

The syntax is RATE(nper, pmt, pv, [fv], [type], [guess]). For CAGR, there are no payments in between, so pmt is 0. The starting value goes in as a negative number, because RATE treats it as money paid out.

  1. Click an empty cell.
  2. Type =RATE(D2,0,-B2,B6) and press Enter.
  3. Format the cell as a percentage.

The minus sign before B2 matters. Without it, RATE usually returns #NUM!, because the inputs no longer describe money going out and coming back.

Microsoft notes that RATE "is calculated by iteration and can have zero or more solutions." For simple CAGR, it finds the same answer as RRI. Use RATE when your colleagues already know it from loan work.

RATE has one more use. Microsoft defines pmt as "The payment made each period and cannot change over the life of the annuity." If you added the same amount to an investment every year, you can enter it as pmt. The result is then a return rate that accounts for those additions, not a plain CAGR. Label it that way in your report.

A CAGR example

Here is a worked example for a made-up company. Harbor Supply sold $1,200,000 in 2021 and $1,850,000 in 2025. The finance lead wants the growth rate for a board slide.

Inputs. Starting value is $1,200,000 in B2. Ending value is $1,850,000 in B6. The number of years is 2025 minus 2021, or 4.

Formula. =(B6/B2)^(1/4)-1

Working. $1,850,000 divided by $1,200,000 is about 1.5417. Raised to the power of 1/4, that is about 1.1143. Subtract 1, and the result is 0.1143.

Result. Harbor Supply's revenue CAGR is 11.43%.

Check. $1,200,000 grown at 11.43% for four years returns $1,850,000. RRI and RATE give the same 11.43%.

The yearly growth rates tell a less even story. Growth was 15.00% in 2022, 5.07% in 2023, 11.03% in 2024, and 14.91% in 2025. The simple average of those four rates is 11.50%, slightly above the CAGR.

The finance lead puts 11.43% on the slide, with a small line chart of the yearly figures. The chart shows the uneven path, and the CAGR gives the single number the board asked for.

The board then asks which region drove the growth. The finance lead adds three rows, one per region, and copies the formula down.

Region 2021 revenue 2025 revenue CAGR
North $400,000 $620,000 11.58%
South $450,000 $610,000 7.90%
West $350,000 $620,000 15.37%
Total $1,200,000 $1,850,000 11.43%

The regions add up to the company totals, and the company CAGR stays at 11.43%. West grew fastest from the smallest base. South grew slowest, which becomes the topic for the next meeting.

Note that the total CAGR is not the average of the three regional rates. Bigger regions weigh more, so always calculate the total from the total values.

CAGR vs. average annual growth rate

These two numbers often get confused. They answer different questions.

CAGR Average annual growth rate
What it measures The steady rate from start to end The mean of each year's growth rate
Inputs First value, last value, number of years Every year's growth rate
Reacts to swings No Yes
Best for Comparing long-term growth Describing typical yearly change

Swings make the gap bigger. Take an investment that goes from $100 to $150 in year one, then falls to $75 in year two. The yearly rates are +50% and −50%, so the average growth rate is 0%.

But the investment lost money. CAGR shows that: (75/100)^(1/2) − 1 is about −13.4%. In this case, the average growth rate hides a real loss, and CAGR tells the truth about the start and end.

That is why investors and analysts prefer CAGR for multi-year comparisons. Use the average only when you care about a typical single year.

How to calculate CAGR for months or quarters

CAGR is annual by name, but your data may be monthly or quarterly. You have two options.

Convert the periods to years. If you have 36 months of data, use 36/12, or 3 years, in the formula. Say a value grows from 10,000 to 16,000 over 36 months. Its CAGR is (16,000/10,000)^(1/3) − 1, or about 16.96%.

Calculate a periodic rate first. Use RRI with the number of months. The result is a monthly rate. To turn a monthly rate into an annual one, use =(1+monthly rate)^12-1.

Both routes give the same annual figure. Label the result clearly, so readers know whether they are looking at a monthly or annual rate.

Calculate CAGR in Excel with AI

When you have many products, regions, or years, building the formula for each one takes time. An AI tool can calculate growth rates across a whole dataset and explain them.

Here is the method in three steps, using Powerdrill Bloom.

Step 1: Upload your Excel file

Upload the workbook with your yearly values. The Excel Calculations page asks you to upload the workbook first. It adds: "Ensure your data is correctly formatted to enable smooth calculations."

Keep one row per year and one column per product or region. That layout lets the AI compare growth side by side.

Uploading an Excel file to calculate CAGR in Powerdrill Bloom

Step 2: Ask for the CAGR in natural language

Describe what you need. For example: "Calculate the CAGR from 2021 to 2025 for each region, and rank them."

The same page says you can "Input your desired calculation request in text, such as standard deviation, CAGR, growth rate, etc." It adds that Powerdrill Bloom "will recognize and apply the correct formula."

Step 3: Check the results and export the summary

Check one result by hand with the formula above. Then turn the numbers into a chart or slide. The Excel AI Assistant can "Export insights as PowerPoint slides, infographics, or Notion pages in one click."

For growth that you plan to project forward, our guide on how to forecast next quarter's sales from historical Excel data picks up from here.

Checking CAGR results before exporting the summary

Common mistakes to avoid

These are the errors that come up most often when people calculate CAGR in Excel.

  • Counting data points instead of periods. Five years of values means four periods.
  • Mixing up start and end. Ending value goes on top of the division.
  • Forgetting the minus sign in RATE. The starting value must be negative.
  • Using a zero or negative start. CAGR does not work when the starting value is zero or below.
  • Leaving the result as a decimal. Format the cell as a percentage.
  • Reading CAGR as a forecast. It describes past growth. It does not promise the same rate ahead.
  • Hiding the path. Show yearly figures next to the CAGR, so readers see the swings.
  • Comparing different time spans. A three-year CAGR and a five-year CAGR are not directly comparable. Use the same years for every series.

For a broader look at patterns over time, see our guide on how to do a trend analysis.

When you are ready to calculate growth across a whole dataset, try Powerdrill Bloom with your last five years of figures.

Frequently asked questions

What is the formula for CAGR in Excel?

Use =(Ending value/Starting value)^(1/Number of years)-1. For example, =(B6/B2)^(1/4)-1. Format the result as a percentage.

How do I calculate CAGR with the RRI function?

Type =RRI(number of years, starting value, ending value). For example, =RRI(4,B2,B6). RRI returns the growth rate directly.

Can I use the RATE function to calculate CAGR?

Yes. Use =RATE(number of years, 0, -starting value, ending value). The starting value must be negative for RATE to work.

Why is my CAGR formula giving an error?

Common causes are a zero or negative starting value, values stored as text, or a missing minus sign in RATE. RRI returns #NUM! for invalid values, per Microsoft.

What is the difference between CAGR and average growth rate?

CAGR is the steady rate from the first value to the last. The average growth rate is the mean of each year's rate. With large swings, the average can overstate real growth.

Sources: Microsoft Support, RRI function · Microsoft Support, RATE function · Microsoft Support, POWER function.