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!
Method:
- 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.
SudhanP.com 
Hi FFN,
calculation of XIRR must be based on historical transactions? for example, can i just calculate for year 2012? If a bought a stock say in 2010, but have been holding in till now, so there is no cash in or out in 2012, but the portfolio value should reflect the market value of that stock right?? or leave that out???
SInce the ending portfolio will changes everyday due to the volaility of market, do we need to look at average over a number of years??
Or it must be done with all tranactions logged to be accurate??
Hi sillyinvestor,
XIRR can be used for single stock performance but it is best to measure your portfolio performance.of stocks and invertible cash.
We are looking for rising trend or stability in our XIRR performance over market cycles and learn to improve and adjust our investing strategies to meet our long term investing goals.
Hi sillyinvestor,
Yes historical transaction. If you bought in 2010 and didn’t buy in between and want to calculate your returns as of today, it’s very simple as you don’t have cash flow in (i.e. you didn’t buy more). In that case, you don’t have to use XIRR. You can just use a compound interest calculator at http://www.1728.org/compint.htm or use a physical financial calculator under the “Compound” mode. Excel can do it too but I don’t know the formula for it. You can Google it.
You don’t have to look at average over a number of years as you want to know your yearly returns as of the current date.
Don’t understand your last question. Mind elaborating?
Cheers!
actually, i was trying to key in the data when i realised .. hey, i start trading shares some 10 years ago,and there was a period of time, i am trading more than investing, so its quite a tedious task to key in all my trades, that is why i ask if we could just key in the trades by year, but i realised there is a end portiflio figure.. thus my previous questions
Upon a time, I was also crazy over day trading and thinking of trading for a living.
XIRR is a good performance measurement tool for long term investors.
XIRR is too tedious for trading records. Just too many trades.
I used to measure trading performance in terms of
1) No of trades in ROC Banding,
2) Total Number of Wins / Total Number of Losses, and
3) Total Win $ / Total Loss $, etc
Hi FFN,
I am quite new in investing stock. Can i ask for some of your advises:
From earlier of March (this month), people saying there might be some correction in the market. True enough, most of the stock has gone down after a bull run for about 2 months.
I have a stock which is asset play (currently traded about 28% of its Nta), PE of about 5.8x, the main business is improving (based on their latest HY2012 in Nov 2012), there is some good prospect which the company is working into.
When i first bought the stock, it was traded at about 9.6 cents. It has reached its peak at 18.3cents last month. I did not sell my shares as i thought the stock is still undervalued. And i was actually hoping to see the FY2012 report which is to be released in May 2013.
But since March 2013, the price has been dropped tremendously to lowest 11.8cents, and currently is traded at about 12.5cents.
Do you think what i am doing to right? Or would not better if i have just sold my shares when it reached about its peak (it was traded at about 16 – 17cents for quite some weeks last month) and had bought it back now?
Is this the way an investor should do? Or can you give me some of your advise on this?
Many thanks.
Hi Newbie,
I don’t pay attention to market predictions. It’s hard to predict the market and no one can predict the markets accurately all the time. Even professional analysts who look at the stock market 24/7 have failed many a times.
If I were you, I would keep the business if it was still undervalued. It’s hard to predict the top and sell exactly at the top. If the price drops, I would buy more if the fundamentals are still intact as when I first bought it. You have to re-evaluate the business from this point on.
If the business is overvalued and the market is overheated, you could sell in tranches if you have a substantial number of lots. For example, if you have 10 lots, you can sell 5 lots this week and 5 two weeks later, or something like that. If you don’t, you can sell all at one shot. The price may go up after selling and no one can stop it from doing so (unless you manipulate the markets) and in that instance, you shouldn’t kick yourself for selling early. I have made such mistakes of selling early too. In one instance, I sold and the price shot up to around 2x a year later!
Hope this helps! Do feel free to ask any other questions you may have and I will be glad to answer those.
hi FFN,
Thanks a lot for your answers. I really appreciate that.
I will ask you if i have such questions in future!. Cheers.
My pleasure =)
How does XIRR change if there are bonus and split transactions in a portfolio, I am calculating XIRR for my stock portfolio, any help would be much appreciated.
Hi aashesh,
I believe when you put the ending amount of your portfolio, the bonus and stock splits will take care of themselves. There’s no cash flow in or out per se. Only if there is cash flow, you need to account for it. Otherwise, you don’t have to.