# How to calculate CAGR in Excel

To calculate the compound annual growth rate (CAGR) in Excel, you can either use the XIRR function or apply the formula =(BX/B2)^(1/Y)-1.
How to calculate CAGR in Excel?
3 min
31-August-2024
The compound annual growth rate, or CAGR for short, is a method to calculate the rate of growth of a particular amount, like an investment. The CAGR is a valuable tool to grasp how your investments are performing over a period. There are ways to calculate the CAGR and one of these includes making categories in tables on an Excel spreadsheet. Unfortunately, there is no method of default calculation, so you have to understand how to calculate CAGR in Excel by applying a particular formula. This basic formula is expressed as [(Ending Balance/Starting Balance)៱(1/Number of Years) – 1]. This article explores the calculation of CAGR and helps you gain a comprehensive understanding of it. Before coming to the actual calculation, some aspects of CAGR must be explored.

## What is CAGR?

Whether investing in mutual funds, stocks, or any other investment instrument, you may ask yourself, “How do I calculate the CAGR in Excel?”. This is a relevant question as investors would like to know how much an investment grows over time. As an investor, you should know that your investment may grow over a year, but this growth may not be the same every year. Furthermore, in particular years, your investment may grow and in some, it may not. When investors wish to find out the average rate of growth of investments over time, maybe over several years, the CAGR calculation comes into play.

The Compound Annual Growth Rate is a simple method by which you can learn about the average growth of your investment each year, over a timeframe. The CAGR does not merely calculate the addition of annual growth rates, but instead, shows you a stable rate of the growth of your investment. For instance, in case you are using an online calculator to compute the CAGR of a potential investment in a mutual fund scheme on an online platform, like the Bajaj Finserv Mutual Fund Platform, the CAGR calculation may be estimated by relying on the historical growth data of the mutual fund in question.

## CAGR formula in Excel

If you wish to learn how to calculate CAGR in Excel, you need to understand that the formula that is applied is the function that generates the value of the Compound Annual Growth Rate (CAGR) from the values that you provide on the Excel spreadsheet. For those individuals dealing with financial planning or analysis, the CAGR is typically calculated in Excel spreadsheets.

In Excel spreadsheets, the CAGR formula measures an investment return value considering a period of investment or growth or decline over time. The CAGR formula is frequently used by business owners, financial analysts, and investment managers to make calculations of the metric in Excel spreadsheets. The resultant value generated by the CAGR calculation helps these individuals to identify the extent to which their business has grown or make comparisons of revenue growth relative to competitive businesses. The CAGR is useful in that it gives clues as to how much a constant rate of return may be generated from any investment (on an annual basis).

The CAGR formula is expressed as:

 (Ending Balance/Starting Balance)៱(1/Number of Years) - 1

## How to calculate CAGR in Excel?

The CAGR calculation in Excel can be used to calculate the CAGR value for any investment. The easy way to calculate it is online, using a calculator. Say, if you are investing in mutual funds, and wish to compare a few funds based on the CAGR value, you can use an online mutual fund calculator to do so. However, you may have to undertake calculations for other investments using the CAGR formula in Excel spreadsheets, so this is vital to understand. There are a few ways to calculate the CAGR value in Excel spreadsheets, and these are explained below:

### 1. Direct CAGR formula method

You can calculate the CAGR in Excel in a straightforward way by applying a direct calculation method. Here is how:

• Assuming that you have recorded your investment year-end values over a period from 2010 to 2016, you would have done so using the columns and cells in the Excel spreadsheet. So, the years (2010 - 2016) would be listed individually in column A, and the year-end investment values for each year would be correspondingly listed in column B. Let us say that you have started your list with row 2 (for the year 2021) and ended your list with row 7.
• Then click on row 8 in column B, or rather cell B8 (column B and row 8). Enter your formula here. However, go through the subsequent points before you do this.
• According to your list in the Excel spreadsheet, your first year of investment is 2010 with a particular year-end value recorded, and your last year of investment (to calculate the CAGR) is 2016 with a different year-end value recorded. In this example, the CAGR is calculated by taking five full years of completion of the investment, although six years (2010-2016) have been listed.
• Based on the CAGR formula, how to calculate CAGR on Excel will be done by entering the formula: (B7/B2)() - 1 (enter this in cell B8)
• In the formula described above, the value of the year-end investment in cell B7 (the last year-end value) is divided by the value of the year-end investment in cell B2 (the first year-end value). This is raised to one-fifth as there are five full years of investment to consider according to the example.
• After you enter the CAGR formula, you must press enter to get your final result. This will show as a decimal figure. If you want to change the result to reflect a percentage, then all you have to do is change the cell format to the percentage style.

