How to Create CAGR Graph in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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:

CAGR 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)

Where,
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.

Steps:

  • To begin with, we have the following known values.

Utilizing Combo Chart to Create CAGR Graph in Excel

  • 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)

Finding Slope to Create CAGR Graph in Excel

  • 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.

Insert Tab to Create CAGR Graph in Excel

  • After that, a default combo chart will pop up.

Basic Chart in Create CAGR Graph in Excel

  • 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.

Read More: How to Calculate 5 Year CAGR Using Excel Formula


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.

Inserting Column Chart to Plot CAGR Graph in Excel

  • So, a basic chart will appear.

  • Lastly, we will change the chart properties to make it look better.

Read More: CAGR Formula in Excel: With Calculator and 7 Examples


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.

Easy Way to Calculate End Value from CAGR

  • After that, use this formula to find the end value froom the CAGR value.

=D4*(1+D6)^D5

Read More: How to Calculate Future Value When CAGR Is Known in Excel (2 Methods)


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:

  • 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.

Calculate CAGR with Multiple Investments in Excel

  • 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.

=SUM(C5:C8)

  • Thirdly, type the following formula to calculate the CAGR with multiple investments.

=RRI(D10,C9,D9)

Read More: How to Use Compound Interest Formula in Excel (4 Easy Ways)


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. Moreover, you can visit our site, ExcelDemy, for more Excel-related articles. Thanks for reading. Keep excelling!


Related Articles

Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo