How do I calculate CAGR in Excel?
Whether you’re a business owner, investment manager, or financial assistant, knowing your Compound Annual Growth Rate is extremely useful. It allows you to get a compact overview of how your business has grown, which opens up another door of possibilities. Compare revenue in different years, see what you did right, and do it again.
This article focuses on calculating your CAGR with Microsoft Excel. While there’s no specific function to get this done, you can approach the calculation from a few different angles and get accurate results. Let’s get started!
What is Compound Annual Growth Rate?
The question is: What is the formula for calculating compound annual growth rate?
Below is an overview of how to calculate CAGR both by hand and by using Microsoft Excel.
What Is CAGR?
To put it simply, the Compound Annual Growth Rate (CAGR) is used to measure the return of investment over a specified amount of time. While it’s not an accounting term, it’s used by financial experts to analyze various situations and make better, more thought-out decisions.
Let’s use an example to put CAGR into action. In this example, we’ll look at the year-end prices for a stock. This value changes every day, often at an uneven rate. CAGR allows us to calculate the one rate that defines the return for the entire measurement period:
-
Stock price in 2018: $140
-
Stock price in 2019: $155
-
Stock price in 2020: $170
As you can see, the growth rate from year to year is inconsistent. However, we can use the CAGR formula to find a single growth rate for the whole time period.
How to calculate CAGR using Excel [CAGR formula in Excel]
At the time of writing, there’s no dedicated formula for calculating your Compound Annual Growth Rate (CAGR) in Excel. Luckily, there are some alternative formulas you can use for this purpose. We compiled two guides below, approaching the calculation from two different angles.
Video: How to Calculate CAGR using Excel
How is CAGR calculated?
The guides below were written using the latest Microsoft Excel 2019 for Windows 10. If you’re using a different version or platform, some steps may vary. Contact our experts if you need any further assistance.
Method 1. Calculate CAGR manually - CAGR formula in Excel
Knowing the generic CAGR formula, you can create a calculator within a matter of minutes. What you need to do is specify the following 3 values in your worksheet:
- Launch Microsoft Excel and open the relevant document you want to work with or create a new one. Make sure you have data in your worksheet(s) before proceeding with the guide.
- Select an output cell for your final CAGR value, and then input your formula. The basic formula for calculating your CAGR is as follows:
-
BV - Beginning value of the investment
-
EV - Ending value of the investment
-
n - Number of periods
-
Your formula should look like this: =(EV/BV)^(1/n)-1. In our example, the formula would be =(B2/B1)^(1/B3)-1.
CAGR formula example
- Done! You can reuse this formula any time you need to calculate your Compound Annual Growth Rate.
Method 2. Use the RRI formula
Another way of calculating your CAGR in Excel is currently the RRI formula. It also has 3 core arguments: the number of periods, start value and end value. Once again, we’ve set up an example to show you how to use this function below.
- Launch Microsoft Excel and open the relevant document you want to work with or create a new one. Make sure you have data in your worksheet(s) before proceeding with the guide.
- Select an output cell for your final CAGR value, and then input your RRI formula:
-
nper - The total number of periods.
-
pv - The present value of the investment.
-
fv - The future value of the investment.
-
Your formula should look like this: =RRI(nper,pv,fv). In our example, the formula would be =RRI(B3,B1,B2).
- Done!
Method 3. Use the CAGR calculator template
CAGR formula excel download template:
Thanks to this automated calculator, you never have to worry about calculating your CAGR again. With Vertex42’s Online CAGR Calculator and the corresponding Excel template, you can easily get this number without having to input formulas manually.
Final thoughts
We hope this article helped you learn how you can calculate your CAGR using Excel’s formulas. Now, you’ll be able to see valuable information about your growth and make better decisions based on the results. We wish you the best on your journey! If you need any further help, don’t hesitate to get in touch with us.