### 2. Calculating CAGR with the Excel RRI function

You may be thinking of whether there are other methods to calculate the CAGR in Excel while you ponder the question “How do I calculate the CAGR in Excel?”. Well, if you are looking for a quick way to compute CAGR in Excel, you can use the RRI function. This is particularly designed to find out the equivalent rate of interest representing investment growth over a fixed period. Now that you are familiar with rows, columns, and cells on an Excel spreadsheet, here is how to calculate the CAGR with the RRI function:

• To calculate the CAGR with the RRI function, you have to know the total number of years or periods over which your investment grows.
• You must also know the initial value of your investment.
• You must know the end value of your investment (what it will be worth when your investment is completed)
• Once you know the figures/values mentioned in the previous points, you can apply the RRI function thus: RRI = (5, B2, B7). You can enter this in cell B8. Note that the same figures and values have been taken as per the previous example (mentioned in the Direct Method section).
• Again, your answer will be shown as a decimal figure, and if you want to view it as a percentage, you must format the cell as a percentage.

### 3. Calculating CAGR with the Excel RATE function

Learning how to calculate CAGR in Excel is not a challenge with operations inherent in the Excel spreadsheet. What’s more, you can calculate the CAGR of any investment, including your investment in mutual fund schemes, by entering a few figures and years of investment. Besides this, you can also calculate estimated returns on any investment if you know the values required, or you can base certain values on past performance standards.

Coming back to more ways to calculate CAGR in Excel, the Excel Rate function is a method that is commonly used for interest rates on loans. However, it can be used as effectively to learn about your investment growth. Here is how you use this function for your calculation:

• The formula consists of the total year of investment as in the previous method.
• The formula also consists of your initial investment amount, and this is expressed in the negative.
• Finally, the formula has the final value of your investment.
• According to the cells and your values entered.
• Your result is expressed as a decimal figure and if you want the result as a percentage, you must change the format to percentage.

## How to use CAGR formula in Excel with examples?

For the purpose of clarity, and how to calculate CAGR in Excel, the following table is a representation of a potential Excel spreadsheet with hypothetical values, and this table can be used to explain the methods of calculation that follow:

 Serial No. A B 1 YEAR AMOUNT/VALUE 2 2008 1000 3 2009 1282 4 2010 1496 5 2011 1617 6 2012 1892 7 2013 2143 8 2014 2456 9 2015 2178 10 2016 2566

### 1 – Basic method

As you can see in the table above, the years of investment begin from cell A2 (column A and row 2) and end at cell A10, while the values of investment start at cell B2 and end at cell B10. Using the formula for the Basic Method to calculate the CAGR, we can apply the formula by clicking on cell B11 and entering the following in cell B11: (B10/B2)៱(1/9)-1. Then you must press enter to get the result.

You will get a decimal figure and if you wish to convert this to a percentage, simply change the cell format to reflect the percentage. In the above example, the decimal result is 0.110383 and the percentage result is 11.04% (rounded off).

### 2 – Using the power function

Using the power function is a simple way to compute the CAGR in Excel. To learn how to calculate CAGR in Excel with the Power function, all you have to do is apply a formula again. You must enter the following in cell B11 after you click on it: =POWER (B10/B2, 1/9)-1. Then you must hit “Enter” and you will get the result as explained in the previous section. Your result will be in decimals but you can convert this into a percentage as explained earlier.

### 3 – Using the RATE function

How to calculate the CAGR on Excel with the Rate function is done, like all other methods, by entering a formula in cell B11, considering the table shown as an example. The following is the syntax of the Rate function: =RATE (nper, pmt, pv, [fv], [type], [guess]), where:

• Nper is the total number of payments
• Pmt is the value of a payment that is made in each period
• Pv is the present value (the initial investment made)
• Fv is the future value (estimated or known)
• Type is optional and is represented as 1 if payments are due at the beginning of a period and 0 if they are due on completion of a period
• Guess is optional to the formula and is assumed at 10%
By substituting values, you can get the results of the CAGR for the tabular example above. The formula after substitution must be entered in cell B11 and after you press “Enter”, you will see your result.

