CAGR Calculator in Excel: How to Build Your Own in Under 5 Minutes

Takes about five minutes to build a CAGR calculator in Excel, once you know which formula to type. No advanced spreadsheet skills needed. This guide walks through the exact formula, a real worked example with actual cell references, and the mistakes that quietly throw off the result.

What CAGR Actually Calculates

CAGR stands for Compound Annual Growth Rate. It's the single steady annual rate that would take an investment from its starting value to its ending value over a given number of years, smoothing out whatever ups and downs happened along the way.

Useful, because it lets you compare two investments held over different time periods on equal footing. A fund up 40% over 3 years and one up 90% over 7 years? Not directly comparable, not until you convert both into an annualized rate.

The CAGR Formula in Excel

Three common ways to calculate this in Excel. All three land on the same answer. Pick whichever feels more natural to type.

Method 1: The Manual Formula

The underlying math:

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

In Excel, ending value in B2, beginning value in B1, number of years in B3. The formula:

=(B2/B1)^(1/B3)-1

Format that cell as a percentage, and you've got your annualized growth rate.

Method 2: Using the POWER Function

Excel's built-in POWER function does the exponent calculation for you. Some people find it easier to read than the caret symbol.

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

Identical result to Method 1. Just a matter of preference.

Method 3: Using the RRI Function

Excel also has a function called RRI, built specifically for this. Stands for "rate of return for investment." Takes three inputs: number of periods, present value, future value.

=RRI(B3,B1,B2)

Note that RRI wants the number of periods first, unlike the other two methods. Double-check your cell order here, swap it and you'll get a nonsensical result.

A Worked Example

Say you invested $8,000 and it grew to $19,500 over 6 years. Here's how that looks in a spreadsheet:

Type that formula into B4, format as a percentage, and you get roughly 16.03%. That's the constant annual rate that, compounded every year for 6 years straight, turns $8,000 into $19,500.

Don't want to build the spreadsheet yourself? Our CAGR Calculator runs the same math instantly for any two values and time period. Handy for double-checking your Excel formula too.

Building a Reusable CAGR Table

Tracking several investments at once? Worth setting up a small table instead of one-off formulas. Something like this:

Build it once, then drag the formula down for every new row. Compare CAGR across as many holdings as you want, no hand recalculating.

Common Mistakes People Make

Getting the year count wrong. Investment ran January 2020 to January 2026? That's 6 years, not 7. An easy off-by-one slip, and it quietly skews the whole result.

Averaging returns instead of compounding them. Add up each year's percentage return and divide by the number of years, and you get a simple average, not CAGR. The two can differ a lot, especially in volatile years, because CAGR accounts for compounding and a simple average just doesn't.

Forgetting to format as a percentage. The formula still calculates fine. But a raw decimal like 0.1603 is easy to misread as 0.16% instead of 16.03% if the cell formatting's off.

Running CAGR on negative starting values. The math breaks down if your beginning value is zero or negative, which can happen with something like net income during a loss year. Not the right tool for that case.

Want the broader picture on what CAGR tells you and where it falls short? Our guide on CAGR explained goes deeper, including why it's worth pairing with a volatility measure instead of using it alone.

Frequently Asked Questions

Does Excel have a built-in CAGR function?

Not one literally named CAGR, but RRI calculates the exact same thing. You can also build it manually with POWER or the basic exponent formula. Same result either way.

What's the difference between CAGR and average annual return in Excel?

Average annual return usually means adding up each year's gain and dividing by the number of years, ignoring compounding entirely. CAGR uses the actual starting and ending values, which makes it a more accurate picture of how an investment really grew.

Can I use monthly or quarterly data instead of yearly?

Yes. Same formula, just adjust the exponent. For monthly data, divide 1 by the number of months instead of years, then multiply by 12 if you want it annualized, or leave it as-is for a monthly rate.

Why is my CAGR formula showing an error?

Usually a zero or negative value sitting in the beginning value cell. You can't raise a negative number to a fractional power in most cases. Check that both your start and end values are positive before digging further.

Putting Your Spreadsheet to Work

Once the formula's built, reusing it for future investments takes seconds. New starting value, new ending value, new time period. That's it.

Prefer to skip the spreadsheet setup? Our CAGR Calculator gives you the same result instantly, and it's a quick way to sanity-check your Excel formula whenever the numbers look off.