How to Calculate Cagr in Excel

In this lesson, you will learn how to calculate CAGR in Excel.

What is CAGR?

CAGR (Compound Annual Growth Rate) is the year-over-year average growth rate over a period of time. By calculating CAGR, you can check how much you earn annually with your investments.

There are several ways to calculate CAGR in Excel.

Methods of CAGR calculations

Method 1. Dedicated formula

To calculate CAGR, you can use that formula:

= ((FV/PV)^(1/n)) - 1

• FV stands for Future Value
• PV stands for Present Value
• n stands for number of investment years

The picture shows an example of the formula. I think everything is clear.

Method 2. RRI function

The other way is to use the RRI Excel function.

RRI function takes 3 arguments:

• nper (the same as n in the previous example)
• PV
• FV

In such a situation, the function in the given example will be: =RRI(C6,C5,C4)

The results are the same, so it proves that both of the methods are working the same way.

Method 3. Power function

Calculating CAGR is possible with the Power function

The other way to calculate a Compound Annual Growth Rate is possible with a Power Excel function.

The formula would be like this: =POWER(I4/I5,1/COUNT(C6:I6))-1 which is in fact =POWER(FV/PV,1/NPER)-1

Power function usage might be surprising to you, but the explanation is simple. To calculate a CAGR, you just need to calculate the rate of future value divided by present value. At this rate, you should power by the number of periods, which is 7 in our example. Of course, the result needs to be decreased by 1 to get the percentage value.

Method 4. GEOMEAN function

Another example to calculate cagr is to use the cagr function.

The first step is to calculate the growth factor. It is simply the ratio of future value to present value. Growth factor is greater than zero when the future value is greater than the present value, and less than zero when the present value is greater.

Then, using the geomean function, we calculate the geometric mean of the growth factor. The CAGR is the geometric mean minus one.

The full geomean formula is: =GEOMEAN(C6:I6)-1

Method 4. IRR function

The IRR function in Microsoft Excel can be used to calculate the internal rate of return (IRR) of an investment, which is closely related to the Compound Annual Growth Rate. The IRR represents the average rate of return per year that would make the present value of the investment equal to its future value. Here's how to use the IRR function to calculate CAGR in Excel:

1. In a column, list the cash flows for each period of the investment, either positive for contributions or negative for withdrawals.
2. In a separate cell, enter the formula =IRR(A1:An), where A1:An is the range of the cash flows.
3. Press the enter key to calculate the IRR. The result will be displayed in the cell with the formula.

The result of the IRR calculation is the average rate of return per year that would make the net present value of the investment equal to zero. This can be interpreted as the CAGR of the investment. Note that the IRR assumes that cash flows are reinvested at the IRR rate, which may not be accurate in all cases.

It's important to keep in mind that the IRR function may not provide an accurate result for investments with multiple IRRs or investments with non-periodic cash flows. In these cases, the XIRR function may be more appropriate.

Method 5. XIRR function

The XIRR function in Microsoft Excel can also be used to calculate the CAGR of an investment. The XIRR function is similar to the IRR function, but it allows for irregular cash flows, meaning that the investment can have multiple contributions or withdrawals at different times. Here's how to use the XIRR function to calculate CAGR in Excel:

1. In a column, list the dates for each cash flow in the investment, such as contributions or withdrawals.
2. In a separate column, list the corresponding cash flows, either positive for contributions or negative for withdrawals.
3. In a third column, enter the formula =XIRR(B1:Bn, A1:An), where B1:Bn is the range of the cash flows and A1:An is the range of the dates.
4. Press the enter key to calculate the XIRR. The result will be displayed in the cell with the formula.

The result of the XIRR calculation is the CAGR of the investment, which represents the average rate of return per year. The XIRR function takes into account the dates of each cash flow, so it provides a more accurate representation of the investment's performance compared to a simple average.

Note: The XIRR function uses an iterative process to calculate the CAGR, so the result may not be exact. However, the accuracy of the result can be improved by increasing the number of iterations in the calculation.

Method 6. RATE function

The RATE function in Microsoft Excel can be used to calculate the CAGR of an investment with regular cash flows, such as contributions or withdrawals made at equal intervals, such as monthly or annually. Here's how to use the RATE function to calculate CAGR in Excel:

1. Input the present value (PV) of the investment in a cell, such as A1. This is the starting value of the investment.
2. Input the future value (FV) of the investment in a separate cell, such as B1. This is the ending value of the investment.
3. Input the number of periods (nper) in a third cell, such as C1. This represents the number of intervals, such as years, between the starting and ending values.
4. Input the payment (pmt) in a fourth cell, such as D1. This represents the amount of the regular cash flow, either a contribution or withdrawal, made at each interval.
5. In a fifth cell, enter the formula =RATE(C1,D1,-A1,B1), which will calculate the CAGR of the investment.
6. Press the enter key to calculate the CAGR. The result will be displayed in the cell with the formula.

The result of the RATE calculation is the CAGR of the investment, which represents the average rate of return per interval, such as per year. The RATE function provides a simple way to calculate CAGR for investments with regular cash flows, but it may not provide an accurate representation for investments with irregular cash flows. In that case, you may need to use the XIRR function instead.

Keep in mind that CAGR provides an average growth rate, and the actual growth rate may have been higher or lower in any given year. CAGR is a useful metric for comparing the performance of different investments, as it provides a way to compare investments with different starting and ending values and durations.

You can download that spreadsheet here for free.