How to Calculate CAGR in Excel

Q: What is the CAGR formula in Excel?

The CAGR formula in Excel is =(Ending Value / Beginning Value)^(1 / Number of Years) - 1. For values in cells B1 (start) and B2 (end) over 5 years, type =(B2/B1)^(1/5)-1 and format the result as a percentage. The RRI function — =RRI(5, B1, B2) — gives the same answer with cleaner syntax.

Q: How do I calculate compound annual growth rate in Excel without a built-in function?

Use the ratio formula =(B2/B1)^(1/n)-1, where n is the number of years. This uses only the division and exponent (^) operators, so it works in every version of Excel including Excel for the web and older releases that lack the RRI function.

Q: Should I use IRR or the CAGR formula?

Use the CAGR formula (or RRI) when you have just a single beginning value and a single ending value. Use IRR only when you have a series of irregular cash flows in and out across the periods. They are different calculations — IRR is not a substitute for CAGR when you only know the start and end points. For multi-period cash flows you may also want NPV in Excel.

Q: How many years do I use in the CAGR formula?

Count the number of periods between your first and last value, not the number of data points. Data for 2020 through 2024 spans four years of growth, so n = 4. Getting this wrong is the single most common CAGR mistake.

Q: Can I calculate CAGR for monthly or quarterly data?

Yes. The exponent is 1 / number of periods and the result is the rate per period. To annualize monthly data, raise the per-month rate to the 12th power, or simply convert your period count to years (e.g. 36 months = 3) and use the standard formula. Keeping the period unit consistent is what matters.

To calculate CAGR (Compound Annual Growth Rate) in Excel, use the formula =(Ending Value / Beginning Value)^(1 / Number of Years) - 1. If your ending value is in B2, your beginning value is in B1, and you have 5 years of growth, the formula is =(B2/B1)^(1/5)-1, then format the cell as a percentage.

That single line answers the most common case. The rest of this guide covers every reliable method — the ratio formula, the dedicated RRI function, and the POWER function — plus the keyboard shortcuts for Windows and Mac, common edge cases, and how to fix the errors that trip people up.

What CAGR Actually Measures

CAGR is the smoothed, constant rate at which an investment (or revenue, user count, or any metric) would have grown each year if it compounded steadily. It strips out the year-to-year volatility and gives you one clean annualized percentage you can compare across investments or time periods.

The math is the same no matter which Excel method you choose:

CAGR = (Ending Value / Beginning Value)^(1 / Number of Years) − 1

The only trap worth flagging up front is the “number of years.” If you have annual values for 2020, 2021, 2022, 2023, and 2024, that is four years of growth, not five — you count the gaps between the data points, not the data points themselves.

Method 1: The Ratio Formula (Most Common)

This is the method you will use 90% of the time. It works in every version of Excel — Windows, Mac, and Excel for the web.

Put your beginning value in a cell, say B1 (e.g. 1000).
Put your ending value in another cell, say B2 (e.g. 2500).
In an empty cell, type:
```
=(B2/B1)^(1/4)-1
```
Press Enter. Replace the 4 with your actual number of years.
Format the result as a percentage (see the shortcut section below).

With a beginning value of 1,000 growing to 2,500 over four years, the result is 25.74%.

Why the caret (^) matters

The ^ symbol is Excel’s exponentiation operator — it raises a number to a power. The parentheses around 1/4 are essential. Without them, Excel follows order of operations and divides by 4 after the exponent, giving you a wildly wrong answer. If exponents are new to you, our guide to exponents in Excel and the POWER function explain the operator in depth.

Method 2: The RRI Function (Cleanest)

Excel has a purpose-built function for exactly this: RRI (“Rate of Return on Investment”). It was added in Excel 2013 and is available on Windows, Mac, and the web. The syntax is:

=RRI(nper, pv, fv)

nper — number of periods (years)
pv — present (beginning) value
fv — future (ending) value

So for our example:

=RRI(4, B1, B2)

This returns 0.2574, the same 25.74%, with no manual exponent math and no chance of forgetting a parenthesis. Note that pv is entered as a positive number here, unlike IRR or RATE which expect signed cash flows. RRI is the most readable option if your team is on Excel 2013 or later.

Method 3: The POWER Function

If you prefer named functions over the ^ operator, POWER does the same job:

=POWER(B2/B1, 1/4)-1

POWER(number, power) raises the first argument to the second. This is functionally identical to Method 1 — pick whichever reads more clearly to you.

Method Comparison

Method	Formula	Best for	Excel versions
Ratio formula	`=(B2/B1)^(1/n)-1`	Quick, universal	All
RRI function	`=RRI(n, B1, B2)`	Readability, fewer errors	2013+
POWER function	`=POWER(B2/B1, 1/n)-1`	Avoiding the caret	All

Formatting the Result as a Percentage

CAGR comes out as a decimal (0.2574). To show it as 25.74%:

Windows: Select the cell and press Ctrl + Shift + % (the percent sign). This applies percent format with zero decimals instantly.
Mac: Press Control + Shift + % (the Mac percent shortcut uses Control, not Command).

