Compound Annual Growth Rates (CAGR) in Excel

Free Preview

CAGR (compound annual growth rate) is a common requirement in spreadsheets. What we are generally asking is what is the average growth in an amount over a number of years, taking compounding into account.

There is unfortunately no CAGR formula in Excel currently but there are a few ways of achieving it with other functions.

Goalseek and Buckets

One method is to create a bucket that uses a constant percentage to grow one number to another number over a number of periods and then goal seek what percentage will give you the desired result.

To follow along go to 17.14

In the example below we want to know what the CAGR is of Sales going from 10 million to 17.5 million over 3 years.

Note that this is a steady increase of 2.5 million per year but the growth each year is different as the starting base will differ i.e. 2.5 million on 10 million is 25% but 2.5 million on 12.5 million is 20%.

In the bucket we have created below we start with a closing balance of 10 million (this is the end of year sales number) and then have a growth row that takes the opening balance and determines how much it grows based on the percentage in B15. We know that we want cell G16 to be equal to 17.5 million so what percentage in B15 will create this.

CAGR

Using Goal Seek

CAGR

we can ask Excel to set cell G16 to be 17.5 million by changing cell B15.

CAGR

As shown below, the number that works is 20.51%. Note that in year 2 and 3 the resultant sales don’t match. It is only in the final year that it matches. This is because we are trying to find the consistent percentage growth that will arrive at the number we want.

CAGR

RATE function for CAGR

A formula driven way to calculate the CAGR is to use the RATE function. The RATE function is normally used to determine what interest rate will take a present value to a future value taking compounding into account. For debt the PV and the FV must have different signs.

With CAGR we are asking a similar question (how do we get from one number to another number with compounding) but it will be two positive numbers.

So you need to slightly change the inputs for the RATE function.

To follow along go to 17.14

As shown below you need to get the NPER correct. We are going from 10 million in year 1 to 17.5 million in year 4.

Although there are 4 years, the growth only happens in 3 years (year 1 is already assumed). So the NPER will be a 3.

The PV needs to be a negative number hence = D7. FV is the last years sales. There is no PMT as we want to see how one amount gets to another amount with no other cash flows.

CAGR

You will see that it generates the same CAGR as in the manual method.

IRR function for CAGR

IRR can also be defined as the ‘interest rate’ that will make a series of cash flows equal given compounding movements. Similar to the RATE function, IRR needs to take a negative number to a positive number so we need to create a new set of cells that will help with the calculation.

To follow along go to 17.14

As shown below the first cell is a NEGATIVE of the Year 1 number, then we have 2 zeros (just to help Excel understand how many periods we are covering) and then the year 4 sales. Then in cell B22 we have done an IRR.

CAGR

When you have two non zero cash flows, the IRR tells you how the first number gets to the last number over that period. You will see that the result is the same answer as the other methods.

XIRR function for CAGR

As we can use IRR, we can also use XIRR and in some ways this is easier.

To follow along go to 17.14

In this case you need to create only 4 cells and specify the start and end date on one row and the start and end amount on another row (the start amount must be negative).

Then you can create the XIRR and Excel will work out what percentage growth will get you from the one number to the other number in the time period specified from the start date to the end date.

CAGR

CAGR if you go from a negative to a positive

This is one of the most common problems that we get asked about. The problem is that mathematically no consistent percentage will change a negative into a positive and then keep the numbers positive.

So in the example below if we want to see the CAGR of the Net Profit we cannot find a single percentage that will take -1 million and convert it to a positive. Also, if by some chance one of the years hits zero, you won’t be able to multiply any further.

CAGR

If you come across this problem you are probably trying to measure CAGR on the wrong thing.




Back to: Financial Calculations and compound interest in Excel > Time Value of Money- Debt, NPV, IRR