Compounded Annual Growth Rate – Calculation in Excel

What is Growth Rate –

Growth Rate is the amount of increase that a specific variable has gained within a specific period and context.

Example –

Consider the below table of Sales of a company and its annual sales growth rate –

Yr 1 Yr 2 Yr 3 Yr 4 Yr 5
Sales 54.20 76.00 79.80 91.90 103.50
Year On Year Growth 0% 40.2% 5.0% 15.2% 12.6%

Now if an average investor looks at these figures he may not be able to find the steady growth rate at which the sales have grown up.

For this we need to use the formula of Compounded Annual Growth Rate.

What is Compounded Annual Growth Rate (CAGR) –

A Compound Annual Growth Rate (CAGR) measures the rate of return for an investment — such as a mutual fund or bond investment etc. — over an investment period, such as 5, 7, or 10 years etc.

The CAGR is also called a “smoothed” rate of return because it measures the growth of an investment as if it had grown at a steady rate on an annually compounded basis.

CAGR is a business and investing specific term for the geometric progression ratio that provides a constant rate of return over the time period.

How to Calculate CAGR in Excel

The Excel formula for calculation of CAGR is as follows è =POWER(G4/C4,1/4)-1

whereas = G4 is the Sales Amount in Year 5, C4 is the Sales Amount in Year 1, G4/C4; then 1 divided by n-1 i.e. total number of years minus one, in this case total number of years are 5 minus 1 for the base year which is to be excluded = 4. The last -1 after the closing bracket gives you the value of growth minus base year value 100%.

Don’t forget to format the result (formula) cell in “%” format in excel.

Let’s try this on your workbook and check whether its works proper….. the result should be 17.55% with the above example.

Cross check is given below –

CAGR isn’t the actual return in reality. It’s an imaginary number that describes the rate at which an investment would have grown if it grew at a steady rate. You can think of CAGR as a way to smooth out the returns.

Now take another example – if suppose you have invested in a fund INR 10,000 in the start of year 2010. The value of fund has been grown to INR 20,000 by end of 2013. It means you have invested in the fund for 4 years. At what rate your fund has grown till 2013?

The calculation is simple. End value 20,000 dived by start value 10,000 raised to power of1/3 minus 1. This can be written as =((20000/10000)^(1/3))-1 = 0.25992105 = 25.992105%.

Applications of CAGR –

The common applications of CAGR are –

1.       Calculating the average return of fund.

2.       Comparing the growth performance of investment.

3.       Forecasting future values based on the CAGR of a data series.

4.       Analyzing the behavior of different business measures such as sales, market share, cost etc.

CA Ghansham Joshi
Author is CA, Diploma in IFRS (ACCA, UK). He can be reached at g_m_joshi@hotmail.com

Leave a Comment