[mlipsman]

Hi Alex,

OK, I see: we add the 10k at the beginning of the period, and the 2k is the profit we realize at the end. Because the Gain/Loss column in your example came before the Add/Withdraw column, I had thought it all took place in a single rebalancing, where the 2k is our profit from the previous period and then we add the 10k.

If we don’t add the 10k, then the profit at the end would be only about 1k instead of 2k, so it would still be 9.5%.

Thanks for clarifying.

Mark

[mlipsman]

Hi Alex,

I set up your example in Excel, and it seems that it has the same problem of additions/withdrawals skewing the performance. For example, if you delete the 10000 in C4, the performance in E4 goes from 10% to 18%. Maybe I’m wrong, but I’d consider 18% the “real” performance, since the reduction to 10% occurs only if 10000 is added. To me, it seems that performance should be what the market gave us, not influenced by any additional money we invested or withdrew. Is there a way to account for the additions/withdrawals so that they don’t change the performance?

To give an analogy, if I’m a merchant and I buy a product for $1 and sell it for $2, I make a 100% profit. That’s my “performance.” It doesn’t matter if I invest $10 or $100,000 in a supply of the products to sell–the *amount* of my profit will change, but the percentage/performance remains at 100. Only if I start selling them for $2.50 will my “performance” increase to 150%–again, independent of how much I invest in having stock on hand.

So my question is, is there a way to separate the amount that we invest from the return that $1 produces from the investment?

Mark

[mlipsman]

Hi Alex,

Where on the spreadsheet would you account for money added to or deducted from the original investment? For example, in week 6, if you wanted to add another $10,000 or cash out a couple of thousand? It can’t really be added to the Total in column J, because that would skew the performance.

Thanks,
Mark

[mlipsman]

Actually, looking at your spreadsheet more closely, it appears that the number of shares to own (cols F and G) is determined by the total investment (col J). But the total investment depends on the number of shares (times the price of each), and the number of shares depends on the total investment. So it seems a bit circular.

It looks like the procedure would be this?
1. Calculate performance (cols P and Q) based on the change in price (cols D and E).
2. Multiply performance this period by the investment last period to determine the updated total investment (e.g., J4 = Q4*J3).
3. Use the updated total investment to determine the new number of shares to own.

[mlipsman]

Great, thanks! I knew there had to be an easier way.

[mlipsman]

Hi Alex,

I’m not sure if you answered it either, so let me try to clarify what I mean.

Suppose I have $10,000 in MYRS, which is at a 50/50 allocation. ZIV is $50, TMF is $20. I start with 100 shares of ZIV at $50 and 250 shares of TMF at $20. My average cost per share for ZIV is $50.

Two weeks later, ZIV is at $53, TMF is at $18, and the allocation goes to 60/40.

I sell 56 shares of TMF at $18 (=$1008) and buy 19 shares of ZIV at $53 (=$1007).

My average cost per share for ZIV is:
100 shrs @ $50 = $5000
+ 19 shrs @ $53 = $1007

= $6007/119 = $50.48

Your charts would presumably show a symbol return of $(53-50)/50 = .06 = 6%
However, my return would be $(53-50.48)/50.48 = .05 = 5%

Two weeks later, ZIV is at $47, TMF is at $23, and the allocation goes to 40/60.

I sell 21 shares of ZIV at $47 (=$987) and buy 43 shares of TMF at $23 (=$989).

My average cost per share for ZIV is:
100 shrs @ $50 = $5000
+ 19 shrs @ $53 = $1007
– 21 shrs @ $47 = $987

= $5020/98 = $51.22

Your charts would presumably show a symbol return of $(47-53)/53 = -0.11 = -11%
However, my return would be $(47-51.22)/51.22 = -.08 = -8%

So every time I rebalance, my average cost per share changes: it’s the total dollar amount of shares I’ve bought less the total dollar amount of shares I’ve sold, divided by the number of shares I have left. Your gain/loss is based on the change in price of 1 share. My gain/loss is based on the change in my average cost of 1 share compared to the new price and may not be anywhere near your figure.

What I’m wondering is if there’s an easier way to calculate (or approximate) my gain/loss without having to figure the average cost per share every two weeks or month, because the calculation gets longer each time.

Thanks,
Mark

[mlipsman]

OK, but I’d still like to know if there’s an easier way to calculate it than figuring an average cost based on all previous buys and sells of that symbol.

[mlipsman]

The return percentages you show in your tables aren’t what most of us will actually realize unless we sell our entire position each time we rebalance and then buy back the new allocation. Instead, our buy price for each symbol will be the average of all the buys and sells we’ve done so far, which becomes increasingly cumbersome to calculate as time passes and new buys and sells have to be accounted for in calculating our average cost. Is there an easier way to do this, even if it’s a good approximation rather than an exact figure? Within .5% or so would be fine. I’d just like to know if I’m winning or losing each time I rebalance, and by approximately how much.

[Author]

Posts