1
$\begingroup$

I am creating a portfolio tracking model in Excel and have run into difficulty on how to track the overall performance of a single asset, given that over time more and less capital (shares) has been allocated to that asset. I want to determine the most fundamentally sound way to calculate this.

I think an example is best to illustrate this.

  1. At t=0, 100 shares of ABC stock are purchased for \$100 per share.

  2. At t=1, 50 shares of ABC stock, which now trades at (costs) \$200, are purchased.

  3. At t=2, 50 shares of ABC stock, which now trades at (costs) \$300, are purchased.

  4. At t=3, the portfolio holds 200 shares of ABC stock and ABC's current price is \$400 per share.

So clearly the price change is (400/100–1) = 300%, but this needs to be adjusted for the fact that additional shares were added at a higher price, and so the total return for the portfolio's ownership of ABC stock is lower.

How is this figure calculated in practice? Is it sound to split up the asset into three (original purchase, add 1, and add 2), calculated each's return since purchase, and weight those returns by the number of shares?

Finally, how would I handle selling shares? Say at t=3 I sold 50 shares at \$ 400 each and at t=5 each share (150 in total now) is worth \$ 500.

$\endgroup$
  • 2
    $\begingroup$ Are you familiar with Internal Rate of Return (IRR), and the Excel function = XIRR() which implements it? $\endgroup$ – noob2 Mar 3 '16 at 20:39
  • $\begingroup$ I am familiar with the XIRR function. How would I implement it in this case? $\endgroup$ – jmabs Mar 3 '16 at 21:56
  • 3
    $\begingroup$ There are a bunch of different ways to do this depending on your goal. Try googling money weighted vs. time weighted returns and the CFA standards for performance presentation. A good book for this is "Practical Portfolio Performance Measurement and Attribution by Carl Bacon. Some measures would report it as if $1 had been invested during the entire period, while other techniques recognize market timing to more greatly weight the returns in which more money is invested. $\endgroup$ – horseless Mar 3 '16 at 22:31
  • $\begingroup$ Thanks horseless for those references. I'll take a look. I think its money-weighted returns that I'm after. If you know, could you tell me if this is theoretically sound on any level?: t=0 shares returned 300% and now account for 50% of the portfolio. t=1 shares returned 100% and now account for 25% of the portfolio. And t=2 shares returned 33% and now account for 25% of the portfolio. The weighted returns are then 150%, 25%, and 8.33%. Summed, I find 183%. I've been told this is correct but I am uncomfortable with the idea of summing returns and I'm not sure. Thanks $\endgroup$ – jmabs Mar 3 '16 at 22:48
  • 2
    $\begingroup$ You are not able to just sum the weighted returns because it is important how much you paid for each share. A better way would be to calculate the weighted average cost basis (.5*100 + .25*200 + .25*300 = 175) and calculate the return off of that (400/175 - 1 ~= 129%). Or, just take the current value and divide by the total outlay ($80k/$35k - 1 ~= 129%). Calculating annualized return has more intricacies and require models as spoken of above. $\endgroup$ – RandyF Mar 3 '16 at 23:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.