What is a realistic return on value investing?

[KopiKat]

Just put all the transactions (eg. purchase values of stocks, cash injections, sale values of stocks, dividends cashed out, dividends re-invested, etc.) in one column and the associated dates in another column. Then point the XIRR to these 2 column ranges and bingo - CAGR!

Hey! That sounds great!
I suppose they have to be in time chronological order?
My current ss has all the stocks buy/sell transaction dates and figures + div dates and figures. So, it'd be a not so difficult task to just cut and paste them into another worksheet in time order (can also use the 'sort' function) to calculate my XIRR since it's a simple matter of just pointing to the 2-columns! But, still have to find some time to do that (plus study XIRR proper usage), tho'.

I went to try out XIRR on my Mother-in-law's portfolio (easier as very few transactions) and was happily surprised that I got XIRR = 34%! I thought I may need to annualise it and got a lowly figure = 6.91%. Something doesn't seem right! One is too high and the other is too low. Using my own non-certified method, I was having an annualised return = 12.55%.

I searched the internet and found a useful explanation on the use of XIRR (it's already an annualised figure) and after applying, I got an XIRR = 12.03%, which looks more right as it's closer to my 12.55%. (Still doesn't look too right as my more conservative method ought to have given a lower figure - will ponder over it).

I extract the key point from this site,

Right. She should ignore all the activity which was internal to her portfolio, including dividends.
Any dividends would influence that final portfolio value, of course, but they shouldn't appear as XIRR cash flows.

ie. it's a lot simpler now. We don't even need to put in the stocks buy/sell transactions nor the dividends, unless they're withdrawn / added (from new cash flows) into the portfolio.

For my own portfolio, it's going to be a lot more complicated as I'm unable to differentiate my cash flows (whether new or not). It's already taking me more than 5mins...

[CityFarmer]

Would the returns using XIRR be very far off from assuming that the amount is deposited from the start of the year? For example, assuming that I buy another stock on 15 May 2011. By right, I should be using XIRR to calculate the returns for 2011 to be accurate, but let's say I am lazy and decide to assume that the amount that I use to buy the stock is done on 1 January 2011 instead. As such, I would be using "(end of year amount-start of year amount/start of year)*100%". Would the XIRR return veered far off from my previous formula?

It will be different from your previous formula.

If fund invested on mid of the year, the newly invested fund's profit/loss is double in % by end of year. It is due to the denominator is 0.5 year, instead of 1 year if you assume the fund injection is at beginning of the year.

[Some-one]

Would the returns using XIRR be very far off from assuming that the amount is deposited from the start of the year? For example, assuming that I buy another stock on 15 May 2011. By right, I should be using XIRR to calculate the returns for 2011 to be accurate, but let's say I am lazy and decide to assume that the amount that I use to buy the stock is done on 1 January 2011 instead. As such, I would be using "(end of year amount-start of year amount/start of year)*100%". Would the XIRR return veered far off from my previous formula?

It will be different from your previous formula.

If fund invested on mid of the year, the newly invested fund's profit/loss is double in % by end of year. It is due to the denominator is 0.5 year, instead of 1 year if you assume the fund injection is at beginning of the year.

It would be different but would it be far off? I am asking because I try using the XIRR to calculate my YTD return for 2012 and I am surprised that it actually throws up 30%...Hmmm...

[CityFarmer]

Would the returns using XIRR be very far off from assuming that the amount is deposited from the start of the year? For example, assuming that I buy another stock on 15 May 2011. By right, I should be using XIRR to calculate the returns for 2011 to be accurate, but let's say I am lazy and decide to assume that the amount that I use to buy the stock is done on 1 January 2011 instead. As such, I would be using "(end of year amount-start of year amount/start of year)*100%". Would the XIRR return veered far off from my previous formula?

It will be different from your previous formula.

If fund invested on mid of the year, the newly invested fund's profit/loss is double in % by end of year. It is due to the denominator is 0.5 year, instead of 1 year if you assume the fund injection is at beginning of the year.

It would be different but would it be far off? I am asking because I try using the XIRR to calculate my YTD return for 2012 and I am surprised that it actually throws up 30%...Hmmm...

It depend the profitability of the new investment

[swakoo]

I went to try out XIRR on my Mother-in-law's portfolio (easier as very few transactions) and was happily surprised that I got XIRR = 34%! I thought I may need to annualise it and got a lowly figure = 6.91%. Something doesn't seem right! One is too high and the other is too low. Using my own non-certified method, I was having an annualised return = 12.55%.

I searched the internet and found a useful explanation on the use of XIRR (it's already an annualised figure) and after applying, I got an XIRR = 12.03%, which looks more right as it's closer to my 12.55%.

I extract the key point from this site,

Right. She should ignore all the activity which was internal to her portfolio, including dividends.
Any dividends would influence that final portfolio value, of course, but they shouldn't appear as XIRR cash flows.

ie. it's a lot simpler now. We don't even need to put in the stocks buy/sell transactions nor the dividends, unless they're withdrawn / added (from new cash flows) into the portfolio.

Regarding your mother-in-law's portfolio, and without knowing the details, I get the feeling the cash dividends received should be all taken into account as outflows. With 34% as the correct CAGR. If the portfolio spans many years and dividends are received thru this period, the XIRR annualises these dividends received at time of receipt. Hence the figure is higher than if they were manually calculated as being all received today. You can tell your MIL you're doing great for her porfolio (and it's the truth)!

[One component of my portfolio spanning 4 years was giving xirr of 10% without including dividends received. After factoring in dividends as outflows, xirr = 16%. My manual calculation was 15%. This is why I feel dividends received should be included as outflows.]

For my own portfolio, it's going to be a lot more complicated as I'm unable to differentiate my cash flows (whether new or not). It's already taking me more than 5mins...

The key is correctly identifying cash flows as inflows or outflows and assigning - or + consistently. Once this is sorted out, everything falls in place. This has been my experience. Of course with all the kung fu you have been doing with your portfolio, you will have a ton of transactions to sort out. But the beauty of xirr is that once sorted out, it all automatically falls into place and voila - CAGR over any period you desire!

---

so under the XIRR system, if you made a profit of 1% in 1 day, it will give you 365% return and loss of 1% in 1 day will give you -100% return. In actual fact that could be your only trade done for the year and your NAV only changes by 1%

If you calculate XIRR at the end of THE day, yes you are right.

If you calculate XIRR at the end of the year, i believe the return is still 1%, consistent with NAV method.

Right!

The key word is annualized. What we're trying to work out is CAGR and the "A" stands for annualized. XIRR calculates on an annualized basis, that's why it represents CAGR accurately.

With the NAV method, the annualization is manually calculated at end of desired period. The cash flows in between are annualized (manually) but not annualized based on the timing they occurred (as with XIRR), that's why it is not a perfect representation of CAGR. But it is a good estimate.

---

Would the returns using XIRR be very far off from assuming that the amount is deposited from the start of the year? For example, assuming that I buy another stock on 15 May 2011. By right, I should be using XIRR to calculate the returns for 2011 to be accurate, but let's say I am lazy and decide to assume that the amount that I use to buy the stock is done on 1 January 2011 instead. As such, I would be using "(end of year amount-start of year amount/start of year)*100%". Would the XIRR return veered far off from my previous formula?

Another question. If I decide to use my portfolio that I started 5 years ago as starting point and the portfolio that I uses 5 years later as the ending point, the XIRR would be considered as annualised (as in yearly) return for 5 years or returns from 5 years of investment?

Again, the key word is annualization, the A in CAGR. If you want CAGR over desired period (that you select), key in the cash flows with the associated correct dates. Any thing else is NOT CAGR - it is your own definition of return %.

[CityFarmer]

I went to try out XIRR on my Mother-in-law's portfolio (easier as very few transactions) and was happily surprised that I got XIRR = 34%! I thought I may need to annualise it and got a lowly figure = 6.91%. Something doesn't seem right! One is too high and the other is too low. Using my own non-certified method, I was having an annualised return = 12.55%.

I searched the internet and found a useful explanation on the use of XIRR (it's already an annualised figure) and after applying, I got an XIRR = 12.03%, which looks more right as it's closer to my 12.55%.

I extract the key point from this site,

Right. She should ignore all the activity which was internal to her portfolio, including dividends.
Any dividends would influence that final portfolio value, of course, but they shouldn't appear as XIRR cash flows.

ie. it's a lot simpler now. We don't even need to put in the stocks buy/sell transactions nor the dividends, unless they're withdrawn / added (from new cash flows) into the portfolio.

Regarding your mother-in-law's portfolio, and without knowing the details, I get the feeling the cash dividends received should be all taken into account as outflows. With 34% as the correct CAGR. If the portfolio spans many years and dividends are received thru this period, the XIRR annualises these dividends received at time of receipt. Hence the figure is higher than if they were manually calculated as being all received today. You can tell your MIL you're doing great for her porfolio (and it's the truth)!

[One component of my portfolio spanning 4 years was giving xirr of 10% without including dividends received. After factoring in dividends as outflows, xirr = 16%. My manual calculation was 15%. This is why I feel dividends received should be included as outflows.]

we should include dividend received as outflow in XIRR calculation.

Dividend make a different in your % of return. It also make a different with the date the dividend received. So XIRR calculation should include the dividend as cash outflow.

[smallcaps]

Maybe this example would give a clearer picture:

-10,000 1/1/2001 bought
2,000 1/1/2002 dividends received
-2,000 1/1/2002 dividends injected back into portfolio
10,000 1/1/2003 market value
2,000 1/1/2003 cash in portfolio

XIRR 9.54%

It gives the same XIRR as the 'dividends excluded' approach below, simply because in the above case, the dividends are injected into the portfolio *immediately*. If its not immediate, then the XIRR would be different. I guess it boils down to how one manages the portfolio... for me, there's no concept of cash idling in portfolio so have to make some assumption...

-10,000 1/1/2001 bought
10,000 1/1/2003 market value
2,000 1/1/2003 cash in portfolio

XIRR 9.54%

---

One observation about google doc's XIRR behavior. It does not seem to require the entries to be in chronological order... it gives the same result even if the dates are not in order.

---

Here's another example in which the dividends are NOT injected back into portfolio (XIRR is higher in this case):

-10,000 1/1/2001 bought
2,000 1/1/2002 dividends received (NOT injected into portfolio)
10,000 1/1/2003 market value

XIRR 10.50%

[swakoo]

Maybe this example would give a clearer picture:

Interesting examples. They can be rationalised thus:

-10,000 1/1/2001 bought
2,000 1/1/2002 dividends received
-2,000 1/1/2002 dividends injected back into portfolio
10,000 1/1/2003 market value
2,000 1/1/2003 cash in portfolio

XIRR 9.54%

Cashflows show dividends received and remain idle in portfolio. Possible scenario.

-10,000 1/1/2001 bought
10,000 1/1/2003 market value
2,000 1/1/2003 cash in portfolio

XIRR 9.54%

Cashflows show dividends received 1/1/2003 (wrong!) when in fact were received 1/1/2002. Example incorrect.

-10,000 1/1/2001 bought
2,000 1/1/2002 dividends received (NOT injected into portfolio)
10,000 1/1/2003 market value

XIRR 10.50%

Cashflows show dividends received and assumed taken out of portfolio (for whatever purpose) - that's why XIRR is higher than 1st example above, where it remains idle in portfolio. Another possible scenario.

Note the distinction between 1st and 3rd examples. The XIRR depends on what the investor does with the dividends received.

One observation about google doc's XIRR behavior. It does not seem to require the entries to be in chronological order... it gives the same result even if the dates are not in order.

As I mentioned earlier, no need to be in chrono order other than 1st one being the earliest one - this is from MS office website.

[freedom]

-10,000 1/1/2001 bought
10,000 1/1/2003 market value
2,000 1/1/2003 cash in portfolio

XIRR 9.54%

Cashflows show dividends received 1/1/2003 (wrong!) when in fact were received 1/1/2002. Example incorrect.

I don't see anything wrong with this particular example. Why should when the dividends received make any difference if the dividends never get taken out?

unless you are calculating performance of particular counter, otherwise, the portfolio does not change no matter when the dividends received.

[KopiKat]

Thx to 'swakoo' for being so patient to explain about XIRR and the easy to understand example by 'smallcaps', I think I may have finally understood how it should be used. After testing on a simple portfolio with only a few transactions, I believe there are two different scenarios of how it could be used. I present below for the experts here to tear apart if my understanding is wrong.

Case I

A dedicated bank account is used solely for all stocks transactions (or you are able to track all cash inflows and outflows). An example would be what I'm using for my Mother-in-law.

In this case, all you need is to include,

1) New cash inflow (ie. new funds) or outflows (ie. withdrawal)
2) The market value of any unsold stocks
3) Bank balance

There's no need to include all the buy/sell transactions. Also, no need to included Dividends (unless you withdraw it out of bank account to spend). Including Dividends would amount to double counting as any unused portion also appears in the Bank balance.


Case II

Like my case, my bank account is used not just for stocks but has a lot of other inflows (Dividends, Salary previously,..) and outflows (ATM withdrawal, GIRO payment, Internet banking,..). As such, it'd require a massive effort to identify my actual cash flow for stocks alone.

In this case, what's included for the XIRR computation would be,

1) All Stocks related proceeds eg. Sales, Purchase, Dividends
2) The market value of any unsold stocks

There's no need to include any Bank related figures (since we are unable to clearly identify all of them) ie. no need to look for any fresh fund inflow, withdrawals for personal use (outflow) etc. No need also for any Bank balance figure.

I tested it out on my M-in-law's case and got 12.02% (Case 1) & 13.88% (Case 2) vs 12.55% (Own non-scientific method). I suppose not having a non-productive bank balance may explain the better performance for Case 2 (or perhaps it makes the denominator smaller?).