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

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: 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 previous 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 3: 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 4: 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 CAGR Calculation Worksheet. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

10 Responses to "How to calculate CAGR (compound annual growth rate) in Excel - formula examples"

  1. sanjeet shokeen says:

    I have a table in which first column i add date 1 July to 26 July 2016. In second column, I give rating to me (e.g. 0 to 10). I want to sum my rating only past 5 days but when last 5 day rating is less than 2, then add one more day. e.g.
    Date Rating
    15 July 5
    16 July 3
    17 July 0
    18 July 8
    19 July 2
    20 July 6
    21 July 7
    23 July 1
    24 July 5
    25 July 6
    26 July 3

    when I sum last 5 day (21 July to 26 July) is 22, but 23 July my rating is less than 2 than I add one more day in rating (20 July to 26 July) than my rating is 27, I remove 23 July in my life.

    Can you you help me in this.

  2. Rado says:

    In my experience you should never use fixed numbers in your formulas because it leeds to simple mistakes...
    For example in the power formula you should use:

    =POWER(B7/B2;1/COUNT(B2:B7))-1

    and the result is 14.47% because in the formula you use 5 years but your data isfor 6

    • Hello Rado,

      Thank you for your comment. Right, the data is for 6 years, which makes 5 periods. Therefore, instead of COUNT, you'd better use the ROW function to calculate the number of periods:

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

      The result will be 17.61% like in all other formulas.

  3. Anonymous says:

    write the output of the below triangular pyramid java program?
    *
    **
    ***
    ****
    *****

  4. Rikki says:

    Below is the problem that I already know the answer of 7.1% is the correct one. I must be making an error with the power function because I get a CAGR of 7.25% which I know is wrong. Thanks for any help.

    with this formula I get CAGR 7.25%? I've tried it many ways and can't come up with CAGR 7.1%

    =POWER(1+85.4%,1/(982-1))-1

    Date Return 1+Return
    2008 -37% 63.0%
    2009 26.60% 126.6%
    2010 15.10% 115.1%
    2011 2.10% 102.1%
    2012 16% 116.0%
    2013 32.30% 132.3%
    2014 13.60% 113.6%
    2015 1.40% 101.4%
    2016 11.90% 111.9%
    TOTAL 85.4%
    CAGR 7.1%

    sum of 2008-2016=982

    Thanks again if anyone can help!

    Post New | Post Reply | Reply Later | Create Poll

    Report Post | Recommend it!

    Print the post

  5. Kartick sur Roy says:

    Kindly CAGR procedure for investment in different date and value i.e.
    Date Invest
    10-10-2013 - 5000000
    28-01-2014 - 5000000
    31-10-2014 - 5000000
    22-12-2014 - 5000000

    Kindly solv the Formula for CAGR

  6. sabiha says:

    Sir how to calculate CAGR when starting four years value are zero and the no. of periods are 9. Kindly advice me

  7. sabiha says:

    2007-08 0
    2008-09 0
    2009-10 0
    2010-11 0
    2011-12 4.6
    2012-13 8.95
    2013-14 10.74
    2014-15 23.48
    2015-16 19.66
    2016-17 28.41
    Please tell me how to calculate CAGR in the above case

  8. Mehboob says:

    I need to calculate the rate of return on regular monthly savings in excel. Have tried the RATE formula but its not giving me the correct answer. The example I have used is:

    Present Value = £123.406
    NPER= 90 (number of months)
    PMT = -£500 (monthly saving)

    Please help!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
40
Ultimate Suite 2018.5 for Excel
40
Christmas sale
Best Price of the Year. Dec. 18 – 28