Cory Diary : Fascination with XIRR

XIRR is a feature use in Excel to measure rate of returns. Best for measuring irregular returns such as stock transactions. XIRR is also the formula we enter in Excel spreadsheet to measure investment performance in a compounded way.

Just type in a single cell in the Excel "XIRR(" and a help note will appear "XIRR(Values,Dates,[guess])". Values is the value of your trade transaction. Date is the day transacted. Typically we can ignore "[guess]".Basically we only need to care for "XIRR(Values,Dates)". If you have weird answer, then "[guess]" needs to be use but this is rare and is due to mathematical solution which has more than one answer.

We know through common sense that for $1000 investment in $1 stock price of a company, if we are able to sell at $2 after holding for a full year, the return will be 100%. If we use XIRR, it will shows 100% too as table 1.

Table 1 on 100% return exactly a year

1-Jan-15	($1,000)	Invested Amount
1-Jan-16	$2,000	Take Profit
XIRR	100%

What if I am only able to sell my stock at $2 on 20th Feb'16 instead of earlier 1st Jan'16 date. This will get complicated in calculating my performance mentally but not with XIRR as in Table 2 below. The performance is 84% which make sense since we take longer to achieve the same profit as in table 1 above.

Table 2 on 100% return more than a year. Time is money !

1-Jan-15	($1,000)	Invested Amount
20-Feb-16	$2,000	Take Profit
XIRR	84%

What if I am able to sell my stock at $2 within  7 months time. The answer is in Table 3 below.

Performance shoot up to 230%.

Table 3 on 100% return in just 7 months

1-Jan-15	($1,000)	Invested Amount
1-Aug-15	$2,000	Take Profit
XIRR	230%

In Summary

We are use to think how we earn last year compare to this year. The problem is this year has not ended. Is only 28 July today and we have 5 more months to go before we know how we perform in 2016. Therefore using today performance measured will skew a lot higher in % if you make an interim profit but much lower in % if is an interim loss of the year.

Thus, Table 3 performance is only for interim reference and not the final result till the year ended. To maintain 230% return at year end, your profit needs to be able to scale to $3300 as shown in Table 4. Which is why performance will decrease over time as chances are, we will not be able to hit $3300 profit from a single trade.

Table 4 To achieve 230% for a full year period

1-Jan-15	($1,000)	Invested Amount
1-Jan-16	$3,300	Take Profit
XIRR	230%

Over the years with consistent performance measured in all your trades, if we are able to achieve strong XIRR results, It will likely reflect your performance capability. The longer the better. I would say 5 years minimum so that a single good trade in your earlier trading days will be properly "amortised" in a compounded way. This is lifetime XIRR measure and a key portfolio measuring tool.

Final Final !

Another thing to watch is the weight-age. The bigger investment you make the larger the impact too on XIRR. This is especially so in later years since with growing income and returns, your investment will get significantly larger that will minuscule your XIRR performance in your earlier years in comparison.

Thus, be aware of fund manager which market their performance using short period and low fund amount to jack up their performance. Only to see their performance flatter after you have subscribed to them.

Cory

20160728