28-06-2012, 09:43 AM
(27-06-2012, 08:37 PM)KopiKat Wrote: [ -> ](27-06-2012, 08:02 PM)swakoo Wrote: [ -> ]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...