How to calculate annualized return (XIRR) from a stock investment

Published: October 10, 2017 at 3:10 pm

Last Updated on

In this post, I discuss how to calculate the annualized return from a stock investment after accounting for corporate actions like dividends, stock splits, bonuses, buybacks and rights issues. I have made a calculator that will compute the annualized return (CAGR for single investments, IRR for periodic investments and XIRR or investments on random dates)

It also gives me a chance to discuss the methods used for the calculation with readers. If you do not know what is XIRR and how it is calculated, I would suggest that you start here: CAGR vs. IRR: Understanding investment growth measures and then go here: What is XIRR?

I am indebted to Captain Navneet Bindra for discussions on the calculations. Thanks are also due to Vignesh Bhaskar, Ashwin Sheoroy for testing the sheet.

Let us discuss the calculation via examples.

Stock XIRR Calculation: buying and selling

Suppose,

  1. you buy 200 stocks of a company at Rs. 1000 a share on 1st Jan 2010
  2. You sell 25 of those stocks at Rs. 6000 a share on 7th July 2017 and
  3. calculate the XIRR as on 8th October 2017 (current market price is Rs. 6100) what is the annualized return?

To do this in Excel

Stock XIRR annualized return calculation

Stock XIRR Calculation: bonus shares

Now suppose the company announces a 1:1 bonus on 2nd Feb 2011.

Since you hold 200 shares, you will be given 200 more shares and the price will fall by half to Rs. 500. This is an internal adjustment will not be used as a cash flow entry for XIRR calculation. The price from this point on will reflect this bonus.

The redemption and valuation entries will be shown as above for all illustrations below.

Stock XIRR Calculation: Stock split

Now on 3rd March a 3:2 split is announced. That is, since you hold 400 shares,  half that number or 200 shares will be given to you. This is because

400 x (3/2) =  400 x (1.5) = 400 + 400 * 0.5 = 400  + 200.

So now, you will hold 600 shares. The price will drop by a factor of 1/.5. For example, if the price before the split is Rs. 500, it will drop to 500/1.5 = 333.33.

This will again not be reflected in the cash flow.

Stock XIRR Calculation

Stock XIRR Calculation: Dividends

Now a dividend of Rs. 2 per share is announced on 4th April 2013. Regardless of what you do with the dividend, in order to find the XIRR of the instrument, it will be assumed to be reinvested at the ex-dividend market price.

So the dividend amount of 2 x 600 = 1200 is used to buy imaginary stocks for the XIRR calculation at the ex-div market price of Rs. 200.

So 1200/500 gives 2.4 stocks. Making the total stocks held as 602.4.

Want to know how to reduce fear, doubt and uncertainty while investing for financial goals? Sign up for our lectures on goal-based portfolio management and join our exclusive Facebook Community

Now a second dividend is announced on 9th Sep 2013 for Rs. 5 per share.  The actual dividend received is 5 x 600 = 3000. However, due to the first dividend, there are 602.4 stocks assumed to be held for the XIRR calculation. Therefore the dividend that will be assumed to be reinvested is:

5 x 602.4 = 3012. This amount will be used to buy 5.48 imaginary stocks at the ex-dividend price of Rs. 550. Taking the XIRR share tally to 607.88

Many investors are confused by this and say that this wrong. It is, however, important to recognise that a dividend is an action by the instrument and not by us.  Reinvesting stock or mutual fund dividends at ex-price is the standard procedure adopted to calculate instrument XIRR. Investor XIRR can be different. Will discuss that in the post when the final calculator is released.

Stock XIRR Calculation: Rights Issue

On 5th May 2014, the company announces a rights issue. This allows existing investors to buy some shares at a discount.

Suppose you buy 10 stocks at Rs. 950 a share against the then market price of Rs. 1000, the no of actual stocks = 610. The no of XIRR stocks (imaginary) = 617.88

Since we spend money to participate in the rights issue, the 10 x 950 = 9500 will be shown in the cash flow.

Stock XIRR Calculation: Buyback

Now a buyback is announced on 6th June 2015. The price is Rs. 1600 a premium over the then market price of Rs. 1500. Suppose, you decide to sell 200 shares to the company.

The total no of actual stocks held will become 410.

A buyback is similar to a dividend (I am referring to accounting aspects only. There are other differences). In a dividend, the no of stocks remains the same, the price falls to the extent of the dividend.

In a buyback, the no of shares decreases, the market price is not directly affected by the buyback and like a dividend, the investor gets money in hand. Therefore we assume that the money received will be reinvested back at the then market price.

