Solved

Cumulative Annual Growth Rate (CAGR)

Posted on 2014-07-29
4
313 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
4 Comments
 
LVL 49

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXCEL 2010 7 65
How to convert JSON file to csv? 7 84
EXCEL Addin problem 7 65
convert publisher file to an outlook email template 8 49
This article will show you how to use shortcut menus in the Access run-time environment.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

776 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