How to Calculate Compound or Average Annual Growth Rate

This post will guide you how to calculate the compound annual growth rate (CAGR) in excel. How to calculate the average annual growth rate (AAGR) using excel formula.

What is Compound Annual Growth Rate (CAGR) and AAGR?

CAGR measures the rate of return for an investment over an investment period. It is also called a “smoothed” rate of return because it measures the growth of an investment have grown on an annual basis.

The average annual growth rate (AAGR) is the arithmetic mean of a series of annual growth rates.

Calculate Compound Annual Growth Rate

To calculate compound annual growth rate, you need to divide the ending value of the investment by the value at the start of the period, then raise the result to the power of one divided by the number of years, and then subtract one form the subsequent result.

If you want to calculate compound annual growth rate, you can try to use the below generic CAGR formula:

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

BV – start value of the investment

EV – End value of the investment

N – Number of years

Assuming that you have the start value in Cell B1, the ending value of the investment in cell B2, the number of periods in B3. Then we can write down the following formula to calculate CAGR:

=(B2/B1)^(1/B3)-1

1# enter the above formula into cell C2

compound annual growth rate1

2# select the cell you entered the formula (Cell C2), then Go to HOME Tab, click “Percent Style” command under Number group to convert the number to percentage format.

compound annual growth rate2

You can also calculate compound annual growth rate using the POWER function to create the following generic excel formula:

=POWER(EV/BV,1/n)-1

For above example, you can use this generic formula to write down the following formula:

=POWER(B2/B1,1/B3)-1

compound annual growth rate3

There is another method for calculating CAGR in excel, you can use the RATE function to create the following generic formula:

=RATE(n,,-BV,EV)

N is the number of years

Bv is the beginning value of the investment

EV is the ending value of the investment

So you can enter the below formula in the Cell C2 to calculate CAGR:

=RATE(B3,,-B1,B2)

compound annual growth rate4

Calculate  Average Annual Growth Rate (AAGR)

You can use the below generic formula to calculate the average annual growth rate in excel.

AAGR=(Growth rate A + Growth rate B +…)/number of years

Assuming that you have a list of revenues for a 5-year period, you need to get the growth rate of each year firstly, such as, calculating the growth rate from 2013-2014 is (B3-B2)/B2=20%. So the generic formula of growth rate is as follows:

Growth rate = (Ending value – Beginning Value)/ Beginning value

And then you need to average those annual growth rates to get the AAGR value.

Let’s refer to the following steps to calculate CAAG:

1# enter the above formula of growth rate into the cell C3 to the growth rate in 2014, then press Enter key. Then drag the AutoFill Handle down to other cells.

=(B3-B2)/B2

average annual growth rate1

2# select the range C3:C6, then go to Home Tab, click “Percent Style” command under Number group

average annual growth rate2

3# you will see that you have the 4 annual growth rates for a 5-year period in C3:C6.

average annual growth rate3

 4# next you need to use the AVERAGE function to average all annual growth rates to get the AAGR. So enter the below formula in Cell C7.

=AVERAGE(C3:C6)

average annual growth rate4


Related Functions

  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Related Posts
Excel DSTDEVP Function
excel dstdevp examples1

This post will guide you how to use Excel DSTDEVP function with syntax and examples in Microsoft excel. Description The Excel DSTDEVP returns the standard deviation of a population based on the entire population of numbers in a column or ...

Excel DSTDEV Function
excel dstdev examples1

This post will guide you how to use Excel DSTDEV function with syntax and examples in Microsoft excel. Description The Excel DSTDEV returns the standard deviation of a population based on a sample of numbers in a column or database ...

Excel DMIN Function
excel dmin function example1

This post will guide you how to use Excel DMIN function with syntax and examples in Microsoft excel. Description The Excel DMIN returns the minimum value from a database or a column of a list that matches the specified conditions. ...

Excel DPRODUCT Function
excel dproduct examples1

This post will guide you how to use Excel DPRODUCT function with syntax and examples in Microsoft excel. Description The Excel DPRODUCT returns the product of values from a set of records that match criteria that you specify. The DPRODUCT ...

Excel XIRR Function
excel xirr examples1

This post will guide you how to use Excel XIRR function with syntax and examples in Microsoft excel. Description The Excel XIRR function returns the internal rate of return for a series of cash flows that is not necessarily periodic. ...

Excel VDB Function
excel vdb examples1

This post will guide you how to use Excel VDB function with syntax and examples in Microsoft excel. Description The Excel VDB function calculates the depreciation of an asset for a specified period based on the double declining balance method. ...

Excel SYD Function
excel syd examples1

This post will guide you how to use Excel SYD function with syntax and examples in Microsoft excel. Description The Excel SYD function calculates the sum-of-years’digits depreciation of an asset for a specified period. And it will return a numeric ...

Excel SLN Function
excel sln examples1

This post will guide you how to use Excel SLN function with syntax and examples in Microsoft excel. Description The Excel SLN function calculates the depreciation of an asset for one period based on the straight line depreciation. And it ...

Excel PV Function
excel pv examples1

This post will guide you how to use Excel PV function with syntax and examples in Microsoft excel. Description The Excel PV function returns the present value of a loan or investment based on constant payments and a constant interest ...

Excel RATE Function
Excel Rate examples1

This post will guide you how to use Excel RATE function with syntax and examples in Microsoft excel. Description The Excel RATE function returns the interest rate per payment period of an annuity. The RATE function is a build-in function ...

Sidebar