Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Cumulative Annual Growth Rate (CAGR)

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
gregfthompson
Asked:
gregfthompson
2 Solutions
 
Rgonzo1971Commented:
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
 
Patrick MatthewsCommented:
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
 
gregfthompsonAuthor Commented:
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
 
Dave BrettVice President - Business EvaluationCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now