CAGR Formula in Excel

This tutorial will show you how to calculate CAGR using Excel formulas. We will show you several methods below, using different functions, but first let’s discuss what CAGR is. Skip ahead if you’re already familiar with CAGR.

Compound Annual Growth Rate (CAGR)

CAGR stands for Compound Annual Growth Rate. CAGR is the year-over-year average growth rate over a period of time. In other words, CAGR represents what the return would have been assuming a constant growth rate over the period. In actuality, the growth rate should vary from year to year.

The CAGR Formula

From Investopedia, Compound Annual Growth Rate ( CAGR ) is calculated as:

=(Ending Value/Begining Value)^(1/# of years) -1

Restated:

=(FV/PV)^(1/n) -1

where FV = Future Value, PV = Present Value, and n = number of periods.

Calculate CAGR in Excel

FV, PV, N

If you have FV, PV, and n, simply plug them into the standard CAGR equation to solve for CAGR.

=(B5/C5)^(1/D5)-1
Result: 20%

The Rate Function
You can also use Excel's Rate Function:
= RATE (nper, pmt, pv, [fv], [type], [guess])
nper - The total number of payment periods

pmt - The payment amount. Payment must remain constant.

pv - The present value.

fv - [optional] The future value, or cash balance you want to be attained after the last pmt. 0 if omitted.

type - [optional] The payment type. 1 for beginning of period. 0 for end of period (default if omitted).

guess - [optional] Your guess for what the rate will be. 10% if omitted.

=RATE(B5,C5,D5)

Result: 12%

You can only use the RATE Function if the cash flows happen on a periodic basis, meaning the cash flows must happen at the same time annually, semi-annually, quarterly, etc.  If the cash flows are not periodic, use the XIRR Function to calculate CAGR instead.
The XIRR Function
For non-periodic cash flows, use the XIRR Function to calculate CAGR. When using the XIRR Function, you create two columns. The first column contains the cash flows. The second column contains the cash flows' corresponding dates.


=XIRR(B5:B8,C5:C8,0.25)

Result: 24%





The IRR Function
Instead of using the Rate Function, you can use the IRR Function to calculate the CAGR for periodic cash flows. The IRR Function works the same as the XIRR Function, except you don't need to specify dates.


=IRR(B5:B10,.25)

Result: 8%

 Apply Percentage Formatting to CAGR
After you finish calculating CAGR, your result may appear as a decimal: ex. .103. To convert your answer into a percentage (10.3%), you need to change the Number Formatting to percentage:


Return the Formula Examples Page.