ValueBuddies.com : Value Investing Forum - Singapore, Hong Kong, U.S.

Hi all,

Just joined. Wanted to say a big thanks for sharing about XIRR !!!
Have never known my annualized return until now. Have kept complete dividend and transactions since 2003 and it only take around 10mins to set up the data for XIRR.

Here's complete data ever since started keeping records, up to the point where sold all in 2010. Later than that not included as do not wish to disclose current holdings. Thought it might be useful information for those who wish to learn about investing in small caps, especially the misses and have a better idea of what happened during this bull run period, and also the effect of dividends.

Note that the first portion is dividends followed by the transactions:

XIRR w divdends 0.26
XIRR w/o dividends 0.22

Unifood 26-Sep-2003 Interim 16,000 $210.88 DIVIDENDS
Unifood 26-Sep-2003 Special 16,000 $142.07
Citiraya 7-May-2004 First & Final 20,000 $192.00
Sincere Watch 22-Aug-2007 Final 254 $2.08
Sincere Watch 22-Aug-2007 Special 1 254 $0.42
Sincere Watch 22-Aug-2007 Special 2 254 $3.18
Unisteel 28-Aug-2007 Interim 5,000 $120.00
Unisteel 28-Aug-2007 Special 5,000 $55.00
Unisteel 28-Aug-2007 Interim 687 $16.49
Unisteel 28-Aug-2007 Special 687 $7.56
Sincere Watch (HK) 11-Sep-2007 Final 288,000 $3,315.01
Unisteel 26-May-2008 Final 687 $24.05
Unisteel 26-May-2008 Special 687 $10.31
Unisteel 26-May-2008 Final 5,000 $175.00
Unisteel 26-May-2008 Special 5,000 $75.00
Sincere Watch (HK) 27-Aug-2008 Final 312,000 $2,809.58
Food Empire 28-May-2009 Final 8,000 $28.00
Food Junction 16-Sep-2009 Interim 280 $0.70
Eastern Holdings 18-Jan-2010 Interim 50,000 $200.00
GK Goh 18-May-2010 Final 15,000 $225.00
Food Junction 25-May-2010 Final 280 $1.40
Food Empire 26-May-2010 Final 8,000 $17.60
Food Empire 26-May-2010 Special 8,000 $62.40
Old Chang Kee 7-Jun-2010 Final 72,000 $720.00
Old Chang Kee 7-Jun-2010 Special 72,000 $1,296.00
Valuetronics 13-Aug-2010 Final 147,000 $1,798.95
Aspial 16-Aug-2010 Final 30,000 $150.00
Eastern Holdings 25-Aug-2010 Final 150,000 $499.50
Japan Food 31-Aug-2010 Final 53,000 $159.00
Food Junction 14-Sep-2010 Interim 280 0.7
Unifood 23-Jul-2003 Buy 16,000 ($8,428.83) TRANSACTIONS
Unifood 29-Sep-2003 Buy 1,000 ($628.19)
Unifood 16-Jan-2004 Buy 3,000 ($1,718.84)
Food Empire 8-Mar-2004 Buy 10,000 ($3,174.75)
Unifood 9-Mar-2004 Sell 10,000 $5,848.82
Unifood 16-Mar-2004 Sell 6,000 $3,475.05
Citiraya 16-Mar-2004 Buy 10,000 ($4,365.38)
Citiraya 22-Mar-2004 Buy 10,000 ($4,125.25)
Unifood 1-Apr-2004 Sell 4,000 $2,215.72
Food Empire 20-Apr-2004 Sell 10,000 $3,275.17
Unisteel 22-Apr-2004 Buy 3,000 ($4,075.22)
Citiraya 2-Sep-2004 Sell 6,000 $4,414.21
Sincere Watch 9-Sep-2004 Buy 9,000 ($5,666.51)
Citiraya 25-Oct-2004 Sell 6,000 $5,133.78
Apex-Pal 1-Nov-2004 Buy 20,000 ($5,326.30)
Citiraya 20-Dec-2004 Sell 8,000 $6,692.83
Sincere Watch 3-Jan-2005 Buy 10,000 ($7,602.67)
Unifood 13-Sep-2005 Buy 18,000 ($4,078.70)
Unifood 17-Jan-2006 Sell 18,000 $4,291.13
Sincere Watch 25-Jan-2006 Buy 6,000 ($5,069.30)
Unisteel 17-Jul-2006 Buy 4,000 ($7,310.65)
Apex-Pal 21-Aug-2006 Sell 24,000 $6,090.06
Sincere Watch 21-Aug-2006 Sell 11,000 $8,108.84
Sincere Watch (HK) 28-Aug-2006 Buy 120,000 ($14,760.79)
Sincere Watch 8-Sep-2006 Sell 10,000 $7,569.16
Sincere Watch (HK) 15-Sep-2006 Buy 69,000 ($8,518.74)
Sincere Watch 26-Apr-2007 Sell 4,000 $4,891.30
Sincere Watch (HK) 26-Apr-2007 Buy 36,000 ($4,932.41)
Sincere Watch (HK) 28-May-2007 Buy 21,000 ($3,654.97)
Unisteel 26-Jun-2007 Sell 4,000 $8,849.32
Sincere Watch (HK) 3-Jul-2007 Buy 21,000 ($4,215.55)
Sincere Watch (HK) 5-Jul-2007 Buy 21,000 ($4,549.30)
Sincere Watch (HK) 14-Nov-2007 Buy 24,000 ($5,463.73)
Sincere Watch (HK) 16-Jul-2008 Buy 27,000 ($4,531.46)
Food Junction 17-Jul-2008 Buy 11,000 ($4,649.09)
Food Empire 17-Jul-2008 Buy 8,000 ($4,749.15)
Unisteel 11-Aug-2008 Sell 5,000 $9,566.87
Sincere Watch (HK) 11-Aug-2008 Buy 36,000 ($5,837.33)
Sincere Watch (HK) 13-Aug-2008 Buy 30,000 ($4,372.40)
Food Junction 15-Sep-2008 Sell 5,180 $2,849.00
Sincere Watch (HK) 18-Sep-2008 Buy 27,000 ($2,263.92)
Unisteel 22-Sep-2008 Sell 687 $1,339.65
Sincere Watch (HK) 18-Sep-2008 Buy 33,000 ($2,678.68)
Food Junction 29-Sep-2008 Sell 5,540 $1,805.89
Sincere Watch (HK) 30-Sep-2008 Buy 30,000 ($2,012.29)
Sincere Watch (HK) 21-Oct-2008 Buy 30,000 ($1,963.78)
Sincere Watch (HK) 24-Oct-2008 Buy 30,000 ($1,889.38)
Sincere Watch (HK) 16-Oct-2009 Sell 555,000 $109,392.65
Aussino 21-Oct-2009 Buy 100,000 ($8,531.07)
Nippecraft 25-Nov-2009 Buy 1,000 ($91.78)
Nippecraft 30-Nov-2009 Buy 140,000 ($9,131.41)
Shining 7-Dec-2009 Buy 1,000 ($76.78)
Eastern Holdings 8-Dec-2009 Buy 50,000 ($9,532.80)
China Lifestyle 9-Dec-2009 Buy 50,000 ($8,531.07)
China Lifestyle 10-Dec-2009 Sell 50,000 $8,718.79
Bonvest 15-Dec-2009 Buy 10,000 ($9,176.56)
Aspial 16-Dec-2009 Buy 30,000 ($9,633.13)
Aussino 18-Dec-2009 Sell 100,000 $8,968.67
Westcomb (Asiasons WFG) 21-Dec-2009 Buy 70,000 ($9,131.41)
GK Goh 21-Dec-2009 Buy 2,000 ($1,247.37)
GK Goh 22-Dec-2009 Buy 5,000 ($3,078.31)
GK Goh 23-Dec-2009 Buy 8,000 ($4,949.25)
Valuetronics 28-Dec-2009 Buy 70,000 ($9,482.61)
Japan Foods 4-Jan-2010 Buy 53,000 ($12,246.95)
Bonvest 11-Jan-2010 Sell 10,000 $10,463.76
Afor 11-Jan-2010 Buy 45,000 ($7,005.31)
Nippecraft 13-Jan-2010 Sell 97,000 $6,759.80
Afor 14-Jan-2010 Sell 45,000 $8,069.13
Old Chang Kee 14-Jan-2010 Buy 40,000 ($9,432.44)
Nippecraft 14-Jan-2010 Sell 44,000 $3,051.67
Old Chang Kee 15-Jan-2010 Buy 32,000 ($7,550.57)
Eastern Holdings 21-Jan-2010 Buy 30,000 ($5,954.77)
Afor 26-Jan-2010 Buy 6,000 ($1,047.27)
Afor 27-Jan-2010 Buy 19,000 ($3,543.54)
Eastern Holdings 29-Jan-2010 Buy 20,000 ($3,728.63)
Valuetronics 29-Jan-2010 Buy 30,000 ($4,378.96)
Old Chang Kee 15-Jun-2010 Sell 72,000 $21,884.22
Afor 25-Jun-2010 Sell 25,000 $5,095.64
Valuetronics 8-Jul-2010 Buy 47,000 ($8,726.17)
Eastern Holdings 13-Jul-2010 Buy 50,000 ($8,781.21)
Food Empire 23-Aug-2010 Sell 8,000 $2,731.85
Aspial 23-Aug-2010 Sell 15,000 $4,445.94
Japan Foods 23-Aug-2010 Sell 53,000 $13,732.44
Aspial 26-Aug-2010 Sell 5,000 $1,472.48
Westcomb (Asiasons WFG) 31-Aug-2010 Sell 1,000 $103.19
Eastern Holdings 31-Aug-2010 Sell 150,000 $26,906.82
Valuetronics 31-Aug-2010 Sell 147,000 $27,101.16
GK Goh 31-Aug-2010 Sell 15,000 $8,818.74
Aspial 31-Aug-2010 Sell 10,000 $2,771.83
Westcomb (Asiasons WFG) 29-Oct-2010 Sell 69,000 $7,559.40

Do you all include commission and brokerage fee when calculating XIRR?

Just completed my XIRR base on transaction data of 2010, 2011 and 2012, i did not keep all data before 2010

2010: +12.6%
2011: -16.5%
2012 (up-to-date): +24.3%

I had adjusted the strategy starting this year, hope to improve my return in due course.

(27-06-2012, 10:59 AM)Some-one Wrote: [ -> ]Do you all include commission and brokerage fee when calculating XIRR?

Yes I do. Every single expense is included.

Hi Some-one,

For my case, yes, the fees are included in the numbers.

(27-06-2012, 11:04 AM)Musicwhiz Wrote: [ -> ]

(27-06-2012, 10:59 AM)Some-one Wrote: [ -> ]Do you all include commission and brokerage fee when calculating XIRR?

Yes I do. Every single expense is included.

Yes, mine are also the same. The transaction value are used, so all expenses included.

(26-06-2012, 06:10 PM)Musicwhiz Wrote: [ -> ]Hi KopiKat,

A few observations though - I computed using an XIRR formula in Excel, using additions and subtractions from portfolio, so not sure how accurate this is. I should have used an NAV approach but it's really beyond me LOL!

I have been re-examining the mechanics of the NAV method for calculating CAGR. And studying the construct of the XIRR formula. My conclusion is that for the purpose of calculating CAGR, the XIRR method is the accurate way of doing it. And a very efficient and easy way too.

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!