### 4 – Using the IRR Function

You may not be asking, “How do I calculate CAGR in Excel”, but there is one last formula you should know about. The Internal Rate of Return or IRR is a formula to use when calculating the CAGR in Excel. The syntax of the formula is: = IRR (values, [guess]). In this formula, “values” stand for all the figures that reflect cash flows. This part of the formula has to have a positive and a negative value. The “guess” component is an optional component and includes the estimation of the rate of return.

## CAGR formula errors

You may come across errors while using any of the methods to calculate CAGR in Excel. The main error to consider is the #VALUE! Error. You may face this error in case any values provided are not valid values in terms of their recognition by Excel spreadsheets.

## Things to remember when calculating CAGR in Excel

For investors, life becomes easy when they know how to calculate the CAGR in Excel as they can compare investment instruments based on the CAGR and plan them accordingly. Whether investing in mutual funds or stocks, the CAGR helps you to make estimations and predictions based on present or past information. Nonetheless, there are some things to remember as highlighted below:

• The CAGR formula is offered by Microsoft Excel and it gives this CAGR value depending on the values provided and entered.
• CAGR is a measure of the value of a return on investment and is calculated over a specific period.
• The CAGR formula, as it is used in Excel, shows the extent of a constant growth rate considering that the investment gives returns yearly.
• In case an error occurs while calculating CAGR, this is likely to be the #VALUE error.

## Conclusion

Today, investors use any metric that helps them to compare investments and monitor their existing investments. Among such methods that inform investors about the performance or estimation of yearly returns is the calculation of CAGR using Excel. This is a quick and easy way to make calculations and track your yearly investment growth or growth over a particular period.

Planning your investments is the key to making them work for you. You may be new to investing or a seasoned player, but it is imperative to know how to plan your investments based on your financial goals and circumstances. An effective way to start investing, or broaden the scope of your financial portfolio is to invest in mutual funds through a convenient platform like the Bajaj Finserv Mutual Fund Platform, where you get more than 1000 mutual fund schemes, according to your financial aims and risk profile.

## Essential tools for all mutual fund investors

 Lumpsum Calculator Step Up SIP Calculator Axis Bank SIP Calculator Canara Robeco SIP Calculator SBI SIP Calculator HDFC SIP Calculator Nippon India SIP Calculator ABSL SIP Calculator

What is the formula to calculate CAGR on Excel?
You can calculate CAGR in Excel using the following formula: (Ending Balance/Starting Balance)˄(1/Number of Years) – 1.

How do you calculate a 5 year CAGR?
To calculate a 5-year CAGR, you must enter 5 for the number of years in the CAGR formula which is = (Ending Value / Beginning Value)^(1 / Number of Years) – 1.

What is the formula for CAGR in Excel IRR?
The syntax of the formula is: =IRR (values, [guess]). In this formula, “values” stand for all the figures that reflect cash flows. This part of the formula has to have a positive and a negative value. The “guess” component is an optional component and includes the estimation of the rate of return

How do I use the CAGR formula in an Excel cell?
You can use the CAGR formula in an Excel cell by entering the formula in the cell below the list of values that you provide on the Excel spreadsheet. You must click on the cell in which you are entering the formula, enter the appropriate formula, and hit the “Enter” button for the result.

Can I use the POWER function to calculate CAGR in Excel?
Yes, you can use the POWER function to calculate CAGR in Excel. The POWER function is used by entering =POWER(B10/B2, 1/9)-1 in the cell for the formula.

How can I calculate CAGR using the RATE function in Excel?
The following is the syntax of the Rate function formula: =RATE (nper, pmt, pv, [fv], [type], [guess]). You must enter this formula after you substitute the appropriate values in the formula cell on the Excel spreadsheet and then press “Enter” to get the result.

How do I convert the CAGR result to a percentage in Excel?
To convert the CAGR result from a decimal figure (default result setting in Excel), you must change the cell format to reflect the percentage format.

Can I calculate CAGR for a non-integer number of years?
Yes, you can calculate the Compound Annual Growth Rate (CAGR) for a non-integer number of years. The CAGR formula is flexible and can make room for a non-integer number of years or fractional periods.