To control decimal places, open the Format Cells dialog with Ctrl + 1 (Windows) or ⌘ + 1 (Mac), choose Percentage, and set the decimal count. Our walkthrough on formatting cells in Excel covers the dialog in detail, and if you want to control rounding precisely, see the ROUND function.

Calculating the Number of Years Automatically

If your beginning and ending dates live in cells rather than a known year count, you do not have to count by hand. Suppose the start date is in A1 and the end date is in A2:

=(B2/B1)^(1/((A2-A1)/365))-1

Subtracting two dates in Excel returns the number of days between them; dividing by 365 converts that to years (including fractional years). If you work with dates often, our guide on the time difference between two dates shows the underlying date math.

Worked Example: Revenue Growth

Say a company’s revenue went from $4,000,000 in 2019 to $9,500,000 in 2024.

Cell	Value
B1	4000000
B2	9500000
Years	5 (2019 → 2024)

Formula: =(B2/B1)^(1/5)-1 → 0.1888, or an 18.88% CAGR. Using RRI: =RRI(5, B1, B2) returns the identical result. Note that 2019 to 2024 spans five gaps, so we use 5.

CAGR vs. Average Growth Rate

A common mistake is averaging the yearly percentage changes instead of compounding them. If a value rises 100% one year and falls 50% the next, the simple average is +25% — but you actually ended exactly where you started, so the true CAGR is 0%. CAGR captures compounding; a plain average in Excel does not. For single-period growth, you want a percentage change instead, which is a different (non-annualized) measure.

Locking Cells When You Copy the Formula

If you are calculating CAGR for many rows and want a fixed beginning value, use an absolute reference so the cell does not shift when you fill down. Type $B$1 instead of B1. Press F4 (Windows) or ⌘ + T (Mac) while editing the reference to add the dollar signs automatically. Our guide to absolute cell references in Excel explains when to lock rows versus columns.

Troubleshooting Common Errors

Problem	Cause	Fix
`#NUM!` error	Negative beginning value, or a negative base under a fractional exponent	CAGR is undefined when a value crosses zero; use `IRR` for mixed cash flows
`#DIV/0!` error	Beginning value (B1) is empty or zero	Make sure B1 holds a non-zero number
Result shows a tiny decimal	Cell is not formatted as a percentage	Apply percent format with Ctrl + Shift + %
Wildly wrong answer	Missing parentheses around `1/n`	Use `^(1/n)`, never `^1/n`
Off-by-one CAGR	Counted data points instead of gaps	Years = number of periods between first and last value

If you see #NAME?, you are likely on a version older than Excel 2013 trying to use RRI — switch to the ratio formula in Method 1, which works everywhere.

Frequently Asked Questions

What is the CAGR formula in Excel?

The CAGR formula in Excel is =(Ending Value / Beginning Value)^(1 / Number of Years) - 1. For values in cells B1 (start) and B2 (end) over 5 years, type =(B2/B1)^(1/5)-1 and format the result as a percentage. The RRI function — =RRI(5, B1, B2) — gives the same answer with cleaner syntax.

How do I calculate compound annual growth rate in Excel without a built-in function?

Use the ratio formula =(B2/B1)^(1/n)-1, where n is the number of years. This uses only the division and exponent (^) operators, so it works in every version of Excel including Excel for the web and older releases that lack the RRI function.

Should I use IRR or the CAGR formula?

Use the CAGR formula (or RRI) when you have just a single beginning value and a single ending value. Use IRR only when you have a series of irregular cash flows in and out across the periods. They are different calculations — IRR is not a substitute for CAGR when you only know the start and end points. For multi-period cash flows you may also want NPV in Excel.

How many years do I use in the CAGR formula?

Count the number of periods between your first and last value, not the number of data points. Data for 2020 through 2024 spans four years of growth, so n = 4. Getting this wrong is the single most common CAGR mistake.

Why is my CAGR result showing as a decimal instead of a percentage?

Excel returns CAGR as a decimal like 0.1888. Select the cell and press Ctrl + Shift + % on Windows or Control + Shift + % on Mac to display it as a percentage. Use Ctrl + 1 (⌘ + 1 on Mac) to set how many decimal places appear.

Can I calculate CAGR for monthly or quarterly data?

Yes. The exponent is 1 / number of periods and the result is the rate per period. To annualize monthly data, raise the per-month rate to the 12th power, or simply convert your period count to years (e.g. 36 months = 3) and use the standard formula. Keeping the period unit consistent is what matters.

Wrapping Up

CAGR turns messy, volatile growth into one comparable annual percentage. The ratio formula =(B2/B1)^(1/n)-1 is the fastest path, RRI is the most readable on modern Excel, and POWER is there if you dislike the caret. Just count your years correctly, format the output as a percentage, and you will get a clean, accurate growth rate every time. For longer-horizon investment math, our guide on compound interest in Excel builds naturally on these same ideas.