How to Calculate 3-Year CAGR with Formula in Excel (7 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Excel is a popular and helpful 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. This article will show you how to calculate the 3-year CAGR formula in Excel.


What Is CAGR?

CAGR stands for Compound Annual Growth Rate which is a measure of growth assuming 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. The conventional formula for CAGR can be defined in the following way.

Where,

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.

Suppose consider the Yearly sales amount. The dataset is shown in the B4:C8 cells, which shows the Year and sales amount respectively. Here, we want to calculate the Compound Annual Growth Rate of this data model, therefore, without further delay let’s explore all the methods with appropriate illustrations.


1. Using Conventional Formula to Calculate 3-Year CAGR

Our first method is based on the conventional formula. Here, we would like to utilize the basic formula of CAGR in Excel. To understand the process clearly, follow the steps.

Steps

  • In order to calculate the CAGR in Excel, select cell C10.
  • Then, write down the following formula.
=(C8/C5)^(1/3)-1

Using Conventional Formula to Calculate 3 Year CAGR with Formula in Excel

  • After that, press Enter to apply the formula.

Read More: How to Calculate End Value from CAGR in Excel


2. Applying RATE Function

Our second method is based on applying the RATE function. Here, we would like to utilize the ROW function to calculate the 3-year CAGR in Excel. To understand the process clearly, follow the steps.

Steps

  • In order to calculate the CAGR in Excel, select cell C10.
  • Then, write down the following formula.
=RATE(ROW(C8)-ROW(C5),,-C5,C8)

Applying RATE Function to Calculate 3 Year CAGR with Formula in Excel

  • After that, press Enter to apply the formula.

🔎 Breakdown of the Formula

RATE(ROW(C8)-ROW(C5),,-C5,C8): Here, the ROW function returns the row number of the C8 and C5 cells. Then, the RATE function returns the interest rate per period of a loan or investment. Here, 3 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 $15250, and the C8 cell is the fv argument that points to the Final Value of $25412.

Read More: How to Calculate Future Value When CAGR Is Known in Excel


3. Utilizing the POWER Function to Calculate 3-Year CAGR

Our third method is based on applying the POWER function. Here, we would like to utilize the POWER function to calculate the 3-year CAGR in Excel. To understand the process clearly, follow the steps.

Steps

  • In order to calculate the CAGR in Excel, select cell C10.
  • Then, write down the following formula.
=POWER(C8/C5,1/3)-1

Utilizing POWER Function to Calculate 3 Year CAGR with Formula in Excel

  • After that, press Enter to apply the formula.

🔎 Breakdown of the Formula

POWER(C8/C5,1/3)-1: The function returns the result of a number raised to a power. Here, C8/C5 is the number argument that refers to the ratio of the Final to Initial Value. Following, 1/3 represents the power argument that indicates the raised indices.

Read More: Excel Formula to Calculate Average Annual Compound Growth Rate


4. Using the 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. To understand the process clearly, follow the steps.

Steps

  • In order to calculate the CAGR in Excel, select cell C10.
  • Then, write down the following formula.

=RRI(3,C5,C8)

Use of RRI function to Calculate 3 Year CAGR with Formula in Excel

  • After that, press Enter to apply the formula.

🔎 Breakdown of the Formula

RRI(3,C5,C8): The function 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 $15250, and the C8 cell is the fv argument referring to the Final Value of $25412. The final output is 18.56%.

Read More: How to Calculate CAGR with Negative Number in Excel


5. Applying the GEOMEAN Function to Compute 3-Year CAGR

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.

Steps

  • In this method, first, we need to calculate the growth factor.
  • Set the growth factor 0 on cell D5 which denotes the first year of our calculation.
  • Then, select cell D6
  • Write down the following formula.
=C6/C5

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

  • After that, we would like to calculate the CAGR.
  • Select cell C10.
  • Then, write down the following formula.
=GEOMEAN(D6:D8)-1

Applying GEOMEAN Function to Calculate 3 Year CAGR with Formula in Excel

  • After that, press Enter to apply the formula.

🔎 Breakdown of the Formula

GEOMEAN(D6:D8)-1: The function returns the geometric mean of an array or range of positive numbers. Here, D6:D8 is the number1 argument that refers to the series of Growth Factors. Here, the output is 18.56%.


6. Utilizing 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.

Now, assuming the Yearly Growth Rate of Revenue dataset shown in the B4:C9 cells, which shows the Year and Revenue respectively.

Steps

  • First, insert zeros in the cells containing the intermediate Revenue values.

  • Then, select cell C11.
  • After that, write down the following formula.
=IRR(C5:C8)

Use of IRR Function to Calculate 3 Year CAGR with Formula in Excel

  • Press Enter to apply the formula.

🔎 Breakdown of the Formula

IRR(C5:C8): The function returns the internal rate of return for a series of cash flows. Here, C5:C8 is the values argument that refers to the series of Revenues. Here, the output is 20.95%.


7. Applying XIRR Function to Calculate 3-Year CAGR

Our last method is based on using 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 3-year CAGR value using the formula in Excel. To understand the method, follow the steps.

Steps

  • First, we need to put the initial and final sales amount and dates in a different place.

  • Then, select cell C14.
  • After that, write down the following formula.
=XIRR(C11:C12,D11:D12)

Utilizing XIRR Function to Calculate 3 Year CAGR with Formula in Excel

  • Then, press Enter to apply the formula.

🔎 Breakdown of the Formula

XIRR(C11:C12,D11:D12): The function returns the internal rate of return for a schedule of cash flows. Here, C11:C12 is the values argument that refers to the Initial and Final Values for the sales amount. Following, D11:D12 represents the dates argument indicating the Initial and Final Values for the Dates.


💬 Things to Remember

  • You need to press the CTRL+SHIFT+”%” keys on your keyboard to change the CAGR value to a percentage or You can open the Format Cells dialog box by pressing CTRL+1 and changing the cell formatting to percentage.
  • Excel’s compound annual growth rate formula can be used to determine how much the investment should return annually at a constant growth rate.
  • The #VALUE! error is most likely to be present if you encounter any Excel CAGR formula errors.

Download Practice Workbook

Download the practice workbook below.


Conclusion

To sum up, this article shows 7 effective methods for calculating the 3-year CAGR 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.


Related Articles


<< Go Back to Compound Interest in ExcelExcel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo