CAGR Formula in Excel: With Calculator and 7 Examples

In Microsoft Excel, there’s no exact built-in CAGR formula but we have numerous functions associated with the compound interest criteria. In this article, you’ll get to learn all possible methods to calculate compound annual growth rate or CAGR with suitable examples and simple explanations.

What is CAGR (Compound Annual Growth Rate)?

CAGR means Compound Annual Growth Rate. The term CAGR is too common while dealing with the calculations of compound interest in finance and business.

“The compound annual growth rate (CAGR) is the rate of return (RoR) that would be required for an investment to grow from its beginning balance to its ending balance, assuming the profits were reinvested at the end of each period of the investment’s life span.”

Source: https://www.investopedia.com/terms/c/cagr.asp

For example, someone has deposited an amount of $5000 in a bank for 3 years. After the end of 3 years, the bank would return him a total of $7500. So, in this entire period, the bank has actually attributed a compound interest rate of 14.47% to his initial investment.

In the meantime, the future value of his initial investment of $ 5,000 would be $5723.50 after the end of one year only. Now this 14.47% interest rate will be applied again to this $5723.50 for the second year and at the end of the second year, it’ll turn into $6551.69. Subsequently, after the end of the third year, the final future value will be $7500. This is how the amount of initial investment compounds annually with a particular interest rate known as CAGR or Compound Annual Growth Rate.

Basic CAGR Formula

The basic and most commonly used CAGR formula is as follows:

CAGR =(Future Value/Initial Value)^1/n-1

Here,

Future Value = The final amount at the end of all periods.

Initial Value = The initial investment or primary deposit at the beginning.

n = Number of periods through which the initial investment will be compounding each time.

The CAGR value usually shows up in the percentile. So, after the calculation with the above CAGR formula, the output has to be multiplied by 100 to convert the decimal or fraction into a percentile value. If the formula returns 0.0539, it refers to the rate of 5.39% (0.0539*100=5.39) accordingly.

CAGR Formula in Excel: 7 Suitable Ways

To calculate CAGR in Excel based on some required input values, we have actually several functions to apply. In the following methods, we’ll combine a generic mathematical formula as well as six different in-built functions separately to find out CAGR. You can utilize any of the formulas from them in your Excel spreadsheets depending on your requirements and preferences.

1. Calculate CAGR with Generic Formula

In the following picture, a dataset is present with 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 mentioned, we’ll calculate the CAGR with a generic formula.

In the output Cell C13, the required formula will be:

After pressing Enter and changing the number format to make it percentile, we’ll get the return value of 5.39%.

The number of total periods is in fact 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 the number of periods manually. In this case, it would be better to use the subtraction formula between the row numbers for the beginning and final years. We can use the ROW function here to input the row numbers in the subtraction formula for counting the number of compounding periods.

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

And the resultant value would be the same as found earlier.

2. Use of 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).

By 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). So, to calculate CAGR with the use of the POWER function, the required formula in Cell C13 will be:

Now press Enter and we’ll get a similar resultant value found in the previous method.

Read More: How to Calculate CAGR with Negative Number in Excel

3. CAGR Formula by Using Excel RATE Function

The RATE function returns the interest rate per period of a loan or an investment. 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.

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 a negative value otherwise the function will return a #NUM error.

So, the required formula to find out the CAGR in Cell C13 will be:

And after pressing Enter, we’ll see the return rate as 5.39%.

4. Insert IRR Formula to Find Out CAGR in Excel

We can also determine the CAGR by inserting the IRR (Interest Rate of Return) function in our spreadsheets. The IRR function returns the interest rate of return for a series of cash flows. 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 calculate the CAGR by using this IRR function too. But there are some criteria you have to maintain while inputting the arguments.

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

So, let’s make the first numeric value or the initial investment in Cell C5 negative.

Now press Enter and you’ll get the CAGR of 108.10%! But we’re supposed to get it as 5.39%.

Here, the problem with the formula is, along with the initial value, we have entered all compounded amounts in the selected range of cells (C5:C11) but these positive values have been counted as additional payments in this function. 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.

Now 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%. And here to be mentioned again, the first value in the range of cells must be a negative number.

Read More: Excel Formula to Calculate Average Annual Compound Growth Rate

5. Using RRI Function to Calculate CAGR

Probably the RRI function in Excel suits most to determine CAGR with all requirements. The RRI function returns an equivalent interest rate for the growth of an investment. 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:

In this formula, we’ve used the ROW functions to assign the total number of periods.

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

Read More: How to Calculate Future Value When CAGR Is Known in Excel

6. CAGR Formula with Excel GEOMEAN Function

We can also use the Excel GEOMEAN function to input the series of growth factors and obtain CAGR. The GEOMEAN function returns the geometric mean of an array or range of positive numeric data. The generic formula of this function is as follows:

=GEOMEAN(number1, [number2],…)

To find out the CAGR with the GEOMEAN function, we have to 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 here represents all the growth factors that we have found by adding ‘1’ to each percentage change.

To calculate the CAGR with the GEOMEAN function, the final formula in the output Cell C13 will be:

Now press Enter and we’ll get the growth rate as 5.39%.

7. Enter XIRR Function to Determine CAGR with Non-Periodic Cash Flows

In our final method, we’ll use the XIRR function. This function is applicable only when we have to go with the non-periodic cash flows. It means the cash flows have no certain periods or intervals.

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, a number of cash flows are present with their corresponding dates. The first value in the Value column is negative because this amount of money has been withdrawn initially. 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, the required formula with the XIRR function in Cell C11 will be:

After pressing Enter, we’ll get a return value of 4.90% for all non-periodic cash flows.

Read More: How to Calculate End Value from CAGR in Excel

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.

You can also look for a CAGR calculator online if you opt to avoid Excel spreadsheets. Here I would like to recommend the CAGRCalculator website. Their online calculator contains all possible input criteria. You can also find out 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 too 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’ll 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.

Download Practice Workbook

You can download our Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope all of the methods mentioned in this article will now help you to apply them in your Excel spreadsheets when you need to calculate compound annual growth rate (CAGR) with a formula. If you have any questions or feedback, please let me know in the comment section. Goodbye!

Related Articles

• How to Calculate 3-Year CAGR with Formula in Excel
• How to Calculate 5 Year CAGR Using Excel Formula
• How to Create CAGR Graph in Excel

<< Go Back to Compound Interest in Excel | Excel for Finance | Learn Excel