You’ve come across the term CAGR and you want to know the CAGR formula in Excel sheet? This post will gives you 3 different methods to do so in Microsoft Excel. But first, let’s understand what CAGR calculator Excel is.
What is CAGR?
CAGR or compound annual growth rate is simply the rate at something that grows over multiple time periods of years that take into account as the effect of compounding. CAGR is used especially if the growth and value of your investment has fluctuated broadly during the time period in question. You have to know that CAGR is an imaginary term. It used purely as an indicator which tells you what a number such as GDP has grown over a period of time. It is assuming that it grew at a stable rate. But in the reality, the actual growth rates will likely to be different for each year.
When you want to calculate CAGR, you should enter the beginning value, number of periods over which your investment has grown, and ending value. On the Microsoft Excel, use the drop down menu to select the length of the time period in question such as years, months, or even weeks. Now we begin to know the formula to count the composite of development value before we dive into Excel. The formula is
CAGR = (Ending value / Beginning value)^(1/N) – 1
N is the number of years.
Method 1: The direct way
There is no major explanation in the first method. You can use the formula above to calculate the CAGR in Excel. It looks something like this in the example:
CAGR = (O$10/F$10)^(1/9)-1
1. O$10 = Ending value
2. F$10 = Beginning value
Method 2: Using RATE to get the rate
Although this method is not too popular, but it is very clean and handy move to count the CAGR.
RATE = (nper, pmt, pv, [fv], [type], [guess])
1. Nper is the total number of payment periods in an income.
2. Pmt is the payment which can’t change over the life of the income and the payment that made each period. Pmt include interest and principal but no taxes or other fees. If “pmt” is absent, you should include the “fv” argument.
3. Pv is present value which is the total amount that a cycles of future payments is worth in the moment.
4. Fv is future value which is a cash balance that you want to make after the last payment is made. If “fv” is absent, it will be assumed as 0. Fv is optional.
5. Type is the number 0 or 1. It shows when payments are due.
It looks something like this in the example:
CAGR = RATE(9,,-F$10,O$10)
Method 3: The powerful way
You can use POWER function in Excel instead of plugging in the formula manually. This method make the formula cleaner. It looks something like this in the example:
CAGR = POWER(O$10/F$10,1/9)-1
That’s 3 methods to calculate CAGR Formula in Excel.