In this tutorial, you’ll learn different ways to calculate the CAGR in Excel:
Using Operators Using the POWER function. Using RATE function. Using the IRR Function.
But before we dive into how to calculate CAGR in Excel, let’s first understand what it means (feel free to skip this section if you already know what it is).
What is CAGR?
CAGR or the Compound Annual Growth Rate tells us the growth rate at which our investments have grown on an annual basis. For example, suppose you bought gold worth USD 100 in 2010 and it is worth USD 300 in 2020, CAGR would be the rate at the which your investment in gold grew every year. Want to know more about CAGR, here is a detailed explanation on Investopedia. Note that this number is completely imaginary. If your gold grew at 11.6% from USD 100 in 2010 to USD 300 in 2020, it doesn’t mean that it grew at this rate every year. The actual growth could be different, but it gives us an indication of how much growth our investment in gold has given us on an annual basis. While we will see how to calculate CAGR in Excel, its importance lies in the fact that it makes it easier for us to compare different investment options.
How is CAGR calculated?
Here is the formula that will calculate the CAGR. Now let’s see how to calculate CAGR in Excel.
Calculating CAGR in Excel Using Operators
Suppose we have the Beginning value in cell C2 and Ending Value in cell C3 (as shown below):
Here is the formula that will calculate the CAGR: =(C3/C2)^(1/10)-1 Here 10 is the number of years between the beginning of the investment period and the end of it.
The 11.6% CAGR means that this investment has grown at a rate of 11.6% every year. This would also help you compare it with your other investments (such as bank interests or government bonds).
Calculating CAGR in Excel Using POWER Function
The POWER function in Excel is a replacement of the ^ operator. It just makes the formula more readable and clean. Here is the POWER function that will give us the CAGR in Excel. =POWER(C3/C2,1/10)-1
Calculating CAGR in Excel Using the RATE Function
In this case, the RATE function can calculate the CAGR when you provide the time period, beginning value, and ending value. The RATE function is made for much more than just CAGR. Here is the syntax of the function: Here is what each argument means:
Nper: The total number of payments done in the specified period. Pmt: Value of the payment made in each period. Pv: The present value of the payment (or all the payments made in the specified period). [Fv] (optional): The future value of the payment (or all the payments made in the specified period). [Type] (optional): It specifies when the payments are due. 0 when the payment are due in the beginning and 1 when due at the end of the period. If omitted, defaults to 0. [Guess] (optional): Your guess on the rate. Defaults to 10%.
Now don’t worry about all the arguments and the complicated syntax. As I mentioned, RATE function can be used for much more than just calculating the CAGR. However, when calculating CAGR in Excel, you only need to use the following syntax: Note that all three arguments are compulsory when calculating CAGR in Excel. Here is the formula that will give the CAGR value:
Now there are a couple of things you need to note about this function while calculating CAGR in Excel:
The second argument is left empty as there are no regular payments. This is used in cases where you make regular payments (monthly, quarterly, yearly), such as in the case of mutual funds. There is a negative sign for the beginning value as it is an outflow of cash. The formula would return an error.
Note: You can also use the RRI function to calculate CAGR in Excel.
Calculating CAGR in Excel Using the IRR Function
IRR stands for Internal Rate of Return. The difference between IRR and other formulas discussed above is that by using IRR, you can account for different value payments made during the time period. For example, suppose you invest USD 100 in gold in 2010 and then you invest the same amount again 2014, and your final gold value is USD 300 in 2020, then you can calculate the CAGR using the IRR function. This is something you can not do using the RATE of IRR functions in Excel. However, to use IRR, you need to structure your data as shown below:
Here there are three scenarios where the outflow is highlighted in red and is negative, while the final value is positive. Here is the formula that will give you the CAGR: All you need to do is select all the cells, where each cell represents payment in an equally spaced interval.
Also, it is interesting to note that in scenarios 2 and 3, the invested amount is the same (i.e., USD 100), but since it’s invested later in scenario 3, the CAGR is higher. These are some of the ways you can use to calculate CAGR in Excel. Which one do you like best? Or is there any other method you use to calculate CAGR in Excel? Let me know in the comments section.
How to Calculate Weighted Average in Excel. Calculating Loan Payments Using PMT Function. How to Calculate Age in Excel using Formulas. How to Calculate and Format Percentages in Excel. Calculating Standard Deviation in Excel. Calculating Compound Interest in Excel. Calculating Moving Average in Excel [Simple, Weighted, & Exponential] How to Calculate Square Root in Excel
