Aug
3

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

by | updated on

The tutorial explains what the Compound Annual Growth Rate is, and how to make a clear and easy-to-understand CAGR formula in Excel.

In one of our previous articles, we unveiled the power of compound interest and how to calculate it in Excel. Today, we'll take a step further and explore different ways to compute Compound Annual Growth Rate (CAGR).

In simple terms, CAGR measures the return on an investment over a certain period of time. Strictly speaking, it's not an accounting term, but it is often used by financial analysts, investment managers and business owners to figure out how their business has developed or compare revenue growth of competing companies.

In this tutorial, we won't be digging deeply in arithmetic, and focus on how to write an effective CAGR formula in Excel that allows calculating compound annual growth rate based on 3 primary input values: investment's beginning value, ending value, and time period.

What is Compound Annual Growth Rate?

Compound Annual Growth Rate (CAGR for short) is a financial term that measures the mean annual growth rate of an investment over a given period of time.

To better understand the CAGR logic, let's have a look at the following example. Supposing, you see the below numbers in a financial report of your company:
Source data

It's no big deal to calculate a year-to-year growth rate using a regular percentage increase formula like shown in the screenshot below:
Calculating a yearly growth rate

But how do you get a single number that shows a growth rate over 5 years? There are two ways to compute this - Average and Compound annual growth rate. The compound growth rate is a better measure because of the following reasons:

  • Average annual growth rate (AAGR) is the arithmetic mean of a series of growth rates, and it is easily calculated using a normal AVERAGE formula. However, it totally ignores the compounding effects and therefore the growth of an investment can be overestimated.
  • Compound annual growth rate (CAGR) is a geometric average that represents the rate of return for an investment as if it had compounded at a steady rate each year. In other words, CAGR is a "smoothed" growth rate that, if compounded annually, would be equivalent to what your investment achieved over a specified period of time.

CAGR formula

The generic CAGR formula used in business, finance and investment analysis is as follows:
CAGR formula

Where:

  • BV - Beginning value of the investment
  • EV - Ending value of the investment
  • n - Number of periods (like years, quarters, months, days, etc.)

As demonstrated in the following screenshot, the Average and CAGR formulas return different results:
Average annual growth rate vs. Compound annual growth rate

To make things easier to understand, the following image shows how CAGR is calculated for different periods in terms of BV, EV, and n:
Calculating CAGR for different periods

How to calculate CAGR in Excel

Now that you have a basic idea of what Compound Annual Growth Rate is, let's see how you can calculate it in your Excel worksheets. Overall, there are 4 ways to create an Excel formula for CAGR.

Formula 1: Direct way to create a CAGR calculator in Excel

Knowing the generic CAGR formula discussed above, creating a CAGR calculator in Excel is a matter of minutes, if not seconds. Just specify the following values in your worksheet:

  • BV - Beginning value of the investment
  • EV - Ending value of the investment
  • n - Number of periods

And then, enter the CAGR formula in an empty cell:

=(EV/BV)^(1/n)-1

In this example, BV is in cell B1, EV in B2, and n in B3. So, we enter the following formula in B5:

=(B2/B1)^(1/B3)-1
Creating a CAGR calculator in Excel

If you have all investment values listed in some column, then you can add a degree of flexibility to your CAGR formula and have it calculate the number of periods automatically.

=(EV/BV)^(1/(ROW(EV)-ROW(BV)))-1

To calculate CAGR in our sample worksheet, the formula is as follows:

=(B7/B2)^(1/(ROW(B7)-ROW(B2)))-1
An improved CAGR formula for Excel

Tip. If the output value displays as a decimal number, apply the Percentage format to the formula cell.

CAGR formula 2: RRI function

The easiest way to calculate Compound Annual Growth Rate in Excel is by using the RRI function, which is designed to return an equivalent interest rate on a loan or investment over a specific period based on the present value, future value and the total number of periods:

RRI(nper, pv, fv)

