CAGR Formula Excel: How to Calculate Compound Annual Growth Rate Easily

It’s probably well past midnight. You’ve got a spreadsheet open, a cup of coffee that went cold three hours ago, and a cursor blinking mockingly in cell B12. You’re staring at two numbers—what you started with, and what you’ve got now—and trying to figure out how to measure your investment's actual annual growth without letting the bumpy ride in between trick your eyes.

Someone asked you for the CAGR, or maybe you just want to know how your portfolio, your small business revenue, or your retirement fund has actually performed over the last five years. You type =GROWTH() because it sounds right. You try a basic percentage difference and realize it makes no sense because money compounded year over year. Suddenly, a simple math question feels like an audition for an accounting firm.

Take a breath. You don’t need to be a financial engineer to get this sorted out. Calculating compound annual growth rate in a spreadsheet isn't nearly as intimidating as it looks, and once you set up the cagr formula excel sheet properly once, you’ll never have to guess again.

Let's walk through how to build it, why standard math tricks will lead you astray, and how to read the story your numbers are actually trying to tell you.

---

The Trap of Simple Averages (And Why CAGR Saves Us)

Imagine you put money into an investment. In year one, it rockets up 50%. You feel like a genius. In year two, it drops 20%. You feel slightly less like a genius.

If you ask someone untrained in finance what your average return was, they might add 50% and -20%, divide by two, and tell you you averaged 15% a year.

They are entirely, delightfully wrong. And if you plan your future around that 15% number, you’re going to be in for a rough surprise.

Compound Annual Growth Rate—CAGR—doesn't care about the emotional rollercoaster of year one or year two. It smooths out the bumps. It tells you what steady, constant rate of return you would have needed to get from your starting line to your finish line, assuming all the gains were reinvested along the way. It’s the great equalizer of finance.

Because investments rarely move in a straight line, CAGR is the only honest way to compare two different assets over a multi-year period. It strips away the volatility and leaves you with a single, brutal, beautiful truth: how fast did my money actually grow per year?

---

The Math Behind the Magic

Before we throw formulas into a spreadsheet, let's look at what the spreadsheet is actually going to do for us.

The formula for CAGR looks like this:

$$\text{CAGR} = \left( \frac{\text{Ending Value}}{\text{Beginning Value}} \right)^{\frac{1}{n}} - 1$$

Let's break down those pieces so they feel less like hieroglyphics:

• Ending Value: What your investment or metric is worth today (or at the end of your timeframe).
• Beginning Value: What you started with at day one.
• $n$: The number of years between the start and the end. (Not the number of data points, but the number of years elapsed).
• $-1$: We subtract 1 at the very end to convert the decimal into a clean percentage.

If you’ve ever tried to calculate exponents on a standard calculator, you know why doing this by hand is a pain. That’s where Excel steps in. Excel loves exponents. It eats them for breakfast.

---

Setting Up Your Sheet: A Step-by-Step Walkthrough

Let’s follow a real person through this. Meet Marcus.

Marcus started a small online side-hustle three years ago. At the end of Year 0 (inception), the business inventory and cash value were valued at $10,000. Three years later, at the end of Year 3, the business is valued at $24,800.

Marcus wants to know his CAGR so he can put it in a pitch deck for a potential partner. He opens a blank sheet. Here is how he sets up his cells:

• Cell A1: Metric | Cell B1: Value
• Cell A2: Beginning Value | Cell B2: 10000
• Cell A3: Ending Value | Cell B3: 24800
• Cell A4: Years (n) | Cell B4: 3

Now, Marcus needs to write the formula in Cell B5.

If he translates the mathematical formula directly into Excel syntax, it looks like this:

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

Let’s trace what Excel does when Marcus hits Enter:

1. It divides the Ending Value ($24,800) by the Beginning Value ($10,000). The result is 2.48.
2. It raises that result to the power of one-third (since $n = 3$). In Excel, the caret symbol ^ handles exponents. Typing 1/B4 inside parentheses ensures Excel calculates the fraction before applying the exponent.
3. It takes that outcome and subtracts 1.

