3

I want to calculate IRR (internal rate of return) for stock purchases. Not sure if I am doing it right, hope some of you might confirm it.
Lets say I bought stock JNJ in 2005, 20 shares for $50 each. JNJ pays dividends quarterly. I am trying to differentiate two cases:

  1. I get dividends in cash
  2. I reinvest dividends and buy some extra JNJ (DRIP)

I am using XIRR calculation - dividends might be paid irregularly or I might get cash and reinvest dividends on random occasion. So I pass these data to calculate it:

  1. (date of purchase, -20x$50)
  2. (date of dividend, dividend amount * stock price) #if I take cash
  3. (date of dividend, dividend amount * stock price) #if I take cash
  4. ...
  5. (today, today's value * shares)

In case I reinvest dividends, I will include it in next rows in number of shares and also any future cash withdrawal will be higher (since I own more shares = more dividends).
Question #1: Is this calculation ok?
Question #2: If I buy 20 shares every year, how do I get proper IRR? I can not just include it to existing calculation, because XIRR calculates difference between date of purchase and dividend date (I would have multiple purchase dates). Or may I? Would I calculate average with respect to today's value of single purchase?
Edit1:
Thanks for answer @Chris, but I don't think you understood my question, so I am posting examples:
Question #1: I bought 20 shares of JNJ on 2014-01-01 for $89. Here is table of dividends and stock price on the dividend date: jnj latest dividend history
If I cash out all dividends, will my XIRR function get these data: xirr simple table
Column C shows what is actually calculated in column B. Number of shares times dividend amount. This is how I calculate XIRR if I get dividends paid out in cash, correct (XIRR = 13%)?
If I do not cash out, but reinvest these dividends, XIRR table looks like this:
xirr drip
Since I did reinvest my dividends, I do not have 20 shares on 2015-03-08 , but 20.707 - adds up to 2070.7 portfolio value and XIRR is 13.7%. Correct?
Question #2:
I bought 20 shares of JNJ on 2014-01-01 for $89 and 10 shares of JNJ on 2014-06-01 for $89 as well. This is my table:
miltiple xirr
Is this correct? In my point of view, XIRR calculates difference between initial date of purchase and date of each transaction(=dividend date). So for every next stock purchase, it will not calculate it's date to t(t in your formula), but date from first purchase as t.
I can not describe it properly. Basicly, can I just add all next purchases like I did in Question #2 or do I need to calculate XIRR for seperate stock purchases and then combine them somehow.

  • You shouldn't be treating dividend reinvestment as an external cash flow. If it is immediately reinvested it is already counted in the total return. – Chris Degnen Mar 8 '15 at 15:42
1

Re. question 2

If I buy 20 shares every year, how do I get proper IRR? ... (I would have multiple purchase dates)

Use the money-weighted return calculation:

http://en.wikipedia.org/wiki/Rate_of_return#Internal_rate_of_return

enter image description here

where t is the fraction of the time period and Ct is the cash flow at that time period.

For the treatment of dividends, if they are reinvested then there should not be an external cash flow for the dividend. They are included in the final value and the return is termed "total return". If the dividends are taken in cash, the return based on the final value is "net return".

The money-weighted return for question 2, with reinvested dividends, can be found by solving for r, the rate for the whole 431 day period, in the NPV summation.

From 2014-01-01 to 2015-03-08 is 431 days.
From 2014-01-01 to 2014-06-01 is 151 days.

NPV = 1780 + 890/(1 + r)^(151/431) - 3000/(1 + r) = 0

∴ r = 0.140739

Now annualising

(1 + r)^(365/431) - 1 = 0.117967 = 11.8 % total return per annum

And in Excel

enter image description here

  • thanks for answer, I added examples because I don't think this answers my question. – Lucas03 Mar 8 '15 at 13:33
  • I have edited my answer to address points on dividend reinvestment. – Chris Degnen Mar 8 '15 at 17:12
  • ok, thanks. For some reason I have mixed up deposits. e.g. I thought that in 2. deposit t should be 451-151/451 and t for withdrawal would be 151/451. But it is always 151/451, no matter if its deposit/withdrawal, right? – Lucas03 Mar 8 '15 at 17:55
  • Yes, that's right. – Chris Degnen Mar 8 '15 at 18:55
0

I use the following method.

For each stock I hold long term, I have an individual table which records dates, purchases, sales, returns of cash, dividends, and way at the bottom, current value of the holding.

Since I am not taking the income, and reinvesting across the portfolio, and XIRR won't take that into account, I build an additional column where I 'gross up' the future value up to today() of that dividend by the portfolio average yield at the date the dividend is received. The grossing up formula is divi*(1+portfolio average return%)^((today-dividend date-suitable delay to reinvest)/365.25)

This is equivalent to a complex XMIRR computation but much simpler, and produces very accurate views of return.

The 'weighted combined' XIRR calculated across all holdings then agrees very nearly with the overall portfolio XIRR.

I have done this for very along time.

TR1933

Yes, 1933 is my year of birth and still re investing divis!

  • Thanks for sharing :) What do you plan on doing with all that stocks and future dividends? Have a big 90th birthday party? :D – Lucas03 Mar 9 '15 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.