WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » CAGR Formula in Excel

CAGR Formula in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

CAGR or compound annual growth rate is method to calculate the growth rate of a particular amount annually, by default we do not have any inbuilt formula in excel to calculate CAGR for us, instead we make categories in tables and in tables we apply the following formula to calculate CAGR which is as follows, (Ending Balance/Starting Balance)˄(1/Number of Years) – 1.

CAGR Formula in Excel (Compound Annual Growth Rate)

CAGR formula in Excel is the function that is responsible for returning CAGR value, i.e., the Compound Annual Growth Rate value from the supplied set of values. If you are into financial analysis or planning, you will need to calculate the compound annual growth rate in excel value in Excel spreadsheets.

CAGR formula in excel measures the value of return on an investment, which is calculated over a certain period of time. Compound Annual Growth Rate formula in excel is used in Excel spreadsheets often by financial analysts, business owners, or investment managers, which helps them in identifying how much their business has developed or in the case of comparing revenue growth with the competitor companies. With the help of CAGR, it can be seen how much constant growth rate should the investment return on an annual basis. In reality, the growth rate should vary over the period of time, or from year to year.

For example, if you have bought gold in the year 2010 worth USD 200 and it is worth USD 500 in the year 2018, CAGR is the rate at which this investment has grown every year.

CAGR Formula

where,

  • Ending Value = Investment’s ending value
  • Starting Value = Investment’s starting value
  • n = number of investment periods (months, years, etc.)

Return Value:

  • The return value will be a numeric value, which can be converted into a percentage because CAGR value is effective when it’s in percentage form.

 

How to Use CAGR Formula in Excel with Examples?

Let’s understand how to use the CAGR formula in excel with examples.

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

#1 – Basic Method

Let’s consider the excel spreadsheet below. Look at the data.

CAGR Function in Excel Example 1

Step 1 – You can see the above spreadsheet where column A has been categorized as “YEAR,” and column B has been categorized as “AMOUNT.”

In column YEAR, the value starts from the A2 cell and is ending at A10 cell.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

CAGR Function in Excel Step 1

Again in the column AMOUNT, the value is starting at B2 cell and is ending at B10 cell.

CAGR Function in Excel Step 1-1

Therefore, we can see that the investment’s starting value (SV) is the B2 cell and the investment’s ending value (EV) is the B10 cell.

CAGR Function in Excel Step 1-2

Step 2– Now, we have the values which can be put in the (Compound Annual Growth Rate) CAGR formula in excel. To successfully do it in your excel spreadsheet, you have to select any of the cells of the C column and type the formula as given below –

= (B10/B2) ˆ (1/9)-1

In the above compound annual growth rate in Excel example, the ending value is B10, the Beginning value is B2, and the number of periods is 9. See the screenshot below.

CAGR Function in Excel Step 2

Step 3 – Now hit enter. You will get the CAGR (Compound Annual Growth Rate) value result inside the cell in which you had input the formula. In the above example, the CAGR value will be 0.110383. The return value is just the evaluation of the CAGR formula in excel with the values which has been described above. Consider the screenshot below.

CAGR Function in Excel Step 3

Step 4– Note down that Compound Annual Growth Rate in Excel is always represented in the form of percentage in the financial analysis field. To get the CAGR value in percentage, you have to select the cell in which your CAGR value is present and change the cell format from ‘General’ to ‘Percentage.’ The percentage value of CAGR (Compound Annual Growth Rate) in the above example is 11.04%. You can see the screenshot below.

Excel Step 4

The above steps show how you calculate the Compound Annual Growth Rate in Excel (CAGR) spreadsheets.

#2 – Using the Power Function

You can also use the POWER formula in excel the method for finding the CAGR value in your excel spreadsheet. The formula will be “=POWER (Ending Value/Beginning Value, 1/9)-1”. You can see that the POWER function replaces the ˆ, which was used in the traditional CAGR formula in excel. If we use the POWER function in the above excel spreadsheet, where we used the traditional method to find the CAGR value, the result will be 0.110383 or 11.03%. Consider the screenshot below.

Using the POWER Method 1

#3 – Using the RATE Function

This is a much lesser-used method for calculating CAGR (Compound Annual Growth Rate) value or percentage but a clean way as well. The syntax of the RATE function in excel can seem a bit complicated to you, but if you know the terms well, it will not be too hard for you either. The syntax of the RATE function is given below-

=RATE (nper, pmt, pv, [fv], [type], [guess])

Let’s now have an explanation of the above-given terms.

  • nper – (Required) This is the total number of payment which has been made in the specific period.
  • pmt – (Required) This is the value of a payment, which is made in each period.
  • pv – (Required) This is the present value.
  • fv – (Optional) This is the future value.
  • type – This means when the payments are due. The value is either 0 or 1. 0 means the payment has been due in the beginning, and 1 means that the payment has been due at the end of the period.

#4 – Using the IRR Function

IRR is the abbreviation of the Internal Rate of Return. IRR method is helpful when you need to find the CAGR (Compound Annual Growth Rate) value for different value payments, which has been made during the specific time period. The syntax of the IRR function in excel is “=IRR (values, [guess]).” Values mean the total range of Numbers, which is representing the cash flows. This section must contain one positive and one negative value. [Guess] in an optional argument in the syntax, which means your guess at what the return rate might be.

CAGR Formula Errors

If you get any error from the CAGR Formula Excel, then this is likely to be the #VALUE! error.

Excel Errors 1

#VALUE! – This error will occur if any of the supplied arguments are not valid values recognized by excel.

Excel Errors 1-1

Things to Remember

  • Microsoft Excel CAGR Formula is the function that is responsible for returning CAGR value, i.e., the Compound Annual Growth Rate in excel value from the supplied set of values.
  • CAGR measures the value of return on an investment, which is calculated over a certain period of time.
  • With the help of the Compound Annual Growth Rate formula in Excel, it can be seen how much constant growth rate should the investment return on an annual basis.
  • If you get any error from the CAGR formula in excel, then this is likely to be the #VALUE! error.

Recommended Articles

This has been a step by step guide to CAGR Formula in Excel. Here we discuss how to Use CAGR Formula in Excel using the basic formula, power method, rate function, and IRR function and use it to solve problems in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel

  • NPER in Excel
  • Mistakes in DCF
  • DDM
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download CAGR Formula Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More