Are you using the right way to calculate return on your investments?

In today's world where awareness around personal finance is spreading rapidly, almost everyone understands the importance of investing - whether it's through stocks, mutual funds, fixed deposits or any other instrument. We realize the importance of making SIPs (Systematic Investment Plans) and the need for being regular with our investments.

But how do we calculate the ROI we get from these investments, especially if you are buying and selling multiple stocks, or making SIPs with varying amounts and on varying dates?

The first instinct is to use the formula we learnt in school -

where: S.P. = Selling Price, C.P. = Cost Price

If you buy a stock today and sell it 1 year from now, this formula for calculating returns works. But what if you buy today and sell the stock 2 years from now? The above formula does not account for the time factor.

Then comes another formula we learnt in school around Compound Interest -

Using the above formula, you can move variables around to get the value of rate -

This rate is called CAGR or Compounded Annual Growth Rate. This takes into account the time factor and can help you arrive at a rate of return on investments for more than a year. But there's a problem with this formula as well. It assumes that only a single investment is made at the start and all the amount is taken out at the same time at the end of the period. However, in reality, things are a little more complicated. Sometimes, we end up with more money available for SIP and sometimes we have less. In the same way, even the dates of investment can vary. In reality, this is how one's SIP or investments table would look like:

Here, the negative values are the investments you make and the positive ones are the amounts you withdraw from your investment.

Now, to calculate the return for such a scenario, we can make use of IRR - Internal Rate of Return. IRR takes in the amount invested and withdrawn along with the dates on which the transaction occurred and provides you with an annual rate of return. Since it accounts for both amounts invested and withdrawn along with the dates, it takes into account both varying investment/withdrawal dates and amounts.

Microsoft Excel makes it easy to calculate IRR using a formula, but one thing to note is that in Excel, IRR has been renamed as XIRR (Extended Internal Rate of Return). There is no difference in the formula or the meaning. Therefore, IRR = XIRR.

To calculate XIRR in Excel, the first input is the range of values of investments with a negative sign and amounts withdrawn with a positive sign. The second input is the range of values of date of investment/withdrawal. The third parameter guess is optional and allows you to guess the XIRR value which can be ignored. Once you get the XIRR value, you can convert it to percentage using excel formatting.

Formula:

Example:

Now 13.6% in the above example is the annual compounded growth rate for your investment. This can apply to all kinds of scenarios such as SIPs done in a single mutual fund month after month, or with buying and selling of multiple stocks.

You should now have an understanding of the difference between the various ways to calculate ROI and which one is more appropriate in which situation.

Bonus Tip: Nifty returns over 5 years are usually 12.5%. That sets a good benchmark for how your investments have been performing.