The result pops up as 0.3539...

Marcus highlights the cell, clicks the % button on his Excel ribbon, and adds a couple of decimal places.

The final number: 35.39%.

Marcus leans back. His side-hustle has grown at a compound annual rate of 35.39% over the last three years. That’s a number he can actually take to a meeting.

---

The Built-In Shortcuts You Didn't Know Existed

Excel is full of hidden doors, and depending on what version you’re using, there are alternative ways to write this formula that might fit your personal preference.

Method 1: The POWER Function

If you want to make your formula look a little more structured for someone else to audit later, you can use Excel’s built-in POWER function instead of the caret (^) symbol.

It looks like this: =POWER(B3/B2, 1/B4) - 1

It does the exact same math, but some people find POWER easier to read at a glance than hunting for the caret key on their keyboard.

Method 2: The RRI Function (The Hidden Gem)

Want a shortcut that makes you feel like an Excel wizard? Excel has a lesser-known financial function specifically designed for this: RRI (Rate of Return).

The syntax for RRI is: =RRI(n, present_value, future_value)

Using Marcus’s numbers, it looks like this: =RRI(B4, B2, B3)

Notice that the order of the arguments is slightly different—it asks for the number of periods first, then the starting amount, then the ending amount. Hit enter, format as a percentage, and you get the exact same 35.39%.

(Note: If you are looking at long-term growth across investments, you might also want to check out tools like a dedicated CAGR Calculator to double-check your work on the go without building a spreadsheet from scratch.)

---

What Trips People Up: Common Mistakes to Avoid

Even smart people mess up spreadsheet formulas because of tiny, invisible data errors. Before you panic and assume your business is failing or your investment manager is lying to you, check for these three common traps:

1. Counting Data Points Instead of Years

This is public enemy number one.

Suppose you have data for four years: 2020, 2021, 2022, and 2023.

• 2020: $1,000
• 2021: $1,200
• 2022: $1,500
• 2023: $2,000

If you count the rows, you get 4. If you plug 4 into your $n$ variable, your formula is going to assume four full years of growth have passed between the start and the end.

But look closer. The growth happened between 2020 and 2023.

• Year 1 to Year 2 (2020 to 2021) = 1 year.
• Year 2 to Year 3 (2021 to 2022) = 1 year.
• Year 3 to Year 4 (2022 to 2023) = 1 year.

Total elapsed time: 3 years, not 4.

If you use 4 as your $n$, you are dividing the exponent too thinly, and your CAGR will look artificially low. Always count the gaps between the dates, not the number of rows on your screen.

2. Forgetting the Parentheses Around the Exponent

If you type =(B3/B2)^1/B4-1 without putting parentheses around 1/B4, Excel follows the standard order of operations (PEMDAS/BODMAS).

It will raise (B3/B2) to the power of 1, and then take that entire result and divide it by B4. Your answer will come out completely wrong, usually resulting in a tiny decimal that makes zero sense.

Always wrap your exponent fraction in parentheses: ^(1/B4).

3. Mixing Up Positive and Negative Cash Flows

CAGR measures growth from a positive starting point to a positive ending point. If your investment ever hit zero, or worse, went negative, the math breaks down because you cannot raise a negative number to a fractional exponent without plunging into complex imaginary numbers.

If your business or portfolio went underwater during the period you're measuring, CAGR is not the right tool for that specific window. You'll want to look at Internal Rate of Return (IRR) instead, which handles cash flows coming in and out at different times.

---

Putting It Into Practice: A Multi-Asset Portfolio

Let's look at one more scenario to see how this scales up when you're comparing multiple things at once.

Say you manage a small fund, and you want to compare three different assets over a 5-year period (from Year 0 to Year 5):

