## How to calculate CAGR in Excel?CAGR refers to the 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 Some important point about CAGR - - CAGR is the Compound Annual Growth Rate, which is the same for all years.
- CAGR is different from the general growth rate.
- CAGR is repetitive in nature, while the traditional growth rate can be different for each year.
- CAGR is helpful to find the overall growth rate for an investment.
- 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.
## General CAGR formulaExcel 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 CAGRExcel 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 - - RRI() function
- RATE() function
## Calculate CARG in Excel using general formulaFirstly, 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.
In the following CAGR calculation, we will find the compound annual growth rate.
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
=(G4/B4)^(1/5)
This value helps to find the future revenue for a period of time. But this formula is incomplete yet.
=B7-1
CAGR value is much near to 20, so it will round off to 20, and you will get the values as following -
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).
=start value * growth rate =B9*1.1992
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.
## 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.
## Calculate Future value using CAGRWe 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:
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)
=start value cell reference * 1.1992
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 CAGRBy 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. ## SyntaxHere 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. ## ParametersAll three parameters are mandatory parameters of the RRI() function. **Nper -**number of years**Pv -**Present value (start value)**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:
Here, pv is 100 (in B4 cell), fv is 248 (in G4 cell), and number of years is 5, same as the above example.
=RRI(5, B4, G4)
Here, you can check and verify that - this compound annual growth rate is same as the above example, which we have got using this
This is the compound annual growth rate in percentage for these five years. ## Calculate CAGR using RATE() functionExcel 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. ## SyntaxFollowing is the syntax of the RATE() function: The first three parameters are mandatory, and other three are optional parameters.
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.
The RATE() function returns the Compound Annual Growth Rate in percentage.
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
=RATE(5, , -E3, E7)
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. ## ConclusionCAGR 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. Next TopicExcel to Word |