CAGR line in Excel Charts

Hello friends!! We’ll be learning how to add CAGR line in bar or Line in single chart in Excel. I’ve attached the workbook to download and reuse.

Compound annual growth rate (CAGR) is the rate of return that would be required for an investment to grow from its beginning balance to its ending balance, assuming the profits were reinvested at the end of each year of the investment’s lifespan. (ref: Investopedia)

CAGR = (Future Value/Present Value)^(1/n)−1
n- number of years

Suppose you want to show the present values and the CAGR in single chart in excel. Quick and easy to follow excel tutorial even if you are new to Excel charts.
By end of the tutorial we’ll be having something like image below

Data

I had the data in following format; with Year and sales separate columns.

We’ll calculate the CAGR and the values required to plot the CAGR line.

CAGR = ((1910/1437)^(1/9))-1
= 0.03

Now since we want to plot a line with constant slope(upward or downward based on the CAGR value), we need to have y values(CAGR plot in image below) for CAGR line. Parameter is x values for CAGR line

Slope = CAGR/number of data points
= 0.03/10
= 0.003
CAGR Plot = Slope * Parameter

Creating chart

Once the data in required shape. Select the data range and insert the bar chart., you’ll have something like below chart.

Now right click on the chart area and click ‘Select Data’. You’ll have a dialogue box as image below. Then
1. Remove Year and Parameter from the left series box
2. Edit the Axis label range to Year range

This step will remove the unnecessary columns.

Adding CAGR line

Now right click on any of the bars and click ‘Select Series Chart Type‘.
Once you’ve the dialogue box open change the chart type of CAGR plot to line and make it secondary axis and that’s done.

After some basic formatting changes you’ll the required chart. So it was about the adding CAGR line in Excel charts.

Download the workbook here!

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries