(02-07-2012, 12:45 PM)smallcaps Wrote: [ -> ] (02-07-2012, 09:09 AM)KopiKat Wrote: [ -> ]So, I thought I finally understood how to use XIRR and went to try it out on my entire portfolio to check my mid-year performance. What I did,
1 Jan 12 : + (Mkt Value of Stocks on 31 Dec11)
Dates : + (Buy Transactions)
.
.
Dates : - (Sell Transactions)
.
.
Dates : - (Dividends)
.
.
30 Jun 12 : - (Mkt Value of Stocks on 30 Jun 12)
Dates above means each individual date and the corresponding value of transaction.
I also did an XIRR without the Dividends for comparison purposes.
Is the above OK?
Actually, I think if we do XIRR in this way, it can't be compared to say, the STI over the same period, since it might cause big differences between the actual CAGR and the XIRR computed.
Would like to suggest that in your case where it is too tedious to compute actual capital injections, that a more conservative method be used as it would be much easier to do in excel and since it is conservative, as long as the rate computed satisfy your target rate, then it should be fine:
conservative rate = ((total value at end of period) / (total injected capital))^(1/number of years)
It is easier to compute since total injected capital can be assumed to be:
total injected capital = total value at end of period - all dividends - all realised profit (just include all realised buys/sells)
It is conservative since it assumes that all capital injections happened at the start of the period.
Managed to get excel to do the XIRR more automatically!
By using conditions in the cells, managed to compute the capital injections.
Attached the spreadsheet so that you can use it as a template...
smallcaps, sincerewatch(HK) has it been converted to S$ ? I see the profit abnormally large compared to rest as different currency may put XIRR into chaos
.
(02-07-2012, 03:09 PM)corydorus Wrote: [ -> ]smallcaps, sincerewatch(HK) has it been converted to S$ ? I see the profit abnormally large compared to rest as different currency may put XIRR into chaos .
Yep, I kept records in sing dollars so that it is easier to do computations. The profit is abormally large because at that point of time, all of my money was in one single stock, 0444
(02-07-2012, 02:05 PM)smallcaps Wrote: [ -> ] (02-07-2012, 12:45 PM)smallcaps Wrote: [ -> ] (02-07-2012, 09:09 AM)KopiKat Wrote: [ -> ]So, I thought I finally understood how to use XIRR and went to try it out on my entire portfolio to check my mid-year performance. What I did,
1 Jan 12 : + (Mkt Value of Stocks on 31 Dec11)
Dates : + (Buy Transactions)
.
.
Dates : - (Sell Transactions)
.
.
Dates : - (Dividends)
.
.
30 Jun 12 : - (Mkt Value of Stocks on 30 Jun 12)
Dates above means each individual date and the corresponding value of transaction.
I also did an XIRR without the Dividends for comparison purposes.
Is the above OK?
Actually, I think if we do XIRR in this way, it can't be compared to say, the STI over the same period, since it might cause big differences between the actual CAGR and the XIRR computed.
Would like to suggest that in your case where it is too tedious to compute actual capital injections, that a more conservative method be used as it would be much easier to do in excel and since it is conservative, as long as the rate computed satisfy your target rate, then it should be fine:
conservative rate = ((total value at end of period) / (total injected capital))^(1/number of years)
It is easier to compute since total injected capital can be assumed to be:
total injected capital = total value at end of period - all dividends - all realised profit (just include all realised buys/sells)
It is conservative since it assumes that all capital injections happened at the start of the period.
Managed to get excel to do the XIRR more automatically!
By using conditions in the cells, managed to compute the capital injections.
Attached the spreadsheet so that you can use it as a template...
Hello smallcaps,
Thank you for all the great explanations on the use of XIRR + a template even!
I think I'm still more inclined to use the approach I'd described earlier as many here seems to be using the same and it'd be easier to benchmark against their posted results. In terms of being conservative, I have my own non-scientific method which is giving me quite a few % points lower (when XIRR is positive). But, I noticed that when XIRR is negative, the approach which I plan to be using is actually more conservative as it also gives a higher XIRR figure (but negative).
As for benchmarking with STI, as mentioned by 'corydorus', if I were to use 31-Dec-12 as my End Date (but still retain the 30-Jun-12 Mkt Price), I'd be getting a 'decompressed' (anyhow copy jargons used by the experts here) XIRR figure that's suitable for this purpose.
PS. I'm already happily applying it to my prior years (for the past few days) and that's one of the main reason why I won't be trying out your approach any time soon (I do have quite a lot of transactions, especially dividends of stocks which pays quarterly Div). Perhaps when I'm done with my 10-Years (individually) + 3-Years + 5-Years...+ any other combo to test the robustness thro' a bear cycle,.. etc., I'll try out your method to compare.
(02-07-2012, 06:06 PM)KopiKat Wrote: [ -> ] (02-07-2012, 02:05 PM)smallcaps Wrote: [ -> ] (02-07-2012, 12:45 PM)smallcaps Wrote: [ -> ] (02-07-2012, 09:09 AM)KopiKat Wrote: [ -> ]So, I thought I finally understood how to use XIRR and went to try it out on my entire portfolio to check my mid-year performance. What I did,
1 Jan 12 : + (Mkt Value of Stocks on 31 Dec11)
Dates : + (Buy Transactions)
.
.
Dates : - (Sell Transactions)
.
.
Dates : - (Dividends)
.
.
30 Jun 12 : - (Mkt Value of Stocks on 30 Jun 12)
Dates above means each individual date and the corresponding value of transaction.
I also did an XIRR without the Dividends for comparison purposes.
Is the above OK?
Actually, I think if we do XIRR in this way, it can't be compared to say, the STI over the same period, since it might cause big differences between the actual CAGR and the XIRR computed.
Would like to suggest that in your case where it is too tedious to compute actual capital injections, that a more conservative method be used as it would be much easier to do in excel and since it is conservative, as long as the rate computed satisfy your target rate, then it should be fine:
conservative rate = ((total value at end of period) / (total injected capital))^(1/number of years)
It is easier to compute since total injected capital can be assumed to be:
total injected capital = total value at end of period - all dividends - all realised profit (just include all realised buys/sells)
It is conservative since it assumes that all capital injections happened at the start of the period.
Managed to get excel to do the XIRR more automatically!
By using conditions in the cells, managed to compute the capital injections.
Attached the spreadsheet so that you can use it as a template...
Hello smallcaps,
Thank you for all the great explanations on the use of XIRR + a template even!
I think I'm still more inclined to use the approach I'd described earlier as many here seems to be using the same and it'd be easier to benchmark against their posted results. In terms of being conservative, I have my own non-scientific method which is giving me quite a few % points lower (when XIRR is positive). But, I noticed that when XIRR is negative, the approach which I plan to be using is actually more conservative as it also gives a higher XIRR figure (but negative).
As for benchmarking with STI, as mentioned by 'corydorus', if I were to use 31-Dec-12 as my End Date (but still retain the 30-Jun-12 Mkt Price), I'd be getting a 'decompressed' (anyhow copy jargons used by the experts here) XIRR figure that's suitable for this purpose.
PS. I'm already happily applying it to my prior years (for the past few days) and that's one of the main reason why I won't be trying out your approach any time soon (I do have quite a lot of transactions, especially dividends of stocks which pays quarterly Div). Perhaps when I'm done with my 10-Years (individually) + 3-Years + 5-Years...+ any other combo to test the robustness thro' a bear cycle,.. etc., I'll try out your method to compare.
no sweat, I actually found it be a useful exercise, after procrastinating for so long, finally put in the effort to compute the returns.
I have been doing this XIRR for a number of years. And able to work back the lost years as well. Is quite interesting to know how you perform in each year, reasons, popular investments and economic situation then. And then learn from it.
(02-07-2012, 12:45 PM)smallcaps Wrote: [ -> ] (02-07-2012, 09:09 AM)KopiKat Wrote: [ -> ].
.
.
Is the above OK?
Actually, I think if we do XIRR in this way, it can't be compared to say, the STI over the same period, since it might cause big differences between the actual CAGR and the XIRR computed.
KopiKat's way of computing XIRR is on the basis that dividends received are
taken into account at time of receipt for XIRR computation, then taken out from portfolio.
If I read smallcaps excel template correctly, the way of computing XIRR there is on the basis that dividends received are not taken into account at time of receipt for XIRR computation, but
taken into account when next used for making fresh purchases of stocks.
To me, both ways seem valid and it just depends on what basis one wants to use to compute XIRR. The "actual" CAGR depends on what basis one chooses to compute XIRR. (The second way will likely give slightly lower XIRR as dividends are taken into account only later when fresh purchases are made.)
In terms of comparison with STI over same period, here are some thoughts:
- The corresponding STI CAGR can be manually calculated or by using XIRR, using initial value/date, final value/date, period.
- A difference is that there are no STI "purchases/sales" within the period as with one's portfolio, so this is a "pure" CAGR over the whole period.
- This is a capital gains only CAGR.
- After taking above considerations into account, my feel is that either of the ways above (with differing dividend treatments) can be compared to get a sense of relative performance.
(03-07-2012, 12:20 AM)swakoo Wrote: [ -> ] (02-07-2012, 12:45 PM)smallcaps Wrote: [ -> ] (02-07-2012, 09:09 AM)KopiKat Wrote: [ -> ].
.
.
Is the above OK?
Actually, I think if we do XIRR in this way, it can't be compared to say, the STI over the same period, since it might cause big differences between the actual CAGR and the XIRR computed.
KopiKat's way of computing XIRR is on the basis that dividends received are taken into account at time of receipt for XIRR computation, then taken out from portfolio.
If I read smallcaps excel template correctly, the way of computing XIRR there is on the basis that dividends received are not taken into account at time of receipt for XIRR computation, but taken into account when next used for making fresh purchases of stocks.
To me, both ways seem valid and it just depends on what basis one wants to use to compute XIRR. The "actual" CAGR depends on what basis one chooses to compute XIRR. (The second way will likely give slightly lower XIRR as dividends are taken into account only later when fresh purchases are made.)
In terms of comparison with STI over same period, here are some thoughts:
- The corresponding STI CAGR can be manually calculated or by using XIRR, using initial value/date, final value/date, period.
- A difference is that there are no STI "purchases/sales" within the period as with one's portfolio, so this is a "pure" CAGR over the whole period.
- This is a capital gains only CAGR.
- After taking above considerations into account, my feel is that either of the ways above (with differing dividend treatments) can be compared to get a sense of relative performance.
Actually just wanted to highlight that including all buys/sells in XIRR computation might result in large differences in certain cases. Take for example this case whereby an investor only injects capital once and rollover the profits from one buy/sell to another. He holds each buy for 1 year and makes a profit of 30%, and then waits 2 years for the next buy:
***** buy 1/1/2001 (100) (100)
***** sell 1/1/2002 130 0
***** buy 1/1/2004 (130) 0
***** sell 1/1/2005 169 0
***** buy 1/1/2007 (169) 0
***** sell 1/1/2008 220 0
***** buy 1/1/2010 (220) 0
final value 1/1/2011 286 286
XIRR 29.97% XIRR 11.07%
The XIRRs are very different. In the case whereby idling of cash in the portfolio was required in order to generate such a return, then the lower XIRR would be more accurate in estimating future returns.
(03-07-2012, 01:11 AM)smallcaps Wrote: [ -> ]Actually just wanted to highlight that including all buys/sells in XIRR computation might result in large differences in certain cases. Take for example this case whereby an investor only injects capital once and rollover the profits from one buy/sell to another. He holds each buy for 1 year and makes a profit of 30%, and then waits 2 years for the next buy:
***** buy 1/1/2001 (100) (100)
***** sell 1/1/2002 130 0
***** buy 1/1/2004 (130) 0
***** sell 1/1/2005 169 0
***** buy 1/1/2007 (169) 0
***** sell 1/1/2008 220 0
***** buy 1/1/2010 (220) 0
final value 1/1/2011 286 286
XIRR 29.97% XIRR 11.07%
The XIRRs are very different. In the case whereby idling of cash in the portfolio was required in order to generate such a return, then the lower XIRR would be more accurate in estimating future returns.
Yes, I see what u mean with this stark example. Somewhat bizarre investor selection of XIRR period but mathematically correct.
(03-07-2012, 01:11 AM)smallcaps Wrote: [ -> ] (03-07-2012, 12:20 AM)swakoo Wrote: [ -> ] (02-07-2012, 12:45 PM)smallcaps Wrote: [ -> ] (02-07-2012, 09:09 AM)KopiKat Wrote: [ -> ].
.
.
Is the above OK?
Actually, I think if we do XIRR in this way, it can't be compared to say, the STI over the same period, since it might cause big differences between the actual CAGR and the XIRR computed.
KopiKat's way of computing XIRR is on the basis that dividends received are taken into account at time of receipt for XIRR computation, then taken out from portfolio.
If I read smallcaps excel template correctly, the way of computing XIRR there is on the basis that dividends received are not taken into account at time of receipt for XIRR computation, but taken into account when next used for making fresh purchases of stocks.
To me, both ways seem valid and it just depends on what basis one wants to use to compute XIRR. The "actual" CAGR depends on what basis one chooses to compute XIRR. (The second way will likely give slightly lower XIRR as dividends are taken into account only later when fresh purchases are made.)
In terms of comparison with STI over same period, here are some thoughts:
- The corresponding STI CAGR can be manually calculated or by using XIRR, using initial value/date, final value/date, period.
- A difference is that there are no STI "purchases/sales" within the period as with one's portfolio, so this is a "pure" CAGR over the whole period.
- This is a capital gains only CAGR.
- After taking above considerations into account, my feel is that either of the ways above (with differing dividend treatments) can be compared to get a sense of relative performance.
Actually just wanted to highlight that including all buys/sells in XIRR computation might result in large differences in certain cases. Take for example this case whereby an investor only injects capital once and rollover the profits from one buy/sell to another. He holds each buy for 1 year and makes a profit of 30%, and then waits 2 years for the next buy:
***** buy 1/1/2001 (100) (100)
***** sell 1/1/2002 130 0
***** buy 1/1/2004 (130) 0
***** sell 1/1/2005 169 0
***** buy 1/1/2007 (169) 0
***** sell 1/1/2008 220 0
***** buy 1/1/2010 (220) 0
final value 1/1/2011 286 286
XIRR 29.97% XIRR 11.07%
The XIRRs are very different. In the case whereby idling of cash in the portfolio was required in order to generate such a return, then the lower XIRR would be more accurate in estimating future returns.
I had a very interesting discussion on this before.
Problem is we assume they have same absolute profit when in reality they are likely not. It boils down to what you want to measure.
Investment Stock performance or Net Worth management .
In the first, there are idle periods where the cash is "out-of-stock-investment". You can use for other purposes when they are away. Maybe extra cash in short term Foreign currency FD.
In the second, your investment is locked throughout the period in order to generate the same amount of profits from stocks only.
Which is more powerful ? They are different animal IMO.
Cory