How to Calculate Cagr in Excel

by Best Excel Tutorial Calculators

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

CAGR formula example Excel

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)

CAGR RRI function

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

CAGR Power function

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.

See also  How to calculate Debt Ratio in Excel

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

CAGR geomean function

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

Method 5. 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.

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 6. 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.
See also  How To Calculate the QBI Deduction

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 7. 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.

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.

See also  How to calculate logarithms and inverse logarithms in Excel

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.

Key Takeaways

  • CAGR is a measure of the average annual growth rate of an investment over a period of time.
  • There are several ways to calculate CAGR in Excel, including using the dedicated formula, the RRI function, the Power function, the GEOMEAN function, the IRR function, and the XIRR function.
  • The CAGR of an investment can be used to compare the performance of different investments with different starting and ending values and durations.

FAQ

  • Q: What is the difference between CAGR and IRR?
  • A: CAGR is the average annual growth rate of an investment, while IRR is the internal rate of return of an investment. CAGR is a simpler metric to understand, while IRR is a more accurate measure of the investment’s performance.
  • Q: What is the best way to calculate CAGR in Excel?
  • A: The best way to calculate CAGR in Excel depends on the type of investment and the data available. For investments with regular cash flows, the RATE function is a good option. For investments with irregular cash flows, the XIRR function is a better option.
  • Q: What is the significance of CAGR?
  • A: CAGR is a useful metric for comparing the performance of different investments. It can also be used to estimate the future value of an investment.

You can download that spreadsheet here for free.

Related posts:

Excel ROI calculateHow to calculate ROI in Excel monthly payment type dataHow to calculate monthly payment in Excel Default ThumbnailHow to create blood alcohol calculator in Excel