Excel CAGR Formula

CAGR in Excel Formula (Table of Contents)

• CAGR Formula in Excel
• Explanation of CAGR (Compound Annual Growth Rate)
• How to Use CAGR Formula in Excel?
• Pros and Cons of CAGR Formula in Excel

CAGR Formula in Excel

CAGR in excel is used for calculating Compound Annual Growth Rate for any invested amount for the mentioned years or period. Although there is no direct function in excel which would help to find CAGR value, we can use the RRI function which also calculates the return rate on investment for this we need to have the number of payments to be made, present and future values of the invested amount. If we calculate CAGR from mathematical formulae then the calculated value will be the same as that of calculated by CAGR.

The formula to calculate CAGR is below:

To calculate the annual growth rate, divide the value of an investment at the end of the period by its value at the beginning of that period, raise the result to an exponent of one divided by the number of years and subtract one from the result.

Explanation of CAGR (Compound Annual Growth Rate)

CAGR is used to calculate the average growth of a single investment. Due to market volatility, the year to year growth may vary. The investment may increase and may decrease. CAGR helps smooth returns when growth rates are expected to be volatile and inconsistent.

As explained above I am going to bring the same aspect, the compound annual growth rate isn’t a real return value return rate. It is a representational figure. It is a number which describes the rate by which the investment would have grown if it had the same rate every year over the period.

In real scenario this sort of growth is unlikely to happen, however, it smoothes the returns so that it is easily compared to the other investments.

CAGR can be considered as the growth rate that we get from initial investment value to the ending investment value if we assume that the investment has been compounding over a period.

How to Use CAGR Formula in Excel?

CAGR Formula in Excel is very simple and easy to use. Let’s understand the working of the compound annual growth rate Formula in Excel by some examples.

CAGR Formula in Excel – Example #1

We have data for an ABC Company as below,

The data display value of sales done in the respective year. Column A has a year in which the sales are done. Column B has sales done for the company in the respective year. Let us assume that sale price is in cr. (crores). As we can see the sales go up and down. By CAGR formula in excel we can calculate the annual growth rate for the sales.

The starting balance is 100 and the ending balance is 150. Now we count the number of years. The first period of the cycle is 2010-2011 and the last is 2013-2014 which is for four years.

• Now in the Cell C2 let us calculate the annual growth rate by the formula.

So the result will be :-

• Now click on % sign in the Home Tab in the general section.

• Now we have the Compound Annual Growth Rate which is 11%.

CAGR Formula in Excel – Example #2

Let us make a ready formula where we will insert the values and it will calculate the compound annual growth rate in excel.

• We need a Starting Value. So in the cell A1 type a header for it as SV (Starting Value).

• Now for an Ending Value. So in Cell B1 type a header for it as EV (Ending Value).

• For the total number of years in cell C1 type a header for it as “NoY”.

• In cell D1 type a header for the final value and type header as CAGR (Compound Annual Growth Rate).

• Type the CAGR formula in cell D2 which is,

• Copy the value of D2 in cell E2 and use % sign in the cell E2 or write =D2 in cell E2 and click on % sign.

Now try putting any random values in the starting values, ending values and number of years. The cell D2 gives a CAGR value.

And exact Rate percentage is displayed in cell E2.

CAGR Formula in Excel – Example #3

Let’s take another example of CAGR Formula in Excel. We have below data,

• Find a starting Value as the beginning balance, which is B1.
• Find an Ending Value as the Ending Balance which is B5.
• Count the number of years, which is 3.
• In Cell C1 write down the formula for CAGR which is (Ending Balance/Starting Balance)˄(1/Number of Years) – 1.

The output will be :-

• Click on % sign in the Home Tab under the general section to get the value of CAGR.

• The final value is 1% which is the required annual growth rate.

Pros

• A compound annual growth rate in excel smoothed rate of growth over a period.
• It implies the growth was steady.

Cons

• By CAGR we cannot assume the growth rate will be the same in the future.
• By CAGR we cannot have insight about the uneven in growth in middle years.
• CAGR is not an absolute value.

Things to Remember about CAGR Formula in Excel

• CAGR Formula in Excel only calculates the average annual growth rate for a period of years.
• CAGR does not show the unevenness in the growth rate in the middle years.
• It only smoothed the growth rate over a period.

Recommended Articles

This has been a guide to CAGR formula in excel. Here we discuss the compound annual growth rate in excel and How to use CAGR formula in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. MS Excel: RANK Function
2. How to Use Excel PERCENTILE Function?
3. SLOPE Function in Excel with Examples
4. Guide To MODE Excel Function