CAGR on a column chart

[William Kiarie]

Hi team,

I would want to have my CAGR shown / overlaid in my column chart. How would I go about that? You can share a file if you have an example done in Excel.

Thanks

 

[Luke M]

Calculate the CAGR per year, and add it to the chart. Since the numbers will be much smaller, plot it on a secondary axis. You didn't say whether you wanted the CAGR as another column or a line or dots, so in my example, I picked line.

Investment uses a random number generator, so you can hit F9 to see how chart changes depending on investment's performance.

 

[William Kiarie]

Thanks Luke for this, it is perfect response.

For a case where the values are generally trending up and where instead of using the CAGRs as you have done in your example, you use the CAGR for the last year, how would we present this single CAGR using a line chart overlaid on the column chart...something similar to the image shown.

 

[Luke M]

Can you elaborate on what you mean by "CAGR for the last year"? I'm not a finance guy, so I was just using the CAGR definition form WIkipedia, which looks like it's taking the increasing period of time. So, in simple speak, what should the CAGR be calculating?
Or, if you can post a spreadsheet with some numbers, then I can focus on just the chart portion.

 

[William Kiarie]

Luke sir,

The way you have calculated the CAGR is perfectly correct.

I think I get the rationale of the chart in the file you attached. Thanks.