EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Excel CAGR Formula

By Madhuri ThakurMadhuri Thakur

Home » Excel » Blog » Excel Tips » Excel CAGR Formula

CAGR Formula in Excel

CAGR in Excel Formula (Table of Contents)

  • Introduction to CAGR Formula in Excel
  • Explanation of CAGR (Compound Annual Growth Rate)
  • How to Use CAGR Formula in Excel?

Introduction to CAGR Formula in Excel

CAGR in excel is used for calculating Compound Annual Growth Rate for any invested amount for the mentioned years or period. Although there is no direct function in excel that would help to find CAGR value, we can use the RRI function, which also calculates the return rate on investment; for this, we need to have the number of payments to be made, present, and future values of the invested amount. If we calculate CAGR from mathematical formulae, the calculated value will be the same as that calculated by CAGR.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

The formula to calculate CAGR is below:

CAGR Formula

To calculate the annual growth rate, divide the value of an investment at the end of the period by its value at the beginning of that period, raise the result to an exponent of one divided by the number of years and subtract one from the result.

Explanation of CAGR (Compound Annual Growth Rate)

CAGR is used to calculate the average growth of a single investment. Due to market volatility, the year-to-year growth may vary. CAGR helps smooth returns when growth rates are volatile.

As explained above, I will bring the same aspect; the compound annual growth rate isn’t an actual return value return rate. It is a representational figure. It is a number that describes the rate by which the investment would have grown if it had the same rate every year over the period.

In a real scenario, this sort of growth is unlikely to happen; however, it smoothes the returns so that it is easily compared to the other investments.

CAGR can be considered the growth rate from the initial investment value to the ending investment value if we assume that the investment has been compounding over a period.

Popular Course in this category
Sale
Excel Training (21 Courses, 9+ Projects)21 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (11,462 ratings)
Course Price

View Course

Related Courses
Excel Data Analysis Training (16 Courses, 8+ Projects)Excel for Finance Training (18 Courses, 7+ Projects)

How to Use CAGR Formula in Excel?

CAGR Formula in Excel is very simple and easy to use. Let’s understand the working of the compound annual growth rate Formula in Excel by some examples.

You can download this CAGR Formula Excel Template here – CAGR Formula Excel Template

CAGR Formula in Excel – Example #1

We have data for an ABC Company as below,

Example 1

The data display value of sales done in the respective year. Column B has sales done for the company in the year. Let us assume the sale price is in cr. (crores). By the CAGR formula in excel, we can calculate the annual growth rate.

The starting balance is 100, and the ending balance is 150. Now we count the number of years. The first period of the cycle is 2010-2011, and the last is 2013-2014, which is for four years.

  • Now, in Cell C2, let us calculate the annual growth rate by the formula.

CAGR Formula Example 1-2

So the result will be:-

CAGR Formula Example 1-3

  • Now click on the % sign in the Home Tab in the general section.

Result of Example 1

  • Now we have the Compound Annual Growth Rate, which is 11%.

CAGR Formula Example 1-5

CAGR Formula in Excel – Example #2

Let us make a ready formula where we will insert the values, and it will calculate the compound annual growth rate in excel.

  • We need a Starting Value. So in cell A1, type a header for it as SV (Starting Value).

Starting Value

  • Now for an Ending Value. So in Cell B1, type a header for it as EV (Ending Value).

Ending Value

  • For the total number of years in cell C1, type a header for it as “NoY.”

NoY

  • In cell D1, type a header for the final value and type the header as CAGR (Compound Annual Growth Rate).

Compound Annual Growth Rate 1

  • Type the CAGR formula in cell D2, which is,

Calculation of Compound Annual Growth Rate

  • Copy the value of D2 in cell E2 and use the % sign in cell E2 or write =D2 in cell E2 and click on the % sign.

Now try putting any random values in the starting, ending, and the number of years. Cell D2 gives a CAGR value.

Result of Compound Annual Growth Rate

And exact Rate percentage is displayed in cell E2.

Final Value

CAGR Formula in Excel – Example #3

Let’s take another example of the Formula in Excel. We have the below data,

CAGR Formula Example 3-1

  • Find a starting Value as the beginning balance, which is B1.
  • Find an Ending Value as the Ending Balance, which is B5.
  • Count the number of years, which is 3.
  • In Cell C1, write down the formula for CAGR, which is (Ending Balance/Starting Balance)˄(1/Number of Years) – 1.

