How to Calculate CAGR?

 

How to Calculate CAGR?

Compound Annual Growth Rate……….

1.       It helps to calculate annual growth rate of an investment over a specific period of time.

2.       It helps to determine Rate at which we have earned on an individual investment, assets and portfolio.

3.       CAGR is the best formulae to compare and determine how various investment options have performed against each other.

4.       Returns over a longer period of time are volatile and may be high in some period and may be negative in some period. Hence CAGR gives you an average rate over the total period under consideration.

5.       It is generally observed that various investment option has given ROI as mentioned below:

Current Account	0%
Saving Account	3.5%
Gold	5% - 6% (Last 5 year Avg.)
Real Estate	5% - 6% (Last 5 year Avg.)
Fixed Deposit	6% - 8%
Debt Mutual Funds	6% - 7%
Mutual Funds – Indirect Investing in Share Market	12% - 18%
Direct Investing in Share Market	18% - 24%

 

6.       Above mentioned returns have been observed considering Index only. It does not mean that all the investors have earned the same return from investing in the said assets. Some may have earned double the average return and some may have suffered losses too.

7.       It is very important that we should calculate return on our investment since timing of investment is very important. Since difference between double of average return and losses is created only due to different timings of the investment.

8.       Correct CAGR of our portfolio will help us in making correct decision regarding choice of assets and duration to invest considering our risk appetite, requirement.

Excel Formulae to Calculate CAGR

1.       Prepare 2 columns:

-          One column for Date of Investment where all dates are entered when you have made the payments (transaction wise)

-          Second for Amount, this is to be entered on corresponding to each date.  Negative values for payment and positive for receipt. Current value of investment can be used where amount is still invested.

2.       Use Formulae (=XIRR(B2:B14,A2:A14) where First range i.e. B2:B14 is for amount and second rage i.e. A2:A14 is for Dates.

3.       Refer image. In case of any doubt please comment