CAGR Formula in Excel (2024)

Download our practice workbook 👉 HERE and follow along:

What is CAGR?

When you invest money, it’s important to know how much your investment grows each year. But sometimes, the growth isn’t the same every year. Some years it might increase a lot, and other years, it might not grow as much or even decrease.

So, how can you find out the average growth rate over several years? This is where CAGR, or Compound Annual Growth Rate, comes in handy.

CAGR is a simple way to understand how much your investment grows on average each year, over a period of time. It doesn’t just add up the yearly growth rates. Instead, it shows you the steady rate at which your investment would have grown. It assumes it grew at the same rate every year.

In the next sections, we’ll show you how to easily calculate CAGR using Excel. This will help you see how well your investments are doing over time, with just a few clicks.

Understanding the CAGR Formula

For the CAGR formula you need to know three things: the initial amount of money you invested, the amount you ended up with, and how many years your money was invested.

The CAGR formula looks like this:

Here’s a simple way to understand the CAGR formula:

• Starting Value: The amount you first invested.
• Ending Value: What your investment is worth now.
• Number of Years: How long the money was invested.

This final result is your CAGR, shown as a percentage. It represents the steady yearly growth rate of your investment.

For example, let’s assume you start with an investment of $1,000 and after 3 years, it grows to $1,500. This is how you calculate the average annual growth rate using the CAGR formula:

So, the CAGR for your investment is 14.5%, meaning on average, your investment grew by 14.5% each year over the 3 years.

How to Calculate CAGR in Excel

Although Excel doesn’t have a dedicated CAGR function, you can still calculate the Compound Annual Growth Rate quite easily using a few different methods. Here’s how you can do it:

1. Direct CAGR Formula Method

Calculating the CAGR in Excel can be done straightforwardly using a basic formula.

Let’s say you have recorded the year-end values of an investment over a period from 20X1 to 20X6. Here’s how you can set up your Excel sheet and calculate the CAGR:

Formula for CAGR:

• Click on cell B8 to select it. This is where you will calculate the CAGR.
• Enter the following formula:

= (B7/B2)^(1/5)-1

• After entering the formula, press Enter. The result in B8 will show the CAGR as a decimal. To view it as a percentage, change the cell format to percentage style in Excel.

This calculation tells you the average annual growth rate of your investment from 20X1 to 20X6 is 7.09%.

❗ Explanation of the Year Count in CAGR Formula

CAGR is calculated from the starting point to the ending point of the investment. It measures the average annual growth over these periods.

In our example, we have year markers from 20X1 to 20X6. However, the investment has only completed five full year-to-year periods. This is similar to birthdays. For instance, if you were born in 20X1, you turn one in 20X2, marking a full year of life.

Calculating CAGR with the Excel RRI Function

Calculating CAGR in Excel can be quickly done using the RRI function. This function is specifically designed to calculate the equivalent interest rate that represents the growth of an investment over a set period.

To use the RRI function, you need:

• Total Number of Periods (nper): This is the number of years or periods over which the investment grows.
• Present Value (pv): The initial amount of money invested.
• Future Value (fv): The amount the investment is worth at the end of the period.

Here’s how you’d enter this in Excel to find the CAGR for our example:

• In cell B9, enter the formula:

= RRI(5, B2, B7)

• Press Enter after typing the formula. Excel will compute the CAGR and display it in cell B9 as a decimal.
• To see this as a percentage, format the cell as a percentage.

Calculating CAGR with the Excel RATE Function

Another way is using the Excel RATE function. It’s typically used for loan interest rates but works just as well for investment growth.

What you need for the RATE Function:

• Total Number of Years (nper): This is the number of years or periods over which the investment grows.
• Present Value (pv): This is your initial investment amount, entered as a negative value to reflect the cash paid out when you made the investment.
• Future value (fv): The amount the investment is worth at the end of the period

Here’s how you’d enter this in Excel to find the CAGR for our example:

In cell B10, enter the formula:

= RATE(5, , -B2, B7)

Here:

• 5 is the total number of years the investment has grown.
• 0 is used because there are no additional payments or withdrawals.
• -B2 is your initial investment (ensure it’s negative).
• B7 is the final value of your investment.

• Press Enter after typing the formula. Excel will compute the CAGR and display it in cell B10 as a decimal.
• To see this as a percentage, format the cell as a percentage.

Understanding Growth Rates: CAGR vs AAGR

Both the Compound Annual Growth Rate (CAGR) and the Average Annual Growth Rate (AAGR) show how investments grow over time. But, they do so in different ways.

• CAGR acts like it smooths out the ride of your investment growth. It pretends the growth is steady every year, which is helpful for looking at long-term investments. This way, it doesn’t matter if one year was really bad or really good—it averages those ups and downs.
• AAGR on the other hand, looks at each year separately to calculate an average. It’s good for seeing how much your investment went up or down each year. This is especially useful if you’re looking at shorter times and want to know more about the growth each year.

Let’s look at an example.

Let’s say you have recorded the year-end values of an investment over a period from 20X1 to 20X6. Here’s how you can set up your Excel sheet and calculate both the AAGR and the CAGR:

Step 1: Calculate Yearly Growth Rates for AAGR

• In cell C3, enter the formula =B3/B2 – 1. This formula calculates the growth rate from 20X1 to 20X2.

= B3 / B2 - 1

💡 For more information about how to calculate percentage in Excel check out our detailed article here.

• Drag this formula from cell C3 down to C7 to calculate the growth for each subsequent year.

Step 2: Calculate AAGR

• In cell B8, enter the AAGR formula:

=AVERAGE(C3:C7)

This uses the Excel AVERAGE function to find the mean of the yearly growth rates you calculated.

• The result in B8 will show the AAGR. In our example, it’s 6.82%.

Step 3: Calculate CAGR for Comparison

In cell B9, enter the CAGR formula:

= (B7/B2)^(1/5) -1

This formula finds the average rate at which the investment has grown per year over the five-year period, assuming the growth was steady each year.

• The result in B9 will show the CAGR. In this example, because the end value (B7) equals your start value (B2), the CAGR would indeed be 0.0%. This reflects a steady rate of no growth over the five years, showing how average calculations can sometimes hide details.

Understanding both AAGR and CAGR is valuable for making informed decisions and comparisons between different investments.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master how to calculate CAGR and AAGR in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.