| Asset | Beginning Value (Yr 0) | Ending Value (Yr 5) | | :--- | :--- | :--- | | Asset A (Real Estate) | $50,000 | $80,525 | | Asset B (Tech Stocks) | $20,000 | $49,765 | | Asset C (Index Fund) | $100,000 | $161,051 |

You want to find the CAGR for all three to see which one actually grew your capital the most efficiently.

Instead of typing the formula three separate times, you can set up your columns:

• Column A: Asset Name
• Column B: Beginning Value
• Column C: Ending Value
• Column D: Years (5 for all)
• Column E: The Formula

In cell E2, you type: =(C2/B2)^(1/D2)-1

Drag that formula down to E3 and E4.

What do you find?

• Asset A CAGR: 10.0%
• Asset B CAGR: 20.0%
• Asset C CAGR: 10.0%

Notice something interesting? Asset A and Asset C grew at the exact same annual percentage rate (10.0%), even though Asset C generated vastly more raw cash profit ($61,051 total profit versus Asset A's $30,525 profit).

That is the superpower of CAGR. It allows you to compare a small $10,000 investment side-by-side with a $1,000,000 investment on a level playing field. It tells you efficiency, not just raw volume.

---

What Changes the Answer? (Edge Cases to Keep in Mind)

As you start plugging your own real-world numbers into these cells, keep a few realities in mind:

• Short Timeframes Lie: If you measure CAGR over 6 months ($n = 0.5$), the annualization can create wild, unrealistic projections. If an asset grows 10% in six months, its annualized CAGR isn't just double—because of compounding, it scales exponentially to over 21%. Be careful extrapolating short-term wins into long-term expectations.
• Inflation Exists: A nominal CAGR of 7% sounds great until inflation is running at 5%. Your real purchasing power is only growing by about 2%. Always weigh your CAGR against the economic backdrop of your specific country and currency (£, $, or ₹) to know if you're actually getting ahead.
• Taxes and Fees: The spreadsheet doesn't know what the taxman is taking out, nor does it account for platform management fees. If your gross portfolio CAGR is 8% but your fees and taxes eat 2%, your net reality is 6%. Always run your numbers on what actually hits your pocket where possible.

---

You've Got This

The cursor is still blinking in cell B12, but the dread is gone. You don't need a degree in corporate finance to make a spreadsheet work for you.

Whether you're tracking a side-hustle, evaluating a retirement account, or trying to understand how your savings have performed over the last decade, the formula is just three pieces: where you started, where you ended, and how many years it took to get there.

Plug those numbers into Excel, wrap your exponent in parentheses, format the cell as a percentage, and let the software do the heavy lifting. Once you see that clean final number pop up on your screen, you can finally close the laptop, finish that cold coffee, and rest easy knowing you actually know what your money is doing.

Disclaimer: This article is for informational and educational purposes only and should not be construed as professional financial or tax advice. Every financial situation is unique; consider consulting a qualified professional before making major investment decisions.

---

Frequently Asked Questions

Can I use the CAGR formula if my start date is in the middle of a year?

Yes, but you need to calculate $n$ as a decimal rather than a whole number. If 18 months have passed between your start date and end date, your $n$ is 1.5 (since 18 months / 12 months = 1.5 years). Using whole numbers when months are involved will throw off your annualized rate.

What is the difference between CAGR and IRR?

CAGR assumes a single, lump-sum beginning value and a single ending value with no money added or withdrawn in between. If you make regular monthly contributions to an investment account (like a 401(k), IRA, or mutual fund), CAGR will break down because the principal keeps changing. For portfolios with ongoing deposits or withdrawals, use Excel’s IRR or XIRR formula instead.

Why does my Excel formula return a #NUM! error?

A #NUM! error almost always means Excel is trying to calculate a mathematical impossibility—usually because your Beginning Value or Ending Value is zero or negative. Remember, you cannot divide by zero or raise a negative number to a fractional power. Check your cell references to make sure you didn't accidentally point to a blank cell.

---

For quick calculations on the go, try the free Finlaa app to run your numbers anywhere.