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, 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 annualised portfolio returns when we have cash flow coming in and going out at random time periods? 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.
Things you need before calculating XIRR:
- A computer with Microsoft Excel
- Transaction dates and amounts (i.e. the date you bought/sold and the amount you bought/sold it for, including commissions)
- Portfolio market value as of the date you are calculating
- That’s all!
- Open Microsoft Excel.
- Type in the transaction dates in chronological order and the corresponding amounts. Note that all buys (cash flow in) should be entered as a positive amount and all sells (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. Ensure that the dividend paid dates are in chronological order with your transaction dates as in point 2. If the dividends are reinvested, you do not have to account for it at all (as they are staying inside the portfolio itself).
- Then, enter the portfolio’s market value as of the date you are calculating and add in the corresponding date. The market value must be entered as negative.
- Double-check and triple-check to make sure the positive and negative signs are entered correctly as a small mistake can mean that the portfolio return can vary widely.
- 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”.
- Viola! Your yearly portfolio returns are shown.
You can refer to the screenshot below for a summary on what to do:
Calculating my own portfolio returns has been very helpful as it gives me the impetus to improve myself further after knowing the figure. I also know where I’m heading and am able to plan for my retirement better. Do feel free to ask me any questions you have regarding calculating your own portfolio’s XIRR, by leaving a reply below.