The NAV method doesn't take into account the time value of money of the transactions in between the first and last transactions, hence is just an estimate for the purpose of calculating CAGR. It's purpose is more for calculating fund value for clients of the fund manager and it does a good job of this.

(27-06-2012, 08:29 AM)KopiKat Wrote: [ -> ]The STI decline from 1996-98 was as bad as the 2008 one. Even tho' %-wise, it was slightly lower, the duration makes one feel like being slowly strangulated to death (if one is not happily buying more of the same stocks at an ever cheaper price).

Yup, the 98 bear was a different flavour - more drawn out, several waves of false recoveries only to sink even deeper just like the scary movie of that era "Jaws", just when you thought it was safe to go in to swim again ......

KopiKat Wrote:When I bought my current property in 1998, I didn't have enough cash to do the down-payment and couldn't bear to sell my stocks. I ended up borrowing cash from my family members (1st and only time in my life - not a very good feeling to be indebted) while I waited for the market recovery that fortunately came along in 1999. Yes, what a recovery (+78%)! I spent 1999, happily selling my stocks and clearing my entire debt.

Good on you! You caught the property cycle bottom good and proper. Yes, 1999 was an unforgettable year.

[Image: URA+Singapore+private+residential+proper...x+2012.png]

(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'.

(27-06-2012, 08:37 PM)KopiKat Wrote: [ -> ]I suppose they have to be in time chronological order?

No need, just that the 1st one has to be the earliest one.

Quote:Still got to find some time to do that (plus study XIRR proper usage), tho'.

For someone of your calibre, not more than 5 minutes.

http://office.microsoft.com/en-gb/excel-...09341.aspx

(27-06-2012, 08:43 PM)swakoo Wrote: [ -> ]

(27-06-2012, 08:37 PM)KopiKat Wrote: [ -> ]I suppose they have to be in time chronological order?

No need, just that the 1st one has to be the earliest one.

Woah... getting easier by the minute! Thx!

Quote:

Quote:Still got to find some time to do that (plus study XIRR proper usage), tho'.

For someone of your calibre, not more than 5 minutes.

http://office.microsoft.com/en-gb/excel-...09341.aspx

Clicking on that link makes my eyes and brain go cali-BLUR...
I think I'll explore it further when my 'working environment' is less like a war zone (like now).

---

Without going into the mechanics of how XIRR works, it struck me that it's doing computations based on cash inflow and outflow. That means the current market value of your stocks (not sold ones) is not taking into consideration.

If my thinking is correct, that means we may be able to show a super positve XIRR by only realising profits but not losses?