We already know that Excel is an incredibly powerful and useful tool, even if perhaps it is pretty pesky to get the hang of! One useful consultancy function to build in Excel is a Compound Annual Growth Rate (CAGR) Calculator. This is the measure of growth on an investment over a period of time, a particularly useful thing to find out if the investment fluctuates over time.
Watch this short video tutorial from this MBA e-mentor programme to understand the basics of CAGR and how it is calculated
Websites such as Investopedia http://www.investopedia.com/calculator/cagr.aspx have their own inbuilt CAGR calculators that can give you the numbers you need if you have the necessary information to hand. But to calculate the numbers from large sets of data you have collated in Excel, it is important to build your own CAGR calculator.
The following 8 websites will give you all the CAGR understanding you need
1) The best video tutorial to show the CAGR function input for a very basic table is offered by Savoir-Faire…
2) https://www.udemy.com/blog/cagr-formula-excel/ The Udemy blog gives a good simply explained introduction to CAGR. Also offers info on the more flexible XIRR function to achieve similar results.
3) However, as this video warns, XIRR can produce some incorrect results. So watch this to ensure you are not using wrong data – it really is imperative that the numbers are correct!!
4) http://www.chartrecipes.com/article-cagr.html Chart Recipes offers a good breakdown of how the CAGR calculation works and includes a downloadable pdf of the page and an Excel template with the charts used in the example so you can test it for yourself
5) http://www.make-money-stock-value-investing.com/cagr-formula.html A simple sheet that gives the spreadsheet CAGR formula too
6) Now of course CAGR functions can be used for bigger sets of data than these small tables. This Youtube video shows how to input the CAGR function in VBA format
7) http://guj800.wordpress.com/2008/06/19/create-your-own-excel-formulas/ This blog post explains the VBA format for the CAGR function
8) http://economistatlarge.com/featured-articles/compound-average-growth-rate This article includes how to calculate CAGR on a financial calculator as well as on Excel
Finally, as CAGR is an advanced Excel function, it may be worth signing up to Udemy’s ‘Microsoft Excel Advanced Functions and Formulas’ course here: https://www.udemy.com/microsoft-excel-advanced-formulas-and-functions/?tc=blog.cagrformulaexcel.text.p&utm_source=blog&utm_medium=udemyads&utm_content=post19152&utm_campaign=content-marketing-blog&xref=blog You get a free trial and lifetime access to the 58 tutorials when you buy the pack. You also get a certificate of completion which is useful to add to your CV.