CAGR Formula In Excel

The compound annual growth rate (CAGR) is a metric commonly used during financial and economic analysis to measure the average rate of return on an investment over a specified period.

It provides potential investors with a comprehensive representation of an investment’s performance. 

Unlike a simple average, the compound annual growth rate takes the initial and ending investment values along with the number of periods between them to compute the growth rate of the investment.  

In today’s ever-changing, fast, and complex financial world, investors need the right tools and the most effective techniques to make informed investment decisions. 

Microsoft Excel is a popular tool thanks to its versatility in financial analysis. The CAGR calculation can be computed using various methods. 

Whether you are a financial analyst, business owner, or individual investor, the ability to calculate the compound annual growth rate is a valuable skill that helps evaluate investment performance, compare different investment opportunities, and make data-driven decisions.

In this article, we will review 4 methods to calculate the compound annual growth rate and understand how to read the arguments needed to satisfy the various CAGR computing formulas and nuances that need attention when doing these calculations. 

So, join us as we delve into CAGR in Excel!

Finding CAGR using Excel

As powerful of a tool as Excel is, there is no dedicated built-in function to calculate the compound annual growth rate. Instead, we must use one of four methods to compute CAGR. 

We can choose between converting the arithmetic formula using Excel’s relative referencing into an excel function. 

We can also use Excel’s exponent calculating formula POWER, the equivalent rate of return (=RRI) formula, the RATE formula, or convert our data table to estimate CAGR using the internal rate of return (=IRR) formula.

We can choose which method to use depending on the available data, whether we are calculating the compound annual growth rate as an intermediate calculation, how much time we have, and our comfort level with Excel.

1. Arithmetic Function Method

The generic method to calculate CAGR using Excel requires understanding the arithmetic formula for the compound annual growth rate. The formula is as follows:

where:

• Ending Value = The ending value of an investment
• Starting value = The initial value of an investment
• n = the number of investment periods (months, years, quarters, etc.)

Knowing this, we can manipulate Excel to find the compound annual growth rate. Below we have the value of an investment over 7 years:

The years the investment is held on to starts at the A2 cell and ends at the A8 cell. The value of the investment begins at cell B2 and ends at B8. We can see the starting value of the investment is $27,500, and the ending value is $31,000.

Using the arithmetic formula for CAGR and Excel’s relative referencing, we can type the following formula into a cell below to calculate our compound annual growth rate:

Excel does not automatically convert our value into a percentage. To fix this, we can select the cell and change the format from general to percent:

This is how you calculate the compound annual growth rate using the arithmetic function.

2. Power Function Method

Another method of calculating the compound annual growth rate is using Excel's POWER function. The POWER function takes two numeric arguments:

Where:

• number = A numerical value to represent the base number
• power = A numerical value to represent the exponent the base number is raised to

To use POWER to calculate our compound annual growth rate, we can denote our first numeric argument as the ending value divided by the beginning value and denote our exponent argument as 1 divided by the number of periods. 

Then, we can close the POWER function out and subtract 1 to calculate CAGR:

This is how you calculate the compound annual growth rate using Excel's POWER function.

3. RRI Function Method

Most of the time, individuals looking to calculate the compound annual growth rate will use the RRI function in Excel. The RRI function is designed to return an interest rate needed for an investment to reach a specific value in a specified period. 

Knowing this, we can interpret the result of our calculations using RRI as our compound annual growth rate. The RRI function takes 3 numerical arguments:

Where,

• nper = the number of investment periods
• pv = initial value of the investment
• fv = ending value of the investment

Now that we understand the arguments needed to use the RRI function in Excel, we can easily translate this to our compound annual growth rate formula, as they both use the same variables. 

Entering the respective values from our table into the RRI formula, we get the same value as we did when using the arithmetic method:

The "=RRI(...)" function will probably be the easiest and most convenient way to calculate the compound annual growth rate in Excel. 

It is important to note a fundamental difference between the RRI function method and the above methods. The RRI function does not need an additional "-1" outside the parentheses at the end of the formula. 

This is because the RRI function is designed to calculate a percentage rate and knows to account for it automatically. 

That is how you use Excel's RRI function to calculate the compound annual growth rate.

4. Rate Function Method

Excel's RATE function is an uncommonly used method to calculate the compound annual growth rate. The RATE function is composed of 6 arguments:

Where:

• nper = the number of investment periods
• pmt = the amount paid to or paid out each period (can be skipped for our purposes)
• pv = initial value of the investment
• [fv] = the ending value of the investment
• [type] = an optional value to specify the annuity type
• [guess] = your guess on the rate (can be skipped for our purposes)

Adjusting the RATE function for CAGR, we get the following formula for our example:

There are multiple important things to note about this equation:

• We input "0" for our pmt argument
• The pv argument is entered as a negative value
• We can ignore the [type] and [guess] arguments when inputting our formula

This is how you calculate the compound annual growth rate using Excel's RATE function.

5. IRR Function Method

Finally, we can use Excel's Internal Rate of Return function to calculate the compound annual growth rate. 

The IRR function is a reasonably tricky function to use for CAGR, having conditions that need to be met for an accurate result, but it is a viable option nonetheless. The Internal Rate of Return function takes only two arguments:

Where,

• values = range of expected cash flows. (The range must include at least one negative and one positive number)
• [guess] = your guess on the rate (can be skipped for our purposes).

Although the IRR function looks simple, we must remember that specific conditions must be met before performing our calculations. 

Firstly, the IRR function requires at least one negative value and one positive value in the table range used for the first argument. If you recall from our earlier examples, our data table only realizes the positive cashflows from our investment over 7 years.

This can be resolved in 3 steps:

1. Convert the initial investment value to a negative number (add a negative sign)
2. Leave the ending value as a positive number
3. Replace all the numbers in between with zeros

This transforms our table as:

Now that we have satisfied the condition needed to calculate IRR, we can insert the table range for our "Value" column to calculate the IRR:

The IRR function is relatively complex to calculate the compound annual growth rate because CAGR calculates cash flows. In contrast, the internal rate of return requires cash outflows and inflows to perform calculations. 

It is important to note that IRR is not a viable option to calculate the compound annual growth rate when the number of periods is over 20, as the calculations fail to converge to the same number.

This is how to calculate the compound annual growth rate through Excel's IRR function.

Summary 

The compound annual growth rate is a measure of growth in finance and economics that calculates the average rate of return on an investment over a specified period while accounting for the initial & ending investment.

Using the compound annual growth rate formula in Microsoft Excel is essential for anyone computing calculations for their finances, business planning, investment analysis, capital budgeting, and data analysis.

Although there is still no dedicated formula to calculate the compound annual growth rate in Excel, we can still utilize the platform to perform our calculations. As discussed above, multiple ways to calculate the CAGR exist, but the RRI function is the easiest. 

The syntax for the RRI function is "=RRI(nper, pv, fv)," where "nper" is the number of periods we are evaluating the investment over, "pv" is the present value (or the cash flow at year 1), and the "fv" is the future value (or the value of the cash flow at the last year).

Not only does the RRI function accept the necessary arguments for the compound annual growth rate, but it also does so without the need for additional operators (i.e., multiplication symbols, division symbols, etc.) or data table manipulation (Like the IRR function).

Researched and Authored by Rohan Rajesh | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Accretion Dilution Model
• Debt Schedule
• Payback Period
• Sensitivity analysis: What is it?
• Working Capital Formula