CAGR in Excel Formula (Table of Contents)
- Introduction to CAGR Formula in Excel
- Explanation of CAGR (Compound Annual Growth Rate)
- How to Use CAGR Formula in Excel?
Introduction to 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 that 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, the calculated value will be the same as that 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. CAGR helps smooth returns when growth rates are volatile.
As explained above, I will bring the same aspect; the compound annual growth rate isn’t an actual return value return rate. It is a representational figure. It is a number that describes the rate by which the investment would have grown if it had the same rate every year over the period.
In a 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 the growth rate from the initial investment value to the ending investment value if we assume that the investment has been compounding over a period.
4.9 (11,462 ratings)
View Course
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.
You can download this CAGR Formula Excel Template here – CAGR Formula Excel Template
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 B has sales done for the company in the year. Let us assume the sale price is in cr. (crores). By the CAGR formula in excel, we can calculate the annual growth rate.
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 Cell C2, let us calculate the annual growth rate by the formula.
So the result will be:-
- Now click on the % 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 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 the 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 the % sign in cell E2 or write =D2 in cell E2 and click on the % sign.
Now try putting any random values in the starting, ending, and the number of years. 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 the Formula in Excel. We have the 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 the % 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 the growth rate over time.
- It implies the growth was steady.
Cons
- By CAGR, we cannot assume the growth rate will be the same in the future.
- We cannot have insight into the uneven growth in the middle years of CAGR.
- 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 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 is a guide to the CAGR formula in excel. Here we have discussed the compound annual growth rate in excel and How to use the CAGR formula in excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
