A compound annual growth rate (CAGR) is your return on investment over a specific period.

Calculating CAGR manually is a rather complicated process, so below we will introduce how to quickly calculate CAGR in Excel.

Compound Annual Growth Rate Excel Formula

The formula for calculating CAGR in Excel is:

= (Final Value/Initial Value)^(1/Number of Years) – 1

This equation uses three different values:

  • end value, This is the amount you will have after that period has passed.
  • starting valuewhich is the amount you started with.
  • Yearswhich is the total number of years in the past.

Below we present an example of how to calculate CAGR for a five-year time frame in Excel using the sample dataset shown below:

sample

1. Determine the numbers you will use in the equation. Using the sample dataset above,

  • The final value is 2143 (in cell B6).
  • The starting value is 1000 (in cell B2).
  • The number of years is 5 (in cell A6).

2. Enter your value into the formula.

Excel provides many shortcuts so you can simply enter the cell number containing each value into an equation.Using the sample dataset above, the equation would be

= (B6 / B2) ^ (1 / A6) -1

Here’s what it looks like in my Excel sheet:

Calculate cagr in excel step 2: enter your number of cells into the equation

Note that the equation changes color to correspond with the cell you’re working with, so you can go back and check that your input is correct before running the equation.

cagr formula using cell numbers in excel

You can also enter actual values ​​instead of cell numbers in formulas. The equation will look like this:

=(2143/1000) ^ (1/5) – 1

3. After entering values, click Enter and run the equation. Your results will appear in the cell containing the equation, as shown in the image below.

The cagr equation calculates the final result in excel

CAGR formula in Excel as a percentage

Your default result will be displayed as a decimal.To view it as a percentage, right-click the cell where the result is located, select format cell Then percentage in the dialog.

Your results will be converted to percentages as shown in the image below.

cagr forumla in excel as a percentage

Now let’s review the shortcuts for calculating CAGR using the Rate function in Excel.

How to Calculate CAGR Using the RATE Function

The RATE function helps you calculate the interest rate on an investment over a period of time. The formula for calculating CAGR is:

=RATE(nper,, pv, fv)

  • nper is the total number of cycles in the time range you want to measure. Since you are calculating the annual growth rate, this will be 12.
  • Photovoltaic is the present value of your investment (must always be expressed as a negative number)
  • fv is the future value.

Note that the standard RATE equation contains more variables, but you only need the three above to calculate CAGR.

Let’s run an equation using the example table below, where nperi is 12, pv is 100, and fv is 500.

Calculate CAGR using the RATE formula in Excel

1. In your worksheet, select the cell that you want to contain the CAGR. I selected cell B5.

2. Enter the RATE formula and enter your numbers.Note that you always Your present value needs to be represented as a negative number or you will get an error.

This is what my formula looks like.

Example cagr equation in Excel using rate formulaNote that you can also simply enter the cell number where your value is located. Using my example sheet, the formula looks like this:

=RATE(A2,,-B2,C2)

3. Click Enter and run your equation. Using the sample data, my CAGR is 14%.

Now you know how to calculate CAGR quickly and easily in Excel without the need for manual calculations.

excel marketing template