How to Calculate CAGR in Excel: The Formula, RRI and POWER, With a Worked Example

CAGR stands for compound annual growth rate. Corporate Finance Institute (CFI) defines it as an investment's annual growth rate over time with the effect of compounding taken into account. You can use it on anything that grows: revenue, traffic, subscribers, the value of an investment.

The CAGR formula, piece by piece

The formula needs only three numbers. CFI describes it in plain steps: divide the ending value by the beginning value, raise the result to the inverse of the number of years, then subtract one.

Letter	Means	Example
EV	Ending value	16,000
BV	Beginning value	10,000
N	Number of compounding periods (years)	4

Written for a cell, that's =(EV/BV)^(1/N)-1. The ^ is Excel's power operator, and 1/N is the inverse of the number of years.

A worked example you can copy

Say something was worth 10,000 at the start and 16,000 four years later. Here's how to set it up:

1. Put the beginning value in B2, the ending value in B3 and the number of years in B4.
2. In B5 type =(B3/B2)^(1/B4)-1 and press Enter.
3. Format B5 as a percentage.
4. Cross-check with =RRI(B4,B2,B3), which should match.

	Value
Start	10,000
End (after 4 years)	16,000
Years	4
Result of =(16000/10000)^(1/4)-1	0.1247

Excel returns 0.1247. Formatted as a percentage, that's a compound annual growth rate of just under twelve and a half per cent. In other words, growing at that steady rate every year for four years takes 10,000 to 16,000.

Three ways to get CAGR in Excel

Method	Formula in Excel	Note
Direct formula	=(EV/BV)^(1/N)-1	Works in every version; N in years
RRI	=RRI(N,BV,EV)	Returns the rate per period you give it
POWER	=POWER(EV/BV,1/N)-1	Same maths as the direct formula

All three give 0.1247 for the example above. Pick the one you'll recognise when you open the file again in six months.

RRI: Excel's built-in CAGR function, and its trap

Microsoft describes RRI as returning an equivalent interest rate for the growth of an investment. The syntax is RRI(nper, pv, fv): nper is the number of periods, pv is the present value and fv is the future value. Invalid values return #NUM!, and the wrong data types return #VALUE!.

Here's the catch, and it's on Microsoft's own help page. Its example is =RRI(96,10000,11000), and the result column shows 0.0009933. The description next to it talks about an eight-year investment and quotes 0.012, or 1.2%. Both are right, because the example uses 96 periods, which is eight years of months. RRI returns the rate for one period, so 0.0009933 is a monthly rate, and multiplying it by 12 gives roughly 0.012.

The lesson: RRI gives you the rate per whatever period you feed it. If you want an annual growth rate, give it the number of years.

POWER: the same maths, spelled out

POWER(number, power) raises a number to a power, and the ^ operator does the same job. Microsoft's examples are =POWER(5,2), which gives 25, and =POWER(4,5/4), which gives 5.656854249. That second one matters here: a fractional power, such as 1 divided by the number of years, is exactly what CAGR needs. So =POWER(16000/10000,1/4)-1 returns the same 0.1247.

Why CAGR beats a simple average

CFI's example shows why you shouldn't just average yearly growth figures. An investment of 1,000 rises 25% in year one to 1,250, then falls 25% in year two to 937.50, which is less than it started with.

	Value	Change
Start	1,000
End of year 1	1,250	+25%
End of year 2	937.50	-25%

Average the two returns and you get zero change, which is plainly wrong: you've lost value. CAGR gets it right. CFI quotes -3%, and if you run =(937.5/1000)^(1/2)-1 yourself, Excel returns -0.0317.

Two things CAGR can't tell you

CFI lists two weaknesses, and both are worth knowing before you put a CAGR figure in a report.

It smooths out the bumps. CAGR assumes growth was constant across the whole period. For something volatile, that can be very different from what happened. In the example above the investment grew 25% in year one, and the -3% figure hides that completely. If the ride matters, show the yearly figures too, or measure the spread with standard deviation.

It can't see cash going in or out. CAGR doesn't account for funds added or withdrawn. In CFI's table, the asset's own returns are zero, but extra funds put in raise its value, and CAGR can't tell that apart from performance. If you've topped something up along the way, CAGR will flatter it.

I'd pair this with this walkthrough of how to calculate days between dates in Excel.

CAGR or plain percentage change?

They answer different questions. Percentage change tells you how far something moved from start to end. CAGR tells you the steady yearly rate that would have produced that move. If you only need the first, my guide to calculating percentages in Excel covers it. Reach for CAGR when the time period matters, or when you're comparing two things that grew over different numbers of years.

My verdict

CAGR is a comparison tool, not a crystal ball. I use it to compare two things over the same stretch, such as revenue across four years or traffic against subscribers, and never to predict next year. Use the direct formula so anyone can follow it, check it with RRI in years, and always remember the 25% gain and 25% loss example: a smooth number can hide a rough ride.

If you're short on time, my Excel Agent Mode breakdown is the quick read.

Frequently asked questions

What is the CAGR formula in Excel?

Type =(EV/BV)^(1/N)-1, where EV is the ending value, BV the beginning value and N the number of years, then format the result as a percentage.

Does Excel have a CAGR function?

Not by that name, but RRI(nper, pv, fv) returns the equivalent growth rate. Feed it years if you want an annual rate.

Why does RRI give me a tiny number?

RRI returns the rate per period. Microsoft's 96-period example returns 0.0009933, a monthly rate, which is about 1.2% a year when multiplied by 12.

Why not just average the yearly growth rates?

Because averages ignore compounding. In CFI's example, +25% then -25% averages to zero, but the investment lost value.

What are the limits of CAGR?

It assumes smooth growth, hiding volatility, and it can't tell new funds added along the way apart from real performance.