Do you know how much your mutual fund scheme has generated till now? Here is how to calculate CAGR in Excel

Returns in a market-linked investment such as equity share or a mutual fund do not grow in the same manner each year. For example, in one year, there could be a growth of 17 per cent while in the second year, the growth may be restricted to only about 9 per cent, while in the third year, there could be negative growth of 5 per cent.

For an investor who has held the investment for three years, calculating the return can be primarily in two ways – Either calculate the absolute return over the three year period or find the compounded annual growth rate (CAGR).

Knowing the absolute return doesn’t help much as it does not take into account the period over which such a return has been generated. For example, an investment of Rs 10,000 grows to Rs 30,000 over a period of time. The absolute return will amount to 200 per cent. Or, if the NAV grows from Rs 10 to Rs 30, the absolute return is 200 per cent but the period is not considered.

The right way to find the returns will be to represent the returns in terms of CAGR.

CAGR basically takes into the period over which the investment was held. It is the actual return ( on initial investment/ NAV) on an annual basis assuming the gains are reinvested at the end of each year to arrive at the ending balance or the ending/current NAV.

CAGR reflects the rate of return which the investment has generated each year although in practice the returns could be different for each year.

It can be calculated for an investment held over a few days, weeks, months or years. Generally, absolute returns are calculated for investments held within 12 months, annualized returns for exactly 12 months and CAGR for investments held over 12 months.

Excel Formula for calculating CAGR

= (((ending value/beginning value)^(1/number of years))-1)*100
= (((ending value/beginning value)^(1/number of months))-1)*100
= (((ending value/beginning value)^(1/number of days))-1)*100

Beginning value or the ending value could be the NAV or the investment amount.

CAGR Example

In the above example, the CAGR, if the investment is held for 2 years, will be –
= (((30000 / 20000)^(1 / 2)-1)*100 = 73.21 per cent

And, the CAGR, if the investment is held for 5 years, will be –
= (((30000 / 20000)^(1 /5)-1)*100 = 24.57 per cent

One may use CAGR to calculate returns from mutual funds schemes by taking into account the initial NAV and the current NAV or the NAV at which the investment was redeemed. As the returns are volatile and uneven in a market-linked investment, CAGR helps in providing a sort-of annualised return for each year of holding.