I have been a regular investor in Stocks mostly on a SIP(Systematic investment plan) basis, I believe majority of the investors invest on a regular basis if not on SIP due to Various reasons.
When we invest in stocks, knowing the profit and loss for each individual stock alone is not enough. We have to know our annual portfolio returns so that we will know whether we have beaten inflation/Stock market Index, we are on track to our financial goals and if we have beaten the market. If we have not beaten the market, it will be better off buying the market itself by investing in exchange traded funds (ETFs).
So how do we calculate our annualized portfolio returns when we have cash flow coming in and going out at random time periods? However I have found the CAGR (Compounded annualized growth rate) formula (CAGR= [(Ending Value/Beginning Value) ^ (1/# of years)-1] not so useful to calculate when doing a SIP or investing during irregular intervals. One of the ways to do that is to use internal rate of return, XIRR.
Calculating XIRR is actually very straightforward. When I was reading up on it, it sounded complicated but to the contrary, it is very simple. In this post, I will provide a step-by-step method to calculate your returns.
- As you see in the Snapshot above, Type in the transaction dates in chronological order and the corresponding amounts. Note that all investments (cash flow in) should be entered as a positive amount and all withdrawals (cash flow out) should be entered as a negative amount.
- For dividends, if they are not reinvested, enter the dividend paid date and the amount as a negative number. If the dividends are reinvested, you do not have to account for it at all (as they are staying inside the portfolio itself).
- In the Last row enter portfolio’s current market value/ Sale Value and add in the corresponding date. The Market Value must be entered as a Negative number.
- After you have keyed in all the required information, type in an empty cell “=XIRR” (without the “”) and type in “(” to open the bracket, highlight all the transaction amounts, add a “,” highlight all the dates and then close the bracket, “)” and hit “Enter”. Eg: XIRR (B3:B13, C3:C13).
- There you go your Portfolio returns are shown.
Calculating my own portfolio returns has been very helpful as it shows the real returns instead of the absolute. My Goal is to beat the Inflation and Index and it gives me the impetus to improve myself further after knowing the returns. So far this has been the best way to calculate returns however this might be one of the options to calculate your returns, I am hoping from you to provide a feedback or suggestions if there is any.
Here is the link to “Sample XIRR” sheet which could be used as a template: Download Sample sheet
Till we meet again , remember “Compound interest is the most powerful force in the universe”~Albert Einstein 🙂 🙂
Investing Mantras 