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) and would like to use this post for its beta release.
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
- you buy 200 stocks of a company at Rs. 1000 a share on 1st Jan 2010
- You sell 25 of those stocks at Rs. 6000 a share on 7th July 2017 and
- 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 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: 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.
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 beta version of the stock XIRR calculator. Use it ONLY if you wish to help me test it.
Use this form to ask Questions or reg. the robo template ONLY (For comments/opinions, use the form at the bottom)
And I will respond to them in the next few days. I welcome tough questions. Please do not ask for investment advice. Before asking, please search the site if the issue has already been discussed. Thank you. PLEASE DO NOT POST COMMENTS WITH THIS FORM it is for questions only.
GameChanger– Forget Startups, Join Corporate & Live The Rich Life You Want
My second book, Gamechanger: Forget Start-ups, Join Corporate and Still Live the Rich Life you want, co-authored with Pranav Surya is now available at Amazon as paperback (₹ 199) and Kindle (free in unlimited or ₹ 99 – you could read with their free app on PC/tablet/mobile, no Kindle necessary).
It is a book that tells you how to travel anywhere on a budget (eg. to Europe at 50% lower costs) and specific investment advice for young earners.
The ultimate guide to travel by Pranav Surya is a deep dive analysis into vacation planning, finding cheap flights, budget accommodation, what to do when travelling, how travelling slowly is better financially and psychologically with links to the web pages and hand-holding at every step. Get the pdf for ₹199 (you will be emailed the pdf after payment)
You Can Be Rich Too with Goal-Based Investing
My first book with PV Subramanyam helps you ask the risk questions about money, seek simple solutions and find your own personalised answers with nine online calculator modules.
The book is available at:
Amazon Hardcover Rs. 271. 32% OFF
Infibeam Now just Rs. 270 32% OFF. If you use a mobikwik wallet, and purchase via infibeam, you can get up to 100% cashback!!
Flipkart Rs. 279. 30% off
Kindle at Amazon.in (Rs.271) Read with free app
Google PlayRs. 271 Read on your PC/Tablet/Mobile
Now in Hindi!
Order the Hindi version via this link
Free Apps for your Android PhoneInstall Financial Freedom App! (Google Play Store)
Install Freefincal Retirement Planner App! (Google Play Store)
Find out if you have enough to say "FU" to your employer (Google Play Store)