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

Last Updated on May 28, 2021 at 8:01 pm

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

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 on 7th July 2107 and the XIRR calculation is done on 8th Oct 2017 (as in the above cases).

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