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

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

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 2 650x191 - How to calculate annualized return (XIRR) from a stock investment

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.

stock XIRR calculation 4 650x239 - How to calculate annualized return (XIRR) from a stock investment

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

stock XIRR calculation 5 650x250 - How to calculate annualized return (XIRR) from a stock investment

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 6 650x301 - How to calculate annualized return (XIRR) from a stock investment

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).

stock XIRR calculation 7 650x357 - How to calculate annualized return (XIRR) from a stock investment

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 wantco-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.

GameChanger review 4 650x477 - How to calculate annualized return (XIRR) from a stock investmentboth covers 650x467 - How to calculate annualized return (XIRR) from a stock investment

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.

mini Final cover - How to calculate annualized return (XIRR) from a stock investment

You can be rich too Review1 650x254 - How to calculate annualized return (XIRR) from a stock investment

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!

Hindi You can be rich too - How to calculate annualized return (XIRR) from a stock investment

Order the Hindi version via this link

Create a "from start to finish" financial plan with this free robo advisory software template


Free Apps for your Android Phone

Install 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)

About Freefincal

Freefincal has open-source, comprehensive Excel spreadsheets, tools, analysis and unbiased, conflict of interest-free commentary on different aspects of personal finance and investing. If you find the content useful, please consider supporting us by (1) sharing our articles and (2) disabling ad-blockers for our site if you are using one. We do not accept sponsored posts, links or guest posts request from content writers and agencies.

Blog Comment Policy

Your thoughts are vital to the health of this blog and are the driving force behind the analysis and calculators that you see here. We welcome criticism and differing opinions. I will do my very best to respond to all comments asap. 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.

6 thoughts on “How to calculate annualized return (XIRR) from a stock investment

  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.

Do let us know what you think about the article