Updated August 16, 2023
What is CAGR Formula in Excel?
Compound Annual Growth Rate, or CAGR in Excel, is a financial metric that calculates the average rate at which an investment grows over a specific period of time. Excel can be very useful for calculating CAGR as it has functions that can simplify the process.
However, Excel does not have a direct CAGR function for calculating the growth rate, but we can use functions like RRI, POWER, and RATE to determine the return rate on investment. Another option is to input the data into an Excel sheet and manually apply the mathematical CAGR formula to calculate it.
This article presents four simple formulas or functions that we can use to calculate CAGR in Excel. Each formula comes with an example, and a downloadable template is also provided for reference.
How to Calculate CAGR? (Formula, Examples & Template)
In this section, we will see the four different formulas and functions to calculate CAGR in Excel.
To make it easier for you to understand the calculations, we have included an Excel template that contains all the solved examples and functions in this article.
1. Simple Mathematical CAGR Formula
We can calculate the compound annual growth rate by using the below formula.
Where,
- EV = Ending Value (the value of the investment at the end of the period)
- BV = Beginning Value (the value of the investment at the beginning of the period)
- N = Number of Years
Example #1
Steffi purchased gold at an initial price of $15,000. After 4 years, the price of gold increased. Calculate CAGR considering the prices given or year-end prices only.
Solution:
We calculate CAGR using the formula given below:
CAGR = ($25,554 / $15,000)^(1 / 3) -1 = 19.43%
Example #2
Brown Pvt Ltd. wants to calculate the annual growth rate using the CAGR formula in Excel based on their sales data from different years. We have the company’s data available as follows:
Solution:
In this example, we can see that the initial sales value is 100 and the final sales value is 150. We can use 100 as the beginning value, and 150 as the ending value for our CAGR calculation. The number of years for this example is four years (2010-2011 to 2013-2014), i.e., N = 4.
Step 1: To calculate the CAGR, we add the following formula in Cell C2,
Step 2: Press Enter. Excel will now calculate the CAGR in Excel and display the result.
CAGR = ((150/100) ^ (1/4)) – 1 = 0.10668
Step 3: Now convert the result into percentages by changing the format of the cell in Excel from number to percentage.
To do this, go to the General section in the Home Tab and select the % option from the format dropdown list.
Step 4: Excel will show the Compound Annual Growth Rate for Brown Pvt Ltd in percentage, which is 11%.
2. RRI Function in Excel
The RRI function in Excel helps determine the interest rate at which an investment grows from its initial value to its future value. It is a function that can directly calculate the CAGR of an investment.
RRI Function Syntax:
In this syntax:
- “Nper” stands for the total duration of the investment
- “Pv” represents the initial investment or present value
- “Fv” is the future value, indicating the amount at the end of the investment period.
So, to calculate CAGR, we need to replace a few values in the syntax with specific values from the formula. Here, we replace “nper” with the number of years “N”, “pv” with the “beginning value”, and “fv” with the “ending value”.
The below table depicts how you can use the RRI function to calculate CAGR.
| RRI Function Syntax | RRI(nper, pv, fv) |
| RRI function for CAGR | RRI(N, Beginning Value, Ending Value) |
Example:
Imagine you want to find the compound annual rate of return for your investment of $10,000 over 6 years. We can use the RRI function in Excel, as shown below, to calculate the CAGR for your investment.
3. POWER Function in Excel
The POWER formula in Excel basically replaces the power operator (^) in the CAGR formula.
POWER Function Syntax:
In this syntax:
- “Number” is the value you want to find the power for
- “Power” is the exponent value
However, to calculate CAGR using the POWER function, we need to substitute certain values in the formula. Specifically, we must replace “number” with “Ending Value/Beginning Value” and “power” with “1/N”.
Additionally, we need to subtract the “1” from the result separately, as we cannot add it to the POWER function. So we will add the “ – 1” at the end of the POWER function.
The table below explains how we use the POWER function to calculate CAGR in the table below.
| POWER Function Syntax | POWER (number, power) |
| POWER function for CAGR | [POWER(Ending Value/Beginning Value, 1/N)] – 1 |
Example:
Taking the same example as the RRI function, let us use the POWER function to calculate the CAGR for your investment of $10,000 over 6 years.
4. RATE Function in Excel
The RATE function in Excel calculates the interest rate for an investment for a period.
RATE Function Syntax:
In this syntax,
- Nper is the total number of payment periods (years) in an investment
- Pmt is the payment of the amount invested in every period (annual or quarter)
- Pv is the present value or the initial investment
- [fv] is the value of the investment by the end of the investment period.
- [type] shows if there are any due payments, either at the beginning (1) or end (0) of the period.
- [guess] is the estimated rate of return, and by default, we assume its value as 0.1 (10%).
To calculate CAGR, we substitute specific values from the CAGR formula into the syntax. We replace “nper” with “N”, “pv” with “beginning value”, and “fv” with “ending value”.
Other than the compulsory arguments, there are optional arguments (type, guess) in the function, which we can exclude or leave blank. Thus, we leave the “pmt” blank and omit the “type” and “guess” arguments.
You can understand how we use the RATE function to calculate CAGR in the below table.
| RATE Function Syntax | RATE(nper, pmt, pv, [fv], [type], [guess]) |
| RATE function for CAGR | RATE(N,, -Beginning Value, Ending Value) |
Example:
Keeping the same problem as above, we will use the RATE function to calculate the CAGR for the investment.
Reverse CAGR Formula
The reverse CAGR formula calculates the starting value of an investment given its ending value and the average annual growth rate over time. By finding the starting value, we can make future projections, determine the historical growth rate, etc.
The formula is:
To use this formula, you need to know the ending value of the investment and the CAGR over time. Then you need to choose the number of years over which we calculate CAGR. The resulting value will be the starting value of the investment.
Example:
Imagine Alicia receives $25,000 as a return on her investment that she made 5 years ago. Calculate the value that she had invested initially (beginning value) if the CAGR is 15%.
Uses of CAGR
- Average Growth Rate: CAGR provides an investment’s average annual growth rate over the specified period. It helps investors to determine the long-term growth potential of the investment.
- Investment Volatility: It considers the volatility of the investment’s returns over the specified period. If an investment has a high CAGR, it indicates that it has had high volatility, with significant fluctuations in value over time.
- Comparison of Investments: CAGR can be helpful in comparing the performance of different investments over the same time. It can help investors to identify the investment that has performed the best over the specified period.
- Predictive Ability: CAGR can be useful in predicting an investment’s future performance. If an investment has a high CAGR, it is more likely to continue to perform well.
CAGR Limitations
- Short-Term Volatility: CAGR may not accurately reflect short-term volatility or fluctuations in an investment’s performance. If an investment has experienced significant short-term volatility, the CAGR may not provide a complete picture of its performance.
- Lack of Context: It doesn’t account for the context of an investment’s performance, such as economic conditions or industry trends. Without this, it may be challenging to understand the performance of an investment.
- Assumption of Constant Growth: CAGR assumes that the investment’s growth rate is constant over the entire period, which may not be true. If an investment’s growth rate fluctuates significantly, the CAGR may not accurately reflect its performance.
- Time Limitations: CAGR usage can only be up to its calculated time. It may not help compare investments with different time horizons or evaluate their performance outside the specified time.
- Dependence on Starting and Ending Values: CAGR is heavily influenced by the investment’s starting and ending values. If these values are outliers or do not accurately reflect the investment’s achievements, the CAGR may not accurately measure its performance.
Things to Remember About CAGR Formula in Excel
The table below presents four formulas/functions for calculating CAGR and illustrates how each of them contributes to calculating the CAGR value.
| Function/Formula | Purpose |
| Simple Mathematical Formula | Calculates CAGR directly |
| RRI | Calculates CAGR directly |
| POWER | Calculates the power component ((Ending Value/Beginning Value) ^ (1/N)) of the CAGR formula |
| RATE | Calculates CAGR directly (When arguments are not included) |
CAGR Formula Calculator
| Ending Value (EV) | |
| Beginning Value (BV) | |
| Number of Year (N) | |
| CAGR | |
| CAGR = | (Ending Value (EV) / Beginning Value (BV))( 1 / Number of Year (N))- 1 |
| = | (0 / 0)(1 / 0)- 1 = 0 |
Frequently Asked Questions (FAQs)
Q1. What does the “#VALUE!” error mean while calculating CAGR in Excel?
Answer: The “#VALUE!” error in Excel suggests that there is an issue with the values that we have entered to calculate the CAGR. This error arises when one or more values in the CAGR formula are non-numeric, such as text, character, or other invalid input. To fix this error, you just have to make sure that all input values are numbers.
Q2. What does 5% CAGR mean?
Answer: A 5% CAGR means that investment has grown at an average annual rate of 5% over a specific period of time, taking into account the compounding effect of the investment. For example, if you invested $10,000 in XYZ stock five years ago and it’s now worth $12,762, the CAGR would be 5%, meaning the stock grew at an average annual rate of 5% during the five-year period.
Q3. What is a good CAGR ratio?
Answer: A good CAGR ratio depends on the investment, the investor’s goals, risk tolerance, and investment horizon.
For example, the S&P 500 index, often useful as a benchmark for the stock market’s performance, has historically provided an average annual return of around 10%. Therefore, a 12% or higher CAGR over the same period could be good.
Recommended Articles
This guide on the CAGR formula in Excel explains how to calculate the CAGR Formula in Excel using Simple mathematical formulas as well as Excel’s RRI, POWER, and RATE function. We have also provided practical examples and a downloadable Excel template. You can also go through our other suggested articles –
