Calculate CAGR in Excel (Compound Annual Growth Rate)

In this post, we are looking at how to calculate CAGR in Excel. CAGR stands for Compound Annual Growth Rate; it calculates the annual percentage growth over a period of time, where the growth is compounded each year.

The compound annual growth rate is often used in finance to understand how different investments have performed over time.

Download the example file

I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download the file: 0063 Calculating CAGR in Excel.xlsx

Contents

What is CAGR (Compound Annual Growth Rate)?

Let’s start by understanding what CAGR (Compound Annual Growth Rate) is.

A simple CAGR example

As a simple example, if you invest $100 in a savings account with a 10% fixed interest rate. After 1 year, your account has earned $10 interest and is now worth $110. Since your account now has $110, the interest in year 2 is higher at $11, making the balance $121. Thus, we are now receiving interest on the previously earned interest. This is known as compounding; the interest earned is added to the total each year, providing a larger base value for the next interest calculation.

After 5 years, the value of that account would be $161. If all we knew about the account was that it started at $100 and after 5 years was worth $161, how could we calculate the interest rate? This is where the compound annual growth rate comes in.

Even though rates of return can change over time, CAGR can be used to calculate the mean average compound rate over any time period.

In summary, CAGR is the percentage growth required for a value to grow from the start value to the end value assuming the increase is added to the base value each year.

A more realistic CAGR example

Now, let’s take a look at a more realistic example

In the screenshot above, sales for Alpha Co increased from $100m in 2016 to $161m in 2021. The growth was not consistent; it fluctuated between 4.0% and 15.8%.

The CAGR calculated in Cell C13 is 10%.

Proving the compound annual growth rate (CAGR) calculation

Let’s prove this calculation to ensure it is compounding the annual growth rate as we are expecting.

In our example above, Alpha Co has a start value of $100m, with 10% CAGR, so what is the value after 5 years.

Method 1: $100m x 1.1 x 1.1 x 1.1 x 1.1 x 1.1 = $161m
Method 2: $100 x 1.1^5 = $161m

There we have it. By using the CAGR, we can calculate the same end result of $161m. Hopefully, that gives you a good understanding of how CAGR works and what it means. The next question is, how can we calculate CAGR in Excel?

Online CAGR calculator

If you just want to calculate the answer quickly and not worry about the Excel formulas, you can find a CAGR Calculator here: https://www.vertex42.com/Calculators/cagr-calculator.html

How to calculate CAGR in Excel

Where is the CAGR formula in Excel, you ask? …. Er, there isn’t one. What! No CAGR formula! What are we going to do?

Don’t worry; instead, we get to choose from many possible ways to calculate the result.

CAGR formula in Excel

The basic CAGR formula is :

CAGR = (EV ÷ BV) ^ (1 ÷ n) – 1

EV = Ending Value
BV = Beginning Value
n = Periods between Beginning and Ending

Using the CAGR formula in Excel

Once we know the basic calculation, it is easy enough to apply the CAGR formula in Excel.

The formula in Cell C15 is:

=(C9/C4)^(1/C11)-1

C9 = 161 (the Ending Value)
C4 = 100 (the Beginning Value)
C11 = 5 (number of periods)

Result: 10%

RRI function

There is an easier option than the generic CAGR formula, the RRI function.

The RRI function in Excel calculates the interest rate for the growth of an investment. Hmmm… 🤔 sounds just like the CAGR formula with a different name.

If we know the present value, future value, and the number of years in between, it will calculate the annual growth percentage.

Arguments of RRI function

The RRI function has the following syntax:

=RRI(Nper, Pv, Fv)

Nper: Number of periods
Pv: Present value (or Beginning Value)
Fv: Future value (or Ending Value)

Example using RRI function

OK, let’s use the RRI function on Alpha Co within our example.

The formula in Cell C16 is:

=RRI(C11,C4,C9)

C11 = 5 (number of periods)
C4 = 100 (the Beginning Value)
C9 = 161 (the Ending Value)

Result: 10%

RATE function

A similar and related function to RRI is the RATE function.

The RATE function in Excel calculates the interest rate per period of a loan or investment.

Arguments of the RATE function

The RATE function has the following syntax:

=RATE(Nper, Pmt, Pv, [Fv], [Type], [Guess])

Nper: Number of periods
Pmt: The consistent payment made each period
Pv: Present value
[Fv]: Future value. This is an optional argument; if omitted, the value is assumed to be 0.
[Type]: An optional argument to represent if repayment is made at the:
- 1 = beginning of the period
- 0 or omitted = end of the period
[Guess]: The guess of what the rate could be. This is an optional argument; if omitted, it is assumed to be 0.1 (10%).

The RATE function calculates the interest rate on a loan repaid over time, while RRI calculates the rate of return on investment. Using the RATE function with zero repayments and a negative present value provides the same result as the RRI function.

Example using the RATE function

Let’s look at an example using the RATE function.

The formula in Cell C17 is:

=RATE(C11,,-C4,C9)

C11 = 5 (number of periods)
C4 = 100 (the Beginning Value) – remember to make this negative to calculate CAGR
C9 = 161 (the Ending Value)

Result: 10%

POWER function

We’ve seen that RATE and RRI are built-in Excel functions that replicate the basic CAGR formula. We don’t see how those functions work; we just trust that Excel calculates the result as expected.

An alternative option that is similar to the original mathematical calculation is to use Excel’s POWER function. This function returns the result of a number raised to a power.

Arguments of the POWER function

The POWER function in Excel has the following syntax:

=POWER(Number, Power)

Number: The base number; can be any real number
Power: The exponent to which the base number is raised

Example using the POWER function

Using Alpha Co as our example once again:

The formula in Cell C18 is:

=POWER(C9/C4,1/C11)-1

C9 = 161 (the Ending Value)
C4 = 100 (the Beginning Value)
C11 = 5 (number of periods)

Result: 10%

Hopefully, by looking at this method, you can see how this replicates the mathematical approach we looked at initially.

GEOMEAN function

For the final method to calculate CAGR, I want to change the scenario slightly. What if we have the annual growth percentages for each year, but not the exact values. Imagine we only had the values in Cells D5-D9 (see screenshot below); what could we do?

For this type of scenario, we can use the GEOMEAN function.

GEOMEAN calculates the average growth rate even where there is compounding with variable rates over time. Therefore, this is the perfect function to calculate CAGR from a list of individual growth rates.

Arguments of the GEOMEAN function

The GEOMEAN function in Excel has the following syntax:

=GEOMEAN(Number1, [Number2],...)

Number1: The number, array, or cell references that contain the values for which to calculate the result
[Number2]…: The GEOMEAN function can take up to 255 arguments of arrays or cell references to include in the calculation

Example using the GEOMEAN function

Using Alpha Co as our example once again.

The formula in Cell C19 is:

=GEOMEAN(1+D5:D9)-1

(1+D5:D9) = is the range of numbers from which to calculate the GEOMEAN. We need to add 1 to each percentage to calculate the growth factor.
-1 = is used at the end to return the calculated growth factor back to a percentage increase.

Result: 10%

Note: If you do not have a dynamic array-enabled version of Excel (Excel 2019 and prior), you will need to press Ctrl + Shift + Enter to commit this formula.

Conclusion

There you go, that’s how to calculate CAGR in Excel.

As we’ve seen, each method evaluates to the same result. So, whether we use the basic CAGR formula in Excel or one of the built-in functions, we can choose the one which best fits our way of thinking.

Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: