How to Calculate CAGR in Excel

How to Calculate CAGR in Excel

How do I calculate CAGR in Excel?

 

Whether you’re a business owner, investment manager, or financial assistant, knowing your Compound Annual Growth Rate is extremely useful. It allows you to get a compact overview of how your business has grown, which opens up another door of possibilities. Compare revenue in different years, see what you did right, and do it again.

 

This article focuses on calculating your CAGR with Microsoft Excel. While there’s no specific function to get this done, you can approach the calculation from a few different angles and get accurate results. Let’s get started!

 

What is Compound Annual Growth Rate?

 

The question is: What is the formula for calculating compound annual growth rate?

 

Below is an overview of how to calculate CAGR both by hand and by using Microsoft Excel.

 

What Is CAGR?


To put it simply, the
Compound Annual Growth Rate (CAGR) is used to measure the return of investment over a specified amount of time. While it’s not an accounting term, it’s used by financial experts to analyze various situations and make better, more thought-out decisions.

 

Let’s use an example to put CAGR into action. In this example, we’ll look at the year-end prices for a stock. This value changes every day, often at an uneven rate. CAGR allows us to calculate the one rate that defines the return for the entire measurement period:

 

  • Stock price in 2018: $140

  • Stock price in 2019: $155

  • Stock price in 2020: $170

As you can see, the growth rate from year to year is inconsistent. However, we can use the CAGR formula to find a single growth rate for the whole time period.

How to calculate CAGR using Excel [CAGR formula in Excel]


At the time of writing, there’s no dedicated formula for calculating your
Compound Annual Growth Rate (CAGR) in Excel. Luckily, there are some alternative formulas you can use for this purpose. We compiled two guides below, approaching the calculation from two different angles.

 

Video: How to Calculate CAGR using Excel

 

 

How is CAGR calculated?

 

The guides below were written using the latest Microsoft Excel 2019 for Windows 10. If you’re using a different version or platform, some steps may vary. Contact our experts if you need any further assistance.

Method 1. Calculate CAGR manually - CAGR formula in Excel 


Knowing the generic CAGR formula, you can create a calculator within a matter of minutes. What you need to do is specify the following 3 values in your worksheet:

 

  1. Launch Microsoft Excel and open the relevant document you want to work with or create a new one. Make sure you have data in your worksheet(s) before proceeding with the guide.

  2. Select an output cell for your final CAGR value, and then input your formula. The basic formula for calculating your CAGR is as follows:

    • BV - Beginning value of the investment

    • EV - Ending value of the investment

    • n - Number of periods

  3. Your formula should look like this: =(EV/BV)^(1/n)-1. In our example, the formula would be =(B2/B1)^(1/B3)-1

    CAGR formula example

    =(EV/BV)^(1/n)-1
    Imgur link
  4. Done! You can reuse this formula any time you need to calculate your Compound Annual Growth Rate.

Method 2. Use the RRI formula


Another way of calculating your CAGR in Excel is currently the
RRI formula. It also has 3 core arguments: the number of periods, start value and end value. Once again, we’ve set up an example to show you how to use this function below.

 

  1. Launch Microsoft Excel and open the relevant document you want to work with or create a new one. Make sure you have data in your worksheet(s) before proceeding with the guide.

  2. Select an output cell for your final CAGR value, and then input your RRI formula:

    • nper - The total number of periods.

    • pv - The present value of the investment.

    • fv - The future value of the investment.

  3. Your formula should look like this: =RRI(nper,pv,fv). In our example, the formula would be =RRI(B3,B1,B2)
    =RRI(nper,pv,fv)
    Imgur link
  4. Done!

Method 3. Use the CAGR calculator template

 

CAGR formula excel download template:


Thanks to this automated calculator, you never have to worry about calculating your CAGR again. With Vertex42’s
Online CAGR Calculator and the corresponding Excel template, you can easily get this number without having to input formulas manually.

 Use the CAGR calculator template

Tip: Want more amazing, time-saving Excel templates? Check out our Top 51 Excel Templates to Boost Your Productivity article.

 

Final thoughts


We hope this article helped you learn how you can calculate your CAGR using Excel’s formulas. Now, you’ll be able to see valuable information about your growth and make better decisions based on the results. We wish you the best on your journey! If you need any further help, don’t hesitate to
get in touch with us.

 

Before you go


If you need any further help with Excel, don’t hesitate to reach out to our customer service team, available 24/7 to assist you. Return to us for more informative articles all related to productivity and modern-day technology!

 

Would you like to receive promotions, deals, and discounts to get our products for the best price? Don’t forget to subscribe to our newsletter by entering your email address below! Receive the latest technology news in your inbox and be the first to read our tips to become more productive.

 

You may also like

» How to Save Excel Chart as Image
» How To Calculate Coefficient of Variation in Excel
» How To Calculate a P-Value in Excel



Copyright © 2013-present Magento, Inc. All rights reserved.