Solved

Cumulative Annual Growth Rate (CAGR)

Posted on 2014-07-29
4
329 Views
Last Modified: 2016-01-30
The attached spreadsheet has a CAGR.
I am attempting to adapt the microsoft example to calculate the CAGR on a transposed table.
CAGR-problem.xlsx
0
Comment
Question by:gregfthompson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 300 total points
ID: 40226404
Hi,

Your formula won't work because the initial investment is not there show MS example

-10000 first then Positive cash-flows


on your file the cumulated growth is 12.55% = (1.03^4)-1

Regards
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 total points
ID: 40228212
What you are in fact trying to calculate here is the internal rate or return, or IRR.

This is a completely different thing from compound annual growth rate.

As to why you have the error, Rgonzo1971 is correct: you cannot compute an IRR unless you have at least one positive cash event and at least one negative cash event.  All of your cash flows were positive.

In any event, I would urge you NOT to use IRR.  It is a flawed measure of investment return.  You are much better off using net present value (NPV) instead.
0
 

Author Closing Comment

by:gregfthompson
ID: 40235494
Thank you both.

Apologies for the tardy response.

I am seeking compound annual growth rate - trying to work out what it was over the last 6 years, to then try to forecast. It's volume (litres), not money.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 41441859
You can use RATE directly to calculate CAGR, ie to get the growth betwen D5 and H5:

=RATE(COUNT(D5:H5)-1,,-D5,H5)

= 3.0%
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question