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

(03-07-2012, 01:48 AM)swakoo Wrote: [ -> ]Yes, I see what u mean with this stark example. Somewhat bizarre investor selection of XIRR period but mathematically correct.

The series of cash flows in the stark example consist of 4 years of being invested and 6 years of complete idling (ie. no investments). Let's break them down thus:

***** buy 1/1/2001 (100) (100)
***** sell 1/1/2002 130 0
XIRR: 30%

2 years of idling

***** buy 1/1/2004 (130) 0
***** sell 1/1/2005 169 0
XIRR: 30%

2 years of idling

***** buy 1/1/2007 (169) 0
***** sell 1/1/2008 220 0
XIRR: 30%

2 years of idling

***** buy 1/1/2010 (220) 0
final value 1/1/2011 286 286
XIRR: 30%

If take all above cash flows into consideration,
XIRR: 30% (KopiKat's way)

If take 1st and last cash flow only (ok since in between cash flows net out ie no fresh funds needed)
***** buy 1/1/2001 (100) (100)
final value 1/1/2011 286 286
XIRR: 11.1% (smallcaps' way)

Who is "more accurate" I guess depends on what alternative yield they think they can get from the 6 years of idling (also cory's point).

Looks like it is good to stress test the computation of XIRR to understand the nuances.

I recalled an experience when i was a post-graduate student

Engineers like to build-up a model when solving a problem. The easier way is to build-up a software model (mathematical model)

There are no generically perfect model, a model is good only on a problem, or at most a categories of problems. The quality of the model depend on the level it can mimic the real world problem.

We as student, was working on a problem, had build-up a model. We were so engrossed with the model that we presented "great and insightful" observation on the model itself, rather then using the model to make us had better understanding on the problem-to-solve. At the end of the day, we all back to square.

I am not saying we are falling into similar trap, but it is good to remind our-self from time to time

In the spirit of conservatism (gives egoism a kick), I decided to try out a variation of 'smallcaps' suggestion as follows,

1 Jan 12 : + (Mkt Value of Stocks on 31 Dec11)
<Date> : <CashFlow>
31 Dec 12 : - (Mkt Value of Stocks on 30 Jun 12)

CashFlow = <Total SELL> + <Total DIV> - <Total BUY>

There are actually 4 different scenarios depending on your Profitabilty (Profit or Loss) + CashFlow (Net Inflow or OutFlow) and the <Date> will have to be adjusted to give a more conservative result.

Case 1 : Profitable Year

(a) <Total SELL> + <Total DIV> MORE THAN <Total BUY>
In this case, I'd use <DATE> = End Period eg. 31 Dec 12

(b) <Total SELL> + <Total DIV> LESS THAN <Total BUY>
In this case, I'd use <DATE> = Beginning Period eg. 1 Jan 12

Case 2 : Loss Year

(a) <Total SELL> + <Total DIV> MORE THAN <Total BUY>
In this case, I'd use <DATE> = Beginning Period eg. 1 Jan 12

(b) <Total SELL> + <Total DIV> LESS THAN <Total BUY>
In this case, I'd use <DATE> = End Period eg. 31 Dec 12

Hope I got the above right! Spending time to play around with this XIRR thingy is going to affect my real XIRR...

---

My thoughts on the 'conservertism' aspects of using XIRR. As posted by 'swakoo' and 'corydorus', it depends on what you are trying to measure or benchmark against.

In the 1st approach where I recognised all stocks related transactions (buy/sell/div) as cashflows and I don't have any cash balance at any one time, what I hoped to measure is Stocks Investment / Trading performance. Here, we're assuming a 100% efficiency usage of cash but which in real life, is unlikely possible.

This is where the conservative approach as suggested by 'smallcaps' comes in. I used a variation and computed a minimum 'Working Capital' from <Sell + Div - Buy> which I introduced as a cashflow item. Depending on whether you'd been making a Nett Profit or Loss, as described above, we can recognise this 'Working Capital' cashflow at the beginning or end of period for maximum conservativeness. You can also put it in the middle of the period if you want to tread on middle ground...

Still, we are kind of assuming a 100% efficiency in 'Working Capital' deployment here and which in real life is also unlikely. What happens to our other Free Cash while we wait for the ideal price to come along? Very likely, it's earning <1% bank interest in the bank.

For those who really wants to be super conservative or like in my case, I was wondering how well I'm managing my total assets, you may have to factor in this as an idle cashflow item. For eg. in my case, I tested it out by putting this 'Idle Cash' at both the beginning and end of period as opposite cashflow. An easier way is to just scale down the original XIRR with an 'Idle Cash' factor (in my case, I track my monthly cash balance and on average, ~5%, so, I could just use XIRR * 0.95).

So, ya, depends on what you're trying to measure, there're many variations. Ultimately, it ought to help us to better understand how and what we are doing, so that we can improve further... I hope!

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

Thanks for the attachment. I realised from your template that you have input a guess value (10%) when computing XIRR. What is the function of this value and is it compulsory? From the formula of XIRR, it is not compulsory.

(03-07-2012, 10:19 AM)KopiKat Wrote: [ -> ]Still, we are kind of assuming a 100% efficiency in 'Working Capital' deployment here and which in real life is also unlikely. What happens to our other Free Cash while we wait for the ideal price to come along? Very likely, it's earning <1% bank interest in the bank.

I prefer to compute the returns together with free cash since free cash in banks represents opportunity costs.
A $10,000 portfolio that doubles in a year sounds impressive. But, if there is a million dollar of cash lying around doing nothing, the gain is nothing fantastic.

It also makes an investor thinks harder whether he should deploy the fund or keep it for some other times.

(03-07-2012, 06:02 PM)yeokiwi Wrote: [ -> ]

(03-07-2012, 10:19 AM)KopiKat Wrote: [ -> ]Still, we are kind of assuming a 100% efficiency in 'Working Capital' deployment here and which in real life is also unlikely. What happens to our other Free Cash while we wait for the ideal price to come along? Very likely, it's earning <1% bank interest in the bank.

I prefer to compute the returns together with free cash since free cash in banks represents opportunity costs.
A $10,000 portfolio that doubles in a year sounds impressive. But, if there is a million dollar of cash lying around doing nothing, the gain is nothing fantastic.

It also makes an investor thinks harder whether he should deploy the fund or keep it for some other times.

If we'd diligently set up a separate bank account for all stocks related transactions (instead of lumping it with our zillions of other daily lives transactions), there'd be no need for >10 pages of this thread to discuss and find a means to do a proper performance measurement like XIRR (Any idle cash would also be properly recognised)!

Nevertheless, a great learning experience for me! Thx all!

(03-07-2012, 05:42 PM)Some-one 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...

Thanks for the attachment. I realised from your template that you have input a guess value (10%) when computing XIRR. What is the function of this value and is it compulsory? From the formula of XIRR, it is not compulsory.

I think it might be a starting point for the iterative numerical computations for which there are no exact solutions...

The so-called more conservative approach may not be so conservative under certain circumstances.... Let's look at a simple example,

Case I

1-Jan-12 : ($4,000) ; Beginning Mkt Value
10-Mar-12 : ($1,225) ; Buy Stock
25-Jun-12 : $1,245 ; Sell Stock
31-Dec-12 : $5,000 ; Closing Mkt Value

XIRR = 23.39%


Case II

1-Jan-12 : ($4,000) ; Beginning Mkt Value
10-Mar-12 : $1,245 ; Sell Stock
25-Jun-12 : ($1,225) ; Buy Stock
31-Dec-12 : $5,000 ; Closing Mkt Value

XIRR = 28.25%

The main difference between the 2 cases are the date sequence of the buy and sell transactions.


'Conservative' Approach

When we apply the more 'conservative' approach to both cases, we get the same thing,

1-Jan-12 : ($4,000) ; Beginning Mkt Value
31-Dec-12 : $20 ; Nett CashFlow = $1,245 - $1,225
31-Dec-12 : $5,000 ; Closing Mkt Value

XIRR = 25.5% <can also compute in % by ($5,000 + $20) / ($4,000) - 1>

The XIRR is more conservative compared to Case II (28.25%) but that's not the case when compared to Case I (23.39%)!

Any mistakes above??

(03-07-2012, 10:12 PM)KopiKat Wrote: [ -> ]The so-called more conservative approach may not be so conservative under certain circumstances.... Let's look at a simple example,

Case I

1-Jan-12 : ($4,000) ; Beginning Mkt Value
10-Mar-12 : ($1,225) ; Buy Stock
25-Jun-12 : $1,245 ; Sell Stock
31-Dec-12 : $5,000 ; Closing Mkt Value

XIRR = 23.39%


Case II

1-Jan-12 : ($4,000) ; Beginning Mkt Value
10-Mar-12 : $1,245 ; Sell Stock
25-Jun-12 : ($1,225) ; Buy Stock
31-Dec-12 : $5,000 ; Closing Mkt Value

XIRR = 28.25%

The main difference between the 2 cases are the date sequence of the buy and sell transactions.


'Conservative' Approach

When we apply the more 'conservative' approach to both cases, we get the same thing,

1-Jan-12 : ($4,000) ; Beginning Mkt Value
31-Dec-12 : $20 ; Nett CashFlow = $1,245 - $1,225
31-Dec-12 : $5,000 ; Closing Mkt Value

XIRR = 25.5% <can also compute in % by ($5,000 + $20) / ($4,000) - 1>

The XIRR is more conservative compared to Case II (28.25%) but that's not the case when compared to Case I (23.39%)!

Any mistakes above??

I think should be like this for Case 1, assuming beginning market value means stocks and not cash...

1-Jan-12 : ($4,000) ; Beginning Mkt Value
10-Mar-12 : ($1,225) ; Injection of capital
31-Dec-12 : $6,245 ; Closing Mkt Value + Cash

XIRR = 20.5%

For a young adult or those who has regular income saving, inclusion of idle cash needs to be careful. You will need to decide which idle cash is considered. And different people has different amount needs. A person who bought property with large loan has little saving compared to ones who park cash. A person who spend on car luxury may end up having a better performing XIRR etc due to lesser idle cash left ...

I would advocate yourself as "fund mgr", and when stocks are sold, cash is returned unit buyers and out of performing metrics. The last thing you want is to be motivated to spend your idle cash to look good.

Net worth comes in more on people who majority income comes from stock investment and no large "surprise" cash injection.