What is the CAGR formula?

CAGR can help you with financial analysis and planning, because it shows how your business investments have developed over a whole time period, and allows you to compare this with other competitors in your industry. The value of your investment will change over time, perhaps at an uneven rate year-by-year. CAGR is useful in that it shows you how much these investments have grown overall in that specific time period (for example, the overall growth of your business over a period of three years).

Pros and cons of using the CAGR formula

You might find CAGR more or less useful depending on your business needs at a given point. It’s also worth bearing in mind its weaknesses so that you can supplement it with other types of formula which can provide different types of information about your investment.

Strengths of the CAGR formula

You can use the CAGR to analyse growth across specific groups of different time periods and compare them with others. It also gives you the opportunity to consider how successful your investment opportunities are, or whether there are fluctuations.

CAGR can be more useful to investors than average growth, if you want to look at historical growth and compare investment value to those of your competitors. That’s because it smooths out changing year-by-year rates – and creates some consistency. It’s convenient as it can provide valuable insights while being easy to use.

Weaknesses of the CAGR formula

One main weakness of using the CAGR formula is that it doesn’t acknowledge the volatility of an asset, for instance if there’s a lot of growth in the investment’s value in the beginning years of a time frame, but a gradual decrease in value in the last years. It would be helpful in this case to show that the investment is decreasing in value but the CAGR wouldn’t account for that as it smooths out the overall value over that time period.

So with this in mind, let’s look at how to input the CAGR formula into Excel.

How to use the CAGR

The CAGR formula can be written as:

CAGR = (Ending Value ÷ Beginning Value) ^ (1 ÷ Number of Periods) – 1

In order to get the CAGR figure, you’ll firstly have to divide the ending value of the investment by the beginning value.

Then, take that number and raise it to the power of 1 (which is divided by the number of time periods that you’re looking to cover). Then finally, you subtract the return value by 1. You are left with the CAGR figure.

You don’t include the year zero in your calculations because we only count years when revenue is compounded. Working out the CAGR figure is easier with Excel – you just have to input numbers according to the formula we’ve shown above.

Inputting the formula for CAGR in Excel

Below, we’ll provide a step-by-step guide to inputting the CAGR formula into Microsoft Excel:

You’ll need to select the cells that include the ending value and beginning value, as well as the cell containing the number of time periods, so that your formula looks something like this:

(B2/B1)^(1/B3)–1

You’ll need to have this formula in an empty cell that you’re not using. ‘B2’ here might be your ending value cell in your Excel spreadsheet, and ‘B1’ could be your beginning value – but the exact cells in your spreadsheet will probably be different. The important thing is to remember to highlight the cells for the ending value, followed by the beginning value for it to be divided by.

In the above example, we’ve used ‘B3’ for the number of time periods – if you choose to use a cell for periods, you’ll need to highlight this too. So this will vary depending on which cell you’re using for this. An example of this in a table looks like this:

You might notice that the CAGR number that comes up first for you in Excel is a decimal. This means that you’ll have to change the format of the cell it’s in to ‘percentage’ in Excel for it to show as a percentage instead (in our example, the formatting of ‘B3’). Your CAGR result will finally be shown as a percentage, rather than as a decimal figure.

There are four different ways in total that you can calculate CAGR using Excel – including the example that we’ve just given. These are:

• The CAGR formula (shown above);
• The POWER function;
• The RATE function;
• The IRR function.

How to tell if your CAGR is ‘good’ or not

Once you’ve calculated your CAGR score, you can now decide whether it’s ‘good’ or not. You can do this by looking at the industry benchmark set by similar companies, or any external factors that could affect the overall figure provided.

Your CAGR score will probably look different depending on how mature your business is, and whether your company is early-stage or late-stage. Mature businesses tend to have less growth than early-stage businesses, and their CAGR score is usually between 3% to 5%. This can still be positive, even if this is a smaller figure than those seen by early-stage businesses.

Early-stage businesses might target much higher figures – at least 50%, as these are required in order to attract investors. Late-stage businesses usually aim for 10% to 20%.

The CAGR function can be a useful tool in order to find out how much your business has grown over a specific time period. However, early-stage businesses usually target higher figures than late-stage businesses, as they usually require this in order to attract investors. Some weaknesses of using the CAGR function include the fact that it doesn’t always acknowledge when an asset is volatile. However, it is usually good for looking at historical growth and comparing this to other similar businesses in your industry.