How Do I Calculate CAGR in Excel?

Found This Helpful

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

(Image: C. Taylor)

Manually create the compound interest formula without functions using the following format:


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

(Image: C. Taylor)

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:


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

(Image: C. Taylor)

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

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


Category: Bank

Similar articles: