The tutorial explains what the CAGR (Compound Annual Growth Rate) is and shows how to create a clear and easy-to-understand formula for calculating CAGR in Excel.
In one of our previous articles, we revealed the power of compound interest and how to calculate it in Excel. Today, we’ll go a step further and explore different methods to calculate the Compound Annual Growth Rate (CAGR)
AGR is a way to measure the return on an investment over a certain period. While it’s not technically an accounting term, financial analysts, investment managers, and business owners often use it to see how their business has grown or to compare the revenue growth of different companies.
In this tutorial, we won’t dive into complex math. Instead, we’ll focus on creating a simple and effective CAGR formula in Excel using three main inputs: the starting value of the investment, the ending value, and the time
Table of Contents
What is Compound Annual Growth Rate?
Compound Annual Growth Rate (CAGR) is a financial term that shows the average yearly growth rate of an investment over a specific period.
To understand how CAGR works, let’s look at the following example. Imagine you see the numbers below in your company’s financial report:
Calculating a yearly growth rate is easy with a basic percentage increase formula, like the one shown in the screenshot below
But how do you find a single number that represents the growth rate over 5 years? There are two methods: the average growth rate and the compound annual growth rate (CAGR). CAGR is a better choice for the following reasons
Average Annual Growth Rate (AAGR) is the simple average of a series of growth rates, and it’s easy to calculate with a basic AVERAGE formula. However, AAGR doesn’t consider the compounding effects, which can lead to an overestimation of an investment’s growth
Compound Annual Growth Rate (CAGR) is a geometric average that shows how an investment would grow if it increased at a steady rate each year. In simpler terms, CAGR is a ‘smoothed’ growth rate that, if applied every year, would match the overall growth your investment achieved over a certain period.
CAGR formula
The generic CAGR formula used in business, finance and investment analysis is as follows:
Where:
- BV – Beginning value of the investment
- EV – Ending value of the investment
- n – Number of periods (like years, quarters, months, days, etc.)
As demonstrated in the following screenshot, the Average and CAGR formulas return different results:
To make this easier to understand, the image below shows how CAGR is calculated for different periods using the values for Beginning Value (BV), Ending Value (EV), and the number of years (n)
How to calculate CAGR in Excel
Now that you have a basic understanding of what Compound Annual Growth Rate (CAGR) is, let’s explore how you can calculate it in Excel. There are four main ways to create a CAGR formula in Excel:
- Using the general CAGR formula
- Using the RRI Function
- Using the POWER function
- Using the RATE function
- Using the IRR function
Formula 1: Direct way to create a CAGR calculator in Excel
With the basic CAGR formula in mind, creating a CAGR calculator in Excel takes just a few minutes, or even seconds. Simply enter the following values in your worksheet:
- BV: Beginning value of the investment
- EV: Ending value of the investment
- n: Number of periods
Then, type the CAGR formula into an empty cell
=(EV/BV)^(1/n)-1
In this example, BV is in cell B1, EV in B2, and n in B3. So, we enter the following formula in B5:
=(B2/B1)^(1/B3)-1
If you have all investment values listed in some column, then you can add a degree of flexibility to your CAGR formula and have it calculate the number of periods automatically.
=(EV/BV)^(1/(ROW(EV)-ROW(BV)))-1
To calculate CAGR in our sample worksheet, the formula is as follows:
=(B7/B2)^(1/(ROW(B7)-ROW(B2)))-1
CAGR formula 2: RRI function
The easiest way to calculate the Compound Annual Growth Rate (CAGR) in Excel is by using the RRI function. This function is designed to give you the equivalent interest rate for a loan or investment over a certain period, based on the present value, future value, and the total number of periods:
RRI(nper, pv, fv)
Where:
- Nper is the total number of periods.
- Pv is the present value of the investment.
- Fv is the future value of the investment.
With nper in B4, pv in B2 and fv in B3, the formula takes this form:
=RRI(B4, B2, B3)
CAGR formula 3: POWER function
Another fast and simple way to calculate CAGR in Excel is by using the POWER function, which gives you the result of a number raised to a certain power.
The syntax of the POWER function is as follows
POWER(number, power)
Where number is the base number, and power is the exponent to raise the base number to.
To make an Excel CAGR calculator based on the POWER function, define the arguments in this way:
- Number – ending value (EV) / beginning value (BV)
- Power – 1/number of periods (n)
=POWER(EV/BV, 1/n)-1
And here is our POWERful CAGR formula in action:
=POWER(B7/B2,1/5)-1
Like in the first example, you can have the ROW function to calculate the number of periods for you:
=POWER(B7/B2,1/(ROW(B7)-ROW(B2)))-1
CAGR formula 4: RATE function
One more method for calculating CAGR in Excel is using the RATE function that returns the interest rate per period of an annuity.
RATE(nper, pmt, pv, [fv], [type], [guess])
At first sight, the syntax of the RATE function looks a bit complicated, but once you understand the arguments, you may like this way to calculate CAGR in Excel.
- Nper – the total number of payments for the annuity, i.e. the number of periods over which a loan or investment should be paid. Required.
- Pmt – the amount of the payment made each period. If omitted, the fv argument must be supplied.
- Pv – the present value of the investment. Required.
- Fv – the future value of the investment at the end of nper payments. If omitted, the formula takes on the default value of 0.
- Type – an optional value that indicates when payments are due:
- 0 (default) – payments are due at the end of the period.
- 1 – payments are due at the beginning of the period.
- Guess – your guess for what the rate might be. If omitted, it is assumed to be 10%.
To turn the RATE function into a CAGR calculation formula, you need to supply the 1st (nper), 3rd (pv) and 4th (fv) arguments in this way:
=RATE(n,,-BV, EV)
I will remind you that:
- BV is the beginning value of the investment
- EV is the ending value of the investment
- n is the number of periods
Note. Be sure to specify the beginning value (BV) as a negative number, otherwise your CAGR formula would return a #NUM! error.
To calculate the compound growth rate in this example, the formula is as follows:
=RATE(5,,-B2,B7)
To spare yourself the trouble of calculating the number of periods manually, you can have the ROW function compute it for you:
=RATE(ROW(B7)-ROW(B2),,-B2,B7)
CAGR formula 5: IRR function
The IRR function in Excel calculates the internal rate of return for a series of cash flows that happen at regular intervals (like days, months, quarters, or years). The syntax for the IRR function is
IRR(values, [guess])
Where:
- Values: A range of numbers representing the cash flows, with at least one negative and one positive value.
- [Guess]: An optional argument for your estimate of the rate of return. If you leave it out, Excel uses a default of 10%.
Since the Excel IRR function isn’t specifically designed for calculating compound growth rate, you’ll need to adjust your data like this:
- Enter the beginning value of the investment as a negative number.
- Enter the ending value of the investment as a positive number.
- Set all intermediate values to zero.
Once your source data is reorganized, you can calculate CAGR with this simple formula:
=IRR(B2:B7)
Where B2 is the beginning value and B7 is the ending value of investment:
That’s how you can calculate CAGR in Excel. If you’ve been following the examples closely, you may have noticed that all four formulas give the same result—12.89%. To help you understand and maybe even reverse-engineer the formulas, feel free to download the sample worksheet below. Thank you for reading, and I hope to see you on our blog next week!
