- The CAGR is the compound annual growth rate that a financial investment can offer us
- Knowing this information will be of vital importance then to decide whether or not to invest in that fund.
- All that is needed is to know the periods and the values for each of those periods
Despite the fact that most of us might think at first that the only purpose of Excel is productivity, in reality this is a super complete -and complex- tool. There are numerous tricks only possible with it, some of which you’ll be glad to know right away. In this article, We show you how to calculate the CAGR, or compound annual growth rate, in Excel. This, so you know how much your investments are going to yield.
Is that no other complicated program is needed to discover how profitable an investment is. Excel will allow you to detect which investments are worthwhile, and which are not, as long as you apply this formula, of course. It really doesn’t have any difficulty to take advantage of the RRI function, which is what we will use in this case.
How to calculate CAGR in Excel with RRI function?
As we said, before starting it is essential to have some basic data to calculate the CAGR correctly. We will need to know essential information such as the periods and the values for each of those periods. You have to list the years in the first column, and the amounts in the secondAs seen in the image.
Then you have to select the cell where you want to calculate the CAGR, which is really indifferent. Beyond this, it is in that cell where you have to enter the RRI function that has the following syntax:
RRI(periods, present value, future value)
For the example we gave above, the concrete syntax should be as follows:
=RRI(A7,B2,B7)
While A7 contains all the periods that make up the investment, B2 contains the present value and B7 the future. That being said, to format the result of a percentage you have to go to the Home tab and click the Percentage Style button, or choose Percentage in the Number Style dropdown box.
Format CAGR as a percentage, the other option
Alternatively, you can insert the actual values into the formula instead of the cell references. It might not make much sense because the other solution is simpler, but it doesn’t hurt to know that too.
In this case, what we will do is opt for a formula with constants instead of cell references. Although the RRI function does an excellent job of calculating the CAGR of an investment, you can always perform these calculations by hand or enter that formula into Excel to double check the result of the RRI formula.
To do this, first Divide the future value by the present value. Then raise the result to an exponent of one divided by the number of periods or years.) Finally, subtract one from the result obtained.
Always following the previous example, This is how the formula we are talking about should be:
How you can see, this formula confirms that the result of the RRI function formula is accurate.
