How to calculate CAGR in Excel?

CAGR refers to the Compound Annual Growth Rate. Excel does not have any direct formula to calculate it. You can use RRI(), RATE(), POWER, and other Excel functions to calculate CAGR. CAGR is calculated for an investment over a period of years.

CAGR is a bit complicated topic to understand for beginners but interesting too. Because, by calculating the CAGR, one can find out the average growth rate over your investment for a period of time (like 5 years, 6 years, and more).

In this chapter of CAGR, we will discuss different methods to calculate the CAGR, especially RRI() and RATE() functions.

What is CAGR?

CAGR stands for the Compound Annual Growth Rate. As the name implies, it is an average growth year-over-year for a given period. Compound Annual Growth Rate is calculated for an investment over a period of years. In simple words, CAGR help to measures how much you have earned on your investment every year.

Some important point about CAGR –

1. CAGR is the Compound Annual Growth Rate, which is the same for all years.
2. CAGR is different from the general growth rate.
3. CAGR is repetitive in nature, while the traditional growth rate can be different for each year.
4. CAGR is helpful to find the overall growth rate for an investment.
5. CAGR calculation requires three essential components, i.e., Present value (PV), Future Value (FV), and the number of years, to find the growth rate of your investment.

CAGR is the rate of return that is required for the investment growth from opening amount to closing amount. Assume that the profits were reinvested at each year-end of the investment’s life span. Basically, CAGR is a number or percentage (growth rate) based on which your investment grows every year.

General CAGR formula

Excel does not provide any direct formula to calculate CAGR for the respective data stored in an Excel worksheet. However, you can calculate by using its general formula:

This formula can also be written as follows –

Where, FV is future value, PV is present value, and n is the number of periods. These values same as

FV = End Value,

PV = Start Value, and

N = years (number of years)

If you already know the overall growth rate, i.e., (FV-PV)/PV for over a period of days, use the following formula instead. It means if you know the number of days instead of years.

Here,

(1+growth) = (End value/Start value)

(1/years) = (365/days)

These were the general formulas using which you can compute the CAGR in Excel. Besides this, there are some others methods to calculate the CAGR in Excel. Besides using the Excel CAGR formula, you can also use an online CAGR calculator to perform quick calculations.

In-built functions to calculate CAGR

Excel offers two or more functions to calculate CAGR. In this chapter, we are going to describe the following two in-built functions to calculate CAGR in Excel. They are –

1. RRI() function
2. RATE() function

Calculate CARG in Excel using general formula

Firstly, we will show you the detailed steps to calculate CAGR using its general formula. For this, we have a dataset stored in an Excel worksheet of 6 years (2011 – 2015) with revenue for each year.

In initial year (2010), the investment is 100 rupees only, which grew in next five years (2015) and increases to 248 at different growth rates each year. It means – for each year growth rate is different.

Example

In the following CAGR calculation, we will find the compound annual growth rate.

Step 1: Here, start value is 100 and end value is 248 to calculate the compound annual growth rate for 5 years.

Year value is 5 here because number of years should start counting from 0. Now, let’s calculate the CAGR using its general formula. Where,

Start value = 100, End value = 248, Years = 5

Step 2: Select a cell to store the result and write the following CAGR formula in it:

=(G4/B4)^(1/5)

Step 3: Hit the Enter key, and you will get the compound annual growth rate, i.e., 1.19919646.

This value helps to find the future revenue for a period of time. But this formula is incomplete yet.

Step 4: To complete the formula and get the actual CAGR result, subtract 1 from the resultant value, i.e.,

=B7-1

Compound Annual Growth Rate is: 0.19919646

Step 5: If you want CAGR value in percentage, just select both the calculated values and go to the Number group and click the % symbol.

CAGR value is much near to 20, so it will round off to 20, and you will get the values as following –

Compound Annual Growth Rate is: 20%

Although you can also use RATE() and RRI() functions in Excel. But we want to show you how it works. We will also brief these functions below in this chapter.

How this CAGR works?

You can now check and reverify the end value using this growth rate. Besides that, you can also find out the values for other years using this compound annual growth rate (CAGR).

Step 1: Copy the defined dataset and paste it into new rows. Leave the revenue only for the first year (2011) and delete others, as showing below.

Step 2: Now, use the following formula to get the money with interest for next year using the CAGR value.

=start value * growth rate

=B9*1.1992

“Type the growth rate manually in formula rather than passing its cell reference.”

Step 3: Hit the Enter key and get the calculated revenue for the year 2011.

Step 4: Now, select the calculated revenue cell of the year 2012. Hold the mouse to the right below corner of the cell; a + symbol will enable, drag this to the last.

It will automatically perform the same calculation for other years, and each time a new updated value will use for each year’s revenue calculation, i.e., pv*1.992.

Step 5: You will see here that the end value is 248.0 that is same as the original data.

Note: For this calculation, we have taken the growth rate same for all years. Although if you will see in the original dataset, growth is different for each year.

