(28-06-2012, 05:06 PM)CityFarmer Wrote: [ -> ] (28-06-2012, 04:36 PM)freedom Wrote: [ -> ] (28-06-2012, 04:33 PM)CityFarmer Wrote: [ -> ] (28-06-2012, 04:15 PM)smallcaps Wrote: [ -> ] (28-06-2012, 04:02 PM)CityFarmer Wrote: [ -> ]Understood on re-investment part.
Does it mean XIRR does not cater for the timing of cash inflow into your cash holding? In other words, it make no diff either the cash holding is idle for 1 day or 365 days.
Actually it does cater for it in the final cash holdings. If the portfolio received the cash earlier then the cash would have earned a higher absolute return (assuming put in the bank) and the final cash holdings would be higher.
But the XIRR will not able to accurately reflect the effect, since it does not have an entry of it.
I am trying hard to reach a point here i.e. we should include dividend as outflow, even it is re-invested into your cash holding. With the market value of the portfolio include cash holding at 1st entry and last entry, the XIRR result is more accurate. 
if dividends are considered outflow, have you really remove the dividends from your subsequent cash holding?
if yes, by all means, you should record it as outflow.
if no, you did double counting on dividends.
OK, i tried and it double count.
If the dividends are not recorded as an entry, and lump all into a single entry at the end of the list, does the result accurate?
So should i suggest that cash holding should not be include into XIRR function, just not to confuse it?
the first question to ask should be whether cash is considered part of your portfolio. if not, you should always record dividend as outflow. of course cash balance will be always 0.
what we are talking about is to calculate XIRR, not about how to track all transactions happened.
The confusion of the last several posts could be the same one as that which confounded freshmen of accountancy faculty when they first learn about balance sheet and cash flow statement. One is a snap shot picture while the other is tracking the flow. Confusion arises when items of the two are mixed together.
I believe XIRR is tracking the cash flow, so we should just focus on cash flow items. In the series of example cash flow :
-10,000 1/1/2001 bought //initial cash outflow
2,000 1/1/2002 dividends received //dividend is cash inflow
-2,000 1/1/2002 dividends injected back into portfolio //see my comment below
10,000 1/1/2003 market value //this is the residual value which is a cash inflow
2,000 1/1/2003 cash in portfolio //not a cashflow item
Only the 1st, 2nd and 4th row is to be plug into the XIRR formula.
For the 3rd row, if one need to devise a way to make dividend as a outflow, perhaps it is an indication that there is a hole in the theory.
(28-06-2012, 07:23 PM)wsreader Wrote: [ -> ]The confusion of the last several posts could be the same one as that which confounded freshmen of accountancy faculty when they first learn about balance sheet and cash flow statement. One is a snap shot picture while the other is tracking the flow. Confusion arises when items of the two are mixed together.
I believe XIRR is tracking the cash flow, so we should just focus on cash flow items. In the series of example cash flow :
-10,000 1/1/2001 bought //initial cash outflow
2,000 1/1/2002 dividends received //dividend is cash inflow
-2,000 1/1/2002 dividends injected back into portfolio //see my comment below
10,000 1/1/2003 market value //this is the residual value which is a cash inflow
2,000 1/1/2003 cash in portfolio //not a cashflow item
Only the 1st, 2nd and 4th row is to be plug into the XIRR formula.
For the 3rd row, if one need to devise a way to make dividend as a outflow, perhaps it is an indication that there is a hole in the theory.
if that's the case, clearly, XIRR is not used to measure the performance of a portfolio. Instead, it is used to measure the performance of transactions only.
(28-06-2012, 07:23 PM)wsreader Wrote: [ -> ]Only the 1st, 2nd and 4th row is to be plug into the XIRR formula.
Only if the dividends are taken out from bank account (for whatever purpose) and not left in bank account (possibly earning some interest), in which case row 5 will show as value of 2000 (+ possible interest). "small caps" alluded to this in an earlier post.
Quote:For the 3rd row, if one need to devise a way to make dividend as a outflow, perhaps it is an indication that there is a hole in the theory.
It is all just a matter of convention. Dividends can be treated as inflow or outflow, so long as the rest of cash flows are consistently treated. And the same XIRR will be derived. (It is possible that the GAAP accords dividends as inflows but this academic matter or principle is not material to real life XIRR practitioners in this forum.)
Just realized what was wrong by feeding all transactions to XIRR and getting too high a rate:
This is the correct XIRR:
-100 1/1/2001 inject 100 into portfolio (buy A)
-100 1/1/2002 inject 100 into portfolio (buy B)
-100 1/1/2003 inject 100 into portfolio (buy C)
400 1/1/2004 market value (A+C as B was sold)
XIRR = 15.09%
And this is the wrong XIRR that we get by blindly considering all transactions:
-100 1/1/2001 buy A
-100 1/1/2002 buy B
200 1/1/2003 sell B (earn 100)
-200 1/1/2003 buy C
400 1/1/2004 market value (A+C)
XIRR = 31.46%
(28-06-2012, 11:51 PM)smallcaps Wrote: [ -> ]This is the correct XIRR:
-100 1/1/2001 inject 100 into portfolio (buy A)
-100 1/1/2002 inject 100 into portfolio (buy B)
-100 1/1/2003 inject 100 into portfolio (buy C)
400 1/1/2004 market value (A+C as B was sold)
XIRR = 15.09%
The cash flows are incomplete - sales proceed for B is missing.
Quote:And this is the wrong XIRR that we get by blindly considering all transactions:
-100 1/1/2001 buy A
-100 1/1/2002 buy B
200 1/1/2003 sell B (earn 100)
-200 1/1/2003 buy C
400 1/1/2004 market value (A+C)
XIRR = 31.46%
Cash flows are complete and this is a possible scenario. Perhaps, you've underestimated yourself and this high XIRR is actually correct.....
(29-06-2012, 12:32 AM)swakoo Wrote: [ -> ] (28-06-2012, 11:51 PM)smallcaps Wrote: [ -> ]This is the correct XIRR:
-100 1/1/2001 inject 100 into portfolio (buy A)
-100 1/1/2002 inject 100 into portfolio (buy B)
-100 1/1/2003 inject 100 into portfolio (buy C)
400 1/1/2004 market value (A+C as B was sold)
XIRR = 15.09%
The cash flows are incomplete - sales proceed for B is missing.
Quote:And this is the wrong XIRR that we get by blindly considering all transactions:
-100 1/1/2001 buy A
-100 1/1/2002 buy B
200 1/1/2003 sell B (earn 100)
-200 1/1/2003 buy C
400 1/1/2004 market value (A+C)
XIRR = 31.46%
Cash flows are complete and this is a possible scenario. Perhaps, you've underestimated yourself and this high XIRR is actually correct.....
I think the correct XIRR should be close to 15% since 300 was the total capital and on average the capital was there for 2 years. Thus the annualized return should be around squareroot(400/300) which gives 15.5%
Just refactored my XIRR calculations to consider only capital injected and final value and as expected the rate is reduced from 26 to 23.8% over the same 7+ years.
I like to measure how well is my investment in stock rather than how well in managing net worth.
Record dividend as outflow from my investment similar to selling stocks. (Both positives)
Invest in stock (Negative)
Sell stock (Positive)
Dividend (Positive)
Stock Value to-date (Positive) <= Balance in trading account at current trading price
Using above 4 typical transactions, i calculate my XIRR.
One scenario I did is do annual compare
Close "Book" annually and use last trading day of each year of measure (eg.31 Dec)
Investment of unsold stock is classified as new investment on first trading day of the year at that day value not price i bought.
(Mark to market)
Another scenario is multi-year XIRR compare
Trading is continuous throughout. Last day is always "today".
First day of trading for each stock is always day i bought the shares at cost value.
Cory
(29-06-2012, 01:34 AM)smallcaps Wrote: [ -> ] (29-06-2012, 12:32 AM)swakoo Wrote: [ -> ] (28-06-2012, 11:51 PM)smallcaps Wrote: [ -> ]This is the correct XIRR:
-100 1/1/2001 inject 100 into portfolio (buy A)
-100 1/1/2002 inject 100 into portfolio (buy B)
-100 1/1/2003 inject 100 into portfolio (buy C)
400 1/1/2004 market value (A+C as B was sold)
XIRR = 15.09%
The cash flows are incomplete - sales proceed for B is missing.
Quote:And this is the wrong XIRR that we get by blindly considering all transactions:
-100 1/1/2001 buy A
-100 1/1/2002 buy B
200 1/1/2003 sell B (earn 100)
-200 1/1/2003 buy C
400 1/1/2004 market value (A+C)
XIRR = 31.46%
Cash flows are complete and this is a possible scenario. Perhaps, you've underestimated yourself and this high XIRR is actually correct.....
I think the correct XIRR should be close to 15% since 300 was the total capital and on average the capital was there for 2 years. Thus the annualized return should be around squareroot(400/300) which gives 15.5%
Just refactored my XIRR calculations to consider only capital injected and final value and as expected the rate is reduced from 26 to 23.8% over the same 7+ years.
It looks like XIRR is more accurate if we can properly identify cash flows in/out of a portfolio (like MIL type portfolio). For your eg., if we were to put in all buy/sell transactions (like what I was planning to do for mine as I can't identify my real in/out flow of funds), we may happily get an XIRR that's a lot better than it should be. Like your example, the Sell B @ 200 (100 profit) is nett off against Buy C @ 200 (new fund 100 + Profit 100 fm B) and when XIRR calculates, it just dumbly see 400/200 and gives a much higher XIRR (although we know that it should be 400/300!
I think I'll just have to stick to my non-scientifc method for my own portfolio...
I believe the NAV method is still a better method to calculate return of a portfolio, especially when there is always cash balance.