Mastering Microsoft Excel: A Step-by-Step Guide to Determining Compound Annual Growth Rate

Quick Links

• Organize the Investment Details
• Enter the Formula for the RRI Function
• Calculating CAGR With a Forumla

To keep track of an investment such as a mutual fund, you likely want to calculate the CAGR (compound annual growth rate). You can do so in Microsoft Excel using the RRI function and it’s much easier than you might think.

Let’s walk through setting up your data and using the RRI function to calculate the CAGR in Excel.

Organize the Investment Details

All you need to calculate the CAGR are the periods and the values for each period. List the years in the first column and amounts in the second as shown below.

Click here

## Enter the Formula for the RRI Function

Next, select the cell where you want to calculate the CAGR. This is where you’ll enter the formula for the RRI function.

Related: The Basics of Structuring Formulas in Microsoft Excel

The syntax for the function is

    `RRI(periods, present value, future value)`

where all three arguments are required.

Using our data above, we would use this formula to find the CAGR with the RRI function:

=RRI(A7,B2,B7)

A7 contains the number of periods in the investment, B2 contains the present value, and B7 contains the future value.

To format the result as a percentage , go to the Home tab and either click the Percent Style button or choose “Percentage” in the Number Style drop-down box.

Click here

As an alternative, you can insert the actual values in the formula instead of the cell references:

=RRI(5,50,400)

vMix Basic HD - Software based live production. vMix Basic HD includes 4 inputs, 3 cameras, streaming, recording, playlist.
This bundle includes Studio 200 for vMix from Virtualsetworks, HTTP Matrix 1.0 automation scheduler, and 4 introductory training videos from the Udemy vMix Basic to Amazing course.

Calculating CAGR With a Forumla

While the RRI function does an excellent job of calculating the CAGR of an investment , you can always do the math by hand or enter that formula into Excel to double-check the RRI formula result.

To calculate CAGR, you first divide the future value by the present value. Then, raise the result to an exponent of one divided by the number of periods (years). Finally, subtract one from the result.

Here is the formula for CAGR using our above example and cell references in Excel:

=(B7/B2)^(1/A7)-1

As you can see in the screenshot below, this formula confirms the result of the RRI function’s formula.

Microsoft Excel is a solid application for keeping track of investments and household finances . So if you’re interested in additional ways it can help you, take a look at these budget functions you can use in Excel.

| | Mastering Excel Functions | | |
| —————————- | —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— | |
| Functions | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH ·MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR | |
| Types | Basic · Budgeting · Data Entry · Logical · Text · Time and Date | |
| Explained | Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas | |