Table Of Contents
CAGR Formula in Excel (Compound Annual Growth Rate)
The CAGR formula in Excel is the function that is responsible for returning the CAGR value, the Compound Annual Growth Rate value, from the supplied set of values. If you are into financial analysis or planning, you will need to calculate the compound annual growth rate in Excel value in Excel spreadsheets.
The CAGR formula in Excel measures the value of return on an investment calculated over a certain period. The compound annual growth rate formula in Excel is often used in Excel spreadsheets by financial analysts, business owners, or investment managers, which helps them identify how much their business has developed or compare revenue growth with the competitor companies. With the help of CAGR, it can be seen how much constant growth rate the investment should return annually. In reality, the growth rate should vary from year to year.
For example, if you bought gold in 2010 worth $200 and $500 in 2018, CAGR is how this investment has grown yearly.
where,
- Ending Value = Investment's ending value
- Starting Value = Investment’s starting value
- n = number of investment periods (months, years, etc.)
Return Value:
- The return value will be numeric, which we can convert into a percentage because the CAGR is effective in percentage form.
How to Use CAGR Formula in Excel with Examples?
Let us understand how to use the CAGR formula in Excel with examples.
#1 - Basic Method
Let us consider the Excel spreadsheet below. Look at the data.
Below are the steps of the CAGR formula in Excel: -
- The above spreadsheet shows where column A has been categorized as "YEAR," and column B has "AMOUNT."
In column "YEAR," the value starts from the A2 cell and ends at the A10 cell.
Again in the column "AMOUNT," the value starts at the B2 cell and ends at the B10 cell.
Therefore, we can see that the investment's Starting Value (SV) is the B2 cell and the investment's Ending Value (EV) is the B10 cell.
- We have the values, which we can put in Excel's Compound Annual Growth Rate (CAGR) formula. However, to successfully do it in an Excel spreadsheet, we have to select any of the cells of the C column and type the formula as given below:
=(B10/B2)^(1/9)-1
In the above compound annual growth rate in Excel example, the ending value is B10, the beginning value is B2, and the number of periods is 9. You may see the screenshot below.
- Now press the "Enter" key. We will get the CAGR (Compound Annual Growth Rate) value result inside the cell where we input the formula. In the above example, the CAGR value will be 0.110383. The return value is just the evaluation of the CAGR formula in Excel with the values which have been described above. Consider the screenshot below.
- Note that Compound Annual Growth Rate in Excel is always represented in the form of percentages in the financial analysis field. To get the percentage, we must select the cell where the CAGR value is present and change the cell format from 'General' to 'Percentage.' The percentage value of CAGR (Compound Annual Growth Rate) in the above example is 11.04%. We can see the screenshot below.
The above steps show how you calculate the Compound Annual Growth Rate in Excel (CAGR) spreadsheets.
#2 - Using the Power Function
You can also use the POWER formula in Excel to find the CAGR value in your Excel spreadsheet. The formula will be "=POWER (Ending Value/Beginning Value, 1/9)-1". We can see that the POWER function replaces the ˆ, which was used in the traditional CAGR formula in Excel. Using the POWER function in the above Excel spreadsheet, using the conventional method to find the CAGR value, the result will be 0.110383 or 11.03%. Consider the screenshot below.
#3 - Using the RATE Function
It is a much lesser-used method for calculating the CAGR (Compound Annual Growth Rate) value or percentage but cleanly. The syntax of the RATE function in Excel can seem a bit complicated, but if we know the terms well, it will not be too hard. The syntax of the RATE function is given below: -
=RATE (nper, pmt, pv, , , )
Let us now have an explanation of the above-given terms.
- nper – (required) This is the total number of payments made in the specific period.
- pmt – (required) This is the value of a payment made in each period.
- pv – (required) This is the present value.
- fv – (optional) This is the future value.
- type – This means when the payments are due. The value is either 0 or 1. 0 represents the payment that has been due in the beginning, and 1 means that the payment has been expected at the end of the period.
#4 - Using the IRR Function
IRR is the abbreviation of the Internal Rate of Return. The IRR method is helpful when finding the CAGR (Compound Annual Growth Rate) value for different value payments made during a specific period. The syntax of the IRR function in excel is "=IRR (values, )." Values mean the entire range of numbers representing the cash flows. This section must contain one positive and one negative value. in an optional argument in the syntax, which means your guess at what the return rate might be.
CAGR Formula Errors
If you get any error from the CAGR formula Excel, this is likely the #VALUE! Error.
#VALUE!Error – This error will occur if any supplied arguments are not valid values recognized by Excel.
Things to Remember
- The Microsoft Excel CAGR formula returns the CAGR value, i.e., the Compound Annual Growth Rate in Excel from the supplied values set.
- CAGR measures the return value on an investment, which is calculated over a certain period.
- Using the Compound Annual Growth Rate formula in Excel, it can be seen how much constant growth rate should the investment return on an annual basis.
- If you get any error from the Excel CAGR formula, this is likely the #VALUE! Error.
