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, 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 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 as the growth rate that we get from the initial investment value to the ending investment value if we assume that the investment has been compounding over a period.
4.9 (9,558 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.
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 the sale price is in cr. (crores). As we can see the sales go up and down. By the 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 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 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 values, ending values, and a 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 the 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 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 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 into the uneven in growth in the 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 the CAGR formula in excel. Here we discuss 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 –
