CAGR is a financial metric that calculates the average annual growth rate of an investment, considering the effect of compounding. Excel has a built-in function to calculate CAGR. In this tutorial, you will learn how to write an effective CAGR function in Excel that allows calculating compound annual growth rate based on three primary input values: an investment’s beginning value, ending value, and time period.
This Tutorial Covers:
- What is Compound Annual Growth Rate?
- CAGR formula
- How to calculate CAGR in Excel
- CAGR Formula 1: Simple Excel formula for calculating CAGR
- CAGR formula 2: RRI function
- CAGR formula 3: POWER function
- CAGR formula 4: RATE function
- CAGR formula 5: IRR function
- Things to Remember
1. What is Compound Annual Growth Rate?
The Compound Annual Growth Rate (CAGR) is a financial metric that calculates the average annual growth rate of an investment over a specific period of time, considering the effect of compounding. It is a useful tool for measuring the performance of an investment over a longer time period and provides a single, easily comparable number that represents the investment’s overall growth rate. CAGR is commonly used in finance to compare the returns of different investments and to forecast future performance.
For example, if you bought land in 2015 worth $2000 and $5000 in 2023, CAGR is how this investment has grown yearly.
2. CAGR formula:
The following is the general CAGR formula used in business, finance, and financial analysis:
(EV/BV)^(1/n)-1
Where,
EV, Ending Value: This is the final value of the investment at the end of the period for which you want to calculate the CAGR.
BV, Beginning Value: This is the initial value of the investment at the beginning of the period for which you want to calculate the CAGR.
N, Number of Years: This is the length of the investment period, measured in years. It is calculated by subtracting the start date from the end date and dividing by 365 (or 366 for leap years) to get the number of years.
Return Value:
The return number will be numerical, and since the CAGR works best when expressed as a percentage, we can convert it into a percentage.
3. How to calculate CAGR in Excel?
Let’s look at how to compute compound annual growth rate in your Excel worksheets now that you have a basic understanding of what it is. There are five different methods to construct an Excel formula for CAGR.
- Using generic CAGR formula
- Using POWER function
- Using RRI function
- Using RATE function
- Using IRR function
- CAGR Formula 1: Simple Excel formula for calculating CAGR:
It takes only a few minutes, if not seconds, to create a CAGR calculator in Excel if you are familiar with the general CAGR formula described above.
Simply enter the values listed below in your worksheet:
BV – Beginning value of the investment
EV – Ending value of the investment
n – Number of periods
Then, in a blank cell, write the CAGR formula as follows:
=(EV/BV)^(1/n)-1
In this example, BV is in cell A2, EV in B2, and n in C2. So, we enter the following formula in B4:
=(B2/A2)^(1/C2)-1
Your CAGR formula can be made more flexible if all investment values are enumerated in a single column, and the number of periods can be determined automatically.
The following formula can be used to compute CAGR in our example worksheet in this situation:
=(B10/B2)^(1/(9-1))-1
Note: It should be noted that the above formula uses B10 as the cell with the end value, B2 as the cell with the beginning value, and 8-1 as the interval between the two. You can alter these variables to suit your needs.
The computation result might not always be formatted as a percentage. Please maintain choosing the calculation result.
Also Read: How to Calculate Standard Deviation in Excel?
- CAGR formula 2: RRI function:
Using Excel’s RRI function, which determines an equivalent interest rate on a loan or investment over a given period based on the present value, future value, and the total number of periods, is the simplest method to determine compound annual growth rate:
=RRI(nper, pv, fv)
Where,
Nper stands for the total amount of periods.
Pv stands for the investment’s present value.
Fv stands for the investment’s future value.
The steps to calculate CAGR in Excel using RRI Function are described below:
Step 1: In a blank cell, write the CAGR formula as follows:
=RRI(C2,A2,B2)
- CAGR formula 3: POWER function:
Excel’s POWER function, which returns the result of raising a number to a specific power, can be used to quickly and easily compute CAGR.
The POWER function has the following syntax:
=POWER(number, power)
Where,
Power is the exponent to increase the base number to, and number is the base number.
Using the POWER function, create an Excel CAGR calculator by defining the parameters as follows:
Number – This is the value of calculation’s ending value (EV) / beginning value (BV)
Power – 1/number of periods (n)
=POWER(EV/BV, 1/n)-1
Here is our strong CAGR formula operates:
=POWER(B10/B2,1/(9-1))-1
- CAGR formula 4: RATE function:
Excel’s RATE function, which gives the interest rate per period of an annuity, can also be used to calculate CAGR.
The RATE function has the following syntax:
=RATE(nper, pmt, pv, [fv], [type], [guess])
Where:
nper: Required. The entire number of payment intervals in an annuity.
pmt: Required. The payment made each period. Over the course of the annuity, this amount won’t alter.
pv: Required. The value of a sequence of future payments as a lump sum at this moment, or the present value.
fv: Optional. The future value, or the cash balance that you want to attain after the last payment is made.The default number is 0 if it is omitted.
type: Optional. Indicates when payments are due. Use 0 for payments at the end of the period or 1 for payments at the beginning of the period. The default number is 0 if it is omitted..
guess: Optional. An estimate for the rate. If omitted, Excel uses the default value of 0.1 (10 percent).
You must provide the first (nper), third (pv), and fourth (fv) parameters in the following manner to convert the RATE function into a CAGR calculation formula:
=RATE(n,,-BV, EV)
Where,
BV – Beginning value of the investment
EV – Ending value of the investment
n – Number of periods
Note: The beginning value (BV) must be specified as a negative integer; otherwise, your CAGR formula will produce the #NUM! error.
The following method can be used to determine the compound growth rate in this example:
=RATE(8,,-B2,B10)
You can use the ROW function to have the ROW function calculate the number of periods for you in order to save yourself the bother of doing it manually:
=RATE(ROW(B10)-ROW(B2),,-B2,B10)
Also Read: How to Calculate Internal Rate of Return in Excel?
- CAGR formula 5: IRR function:
For a sequence of cash flows that happen at regular intervals, Excel’s IRR function returns the internal rate of return. (i.e., days, months, quarters, years, etc.).
It has the following syntax:
=IRR(values, [guess])
Where:
values: Required. An array or range of cash flows that represent the series of payments and income from an investment or project. The array must contain at least one positive and one negative value, and the first value must be negative to represent an initial investment.
guess: Optional. A guess at the internal rate of return (IRR). If omitted, Excel uses 0.1 (10 percent).
You would have to reshape the initial data in this manner because the Excel IRR function is not specifically made for calculating compound growth rate:
- Enter a negative figure for the investment’s starting value.
- The investment’s ending value is a positive value.
- The numbers in the middle are all zeros.
After rearranging your primary data, use the following straightforward formula to determine CAGR in Excel:
=IRR(B2:B10)
Where B2 is the beginning value and B10 is the ending value of investment:
4. Things to Remember:
- The Compound Annual Growth Rate, or CAGR, value is returned by the Microsoft Excel CAGR formula from the collection of supplied values.
- CAGR calculates the return value on an investment over a specific time frame and measures it.
- Excel CAGR formula can be used to determine how much the investment should yield annually at a constant growth rate.
- The #VALUE! mistake is most likely to be present if you encounter any Excel CAGR formula errors.
