How to use a CAGR formula in Excel (with tips and examples)

By Indeed Editorial Team

Updated 30 September 2022

Published 29 April 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

When a company looks to calculate their rate of growth over a particular period, it can use a Compound Annual Growth Rate (CAGR) formula. Although there isn't a predefined formula in Microsoft Excel for this, you can use categories in tables to apply a CAGR formula. This is a useful tool because it incorporates compound growth, allowing for more accurate calculations. In this article, we explain how to use a CAGR formula in Excel and discuss the pros and cons of this method.

What is the CAGR formula in Excel?

The CAGR formula in Excel is a mathematical function that can return Compound Annual Growth Rate (CAGR) values when using a set of figures. This is a useful way for businesses to perform financial planning from within Excel using Excel values. Companies can assess the overall health of their investments and make adjustments if they see issues with their return on investment. The CAGR formula can help determine the overall value of a return on an investment, which can be quickly calculated over a specified time frame.

The CAGR formula is a tool most often used by financial analysts, but it's equally useful for business owners and investors looking to determine how much a business has grown. Using CAGR gives you a fuller picture of the growth rate of an organisation and can give you an idea of what the return on investment might be year-on-year.

Related: What is ROI and how to calculate it (examples and formulas)

Example of the CAGR formula

To use the CAGR formula, you first define the terms and values you're going to use. When using the CAGR formula, it's important to remember that the overall value of something can change over the years. Usually, the values of things grow or shrink at an uneven rate over time. Using CAGR gives you a single rate that can define the returns over a specified period of time. For example, consider these year-end prices on company stocks:

  • 2019: £100

  • 2020: £120

  • 2021: £125

These figures show that between 2019 and 2020, there was a 20% increase in year-end prices. Between 2020 and 2021, there's been a 4% increase in year-end prices. The growth rates are clearly different year on year, but you can determine a single growth rate for all of the years at once with a growth rate formula. To do this, you require three variables: the beginning value, the ending value and the period of time in years.

Constructing a CAGR formula

Using these three variables, you can construct a CAGR formula. The basic formula is:

CAGR = (BVEV)n1 - 1 x 100

EV = Ending value, BV = Beginning value, n = Number of years. Using the above data points, your CAGR formula looks like this:

[(125 / 100) ^ (1 / 2) - 1] = 11.8

This means that you now have an overall growth rate of 11.8%, even though the growth rates year-on-year were different. It's worth noting that the return value is automatically converted into a percentage through the formula, which is the most effective way to use CAGR.

Related: What is operating profit? (With examples and calculations)

How to use the basic CAGR formula in Excel

Below is a guide on using the basic CAGR method in Excel. To begin, open the Excel program:

1. Input your values

Create a category in column A for the year and a category in column B for the amount. In the year column, input the values from the A2 cell and work downwards until you've added all values. Do the same for column B using the amount values.

2. Add the formula

With the values in place, you can now implement the CAGR formula. To do this, select any cell in column C and type in the following formula: =(BX/B2)^(1/Y)-1. Substitute BX for the ending value in column B and Y for the number of periods.

3. Format the result as a percentage

With the formula in place, you can now hit the enter key to finish. This generates the CAGR results in the cell that included the formula. Often, it's most useful to present the CAGR result as a percentage, as this is more useful for financial planning and analysis. To do this, select the cell that contains the CAGR result and adjust the cell format from ‘general' to ‘percentage'.

Related: Total variable cost formula (and how to calculate it)

Using the POWER function with a CAGR formula

Another approach to using the CAGR formula is to employ it in conjunction with the POWER formula, which can find CAGR values in your spreadsheets. The formula to use the POWER function is as follows:

=POWER (Ending value / Beginning value) - 1

Compared to the basic formula, the POWER function replaces the ˆ function. In Excel, the POWER function calculates the power of any given number or base.

Related: How to calculate mean in Excel and why it's essential

Using the RATE function with a CAGR formula

The RATE function can also work for CAGR formulas, although it's much less common than the other two approaches. The RATE function in Excel can be quite complex, which is why it's a less popular choice, but it can provide clean results when used appropriately. The formula to use the RATE function in Excel is:

=RATE (nper, pmt, pv, [fv], [type], [guess])

Unlike the previous formulas, the RATE formula has a considerable amount of variables and abbreviations. The abbreviations denote the following:

  • nper (mandatory): nper is the total number of payments made over a specified time frame.

  • pmt (mandatory): pmt is the value of a payment made over a specified time frame.

  • pv (mandatory): pv is the present value.

  • fv (optional): fv is the future value and not essential to the formula, although it can provide more precision.

  • type: type shows when payments are due. This value is set to either 1 or 0, with 0 denoting that the payment is due at the beginning and 1 denoting that payment is due at the end of the specified period.

Related: How to make a Gantt chart in Excel in 5 simple steps

Using the IRR function with a CAGR formula

IRR stands for Internal Rate of Return and can work with a CAGR formula. This is particularly useful when you're trying to determine the value of different value payments that have occurred over a specific time period. The formula for the IRR function is:

=IRR (values, [guess])

The ‘values' denotes the total range of numbers that represents the cash flows for the business. This section contains at least one positive and one negative value. The ‘guess' variable is an optional value that can guess what the return rate might be.

Errors found in a CAGR formula

Occasionally, you may receive an error when applying the CAGR formula. Most of the time, this is the result of a #VALUE! error. This appears if there are any supplied arguments that contain invalid values that Excel cannot recognise. To avoid this, always make sure that the beginning value is a negative number. Otherwise, you may see an error in your formula.

The advantages of using CAGR formulas in Excel

The CAGR formula is a useful and powerful tool that can help you determine compound growth rates for a company. From assessing the return on investment to measuring how well an investment is performing, the CAGR formula is a great way to monitor the health of investments and stocks. This allows companies and investors to gain a better understanding of their investments. There are a few associated benefits with using the CAGR formula, such as:

  • It allows investors to compare the performances of their various investments.

  • It's a useful way to measure and compare the overall performance of one stock over another.

  • It can determine what the expected returns on investment may be.

  • It can act as a risk-free instrument that measures the returns of specific investments over specified time frames.

  • It's considered one of the most dependable and accurate ways to calculate the return on investment over a specific time frame, and you can compare investments with different time scales.

  • It allows you to determine whether the potential worth of a risk taken in a specific investment is high enough to justify the risk itself.

Related:

  • What is Bayes' formula and what do you use it for?


Explore more articles