How to calculate Compound Annual Growth Rate - CAGR in Microsoft Excel?

What Does Compound Annual Growth Rate - CAGR Mean?

The CAGR is a smoothed rate of return because it calculates the growth of an investment as if it had grown at a constant rate on an annually compounded basis.

Investopedia explains Compound Annual Growth Rate

CAGR isn't the actual return in reality. It's an imaginary number that describes the rate at which an investment would have grown if it grew at a steady rate. You can think of CAGR as a way to smooth out the returns.

CAGR is one of those terms best defined by example. Suppose you invested $10,000 in a portfolio on Jan 1, 2005. Let's say by Jan 1, 2006, your portfolio had grown to $13,000, then $14,000 by 2007, and finally ended up at $19,500 by 2008.
Your CAGR would be the ratio of your ending value to beginning value ($19,500 / $10,000 = 1.95) raised to the power of 1/3 (since 1/number of years = 1/3), then subtracting 1 from the resulting number:

1.95 raised to 1/3 power = 1.2493. (This could be written as 1.95^0.3333).
1.2493 - 1 = 0.2493
Another way of writing 0.2493 is 24.93%.

Thus, your CAGR for your three-year investment is equal to 24.93%, representing the smoothed annualized gain you earned over your investment time horizon.

Mathematically the CAGR formula is written as:



How to calculate Compound Annual Growth Rate - CAGR in Microsoft Excel?

In Microsoft Excel you can use the function of "Power" to calculate CAGR. I am uploading a Compound Annual Growth Rate - CAGR Calculator. You can download it free from here. You can edit the information  presented in the "grey cells" to calculate the rate of return on your investments.

Application of CAGR in finance.
1) To calculate the average returns of investment funds and money managers.
2) Comparing the historical returns of various financial instruments like stocks with precious metals or fixed income products.
3) Analyzing and forecasting the value of sales and costs to company based on the CAGR of past data.

4 comments:

  1. Hi Anuj,

    Good morning,

    How are you ? long back seen your post!

    Thanks.

    ReplyDelete
  2. Hi Abhishek,
    Good afternoon, I am good, received your message after a long time too!
    How are you? I hope your trading is going well.
    Stay in touch :)

    ReplyDelete
  3. HI AJ,:)
    Back testing in final stages ?
    howz d net connection now?

    ReplyDelete
  4. Hi VJ,

    Back-testing is in initial stages ;)
    I was replying to your comment and it became the size of a post!.You might find it of use.

    http://financeandtradingmadeeasy.blogspot.com/2011/06/back-testing-results-swing-positional.html

    Internet's performance is still the same, will have to build a cell phone tower over my head to improve connectivity!

    Cheers :)

    ReplyDelete