Historical Positions - How to manage splits correctly

[tokyo_trader]

Hi,

I have an issue with splits. I am trying to input historical positions for a current ETF portfolio.
In the attached ETF portfolio 3 of them had splits back on 24th July 2008 as follows:

- EEM 3 for 1 (original purchase price $32.71)
- EPP 3 for 1 (original purchase price $32.71)
- IEV 2 for 1 (original purchase price $32.71)

All of the ETF's within the portfolio were originally purchased on February 2nd 2006.
The 3 ETF's that have splits are not displaying correctly. After the splits, the number of shares for each ETF was increased and the share price for each was reduced automatically based on the split ratio (which is normal).

I now have a situation where the value/performance for the individual ETF and the whole portfolio is incorrect.

As an example, this is what has happened with EEM:

Purchased 304 shares on Feb 2nd 2006 at $32.71 + $25 commission = $9993.84

On 24th July 2008 3 for split issued. Number of shares held automatically increased to 912 and the cost basis price was reduced to $10.95. The affect of this has played havoc with the value of the portfolio and the charts. Basically any gains have been multiplied many times due to the increased number of shares being considered bought a a much reduced price.

Also, when all of the ETF's that have had splits are graphed with the rest of the portfolio (see attached) the initial value of the portfolio instantly shoots up from $100,000 to almost $150,000 (i.e. an instant fake gain of almost 50% - nice if it were true but it isn't)..

Also, as can be seen in the attached, the unrlz gain for the 3 split ETF's is wrong. EEM is showing a gain of 242% when in reality it has only gained 14% (current price of $37.33 v purchase price of $32.71)

What is the best method for adding historical portfolios without having the splits mess everything up?

I hope the above is clear and makes sense?

With thanks

Tokyo_Trader

[Mark]

Hi Tokyo_Trader,

Let's focus on just the EEM issue to keep things simple at first. You mentioned you bought 304 shares at $32.71 for $9993.84. (I assume these are prices/shares as they happened on the trade date, and not in post-split terms. You want to record all transactions in Fund Manager as they happened on the transaction date.) It then split 3 for 1, and now the price is trading at $37.33. This means you have 912 shares worth $34,044.96. This is a large gain of $24,051, which comes out to the 240% gain number. You don't want to compare pre-split prices with post-split prices. Your shares didn't just change from 32.71 to 37.33, they also split 3 times, so for comparison purposes it is 10.9 to 37.33. You can also think of it in market values, in addition to pricing. It went from $9993.84 up to $34,044.96. This is equivalent to thinking of it in post-split prices going from 10.9 to 37.33.

That graph is a Portfolio Value graph. If you added money to the portfolio on a particular date, the portfolio value will jump like this. You might check a couple of Portfolio Value reports around the date of this jump to see why the portfolio value increased on that date.

[tokyo_trader]

Hi Mark,

Firstly, this ETF portfolio is a live 'Buy and Hold' portfolio, which started on Feb 2nd 2006. Nothing has been bought or sold since.
$100K was originally invested, and nothing has been added or withdrawn since.

Yes, 304 EEM shares were bought on Feb 2nd 2006 at $32.71(2 years prior to the 3:1 share split).
After the split occurred, the purchase price (cost basis) was automatically reduced to $10.95 and the number of shares increased to 904 (see attached 'data manager - purchases' image)

However, the prices within the data manager (prices screen) are still showing $32.71 on the purchase date (which is correct) but it is also showing 912 shares being purchased. 912 x 32.71 = $29,831 which is not correct as only circa $9,993 was used to buy 304 shares. (See attached image)

Finally, the initial value of EEM jumped from circa $10k to $30K as a result of the share split. (See attached image)

As can be seen within the data manager - (purchases screen & prices screen) an anomaly/conflict exists because they both show different information (prices, number of shares and purchase cost).

Finally, if you look at the portfolio value chart in my original post, you will see that the initial value of $100K has shot straight up to circa $150K. Once again this has occurred due to all 3 of the ETF's that issued splits in 2008.
With all 3, the purchases screen within data manager is correct. However the prices screen within data manager is showing the original share price, but it has automatically increased the number shares (based on the split ratio) which has caused the size/value of the initial purchases to be multiplied. Hence the charts are all being messed up and displaying incorrect values and performance.
So my question is:

Is there a bug/conflict within data manager (prices screen v purchases screen) and how it accounts for splits, or is there something I as a user should amend if and when a split has been issued?

With thanks

Tokyo Trader

[Mark]

Hi Tokyo Trader,

It looks like your closing prices are recorded inconsistently with your purchase price. Your closing price on 2/2/06 should be closer to $10 instead of $30 to be consistent with your purchase price. How did you update prices for this ETF?

I just took a look at Yahoo for the symbol EEM, and it is showing an actual closing price of $98.14 on 2/2/06, and a split adjusted price of $29.85. Something is not consistent between what you're describing, and the prices on Yahoo. Based on pricing from Yahoo you would have had to purchase your shares at about $98 back on Feb 2, 2006, and not $32? See:

http://finance.yahoo.com/q/hp?s=EEM&a=0 ... &z=66&y=66

(As an aside... It appears that all of these screen shots were taken with "View / Split Adjusted" turned on. Please note, that this option allows you to show all prices/shares either adjusted for splits, or un-adjusted. When this is off, all prices/shares are shown as they actually were on that date. When this option is on, everything is adjusted into today's post-split terms for all splits. Changing this option doesn't change how your data is recorded, just how it is displayed. All transactions and closing prices should be recorded how they were on that price/transaction date. So, if you bought on 2/2/06 304 shares for $32, record it that way.)

So, did you really buy 304 shares of EEM on 2/2/06 at a price of $32.17 on that 2/2/06 date? This doesn't seem consistent with the pricing on Yahoo, that shows EEM closer to $98/share back on 2/2/06? It actually looks like your closing prices are correct (assuming Yahoo's pricing is correct), but your purchase price is wrong... You record your purchases as they occurred on the transaction date, not in post-split adjusted pricing.

[tokyo_trader]

Hi Mark,

What a stupid mistake on my part.
I realize now that I was using the price in FM as the purchase price (which was already adjusted for the splits).

I knew the amount invested into EEM was $10,000 in 2/2/06 but I failed to check the actual purchase price on the portfolio statement.
Nevermind I have now amended the 3 split price ETF's and the graphs and values are now all correct.

Thanks for your help.

Tokyo Trader