CAGR in Microsoft Excel

Recently I was asked “How can you prove that the CAGR answer is correct?“. In this post I’ll show you how to verify the CAGR result. I’ll also review CAGR basics and pitfalls.

 

Is the CAGR Correct?

We want the CAGR for 2006 to 2013 (sheet ‘CAGR answer proof’ in my Excel file). 

 

 

 

 

 

 

  • logic  =((EndYearAmount/StartYearAmount)^(1/NumberOfYearIntervals))-1   
  • formula  =((C11/C4)^(1/7))-1
  • answer  0.032926657892244 in cell H7

 

Now Let’s Prove It’s Correct!

We’ll use column D to increase original 302.50 amount in 7 steps (years or periods)

 

 

 

 

 

 

 

  1. Cell D4 has formula =C4 (cell C4 is 2006 amount or year 0 amount)
  2. Cell D5 has formula =D4*(1+$H$7)  Drag it down to cell D11
  3. Cell D11 calculated amount = original ending amount in cell C11

In step 2 (cell D5) we add 1 to our CAGR answer to get 1.032926657892244 so that column D amount increases as we drag formula down.

 

 

CAGR Review

CAGR formula (Compound Annual Growth Rate) is used to analyze and compare investments.

The CAGR formula below does all steps in a single formula. The pic above shows you what happens inside the formula year by year. Pic below goes into more detail.

=((EndYearAmount/StartYearAmount)^(1/NumberOfYearIntervals))-1

CAGR doesn’t take the difference between EndYear and StartYear amounts divided by number of years.  It would be easier to add 11 to starting amount 302.50 seven times to end up with 379.50 (columns G & H) but that’s not a CAGR. Let’s walk through the compounding nature of CAGR: 

  • Column E shows yearly increases:  (1+CAGR) X previous amount. Cell E11 = D11 year end value
  • Column F yearly increase difference (312.46 – 302.50 = 9.96 in cell F5) is a compounding amount (starts smaller, ends larger). Cell F17 average of F5 to F11 = 11.
  • Column F has compounding amounts, column G is always the same amount

 

 

 

 

 

 

 

 

 

 

Does this help explain CAGR’s compounding nature? In my Excel file see sheet ‘what CAGR does’. All 3 examples show CAGR starting smaller and ending larger. The mid point (year 4 in this example) is almost identical to the average. The charts show this well.

 

 

CAGR Pitfall

The most common pitfall is incorrectly entering the number of years.

If we want the CAGR for 2006 to 2013 that’s 7 intervals NOT 8! I’d suggest altering the formula to this:

=((EndYearAmount/StartYearAmount)^(1/(EndYear-StartYear)))-1

When replacing the text above with cells references it looks like this:

=((C11/C4)^(1/(B11-B4))-1)  dollar amounts in column C, years in column B.

CAGR is one of many financial calculations (i.e. IRR, MIRR, NPV). Each have their uses and limitations.

 

Further Reading About CAGR

Would you like to dive deeper into the math? I recommend these articles:

 

About Me

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.

It wasn’t my dog Cali who asked me to prove that my CAGR was correct. But it was a real question from just last week. We should question how a solution works. Imagine all the errors that would be eliminated if all solutions were reviewed carefully.

Fenton and Cali went to the vet this past weekend for shots and and a TRIM. They look so different with short hair 🙂

 

Leave a Reply

Your email address will not be published. Required fields are marked *