The buyback amount received = 200 x 1600 = 320000. This is reinvested at the market price of Rs. 1500 to buy 213.33 imaginary shares, taking the XIRR tally to 831.21 shares. 

Finally, there is a redemption of 25 shares 7th July 2107 and the XIRR calculation is done as on 8th Oct 2017 (as in the above cases).

The XIRR or the annualized return of the stock instrument can be seen in each of the above figures.

This is the stock XIRR calculator.

Do share if you found this useful
Hate ads but would like to support the site? Subscribe to our ad-free newsletter and get beautifully formatted full articles delivered to your inbox!

About the Author

Pattabiraman editor freefincalM. Pattabiraman(PhD) is the founder, managing editor and primary author of freefincal. He is an associate professor at the Indian Institute of Technology, Madras. since Aug 2006. Connect with him via Twitter or Linkedin Pattabiraman has co-authored two print-books, You can be rich too with goal-based investing (CNBC TV18) and Gamechanger and seven other free e-books on various topics of money management. He is a patron and co-founder of “Fee-only India” an organisation to promote unbiased, commission-free investment advice.
He conducts free money management sessions for corporates and associations on the basis of money management. Previous engagements include World Bank, RBI, BHEL, Asian Paints, Cognizant, Madras Atomic Power Station, Honeywell, Tamil Nadu Investors Association. For speaking engagements write to pattu [at] freefincal [dot] com

About freefincal & its content policy

Freefincal is a News Media Organization dedicated to providing original analysis, reports, reviews and insights on developments in mutual funds, stocks, investing, retirement and personal finance. We do so without conflict of interest and bias. We operate in a non-profit manner. All revenue is used only for expenses and for the future growth of the site. Follow us on Google News
Freefincal serves more than one million readers a year (2.5 million page views) with articles based only on factual information and detailed analysis by its authors. All statements made will be verified from credible and knowledgeable sources before publication. Freefincal does not publish any kind of paid articles, promotions or PR, satire or opinions without data. All opinions presented will only be inferences backed by verifiable, reproducible evidence/data. Contact information: letters {at} freefincal {dot} com (sponsored posts or paid collaborations will not be entertained)

Connect with us on social media

Our Publications


You Can Be Rich Too with Goal-Based Investing

You can be rich too with goal based investingThis book is meant to help you ask the right questions, seek the right answers and since it comes with nine online calculators, you can also create custom solutions for your lifestyle! Get it now. It is also available in Kindle format.
   

Gamechanger: Forget Startups, Join Corporate & Still Live the Rich Life You Want

Gamechanger: Forget Start-ups, Join Corporate and Still Live the Rich Life you wantThis book is meant for young earners to get their basics right from day one! It will also help you travel to exotic places at low cost! Get it or gift it to a young earner

Your Ultimate Guide to Travel

Travel-Training-Kit-Cover-new

This is a deep dive analysis into vacation planning, finding cheap flights, budget accommodation, what to do when traveling, how traveling slowly is better financially and psychologically with links to the web pages and hand-holding at every step. Get the pdf for Rs 199 (instant download)  

Free Apps for your Android Phone

Comment Policy

Your thoughts are the driving force behind our work. We welcome criticism and differing opinions.Please do not include hyperlinks or email ids in the comment body. Such comments will be moderated and I reserve the right to delete the entire comment or remove the links before approving them.

8 Comments

  1. valueresearchonline portfolio also does a good job of giving you annualized returns. It takes into account buy/sell, dividends and splits, etc. Unsure of buybacks though. I will be curious to know if there are any discrepancies and if so why.

    1. It assumes (at least for the case of mfs) dividends are assumed to be cashflows in the hands of the investor. You can check this yourself if the ex-price on dividend dates are not used for calculation. Then it is wrong.

  2. You are right in suggesting that there is a difference in calculating returns of a stock vs returns of an investor. But if you want to calculate returns from a stock, a redemption made by an investor should not be included in the cash flows.

    1. ha ha! The idea is to differentiate an action by the investor (buy = -ve entry, sell = +ve entry) and an action by the instrument. With the exception of a rights issue, all other corporate actions are instrument driven with only the buyback having an option to participate or not.

  3. Sir excellent article.Very easy to understand and very useful.Pl think of publishing all such articles in a book format.

  4. Hello i need to calculate the annualized return for a chit fund scheme. I have a dataset and i need to calculate the annualized return for the first bidder, last bidder and all the bidder.
    Could you please help me in doing so? I am not from finance background so i need some help. Kindly help.

Leave a Reply

Your email address will not be published. Required fields are marked *