Have you ever calculated your mutual fund’s CAGR using Excel? While a compound growth rate calculator is the most effective way to calculate CAGR today, you can also choose to calculate CAGR using Excel. Read on to know the different ways in which you can calculate CAGR for your mutual fund.
What is CAGR and how is it calculated?
CAGR or compound annual growth rate is a measure of the annual growth of your investment. The CAGR of a mutual fund investment or a stock investment shows you the average rate of return earned on your investments in a year’s period. Investors rely on a mutual fund scheme’s CAGR to analyse its past performance and decide on whether or not they must invest in the fund. When a CAGR calculator or an investor calculates CAGR, they assume that the fund’s returns will be reinvested in the fund at the end of each year of the time horizon. A fund’s CAGR is a representative number and not an accurate reflection of returns.
CAGR formula in Excel –
One of the most convenient ways to calculate your fund’s CAGR is to use a compound annual growth rate calculator. However, you can also calculate a fund’s CAGR using Microsoft Excel using the following formula –
CAGR = (Ending Balance/Starting Balance)^(1/Number of years) – 1
You can use this formula in your Excel sheet to calculate your fund’s CAGR.
Ways to calculate CAGR using Excel –
You can use more than one ways to calculate CAGR using Excel –
1. Use the Direct CAGR formula method:
Here are the steps you must follow –
· Suppose you have listed the years 2010 to 2016 in the column A of your Excel sheet and the year-end investment values for every year are listed in column B. You started your list with row 2 (for the year 2021) and ended your list with row 7.
· Click on row 8 in column B. Enter the CAGR formula here. You must go through the following points before doing this:
o Remember that CAGR is calculated by taking five full years of completion of the investment, although six years (2010-2016) are listed.
o You can use the CAGR formula (B7/B2)^(1/5) – 1 and calculate CAGR using this method.
2. Calculate CAGR using the RRI function in Excel:
You must know the total number of years over which your investment will grow to calculate CAGR using the RRI function in Excel. You should also know the initial and end values of your investment. Once you know these points, you can apply the formula RRI = (5, B2, B7) in the cell B8 to calculate CAGR.
3. Use the RATE function in Excel to calculate CAGR:
The RATE function requires you to enter the total years of investment, your initial investment amount (expressed in negative), and the final value of your investment. Your result is expressed as a decimal figure. You can also choose to view it as a percentage (Change the format to “Percentage”).
In conclusion
You can calculate your fund’s CAGR in three main ways – using the Direct CAGR formula method, using the RRI function in Excel, and using the RATE function in Excel.