In this article, you will learn about two quick methods for how to plot the CAGR graph in Excel. We will be using the Microsoft 365 version. The tutorial can be used with any version of Microsoft Excel starting in 2013.
What Is CAGR?
CAGR means “Compound Annual Growth Rate”, which is the required rate of return for a company to grow to reach its final balance value. Furthermore, we’ll assume that the profits were reinvested in the company’s operations. From this, we get a smooth growth rate for the company. Investors can also assess different companies by comparing their CAGR values.
- Now, we will show you two ways to calculate the CAGR growth rate value.
- Firstly, to calculate the CAGR, you can use the following formula:
- Secondly, you can use the RRI function to calculate the CAGR too. The syntax of the function is as follows:
=RRI (nper, pv, fv)
Where,
nper = Number of Periods
pv = Present Value or Beginning Value
fv = Future Value or Ending Value
Read More: CAGR Formula in Excel
2 Handy Approaches to Create CAGR Graph in Excel
There will be two simple methods to create the CAGR graph in Excel. Firstly, we will use the combo chart feature. Lastly, we will use the column chart to do this.
1. Utilizing Combo Chart
We will show you how to use the Insert Combo Chart feature to create the CAGR graph in Excel. However, the default settings are not enough to get the desired output. So, we will tweak the settings. Additionally, we will use the ROW and COUNT functions in this section.
Steps:
- To begin with, we have the following known values.
- Then, type the following formula in cell D11 to get the CAGR value.
=(C10/C5)^(1/B10)-1
- After that, use this formula to get the slope value.
=D11/COUNT(B5:B10)
- Then, insert another formula to find the CAGR graph values. We will plot these values in the secondary axis in a line chart to create the CAGR graph. After that, fill the formula down.
=$D$12*ROW(A1)
- Afterward, select the cell range B4:D10.
- Then, from the Insert tab ➤ Insert Combo Chart ➤ select Clustered Column – Line.
- After that, a default combo chart will pop up.
- Then, select the chart, and from the Chart Design tab, click on “Select Data”.
- So, a dialog box will appear.
- Then, deselect “Year”, and edit the horizontal axis labels.
- Afterward, set the Axis Labels to the cell range B5:B10 and press OK.
- Finally, with some modifications, the CAGR graph will look better.
Read More: Excel Formula to Calculate Average Annual Compound Growth Rate
2. Inserting Column Chart to Plot CAGR Graph in Excel
Column charts can be useful for plotting various types of data. However, the column chart alone is not enough to show the CAGR graph in Excel. Therefore, we will need to add line shapes manually to indicate the CAGR value.
Steps:
- Firstly, select the dataset range.
- Secondly, from the Insert tab ➤ Insert Column or Bar Chart ➤ select Clustered Bar.
- So, a basic chart will appear.
- Lastly, we will change the chart properties to make it look better.
Read More: How to Calculate Future Value When CAGR Is Known in Excel
Easy Way to Calculate End Value from CAGR
Now, we will show a quick way to calculate the end value from the CAGR value in Excel. We will use the modified formula from the CAGR section of this article.
Steps:
- To begin with, the following values are given.
- After that, use this formula to find the end value from the CAGR value.
=D4*(1+D6)^D5
Read More: How to Calculate CAGR with Negative Number in Excel
Calculate CAGR with Multiple Investments in Excel
In this section, we will show you how to calculate CAGR with multiple investments in Excel. We will be using the RRI and SUM functions to achieve the goal.
Steps:
- First, suppose a company has invested in 4 securities. Each of them was on the same date. Now, 5 years have passed, and our task is to find the CAGR. Moreover, the beginning and ending values are given.
- Next, add all the beginning and ending values. We have shown the formula to find the beginning value. Then, drag the Fill Handle to the right side to get the total ending value.
=SUM(C5:C8)
- Thirdly, type the following formula to calculate the CAGR with multiple investments.
=RRI(D10,C9,D9)
Download Practice Workbook
You can download the Excel file from the link below.
Conclusion
We have shown you two quick methods for how to plot the CAGR graph in Excel. If you face any problems regarding these methods, feel free to comment below. Moreover, you can also leave any feedback for us, so we can serve you better.
However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible.
Related Articles
<< Go Back to Compound Interest in Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