Conclusion: Similar to this verification, you can calculate the future value for other years using this growth rate.

Calculate Future value using CAGR

We will show you how this compound annual growth rate helps compute the revenue for future years. We will use this calculated CAGR to find the revenue for the next five years. Steps are almost the same but values are different. Learn with the example below:

Example –

For this time,

Start Value: 248

Compound annual growth rate is: 1.1992 (when CAGR 120%)

There are two formulas to find the investment money after five years (2016 to 2020)

Step 1: Create a new table as created below from the year 2016 to 2020 with start value 248. Write the formula as below:

=start value cell reference * 1.1992

Step 2: By hitting the Enter key, you will get the subsequent year’s revenue. Now, hold the mouse to the corner of calculate revenue and drag the + sign to the last.

Step 3: See all value now. You can remove number after point from the Excel ribbon by choosing the comma style (,) present in the Number section.

Except this, you can use another formula, which provides the same output –

=start value+(start value*0.1992)

Here, CAGR value is the second one (CAGR value from which you have subtracted 1). However, the formula is different, but it will provide you the same value.

RRl() function to calculate CAGR

By taking an example, we will show you how RRI() function helps calculate the compound annual growth rate. The RRI() function accepts three parameters: number of years, present value, and future value. It makes the calculation easy for you.

Syntax

Here is the syntax for the RRI() function –

As we already brief all three parameters. These three values are required by the RRI() to calculate the CAGR.

Parameters

All three parameters are mandatory parameters of the RRI() function.

1. Nper – number of years
2. Pv – Present value (start value)
3. Fv – future value (end value)

Steps to calculate CAGR using RRI()

The process we have used to calculate CARG using the general formula can also be done using the RRI() function. You will find this formula easier compared to the general CAGR formula. This makes that process easy and fast. So, let’s see how it will be done:

Step 1: Take the same dataset table for this example containing revenue for each year (2011 – 2016).

Here,

pv is 100 (in B4 cell),

fv is 248 (in G4 cell), and

number of years is 5, same as the above example.

Step 2: Write the following RRI() formula in a cell where you want to keep the resultant value.

=RRI(5, B4, G4)

Step 3: Press the Enter key and see the CAGR result returned after calculating using RRI() function.

Here, you can check and verify that – this compound annual growth rate is same as the above example, which we have got using this (end value/start value)^(1/years)-1 formula.

Extra: If you want growth rate in percentage, select the value and click on the % symbol inside the Number section in Home tab. You can see, it is 20% now.

This is the compound annual growth rate in percentage for these five years.

Calculate CAGR using RATE() function

Excel offers one more in-built function that helps calculate the CAGR. This function takes six parameters, in which the first three are essential and other are optional parameters.

Syntax

Following is the syntax of the RATE() function:

The first three parameters are mandatory, and other three are optional parameters.

Parameters	Description
Nper	This parameter takes the total number of payment periods.
Pmt	This is the payment amount. It always remains constant.
Pv	pv refers to the present value.
[fv]	Fv for future value. It is an optional parameter.
[type]	This refers to the payment type. “1” for the beginning of the period and “0” for end of the period.
[guess]	This one is also an optional parameter. It is an estimate what an internal rate of return should be.

To calculate the CAGR using the RATE() function, you have to provide the first parameter nper, third – pv value, and fourth – fv value. Leave the second parameter blank.

Note: Specify the present value (pv) in the third parameter with a negative sign. Otherwise, you will face #NUM! Error when returning the result.

Return value

The RATE() function returns the Compound Annual Growth Rate in percentage.

Example

We will show this example for the following dataset. We have five years of data where initial investment value is 2,00,000, which grows in the next four year and becomes 3,90,000.

So,

PV = 2,00,000, FV = 3,90,000, and nper = 4

Step 2: Write the RATE() formula as following with respect to the CAGR cell.

=RATE(5, , -E3, E7)

Step 3: Hit the Enter key, you will get the compound annual growth rate in percentage, i.e., 18%. It can be near the round-off value.

This growth rate can also be in normal form, as showing below:

Is CAGR and growth rate is different?

Yes, both the terms CAGR and growth rate are different. CAGR means “Compound Annual growth rate”, which is always the same for all years. Whereas, the growth rate can be different for each year. However, the end value will be the same after the final when applying CAGR on normal growth rate calculated data.

In the below screenshot, you can see and analyze the difference between them.

CAGR assumes that the growth rate is repeated in nature for each year. Whereas according to the traditional method growth rate can be different for each year. This one is the key difference between them. But at the end, ending value using both growth rates.

Conclusion

CAGR is a helpful technique to find the overall growth rate for an investment. It can calculate the past returns as well as future returns of your investment, as we calculated in the first example. Remember one thing – it does not calculate periodic investment accurately. It mainly focuses on the initial and final value of an investment for the calculation.

The conclusion is that – CAGR is very useful as it helps to analyze the growth of your investment.

---