CAGR Formula Example 3-2

The output will be:-

CAGR Formula Example 3-3

  • Click on the % sign in the Home Tab under the general section to get the value of CAGR.

CAGR Formula Example 3-4

  • The final value is 1% which is the required annual growth rate.

Result of Example 3

Pros

  • A compound annual growth rate in excel smoothed the growth rate over time.
  • It implies the growth was steady.

Cons

  • By CAGR, we cannot assume the growth rate will be the same in the future.
  • We cannot have insight into the uneven growth in the middle years of CAGR.
  • CAGR is not an absolute value.

Things to Remember about CAGR Formula in Excel

  • CAGR Formula in Excel only calculates the average annual growth rate for years.
  • CAGR does not show the unevenness in the growth rate in the middle years.
  • It only smoothed the growth rate over a period.

Recommended Articles

This is a guide to the CAGR formula in excel. Here we have discussed the compound annual growth rate in excel and How to use the CAGR formula in excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. MODE Excel Function
  2. Write Formula in Excel
  3. TRIM Formula in Excel
  4. LEFT Formula in Excel

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Excel Tips
    • Recover Document in Excel
    • Excel Shortcut to Select Row
    • Excel Header Row
    • Excel Insert Button
    • Excel Translate
    • Budget in Excel
    • KPI Dashboard in Excel
    • Blank Invoice Excel Template
    • Write Formula in Excel
    • Auditing Tools in Excel
    • Autofit Row Height in Excel
    • Formatting Text in Excel
    • Add Rows in Excel Shortcut
    • Divide Cell in Excel
    • Compare Two Columns in Excel for Matches
    • Excel Troubleshooting
    • Unprotect Excel Workbook
    • Subtract Date in Excel
    • Carriage Return in Excel
    • Negative Numbers in Excel
    • Worksheets in Excel
    • Excel Match Multiple Criteria
    • Exponential Moving Average Formula
    • Sentence Case in Excel
    • Excel Reverse Order
    • Excel Timesheet Template
    • Weighted Average in Excel
    • CTRL Shift-Enter in Excel
    • Excel Expense Tracker
    • INDEX MATCH Function in Excel
    • Percent Change in Excel
    • Drag and Drop in Excel
    • Shade Alternate Rows in Excel
    • Excel Database Template
    • Inverse Matrix in Excel
    • New Line in Excel Cell
    • Format Cells in Excel
    • Excel Text with Formula
    • Project Management Template in Excel
    • Vlookup vs Index Match
    • Embedded in Excel
    • Count Colored Cells in Excel
    • Excel Hacks
    • Column Header in Excel
    • Database Function in Excel
    • Excel Spell Check
    • Linear Interpolation in Excel
    • Convert Excel to CSV
    • Excel Not Responding
    • Delta Symbol in Excel
    • Excel Mortgage Calculator
    • Excel Show Formula
    • Break Links in Excel
    • VLOOKUP Examples in Excel
    • Alternate Row Color Excel
    • Checklist in Excel
    • Microsoft Office Tips
    • Excel vs Access
    • Excel Change Case
    • Count Characters in Excel
    • Formula Bar in Excel
    • Invoice Template in Excel
    • Find and Replace in Excel
    • PowerPivot in Excel
    • Wildcard in Excel
    • Line Break in Excel
    • NULL in Excel
    • Text Formula in Excel
    • Family Tree in Excel
    • Insert Calendar in Excel
    • Page Setup in Excel
    • Search For Text in Excel
    • Switching Columns in Excel
    • 3D Cell Reference in Excel
    • Roadmap Template in Excel
    • Cell References in Excel
    • Excel Automation
    • Matching Columns in Excel
    • Evaluate Formula in Excel
    • How to Find Mean in Excel
    • How to Add Cells in Excel
    • Project Timeline in Excel
    • TODAY Formula in Excel
    • Poisson Distribution in Excel
    • YEAR Formula in Excel
    • REPLACE Formula in Excel
    • Create Spreadsheet in Excel
    • SEARCH Formula in Excel
    • HYPERLINK Formula in Excel
    • Pivot Table with Multiple Sheets
    • COLUMNS Formula in Excel
    • DAY Formula in Excel
    • RIGHT Formula in Excel
    • INDIRECT Formula in Excel
    • Excel IRR Formula
    • Pivot Table Sort
    • OR Formula in Excel
    • Pivot Table Formula in Excel
    • How to Edit Drop Down List in Excel?
    • WEEKDAY Formula in Excel
    • Chart Wizard in Excel
    • Time Difference in Excel
    • Lookup Table in Excel
    • SUBTOTAL Formula in Excel
    • LOOKUP Formula in Excel
    • Themes in Excel
    • Group Worksheets in Excel
    • Excel ISNUMBER Formula
    • 3D Maps in Excel
    • VLOOKUP Tutorial in Excel
    • Moving Columns in Excel
    • Grouping Columns in Excel
    • Excel Formula of Percentage
    • Excel Leading Zeros
    • Timesheet in Excel
    • Nested IF Formula in Excel
    • SUMIF Formula in Excel
    • Concatenation in Excel
    • Excel Insert Page Break
    • LEN Formula in Excel
    • Insert Comment in Excel
    • Excel Print
    • Fractions in Excel
    • Excel Add a Column
    • Borders in Excel
    • Excel Sum by Color
    • Excel vs Google Sheets
    • Calculate Percentage Increase in Excel
    • Excel Format Phone Numbers
    • Excel Search Box
    • Excel vs CSV
    • Excel vs Numbers
    • Excel Concatenate Date
    • Excel Bullet Points
    • Calculate Compound Interest in Excel
    • Find External Links in Excel
    • Excel Comma Style
    • Excel Remove Leading Spaces
    • Combine First and Last Name in Excel
    • How to Calculate Ratio in Excel
    • SUMPRODUCT Function with Multiple Criteria
    • Create Excel Template
    • Excel Greater than or Equal
    • Excel Keyboard Shortcuts
    • Excel Row vs Excel Column
    • Excel Operators
    • Excel Text Compare
    • Excel Uppercase Function
    • Excel Auto Numbering
    • Watermark in Excel
    • Excel Delete Row Shortcut
    • Excel Word Count
    • Row Count in Excel
    • Excel Grade Formula
    • Excel Percentage Difference
    • Excel Rows and Columns
    • Excel Group
    • Excel Move Columns
    • Excel Row Height
    • Excel Sparklines
    • Excel Drawing a line
    • Multiple IFS in Excel
    • Excel Strikethrough
    • Numbering in Excel
    • Share Excel Workbook
    • Excel Shortcut For Merge Cells
    • Excel OneDrive
    • Excel Track changes
    • Excel Hide Formula
    • Excel Filter Shortcuts
    • Excel 3D Reference
    • Protect Excel Workbook
    • Excel Moving Averages
    • Excel Row Limit
    • Excel Absolute Value
    • Excel Print Area
    • Excel Format Painter
    • Excel Separate text
    • Excel Autofit
    • Excel Columns to Rows
    • Excel Paste Shortcut Option
    • Excel Rows to Columns
    • Excel Insert Row Shortcut
    • Excel Extensions
    • Excel Unmerge Cells
    • Divide in Excel Formula
    • Excel Compare Two Columns
    • Not Equal To Excel
    • Excel Remove Duplicates
    • Excel Remove Spaces
    • Excel Calculate Age
    • Copy Excel Sheet
    • Excel Conditional Formatting Based on Another Cell Value
    • Excel Cell Reference
    • Excel Split Cell
    • Excel Circular Reference
    • Highlight Every Other Row in Excel
    • Errors in Excel
    • Excel Delete Pivot Table
    • Excel Pivot Table
    • Subtraction in Excel
    • Count Unique Values in Excel
    • Excel Highlight Duplicates
    • Excel Data Formatting
    • Random Numbers in Excel
    • Basic Excel Formulas
    • Page Numbers in Excel
    • Excel CAGR Formula
    • Excel Combine cells
    • IFERROR with VLOOKUP in Excel
    • Column Sort in Excel
    • Print Comments in Excel
    • Cheat Sheet of Excel Formulas
    • Remove Hyperlinks in Excel
    • Excel Drop Down List
    • Remove (Delete) Blank Rows in Excel
    • Relative Reference in Excel
    • Subscript in Excel
    • Superscript in Excel
    • Excel Insert Multiple Rows
    • Excel Absolute Reference
    • Unhide Columns in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download CAGR Formula Excel Template

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

डाउनलोड CAGR Formula Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - EXCEL COURSE Learn More