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.
Download Practice Workbook
You can download the Excel file from the link below.
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 a 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 Excel RRI function to calculate the CAGR too. The syntax of the function is as follows:
=RRI (nper, pv, fv)
nper = Number of Periods
pv = Present Value or Beginning Value
fv = Future Value or Ending Value
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.
- To begin with, we have the following known values.
- Then, type the following formula in cell D11 to get the CAGR value.
- After that, use this formula to get the slope value.
- 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.
- 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 modification the CAGR graph will look better.
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.
- 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.
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.
- To begin with, the following values are given.
- After that, use this formula to find the end value froom the CAGR value.
Calculate CAGR with Multiple Investments in Excel
- Firstly, suppose a company has invested in 4 securities. Each of them 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.
- Secondly, 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.
- Thirdly, type the following formula to calculate the CAGR with multiple investments.
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. Moreover, you can visit our site, ExcelDemy, for more Excel-related articles. Thanks for reading. Keep excelling!
- How to Calculate Compound Interest in Excel in Indian Rupees
- Excel Formula to Calculate Average Annual Compound Growth Rate
- Formula for Monthly Compound Interest in Excel (With 3 Examples)
- A Daily Compound Interest Calculator in Excel (Template Attached)
- Compound Interest Formula in Excel: Calculator with All Criteria
- How to Calculate Compound Interest for Recurring Deposit in Excel
- Excel Formula to Calculate Compound Interest with Regular Deposits