The compound annual growth rate, known as CAGR, describes a value's change over time as an average percentage. The CAGR offers a single, broad indication of direction and magnitude, even if actual values vary wildly over time. Excel accepts several formulas to derive the CAGR between two times, but they're all based on the compound interest formula. The exception to this formula is if you already calculated several annual rates and need to average them using the geometric mean formula.
Constructing the Compound Interest Formula
Manually create the compound interest formula without functions using the following format:
Video of the Day
Here, "n" is the number of years between the future value (fv) and present value (pv). Replace the variables with actual values or references to cells that contain actual values, such as "=((C2/C1)^(1/B3))-1" or "=(($180,000/$100,000)^(1/5))-1." In either case, keep the parentheses, so Excel knows in which order to perform calculations.
To force the result to display as a percentage, rather than a decimal, click the Home tab, select the General drop-down menu in the Numbers group and choose Percentage.
Using the POWER Function
The previous formula required several parentheses, which visually complicate the formula. If you find cascading parentheses distracting, use the Power function instead. Because Excel lacks a separate nth root function, this Power function addresses both raising numbers to powers or taking nth roots; in the latter case, enter "1/n," similar to the previous formula. Use the following format with the Power function and replace the variables with actual or referenced values:
As an example, you might enter "=POWER(180000/100000,1/5)" or "=POWER(C2/C1,1/B3)".
Although you could include commas in the previous formula's variables, you cannot include them in function-based formulas, including Power, Rate or GeoMean.
Using the RATE Function
Although Microsoft primarily designed the Rate function for annuity calculations, its versatility calculates a compound interest rate by ignoring the regular payments that are typical of annuities. The advantage is what many consider a neater formula using the following format:
Notice the double commas, between which would normally include a regular payment. However, by omitting the payment, the calculation reduces to a standard compound interest calculation. Also, note that the present value variables includes a negative sign, without which the formula will return an error. Just as before, you have the option of directly entering variables, such as "=RATE(5,,-100000,180000)," or by referencing variables, such as "=RATE(B3,,-C1,C2)."
The Rate function automatically formats the answer as a percentage, so you don't need to specify the format.
Using the GEOMEAN Function
The GeoMean function takes the geometric mean of a series of values. If these values are a series of consecutive annual rates, the result is an average rate that takes into account the effects of compounding. Use the following format to calculate CAGR from a series of rates:
Here, the range is the cells containing the annual rates or a series of values separated by commas. As an example, you might enter "=GEOMEAN(D2:D6)-1" or "=GEOMEAN(1.2,1.21,1.21,0.91,1.13)."