How to calculate compound annual growth rate (CAGR) in Excel (7 Methods)

Method 1 – Calculating CAGR with a Generic Formula

The following dataset contains some compounded amounts over successive years. The initial value is $5000, which has been compounded to $6850 after a period of 6 years. Using these values, we’ll calculate the CAGR with a generic formula.

Steps:

Enter the following formula in Cell C13:

=(C11/C5)^(1/6)-1

Press Enter and change the number format to a percentile. We’ll get a return value of 5.39%.

The number of total periods is the subtraction between the final and the beginning years for our dataset. So, for a large number of periods, it could be quite exhausting to count them manually. In this case, using the subtraction formula between the row numbers for the beginning and final years would be better. We can use the ROW function here to input the row numbers in the subtraction formula to count the number of compounding periods.

Use the ROW function to define the number of periods, the modified formula would look like the following:

=(C11/C5)^(1/(ROW(B11)-ROW(B5)))-1

The resultant value is the same as found earlier.

Method 2 – Using the POWER Function to Determine CAGR in Excel

The POWER function returns the result of a number raised to a power. The syntax of this function is as follows:

=POWER(number, power)

Where,

number = A valid numeric value.

power = The power (index) to be applied to the numeric value (base).

Using this simple POWER function, we can easily define the ratio of the future value and initial value along with the number of total periods (months, years).

Steps:

To calculate CAGR with the use of the POWER function,

Enter the following formula in Cell C13:

=POWER(C11/C5,1/6)-1

Press Enter, and we’ll get a similar result value to the one found in the previous method.

Method 3 – Using the Excel RATE Function

The RATE function returns the interest rate per loan or investment period. The syntax of this function is:

=RATE(nper, pmt, pv, [fv], [type], [guess])

Here,

nper = Number of total periods (in years or months).

pmt = Additional payment in each period.

pv = Present Value, known as Initial investment value.

[fv] = Option argument, denotes the Future Value of an investment.

[type] = Optional value. ‘0’ refers to the due payments at the beginning and ‘1’ refers to the due payments at the end.

[guess] = Optional argument, means estimated guess of the rate. If omitted, the default value will be 10% for this argument.

Steps:

To calculate CAGR with the RATE function, we have to use only three arguments: nper, pv, and [fv]. Here, the pv (Present Value) must be negative; otherwise, the function will return a #NUM error.

Enter the following formula in Cell C13:

=RATE(6,0,-C5,C11)

Press Enter to get the return rate as 5.39%.

Method 4 – Inserting IRR Formula to Find Out CAGR in Excel

The syntax of the IRR function is:

=IRR(values, [guess])

Here,

values = Range of cells or an array containing numeric values.

[guess] = Optional argument. An estimation of the interest rate, if omitted, the default will be 10%.

We can also calculate the CAGR using this IRR function. However, you must maintain some criteria while inputting the arguments.

In the following dataset, the compounded growths of the initial investment value have been displayed over many periods by year. If we input the investment value with all chronic growths in the IRR function, the function will return a #NUM! error. It’s because the initial value in the range of cells must be negative.

Make the first numeric value or the initial investment in Cell C5 negative.

Press Enter, and you’ll get a CAGR of 108.10%! But we need to get 5.39%.

The problem with the formula is that, along with the initial value, we have entered all compounded amounts in the selected range of cells (C5:C11). Still, this function has counted these positive values as additional payments. So, we cannot use this sort of range of cells where all compounded values are present. Rather, we have to use only the range of cells containing an initial value and a future value.

If we remove all intermediate amounts from the selected range of cells, the function will return an accurate compound annual growth rate of 5.39%. The first value in the range of cells must be a negative number.

Method 5 – Using the RRI Function to Calculate CAGR

The syntax of this RRI function is:

=RRI(nper, pv, fv)

Where,

nper = Number of total periods (in years or months).

pv = Present value or the initial investment.

fv = Future value or the final compounded amount after a certain period.

The required formula with the RRI function based on our dataset to find out CAGR will be:

=RRI(ROW(B11)-ROW(B5),C5,C11)

We’ve used the ROW functions in this formula to assign the total number of periods.

Press Enter, and the formula will return the CAGR of 5.39%, as found in all other previous methods.

Method 6 – Using the Excel GEOMEAN Function

The generic formula of this function is as follows:

=GEOMEAN(number1, [number2],…)

To determine the CAGR with the GEOMEAN function, we must add two more columns, as shown in the following image. The Change of Value column shows the percentage change of each preceding value from the Value column. The Factor column represents all the growth factors we found by adding ‘1’ to each percentage change.

To calculate the CAGR with the GEOMEAN function,

Enter the following formula in Cell C13:

=GEOMEAN(E6:E11)-1

Press Enter, and we’ll get the growth rate of 5.39%.

Method 7 – Entering the XIRR Function to Determine CAGR with Non-Periodic Cash Flows

The XIRR function returns the internal rate of return for a schedule of cash flows. The syntax of this XIRR function is:

=XIRR(values, dates, [guess])

Here,

values = Range of cells containing all cash flows.

dates = Corresponding dates for the cash flows.

[guess] = An estimated rate of return; if omitted, the default value is 10%.

In the modified dataset below, several cash flows are present with their corresponding dates. The first value in the Value column is negative because this amount of money was initially withdrawn. The rest of the values are positive as they’re all deposited amounts. The cash flows were recorded between 26 January 2021 and 26 January 22.

To find out the CAGR associated with the mentioned data,

Enter the following formula with the XIRR function in Cell C11:

=XIRR(C5:C9,B5:B9)

Press Enter to get a return value of 4.90% for all non-periodic cash flows.

Online Calculator with CAGR Formula

In the downloadable Excel workbook attached to this article, you’ll find the templates with all the formulas and methods described so far. You can use those templates as calculators where you’ll find all possible criteria to input.

If you prefer to avoid Excel spreadsheets, you can also look for a CAGR calculator online. I recommend the CAGRCalculator website. Their online calculator contains all possible input criteria. You can also find the future value, internal rate of return, and initial investment amount with other calculators associated with the compound interest formulas.

The following screenshot is an overview of the interface of their CAGR calculator. You have to input the starting value, ending value, and the no. of periods only in the corresponding sections.

The calculator also returns the growth table for the compounded values over all periods.

You’ll find a graphical chart showing year-by-year compounded amounts.

Things to Keep in Mind

While using the RATE function to determine CAGR, the argument pv (Present Value) will be negative.
The argument pv will be positive in the RRI function.
The first or initial value for the IRR function has to be negative.
In the XIRR function, the cash flows must contain at least one negative value; otherwise, it will return a #NUM! error.
For non-periodic cash flows, the percentage of CAGR for a similar future value won’t be identical to the CAGR calculated for the periodic cash flows.