For example, suppose you have a company’s financial report and need to know the annual growth rate of an investment over a given period. You may estimate a year-to-year growth using a formula in your Excel worksheets without hand calculations.

CAGR Formula in Excel (Compound Annual Growth Rate)

The CAGR formula in Excel is the function that is responsible for returning the CAGR value, the Compound Annual Growth Rate value, from the supplied set of values. If you are into financial analysis or planningFinancial Analysis Or PlanningFinancial planning and analysis (FP&A) is budgeting, analyzing, and forecasting the financial data to align with its financial objectives and support its strategic decisions. It helps investors to know if the company is stable and profitable for investment.read more, you will need to calculate the compound annual growth rate in Excel value in Excel spreadsheets.

The CAGR formula in Excel measures the value of return on an investment calculated over a certain period. The compound annual growth rate formula in Excel is often used in Excel spreadsheets by financial analysts, business owners, or investment managersInvestment ManagersAn investment manager manages the investments of others using several strategies to generate a higher return for them and grow their assets. They are sometimes also referred to as portfolio managers, asset managers, or wealth managers. They may also be considered financial advisors in some cases, but they are typically less involved in the sales aspect.read more, which helps them identify how much their business has developed or compare revenue growth with the competitor companies. With the help of CAGR, it can be seen how much constant growth rate the investment should return annually. In reality, the growth rate should vary from year to year.

For example, if you bought gold in 2010 worth $200 and $500 in 2018, CAGR is how this investment has grown yearly.

where,

  • Ending Value = Investment’s ending valueStarting Value = Investment’s starting valuen = number of investment periods (months, years, etc.)

Return Value:

  • The return value will be numeric, which we can convert into a percentage because the CAGR is effective in percentage form.

How to Use CAGR Formula in Excel with Examples?

Let us understand how to use the CAGR formula in Excel with examples.

#1 – Basic Method

Let us consider the Excel spreadsheet below. Look at the data.

#2 – Using the Power Function

You can also use the POWER formula in ExcelPOWER Formula In ExcelPOWER function calculates the power of a given number or base. To use this function you can use the keyword =POWER( in a cell and provide two arguments one as number and another as power.read more to find the CAGR value in your Excel spreadsheet. The formula will be “=POWER (Ending Value/Beginning Value, 1/9)-1”. We can see that the POWER function replaces the ˆ, which was used in the traditional CAGR formula in Excel. Using the POWER function in the above Excel spreadsheet, using the conventional method to find the CAGR value, the result will be 0.110383 or 11.03%. Consider the screenshot below.

  • The above spreadsheet shows where column A has been categorized as “YEAR,” and column B has “AMOUNT.” In column “YEAR,” the value starts from the A2 cell and ends at the A10 cell.Again in the column “AMOUNT,” the value starts at the B2 cell and ends at the B10 cell.Therefore, we can see that the investment’s Starting Value (SV) is the B2 cell and the investment’s Ending Value (EV) is the B10 cell. We have the values, which we can put in Excel’s Compound Annual Growth Rate (CAGR) formula. However, to successfully do it in an Excel spreadsheet, we have to select any of the cells of the C column and type the formula as given below: =(B10/B2)^(1/9)-1In the above compound annual growth rate in Excel example, the ending value is B10, the beginning value is B2, and the number of periods is 9. You may see the screenshot below. Now press the “Enter” key. We will get the CAGR (Compound Annual Growth Rate) value result inside the cell where we input the formula. In the above example, the CAGR value will be 0.110383. The return value is just the evaluation of the CAGR formula in Excel with the values which have been described above. Consider the screenshot below. Note that Compound Annual Growth Rate in Excel is always represented in the form of percentages in the financial analysis field. To get the percentage, we must select the cell where the CAGR value is present and change the cell format from ‘General’ to ‘Percentage.’ The percentage value of CAGR (Compound Annual Growth Rate) in the above example is 11.04%. We can see the screenshot below. The above steps show how you calculate the Compound Annual Growth Rate in Excel (CAGR) spreadsheets.

In column “YEAR,” the value starts from the A2 cell and ends at the A10 cell.Again in the column “AMOUNT,” the value starts at the B2 cell and ends at the B10 cell.Therefore, we can see that the investment’s Starting Value (SV) is the B2 cell and the investment’s Ending Value (EV) is the B10 cell.

=(B10/B2)^(1/9)-1In the above compound annual growth rate in Excel example, the ending value is B10, the beginning value is B2, and the number of periods is 9. You may see the screenshot below.

The above steps show how you calculate the Compound Annual Growth Rate in Excel (CAGR) spreadsheets.

#3 – Using the RATE Function

It is a much lesser-used method for calculating the CAGR (Compound Annual Growth Rate) value or percentage but cleanly. The syntax of the RATE function in ExcelRATE Function In ExcelRate function in excel is used to calculate the rate levied on a period of a loan. The required inputs for this function are number of payment periods, pmt, present value and future value.read more can seem a bit complicated, but if we know the terms well, it will not be too hard. The syntax of the RATE function is given below: –

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

Let us now have an explanation of the above-given terms.

  • nper – (required) This is the total number of payments made in the specific period.pmt – (required) This is the value of a payment made in each period.pv – (required) This is the present value.fv – (optional) This is the future value.type – This means when the payments are due. The value is either 0 or 1. 0 represents the payment that has been due in the beginning, and 1 means that the payment has been expected at the end of the period.

#4 – Using the IRR Function

IRRIRRInternal rate of return (IRR) is the discount rate that sets the net present value of all future cash flow from a project to zero. It compares and selects the best project, wherein a project with an IRR over and above the minimum acceptable return (hurdle rate) is selected.read more is the abbreviation of the Internal Rate of Return. The IRR method is helpful when finding the CAGR (Compound Annual Growth Rate) value for different value payments made during a specific period. The syntax of the IRR function in excelSyntax Of The IRR Function In ExcelThe internal rate of return, or IRR, calculates the profit generated by a financial investment. IRR is a built-in function in Excel that calculates the IRR using a range of values as an input and an estimate value as the second input.read more is “=IRR (values, [guess]).” Values mean the entire range of numbers representing the cash flows. This section must contain one positive and one negative value. [Guess] in an optional argument in the syntax, which means your guess at what the return rate might be.

CAGR Formula Errors

If you get any error from the CAGR formula Excel, this is likely the #VALUE! Error.

#VALUE!Error – This error will occur if any supplied arguments are not valid values recognized by Excel.

Things to Remember

  • The Microsoft Excel CAGR formula returns the CAGR value, i.e., the Compound Annual Growth Rate in Excel from the supplied values set.CAGR measures the return value on an investment, which is calculated over a certain period.Using the Compound Annual Growth Rate formulaGrowth Rate FormulaThe Growth rate formula is used to calculate the annual growth of the company for a particular period. It is computed by subtracting the prior value from the current value and dividing the result by the prior value.read more in Excel, it can be seen how much constant growth rate should the investment return on an annual basis.If you get any error from the Excel CAGR formula, this is likely the #VALUE! Error.

This article is a step-by-step guide to CAGR Formula in Excel. We discuss how to use CAGR formula in Excel and calculate the compound annual growth rate in Excel and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • NPER in ExcelMistakes in DCFDDM