| Date | Cash Flows |
| 1-Jan-08 | $(1,000.00) |
| 31-Mar-08 | $25.00 |
| 30-Jun-08 | $25.00 |
| 30-Sep-08 | $25.00 |
| 31-Dec-08 | $25.00 |
| 31-Mar-09 | $25.00 |
| 30-Jun-09 | $25.00 |
| 30-Sep-09 | $25.00 |
| 31-Dec-09 | $25.00 |
| 31-Dec-09 | $1,000.00 |
| Return (per annum) | 10.38% |
In this example I am assuming that I buy a dividend paying stock worth $1000 on 1 Jan 2008 and I sell the same stock worth $1000 at the end of 2009. I assume also that this stock is priced at a constant of $1 and the stock pays out quarterly dividends of $0.025 per share. If you put those cash flows in Excel and use the XIRR function, the annualised return works out to be 10.38% per annum. Now my question is does this 10.38% per annum return include dividends reinvested or without dividends reinvested into account?
If you think the 10.38% per annum is the return without dividends reinvested, I am sorry to say that you got it wrong just like me in the beginning. The 10.38% per annum as computed by Excel XIRR gives a return assuming that you reinvest the dividends at the same rate of return. This is an important point that I wish to point out in this article. It is difficult to explain mathematically but let me present the same example in a different manner.
| Date | Stock Price | Dividends | New Shares from Dividends Reinvestment | Total Shares | Stock Value |
| 1-Jan-08 | $1.00 | $- | 0.00 | 1000.00 | $1,000.00 |
| 31-Mar-08 | $1.00 | $0.025 | 25.00 | 1025.00 | $1,025.00 |
| 30-Jun-08 | $1.00 | $0.025 | 25.63 | 1050.63 | $1,050.63 |
| 30-Sep-08 | $1.00 | $0.025 | 26.27 | 1076.89 | $1,076.89 |
| 31-Dec-08 | $1.00 | $0.025 | 26.92 | 1103.81 | $1,103.81 |
| 31-Mar-09 | $1.00 | $0.025 | 27.60 | 1131.41 | $1,131.41 |
| 30-Jun-09 | $1.00 | $0.025 | 28.29 | 1159.69 | $1,159.69 |
| 30-Sep-09 | $1.00 | $0.025 | 28.99 | 1188.69 | $1,188.69 |
| 31-Dec-09 | $1.00 | $0.025 | 29.72 | 1218.40 | $1,218.40 |
| Date | Cash Flows |
| 1-Jan-08 | $(1,000.00) |
| 31-Dec-09 | $1,218.40 |
| Return (per annum) | 10.38% |
All the numbers are exactly the same here except that new shares are included to give an easier illustration of a dividend reinvestment scheme. Since all dividends are reinvested, there are only two cash flows remaining which are the starting and ending values. The return works out to be 10.38% per annum for this case too!
At this point, you know that there are two methods of calculating returns with dividends reinvested. How about the return of a stock if you choose to hold or spend the dividends instead? It is important to know how to calculate since this scenario can be a more likely case for everyone too. Under this scenario, you simply add all dividends collected to your final stock value (ie $1000 + 8 dividend payouts).
| Date | Cash Flows |
| 1-Jan-08 | $(1,000.00) |
| 31-Dec-09 | $1,200.00 |
| Return (per annum) | 9.54% |
A few points that you need to take note. The last example shows the effect of return if you choose to spend or hold the dividends instead. It also means that you reinvest the dividends at a rate of return being equal to 0% per annum and rate of inflation is assumed to be 0% per annum. Of course in reality inflation rate is not 0% so you are better off spending it now than spending it later or holding the dividends in your hands. And lastly for this example, the overall return falls to 9.54% per annum if you choose not to reinvest the dividends. It is lower due to a lesser compounding effect.
Now I hope you have a better understanding in calculating the annualised returns of your stocks investments after taking into account the effect of dividend payouts. And your decision on the dividends received can affect your overall returns.
6 comments:
Hi, Mike,
wondering whether you have used credit card to purchase share b4, thanx :)
The XIRR function measures the returns from the (or a number of) capital inputs into an investment or project. It does not make any assumption on what happens after the cashflow have been provided by the investment. So your thesis about the XIRR is incorrect.
Lets take a scenario: If you invest a certain amount of money into a stock, and the stock generated a series of dividends over the period when you are holding it, and including the final sale of the stock you have realised x% IRR.
Then lets say you decide to go out and spend all the dividends on hookers in Geylang, it still does not change the fact that the original capital spent to buy the stock in the first investment case, have yielded x% IRR. The returns on the dividends, are a separate matter, or rather a separate investment case.
Another way to look at this would be a ring-fenced investment account, in which you input capital and take nothing out. The growth (or loss), would be purely traced back to the original capital input. Even the case of you reinvesting the dividend properly, the XIRR will benchmark all the returns to capital inputed into the account.
Thanks for your comments. It will be more helpful if you can give some example with numbers :)
So how do you calculate your returns for a particluar stock investment under the scenario that I posted?
From what I understand, the returns will depend on what I choose to do with the dividends. If I choose to reinvest at the same rate of return, I will definitely get a higher return than the return if I choose to hold the dividends in hands (reinvested at 0% rate of return).
XIRR takes into account the Deposit/Withdrawal cashflows from an investment. In my understand, dividends can also be considered as a withdrawal of an investment.
What I am trying to show is that you are able to calculate your returns with dividends reinvested in 2 methods. Both methods will yield the same returns.
1st method is the straight forward way where I consider 1 Deposit and 1 Withdrawal (see 3rd table). This one and only withdrawal will take into account the enlarged final value due to dividends.
2nd method is a more longer method where I consider 1 Deposit and multiple Withdrawals. The in-between Withdrawals are the dividends and these dividends must be reinvested at the same rate of return to get the same result as 1st method.
"It does not make any assumption on what happens after the cashflow have been provided by the investment."
After looking back at my finance theory, let me prove to you that your understanding is wrong. The rate of return of your Withdrawals (Dividends) is important.
You can google on the concept of annuity and "assumption irr"
Let me give you an example of a 5 year annuity of $1000 at 12% pa. You will have 5 yearly and end withdrawals of $277.41. You can use the compounding formula to calculate the yearly payments. "The time value of money formula assumes that each payment will be invested at the same rate of interest" -> This concept is very important!
So if you were to list these cashflows in excel and use the XIRR, you will get back 12%!
Year 0, -$1000
Year 1, $277.41
Year 2, $277.41
Year 3, $277.41
Year 4, $277.41
Year 5, $277.41
You can apply the same concept to stocks.
Looking back I think you are right. The IRR basis annualises any gain by taking into account the time period needed to realise it. So if you made money over 3 months, the IRR is an annualised number meaning that for it to be correct you have to repeat that another 3 times for the IRR to mean something. Thanks for pointing it out. Cheers.
By the way the Excel and XIRR works well for a single stock or a portfolio with minimal withdrawals/contributions. If you have many withdrawals/contributions then the XIRR function is unable to find a solution.
By the way, what i mentioned so far is finding a Dollar Weighted Returns (DWR). I am intending to improvise my stock return calculations into using a Time Weighted Returns (TWR). I think CIF5000 is using this method.
Post a Comment