What are common mistakes to avoid when calculating CAGR in Excel?
The most common mistake that you can make while calculating CAGR in Excel is to enter incorrect values or values that are not conducive to the Excel spreadsheet format.

How do I interpret the CAGR result?
The Compound Annual Growth Rate (CAGR) is an effective metric for evaluating the average annual growth rate of an asset, investment, or any other variable over a specified time, assuming that growth occurs at a constant yearly rate. The resulting CAGR can help you to compare different investment options and investments, as it tells you how much investments have grown over time, or are estimated to grow, given certain factors.

## Bajaj Finserv App for All Your Financial Needs and Goals

Trusted by 50 million+ customers in India, Bajaj Finserv App is a one-stop solution for all your financial needs and goals.

You can use the Bajaj Finserv App to:

Apply for loans online, such as Instant Personal Loan, Home Loan, Business Loan, Gold Loan, and more.

Explore and apply for co-branded credit cards online.

Invest in fixed deposits and mutual funds on the app.

Choose from multiple insurance for your health, motor and even pocket insurance, from various insurance providers.

Pay and manage your bills and recharges using the BBPS platform. Use Bajaj Pay and Bajaj Wallet for quick and simple money transfers and transactions.

Apply for Insta EMI Card and get a pre-approved limit on the app. Explore over 1 million products on the app that can be purchased from a partner store on Easy EMIs.

Shop from over 100+ brand partners that offer a diverse range of products and services.

Use specialised tools like EMI calculators, SIP Calculators

Download the Bajaj Finserv App today and experience the convenience of managing your finances on one app.

## Do more with the Bajaj Finserv App!

UPI, Wallet, Loans, Investments, Cards, Shopping and more

## Disclaimer:

Bajaj Finance Limited (“BFL”) is an NBFC offering loans, deposits and third-party wealth management products.

The information contained in this article is for general informational purposes only and does not constitute any financial advice. The content herein has been prepared by BFL on the basis of publicly available information, internal sources and other third-party sources believed to be reliable. However, BFL cannot guarantee the accuracy of such information, assure its completeness, or warrant such information will not be changed.

This information should not be relied upon as the sole basis for any investment decisions.Hence, User is advised to independently exercise diligence by verifying complete information, including by consulting independent financial experts, if any, and the investor shall be the sole owner of the decision taken, if any, about suitability of the same.

Show All Text

## Disclaimer

Bajaj Finance Limited ("BFL") is registered with the Association of Mutual Funds in India ("AMFI") as a distributor of third party Mutual Funds (shortly referred as 'Mutual Funds) with ARN No. 90319

BFL does NOT:

(i) provide investment advisory services in any manner or form:

(ii) carry customized/personalized suitability assessment:

(iii) carry independent research or analysis, including on any Mutual Fund schemes or other investments; and provide any guarantee of return on investment.

In addition to displaying the Mutual fund products of Asset Management Companies, some general information is sourced from third parties, is also displayed on As-is basis, which should NOT be construed as any solicitation or attempt to effect transactions in securities or the rendering any investment advice. Mutual Funds are subject to market risks, including loss of principal amount and Investor should read all Scheme/Offer related documents carefully. The NAV of units issued under the Schemes of mutual funds can go up or down depending on the factors and forces affecting capital markets and may also be affected by changes in the general level of interest rates. The NAV of the units issued under the scheme may be affected, inter-alia by changes in the interest rates, trading volumes, settlement periods, transfer procedures and performance of individual securities forming part of the Mutual Fund. The NAV will inter-alia be exposed to Price/Interest Rate Risk and Credit Risk. Past performance of any scheme of the Mutual fund do not indicate the future performance of the Schemes of the Mutual Fund. BFL shall not be responsible or liable for any loss or shortfall incurred by the investors. There may be other/better alternatives to the investment avenues displayed by BFL. Hence, the final investment decision shall at all times exclusively remain with the investor alone and BFL shall not be liable or responsible for any consequences thereof.

Investment by a person residing outside the territorial jurisdiction of India is not acceptable nor permitted.

Disclaimer on Risk-O-Meter:

Investors are advised before investing to evaluate a scheme not only on the basis of the Product labeling (including the Riskometer) but also on other quantitative and qualitative factors such as performance, portfolio, fund managers, asset manager, etc, and shall also consult their Professional advisors, if they are unsure about the suitability of the scheme before investing.

Show All Text