Excel is a popular and useful tool for modeling growth and analyzing data. One such case is the calculation of CAGR. In this regard, Excel becomes a handy tool to perform such calculations effortlessly. Keeping this in mind, this article demonstrates 7 useful ways how to calculate 5 year CAGR using formula in Excel.
Download Practice Workbook
What Is CAGR?
First of all, let’s dwell a little upon what “CAGR” is.
CAGR stands for Compound Annual Growth Rate which is a measure of growth on the assumption that it compounds at a constant rate over a specified period of time. Typically, financial professionals use CAGR to evaluate and contrast numerous possibilities and come to a more informed decision.
Formula to Calculate CAGR in Excel
Now, the formula to calculate the CAGR is as follows:
- Final Value is the closing value of the growth model.
- Initial Value refers to the beginning value of the model.
- Year represents the time interval in years, months, quarters, etc.
7 Ways to Calculate 5 Year CAGR Using Formula in Excel
Suppose consider the Yearly Growth Rate of ExcelDemy Subscribers dataset shown in the B4:C10 cells, which shows the Year and Number of Subscribers respectively. Here, we want to calculate the Compound Annual Growth Rate of this data model, therefore, without further delay let’s explore all the nitty-gritty of each method with appropriate illustrations.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.
Method 1: Using Arithmetic Formula
First and foremost, let’s kick things off by applying the arithmetic formula of CAGR in Excel.
- At the very beginning, go to the C12 cell >> and enter the formula given below.
Here, the C5 and C10 cells refer to the Initial and Final Values while the 5 represents the Year.
📃 Note: Please make sure to press the CTRL + SHIFT + % keys on your keyboard to change the CAGR value to a percentage.
That’s it. You’ve calculated the 5 year CAGR formula in Excel. It’s that simple!
Method 2: Utilizing RATE Function
If you’re one of those people who enjoy using Excel functions, then the following methods have you covered. On this occasion, we’ll use the RATE function to calculate the growth rate of the subscribers and the ROW function to return the Initial and Final Values. So, let’s see it in action.
- In the first place, move to the C12 cell >> type in the equation given below.
In the above equation, the C5 and C10 cells indicate the Initial and Final Values while the 5 represents the Year.
- ROW(C10) → returns the serial number of a reference. Here, the ROW function returns the row number of the C10 cell.
- Output → 10
- ROW(C10)-ROW(C5) → becomes
- 10-5 → 5
- RATE(ROW(C10)-ROW(C5),,-C5,C10) → becomes
- RATE(5,,-C5,C10) → returns the interest rate per period of a loan or investment. Here, 5 is the nper argument that represents the number of periods, the pmt argument is left blank, the C5 cell refers to the pv argument which indicates the Initial Value of 26,455, and the C10 cell is the fv argument that points to the Final Value of 38,871.
- Output → 8%.
Finally, the results should look like the image shown below.
Method 3: Applying POWER Function
Alternatively, we can apply the POWER function to compute the 5-year CAGR formula of the growth model. For instance, the POWER function returns the result of the ratio of the Final to Initial Value raised to the reciprocal of the Year value.
- Initially, navigate to the C12 cell >> insert the following expression.
In this expression, the C5 and C10 cells point to the Initial and Final Values respectively whereas, 5 refers to the Year value.
- POWER(C10/C5,1/5)-1→ returns the result of a number raised to a power. Here, C10/C5 is the number argument that refers to the ratio of the Final to Initial Value. Following, 1/5 represents the power argument that indicates the raised indices.
- Output → 8%
Finally, the output should look like the screenshot given below.
Method 4: Using RRI Function
Normally, the RRI function estimates the corresponding interest rate for the growth of an investment. However, it can also be used to calculate the compound annual growth rate. It’s simple and easy; just follow along.
First, insert the following formula into the C12 cell.
Specifically, the C5 and C10 cells represent the Initial and Final Values, in contrast, 5 is the number of Years.
📃 Note: You can open the Format Cells dialog box by pressing CTRL + 1 and changing the cell formatting to percentage.
- RRI(5,C5,C10)→ returns an equivalent interest rate for the growth of an investment. 5 is the nper argument representing the number of periods, the C5 cell is the pv argument which is the Initial Value of 26,455, and the C10 cell is the fv argument referring to the Final Value of 38,871.
- Output → 8%
Subsequently, your result should look like the image shown below.
- A Daily Compound Interest Calculator in Excel (Template Attached)
- How to Calculate Compound Interest for Recurring Deposit in Excel
- Excel Formula to Calculate Compound Interest with Regular Deposits
Method 5: Applying GEOMEAN Function
Conversely, we can use the GEOMEAN function to enter a series of Growth Factors and obtain the geometric mean of this array which is the CAGR value. Now, allow me to demonstrate the process in the steps below.
- To begin with, go to the D6 cell >> enter the formula given below.
In this case, the C5 and C6 cells represent the Number of Subscribers for the Years 2020 and 2021.
- Then, use the Fill Handle Tool to copy the formula into the cells below.
- Next, insert the GEOMEAN function into the C12 cell.
For instance, the D6:D10 series points to the Growth Factor values for the Years 2021 to 2025.
- GEOMEAN(D6:D10)-1 → returns the geometric mean of an array or range of positive numbers. Here, D6:D10 is the number1 argument that refers to the series of Growth Factors.
- Output → 8%
Ultimately, the output should appear in the picture shown below.
Method 6: Employing IRR Function
Although the IRR function calculates the internal rate of return for a series of cashflows, it can be re-purposed to get the CAGR value. However, we have to manipulate the dataset to get the desired results. Hence, let’s see the process in detail.
Now, assuming the Yearly Growth Rate of Revenue dataset shown in the B4:C11 cells, which shows the Year and Revenue respectively.
- To start, insert zeros in the cells containing the intermediate Revenue values.
- Later, copy and paste the expression into the Formula Bar.
In this situation, the C5:C10 array indicates the Revenue values for the Years 0 through 10.
- IRR(C5:C10) → returns the internal rate of return for a series of cash flows. Here, C5:C10 is the values argument that refers to the series of Revenues.
- Output → 15%
Consequently, the CAGR value should be equal to 15%.
Method 7: Utilizing XIRR Function
Last but not least, we’ll employ the XIRR function which returns the non-periodic cash flows, so we’ll modify our existing dataset to show the Dates instead of the Year, such that we can compute the CAGR value.
- First, proceed to the C16 cell >> type in the equation given below.
For example, the C13:C14 and D13:D14 range of cells refer to the Initial and Final Values corresponding to the Number of Subscribers and the Date.
- XIRR(C13:C14,D13:D14)→ returns the internal rate of return for a schedule of cash flows. Here, C13:C14 is the values argument that refers to the Initial and Final Values for the Number of Subscribers. Following, D13:D14 represents the dates argument indicating the Initial and Final Values for the Dates.
- Output → 8%
Eventually, the results should look like the picture given below.
How to Calculate 3 Year CAGR Using Formula in Excel
So far, we’ve discussed the 5 year CAGR formula in Excel. If you want to obtain the CAGR for a different number of years, for instance, 3 years, the following section answers this exact question. In this case, we’ll apply the COUNT function to count the number of years in the dataset and obtain the CAGR value with the RRI function. So, follow along.
In the first place, enter the formula into the C10 cell.
Here, the B5:B8 cells represent the number of Years.
- In turn, type in the RRI function in the C11 cell as shown below.
On this occasion, the C5 and C8 cells represent the Initial and Final Values and the C10 cell is the Year value.
📃 Note: You can press CTRL + SHIFT + % shortcut to change the cell formatting to percentage.
Finally, your output should look like the screenshot given below.
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
To sum up, this article shows 7 effective methods on how to calculate 5-year CAGR using formula in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here. Lastly, visit ExcelDemy for many more articles like this.
- Calculate Compound Interest in Excel in Indian Rupees
- How to Create Quarterly Compound Interest Calculator in Excel
- Reverse Compound Interest Calculator in Excel (Download for Free)
- Methods to Apply Continuous Compound Interest Formula in Excel
- Formula for Monthly Compound Interest in Excel (With 3 Examples)