How to Calculate the Compound Annual Growth Rate “CAGR” in Microsoft Excel 2010

Compound annual growth rate (CAGR) is a business and investment term that provides a constant rate of return over the time. It can be thought of as the growth rate that gets you from the initial investment value to the ending investment value if you assume that the investment has been compounding over the time.

CAGR is often used to describe some element of the business, although it is not an accounting term, for example, revenue, units delivered, etc. CAGR is useful for comparison of growth rates from different data sets such as revenue growth of companies in the same industry.
Let us see how CAGR works:

The formula for CAGR is:

CAGR = (EV / BV)^1 / n – 1

Where:

EV = Investment’s Ending Value

BV = Investment’s Beginning Value

n   = Number of periods (months, years, etc.)

CAGR is the ratio of your ending value to beginning value ($ 180,000 / $ 100,000 = 1.8) raised to the power of 1/5 (since 1/ number of years = 1/5), then subtracting 1 from the result:

img1

1.8 raised to 1/5 power = 1.1247. This could be written as 1.8^0.2=1.1247-1 = 0.1247

Another way of writing 0.1247 is 12.47%

Thus, CAGR for 5 year investment is equal to 12.47%

img2

The compound annual growth rate (CAGR) reflects how much income has been generated by the initial investment which gets reinvested again & helps in increasing the Ending Value. It is useful when investment experiences significant fluctuations in growth pattern, since a volatile market means an investment may see large returns first year, & losses in the next year. CAGR can be used to compare returns on different types of investments such as stocks, bonds, stocks and a savings account. CAGR can be used by Business owners to analyze the performance.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube