Understanding the Compound Annual Growth Rate (CAGR) is super helpful for checking how your investments are doing over time. It basically gives you the average yearly growth rate, without all the ups and downs that happen from year to year.
calculating cagr in excel
To figure out the CAGR in Excel, you’ll need three things: the starting value, the ending value, and the number of periods (usually years). With these, you can calculate the average yearly growth rate of an investment, which is great for comparing different investments over the same period.
The basic CAGR formula looks like this:
CAGR = (Ending Value / Beginning Value)^(1 / n) - 1
Where:
- Ending Value – This is what the investment is worth at the end of the period.
- Beginning Value – This is what the investment was worth at the start of the period.
- n – This is the number of periods (years) the investment grew over.
methods to calculate cagr in excel
Excel doesn’t have a specific CAGR function, but there are several good ways to calculate it:
- Using the
RRIfunction. - Using the arithmetic formula directly.
- Using the
POWERfunction. - Using the
RATEfunction. - Using the
IRRfunction.
method 1: using the rri function
The RRI function is probably the easiest way to calculate CAGR in Excel. This function figures out the equivalent interest rate for how much an investment has grown over a set amount of time.
The syntax for the RRI function is:
=RRI(nper, pv, fv)
Where:
nper– The total number of periods.pv– The present value (starting value).fv– The future value (ending value).
Let’s say we have some revenue data for a company:
| Year | Revenue |
|---|---|
| 1 | 1000 |
| 2 | 1100 |
| 3 | 1250 |
| 4 | 1380 |
| 5 | 1500 |
| 6 | 1700 |
| 7 | 1900 |
| 8 | 2100 |
| 9 | 2300 |
| 10 | 2600 |
To use the RRI function to calculate CAGR:
- Identify the starting value (pv), the ending value (fv), and the number of periods (nper). For example, the starting value is in cell B2, the ending value is in cell B11, and the number of periods is 9.
- Enter the following formula into a new cell:
=RRI(9, B2, B11)
3. Hit Enter and the CAGR will be calculated.
The result will be a decimal; format it as a percentage in the “Home” tab under the “Number” group. In this case, the CAGR is about 10.77%, showing the company’s revenue grew by an average of 10.77% each year.
method 2: using the arithmetic formula
You can also calculate the CAGR by using the basic formula we mentioned earlier with regular math operators.
- Using the generic formula, if your starting value is in cell B2, your ending value is in cell B11, and you have 9 periods, then enter the following formula:
=(B11 / B2)^(1 / 9) - 1
2. Press
Enter.3. Format the result as a percentage.
This gives you the same 10.77% CAGR.
method 3: using the power function
The POWER function makes the calculation simpler by doing the exponent part of the formula for you.
The syntax of the POWER function is:
=POWER(number, power)
Where:
number– This is the base number (ending value divided by starting value).power– This is the exponent (1 divided by the number of periods).
- Using the example data, enter this formula:
=POWER(B11 / B2, 1 / 9) - 1
2. Press
Enter.3. Format the result as a percentage.
This will also give you a CAGR of 10.77%.
method 4: using the rate function
The RATE function calculates the interest rate for an investment or loan. We can adapt this to find the CAGR.
The syntax for the RATE function is:
=RATE(nper, pmt, pv, [fv], [type], [guess])
For CAGR, you can simplify the function, leaving out some optional arguments:
=RATE(nper, , -pv, fv)
- Enter the following formula into a new cell, using the data:
=RATE(9, , -B2, B11)
2. Press
Enter.
3. Format the result as a percentage.
This will also give you a CAGR of 10.77%.
method 5: using the irr function
The IRR function finds the internal rate of return for a series of cash flows, which can be modified to calculate the CAGR.
- Adjust the data by setting the initial investment to a negative value, the final value as positive, and intermediate values as zero:
- Enter the following formula, assuming the modified cash flows are from B2 to B11:
=IRR(B2:B11)
3. Press
Enter to find the CAGR.The result will be your CAGR as a percentage.
Calculating CAGR in Excel is pretty easy once you know these methods. Whether you like using functions like RRI or using the arithmetic formula, Excel has lots of ways to analyze investment growth.