How to Create a CAGR Formula in Excel

CAGR stands for Compound Annual Growth Rate, which measures the annual growth rate (smoothed rate) of an investment every year during a given interval. For instance, suppose you bought stock worth $100 in 2011 and it is worth $400 in 2021, CAGR would be the rate at which your investment in that stock grew every year.

Stock markets are volatile, the growth of an investment may vary year to year. The return on investment may increase or decrease. CAGR helps smooth returns of an investment like it had grown at a balanced rate every year.

Although there’s no CAGR function in Excel, there are several ways you can calculate CAGR in Excel. This article shows you how to create clear and easy-to-understand CAGR formulas using different Excel functions.

How to Create a Compound Annual Growth Rate (CAGR) formula in Excel

Compound Annual Growth Rate (CAGR) is very helpful for business, financial planning, financial modeling, and investment analysis. The CAGR formula calculates the annual growth of an investment that can be used to compare to other investments.

To calculate CAGR you need three primary inputs: an investment’s starting value, ending value, and the number of periods (years).

CAGR Formula

The syntax of CARG formula is:

CAGR =(Ending value/Beginning value)^1/n - 1

where:

Ending value – Ending balance of the investment at the end of the investment period.
Beginning value – Beginning balance of the investment at the start of the investment period.
n – Number of years you have invested.

Calculating CAGR in Excel

Now that you learned the arithmetic behind compound interest, let’s see how you can calculate CAGR in Excel. There are 5 ways to create an Excel formula to calculate CAGR:

Using Arithmetic Operators
Using RRI function
Using the POWER function.
Using RATE function.
Using the IRR function.

Calculating CAGR in Excel Using Operators

The direct way to calculate CAGR is by using operators. Use the above generic formula to calculate CAGR.

Let’s assume we have sales data for a certain company in the spreadsheet below.

Column A has the years in which the revenues are earned. Column B has the revenue of the company in the respective year. With the CAGR formula in excel, you can calculate the annual growth rate for the revenue.

Enter the below formula to calculate CAGR by the direct method:

=(B11/B2)^(1/9)-1

In the example below, the investment’s starting value is in cell B2 and the ending value in cell B11. The number of years (period) between the start and the end of the investment period is 9. Usually, each investment cycle period starts one year and ends the next year, hence, the first period of the cycle, in this case, is 2011-2012 and the last cycle is 2019-2020. So the total number of years invested is ‘9’

The result will be in decimal numbers not in percentage as shown above. To convert it to a percentage, go to the ‘Home’ tab, click on the drop-down that says ‘General’ in the Number group, and select the ‘% Percentage’ option.

Now, we got the compound annual growth rate that is ‘10.77%’ in cell B13. This the single smoothed growth rate for the whole time period.

Calculating CAGR in Excel Using RRI Function

RRI function measures a periodic equivalent interest rate for the return of an investment or loan over a particular period of time.

The interest rate is calculated based on the present and future value of the investment, and duration.

The syntax:

=RRI(nper,pv,fv)

nper – total number of periods (years)
pv – This specifies the present value of an investment or a loan (same as the beginning value)
fv – This specifies the future value of an investment or a loan (same as the ending value)

We have the value for nper in cell A11, pv in cell C2, and fv in cell C11. Use this formula:

=RRI(A11,C2,C11)

The CAGR is ‘10.77%’, which is in cell B13.

Calculating CAGR in Excel Using POWER Function

Another easy method to calculate Compounded annual growth rate (CAGR) in Excel is by using the POWER function. The POWER function replaces the ^ operator in the CAGR function.

The syntax of the POWER function:

=POWER(number,power)

Arguments of the POWER function:

number – It is the base number found by dividing ending value (EV) by beginning value (BV) (EV/BV).
power – It is to raise the result to an exponent of one divided by duration (1/number of periods (n)).

Now, the arguments are defined this way to find CAGR value:

=POWER(EV/BV,1/n)-1

Let’s apply the formula to an example:

=POWER(C11/C2,1/A11)-1

The result:

Calculating CAGR in Excel Using RATE Function

Rate function is yet another function you can use to find CAGR in Excel. When you look at the syntax of the RATE function, it may look a bit complicated with 6 arguments no less, but once understand the function, you may prefer this method for finding CAGR value.

The Syntax of RATE function:

=RATE(nper,pmt,pv,[fv],[type],[guess])

Where:

nper – The total number of payments period (the loan term).
Pmt (optional) – The amount of the payment made in each period.
Pv – This specifies the present value of the loan/investment (beginning value (BV))
[Fv] – This specifies the future value of the loan/investment, at the last payment (ending value (EV))
[Type] – This specifies when the payments for the loan/investment are due, it is either 0 or 1. The argument 0 means the payments are due at the start of the period and 1 means payments are due at the end of the period (default is 0).
[Guess] – Your guess of rate. If omitted, it defaults to 10%.

The reason the RATE function has six arguments is that it can be used for many other financial calculations. But we can convert the RATE function to a CAGR formula, with only three arguments 1^st (nper), 3^rd (pv), and 4^th (fv) arguments:

=RATE(nper,,-BV,EV)

Since we don’t make regular payments (monthly, quarterly, yearly), we leave the second argument empty.

To calculate the compound annual growth rate using RATE function, use this formula:

=RATE(A11,,-C2,C11)

If you don’t want to calculate the number of periods manually, use the ROW function as the first argument of the RATE fromula. It will compute the npr for you.

=RATE(ROW(A11)-ROW(A2),,-C2,C11)

Calculating CAGR in Excel using IRR Function

The IRR short for ‘Internal Rate Return’ is an Excel function that calculates the IRR for payments and income occurring at regular intervals (i.e. monthly, annual).

The IRR method is helpful when you need to calculate the CAGR value for periodic cash flows.

The syntax is:

=IRR(values,[guess])

Where:

values – a range of payments. The range of payments should have at least one negative and one positive cash flows.
[guess] (Optional) – This represents your guess of rate value. If ignored, it defaults to 10%.

Excel IRR function requires you to re-adjust the data set in this way: