Whenever you feel interested in getting into financial analysis or planning, you may have heard about CAGR or Compound Annual Growth Rate.
Our tutorial will allow you to learn about the various ways you can use Excel to calculate the CAGR.
These are the following:
- The use of the POWER function
- Use of the Operators
- The use of the IRR function
- Use of the Rate function
However, before we can dive into how we can have CAGR calculated in Excel, we first need to understand its meaning.
CAGR: What is it?
CAGR is also referred to as the Compound Annual Growth Rate.
It informs people about the growth rate at which their investments were able to grow every year.
If you could buy gold that is worth $100 last 2010 and it is now worth $500, the CAGR is 400%.
Take note that this is an entirely imaginary number.
Your gold may have increased by 13% from $100 in 2010 to $500 in 2020.
However, it doesn’t mean that it could grow at this rate each year.
In reality, the growth may be different.
However, this can indicate the amount of change that your investment in gold could give you every year.
As we see how you can use Excel to calculate the CAGR, its vitality relies on the fact that it can make it easier for anyone to have various options in investment compared.
How can you calculate CAGR?
Calculate for the CAGR with this formula:
CAGR = (Final value / Starting value)^(1/n) – 1
Here are ways you can use Excel to calculate for the CAGR:
Calculate CAGR in Excel with the Use of the POWER Function
In Excel, the POWER function is the ^operator’s replacement.
It can make the formula more clean and readable.
The equation =POWER(C3/C2,1/10)-1 is the POWER function that can provide the people with the CAGR in Excel.
Calculation of CAGR in Excel with the Use of the Operators
Whenever you have the Ending Value in cell C3 and the beginning value in cell C2, as what is shown here:
Here is the formula that will allow you to calculate the CAGR:
With this, 10 is the number of years from the start to the end of the investment period.
The CAGR of 11.6% means that the investment could grow at a rate of 11.6% annually.
It can help you compare it with other investments like government bonds and bank interests.
The Calculation of CAGR in Excel with the use of the IRR Function
Internal Rate of Return is what IRR stands for.
IRR and the other formulas are different because the use of the IRR allows you to account for various value payments that you made in this period.
For example, whenever you invest $200 in gold last 2009 and invest the same amount in 2014, you will have a final gold value of $600 by 2020. With the use of the IRR function, you can have the CAGR calculated.
You may not be able to do this by using Excel’s RATE of IRR functions.
However, whenever you use the IRR, you may have to structure your data as what you can see here:
These are the three scenarios wherein the outflow is negative and highlighted in red as the final value will be positive.
To get the CAGR, use this formula:
With this, you just need to have all the cells selected, wherein every cell represents the payment in an interval with equal spacing.
It can be interesting to note that in these two scenarios wherein the invested amount is similar, the one invested in one of the scenarios has a higher CAGR.
The Calculation of CAGR in Excel with the Use of the RATE Function
For this, you can calculate the CAGR with the RATE function whenever the time is provided with the start and ending value.
Created for something that is more than just the CAGR is the RATE function.
Here is the syntax of the function:
RATE(Nper, Pmt, Pv, [Fv], [Type],[Guess])
Here is what each argument means:
- Pmt: Payment value made for every period.
- Nper: The total amount of payments made in a certain period.
- [Fv] optional: All of the payments made in a specific period or the payment’s future value.
- Pv: All the payments made in a specific period or the payment’s present value
- [Guess] optional: Your guess on the rate that defaults to 10%.
- [Type] optional: Specifies the due date of the payments. It is 0 whenever the cost is expected from the start and 1 when they have expected it by the end. It defaults to 0 once it gets omitted.
Never worry about the complicated syntax and arguments.
As mentioned, you can use the RATE function for something much more than the CAGR calculation.
Yet, whenever you use Excel to calculate for the CAGR, you just need to have this syntax used:
=RATE(Nper, Pv, Fv)
Take note that these are three compulsory arguments whenever you calculate the CAGR in Excel.
The formula that can provide the CAGR value:
These are what you need to take note of about this function as you use Excel to calculate the CAGR.
- Since there are no regular payments, the second argument is left empty. You can have this used whenever you make payments regularly every year, quarter, or month similar to mutual funds.
- At the start of the value is a negative sign as it involves a cash outflow with the formula returning an error.
Note: You can have the RRI function used for calculating the CAGR in Excel.
These are just some of the recommended ways you can use Excel to calculate the CAGR.
Which one is the best for you?
Could there be any other method that you can use to calculate the CAGR in Excel.
Tell us about it in the comments!