Written and maintained by CASRAI Editorial Board
Last updated
To calculate CAGR (compound annual growth rate) in Excel, divide the end value by the start value, raise the result to the power of 1 divided by the number of years, and subtract 1: =(B2/B1)^(1/B3)-1. Excel also has a dedicated function that returns the same number: =RRI(B3,B1,B2). Format the result cell as a percentage to read it as a growth rate.
This guide gives both methods on one worked example, shows how to check that they agree, and covers the mistakes that most often produce a wrong CAGR: counting periods incorrectly, and averaging yearly changes instead of compounding them.
What CAGR is used for in research
CAGR is the constant yearly rate that would take a quantity from its start value to its end value if it grew by the same percentage every year. Research offices and analysts use it to summarise a trend in a single figure: a funder’s annual award budget, an institution’s publication count, citations to a body of work, or the volume of data a repository holds. Because it compounds, it is the right summary when each year’s change builds on the previous year, which is the same reason the geometric mean is preferred to the arithmetic mean for ratios. The idea is explained in our geometric mean guide, and CAGR is the geometric mean of the year-on-year growth factors minus 1.
CAGR describes the endpoints only. It says nothing about what happened between them, so pair it with the yearly figures or with descriptive statistics when the path matters. For forecasting beyond a simple growth rate, see ARIMA models for research time series.
Install or access Excel
Use Excel from Microsoft’s own site and support pages. Both methods below rely on built-in worksheet features: the caret operator ^ (equivalent to the POWER function) and the RRI function. To confirm your copy has RRI, type =RRI( in a cell. Excel shows the argument prompt if the function is available.
Step-by-step: calculate CAGR in Excel
The example uses made-up numbers for illustration, not real data: an annual research budget of 2,400,000 in 2019 that grows to 3,150,000 in 2024.
- Enter the labels and values: in A1 type Start value and in B1 type
2400000; in A2 type End value and in B2 type3150000. - Work out the number of periods. In A3 type Periods (years) and in B3 type
=2024-2019, which returns 5. Periods are the intervals between the start and end points, not the number of data points. Six yearly values (2019 to 2024) make five periods. - Method 1, the formula. In B4 type
=(B2/B1)^(1/B3)-1. The same calculation with the POWER function is=POWER(B2/B1,1/B3)-1. Microsoft documents POWER as returning a number raised to a power, with the caret operator as a replacement for it (5^2 equals POWER(5,2)). - Method 2, the RRI function. In B5 type
=RRI(B3,B1,B2). Microsoft describes RRI as returning an equivalent interest rate for the growth of an investment. Its syntax is RRI(nper, pv, fv): nper is the number of periods, pv the present (start) value and fv the future (end) value. All three arguments are required, and the order is periods, start, end. - Select B4 and B5 and format them as percentages, then add decimal places if you need them.
Output and how to read it
Both cells return 0.0558929, which displays as 5.59% when formatted as a percentage. Read it as: the budget grew at a steady 5.59% a year, compounding, to get from 2,400,000 to 3,150,000 in five years.
| Calculation | Excel entry | Result for the example |
|---|---|---|
| CAGR with the caret operator | =(B2/B1)^(1/B3)-1 |
5.59% |
| CAGR with POWER | =POWER(B2/B1,1/B3)-1 |
5.59% |
| CAGR with RRI | =RRI(B3,B1,B2) |
5.59% |
| Total growth over the period | =(B2-B1)/B1 |
31.25% |
| Wrong: total growth divided by years | =((B2-B1)/B1)/B3 |
6.25% |
To check the answer, grow the start value forward: =B1*(1+B4)^B3 should return the end value, 3,150,000. If it does not, the period count or a cell reference is wrong.
How to calculate growth rate in Excel
The simple growth rate between two values is =(B2-B1)/B1, which gives 31.25% total for the example. That is a single-step change, not a yearly rate. Dividing it by the number of years (6.25%) overstates the compound rate because it ignores compounding; CAGR (5.59%) is the yearly rate to quote when the question is how fast something grew each year on average.
How to calculate compound growth and compound growth rate in Excel
These phrasings mean the same calculation as CAGR whenever the periods are years. If your periods are months or quarters, the same formula returns a rate per month or per quarter; the rate is per period, whatever length of period you count. Microsoft’s own RRI example shows this: =RRI(96,10000,11000) returns 0.0009933 per period, and 96 is the number of periods.
Troubleshooting
- #NUM! Microsoft lists #NUM! as the RRI result when an argument value is not valid. Check that the periods are a positive number and that the start and end values are positive numbers, since a growth rate needs a positive ratio of end to start.
- #VALUE! Microsoft lists #VALUE! for invalid data types. A number stored as text, such as a value pasted with a currency symbol or a trailing space, is the usual cause; convert it to a true number.
- #DIV/0! in the formula method means the start value is zero, so there is no growth rate to compute. CAGR is only meaningful when the start value is a positive number.
- A result that looks too low or too high. Count periods, not points. Using 6 instead of 5 for the example turns 5.59% into 4.64%.
- Negative values. The ratio of end to start must be positive for the root to exist, so CAGR does not apply when the values change sign.
- Result shows 0.0558929 instead of 5.59%. The cell is not formatted as a percentage. The value itself is correct.
Alternatives to Excel
The same two methods work in other spreadsheet programs, because the formula uses only division, a power and subtraction, and RRI is documented in both of these:
| Program | RRI syntax in its documentation | Documentation |
|---|---|---|
| LibreOffice Calc | RRI(P; PV; FV): P is the number of periods, PV the present value, FV the future value. The help page’s example, =RRI(4;7500;10000), gives 7.46%. | LibreOffice Help, Financial Functions Part Three |
| Google Sheets | RRI(number_of_periods, present_value, future_value): returns the interest rate needed for an investment to reach a specific value within a given number of periods. | Google Docs Editors Help, function list |
Note the argument order in all three programs: periods first, then start value, then end value. Argument separators differ by program and locale (LibreOffice’s help uses semicolons).
Frequently asked questions
How do I calculate CAGR in Excel?
Type =(end/start)^(1/periods)-1 with cell references, or =RRI(periods,start,end), and format the cell as a percentage. Both return the same value.
How do I calculate compound annual growth rate in Excel without the RRI function?
Use the caret operator or POWER: =(B2/B1)^(1/B3)-1 or =POWER(B2/B1,1/B3)-1. Microsoft documents the caret as a replacement for POWER.
What is the difference between growth rate and CAGR in Excel?
A growth rate such as =(B2-B1)/B1 measures the total change between two values. CAGR converts that into a steady yearly rate that compounds. For the example, the total is 31.25% and the CAGR is 5.59% a year.
How many periods do I use?
The number of intervals between the first and last value: end year minus start year for yearly data.
Can I use RRI for monthly or quarterly data?
Yes, but the result is a rate per month or per quarter, not per year, because RRI returns the rate for each period you count.
Sources
- Microsoft Support, RRI function (checked 2026-10-29).
- Microsoft Support, POWER function (checked 2026-10-29).
- LibreOffice Help, Financial Functions Part Three (checked 2026-10-29).
- Google Docs Editors Help, Google Sheets function list (checked 2026-10-29).
The worked example uses illustrative numbers, not data from a real institution. Results in the table were computed from those numbers.
Related: choosing software for wider analysis? See SPSS vs R.