Where:

  • Nper is the total number of periods.
  • Pv is the present value of the investment.
  • Fv is the future value of the investment.

With nper in B4, pv in B2 and fv in B3, the formula takes this form:

=RRI(B4, B2, B3)
RRI formula to calculate CAGR in Excel

CAGR formula 3: POWER function

Another quick and straightforward way to calculate CAGR in Excel is by using the POWER function that returns the result of a number raised to a certain power.

The syntax of the POWER function is as follows:

POWER(number, power)

Where number is the base number, and power is the exponent to raise the base number to.

To make an Excel CAGR calculator based on the POWER function, define the arguments in this way:

  • Number - ending value (EV) / beginning value (BV)
  • Power - 1/number of periods (n)
=POWER(EV/BV, 1/n)-1

And here is our POWERful CAGR formula in action:

=POWER(B7/B2,1/5)-1
CAGR formula based on the POWER function

Like in the first example, you can have the ROW function to calculate the number of periods for you:

=POWER(B7/B2,1/(ROW(B7)-ROW(B2)))-1

CAGR formula 4: RATE function

One more method for calculating CAGR in Excel is using the RATE function that returns the interest rate per period of an annuity.

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

At first sight, the syntax of the RATE function looks a bit complicated, but once you understand the arguments, you may like this way to calculate CAGR in Excel.

  • Nper - the total number of payments for the annuity, i.e. the number of periods over which a loan or investment should be paid. Required.
  • Pmt - the amount of the payment made each period. If omitted, the fv argument must be supplied.
  • Pv - the present value of the investment. Required.
  • Fv - the future value of the investment at the end of nper payments. If omitted, the formula takes on the default value of 0.
  • Type - an optional value that indicates when payments are due:
    • 0 (default) - payments are due at the end of the period.
    • 1 - payments are due at the beginning of the period.
  • Guess - your guess for what the rate might be. If omitted, it is assumed to be 10%.

To turn the RATE function into a CAGR calculation formula, you need to supply the 1st (nper), 3rd (pv) and 4th (fv) arguments in this way:

=RATE(n,,-BV, EV)

I will remind you that:

  • BV is the beginning value of the investment
  • EV is the ending value of the investment
  • n is the number of periods

Note. Be sure to specify the beginning value (BV) as a negative number, otherwise your CAGR formula would return a #NUM! error.

To calculate the compound growth rate in this example, the formula is as follows:

=RATE(5,,-B2,B7)

To spare yourself the trouble of calculating the number of periods manually, you can have the ROW function compute it for you:

=RATE(ROW(B7)-ROW(B2),,-B2,B7)
CAGR formula based on the RATE function

CAGR formula 5: IRR function

The IRR function in Excel returns the internal rate of return for a series of cash flows that occur at regular time intervals (i.e. days, months, quarters, years, etc.). It has the following syntax:

IRR(values, [guess])

Where:

  • Values - a range of numbers that represent the cash flows. The range must contain at least one negative and at least one positive value.
  • [Guess] - an optional argument that represents your guess at what the rate of return might be. If omitted, the default value of 10% is taken.

Because the Excel IRR function is not exactly designed for calculating compound growth rate, you would have to reshape the original data in this way:

  • The beginning value of investment should be entered as a negative number.
  • The ending value of investment is a positive number.
  • All intermediate values are zeros.

Reshaping the source data for an IRR-based CAGR formula

Once your source data is reorganized, you can calculate CAGR with this simple formula:

=IRR(B2:B7)

Where B2 is the beginning value and B7 is the ending value of investment:
CAGR formulas based on the IRR function

Well, this is how you can calculate CAGR in Excel. If you have been following the examples closely, you might have noticed that all 4 formulas return the identical result - 17.61%. To better understand and probably reverse-engineer the formulas, you are welcome to download the sample worksheet below. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

CAGR Calculation Formulas (.xlsx file)

You may also be interested in

Table of contents