• filter:

How to calculate weighted average when stocks are traded mid-stream?

  • Page :
  • 1
  • Text Only
  • Search this Topic »
Voting History
rated:
I need to calculate the weighted average of my portfolio for a certain month or quarter. I know how to do that when securities aren't traded during that time. But what if stocks and mutual funds are bought and sold during the quarter or month? How do you calculate the weighted average in these situations? Is there an Excel spreadsheet or program where I can simply enter buy and sell dates?

Member Summary
Staff Summary
Thanks for visiting FatWallet.com. Join for free to remove this ad.

rated:
Homework?

rated:
Weight average of what?

rated:
If you got access to daily market close prices of every security you owned you could mock up excel formulas to give you a daily portfolio weight in about 5 minutes.

No, I'm not going to do it for you.

rated:
If you are talking about weighted average performance at the portfolio level, here is how you do it:

1. Determine initial total portfolio balance (B1)
2. Determine closing total portfolio balance (B2)
3. Portfolio change (C) = B2 - B1
4. Portfolio gain % (G) = (C - total contributions during the period + total withdrawals during the period) / B1
5. Perform this calculation for each sub-period (e.g. year), then take the average of G1, G2, ... for multiple years; weighting is built in as larger positions have greater impact on total performance. Trades don't matter because you are measuring total portfolio performance.

For individual position performance, I don't know how to time-weight performance based on fluctuating position size, but you can calculate the compound average growth rate (CAGR) of a single security's price per share (P1, P2) over a given elapsed number of years (E) as follows:

E = Date at end of period - date at beginning of period

CAGR = (P2/P1)^(1/E)-1

Note: ^ indicates exponential function. Elapsed years may be fractional, however CAGR is not very useful in year 1.

rated:
The good broker tools do this automatically. Nice to see the nuts and bolts though. And if it's homework, he'll have to show his work anyway.

  • Quick Reply:  Have something quick to contribute? Just reply below and you're done! hide Quick Reply
     
    Click here for full-featured reply.


Disclaimer: By providing links to other sites, FatWallet.com does not guarantee, approve or endorse the information or products available at these sites, nor does a link indicate any association with or endorsement by the linked site to FatWallet.com.

Thanks for visiting FatWallet.com. Join for free to remove this ad.

While FatWallet makes every effort to post correct information, offers are subject to change without notice.
Some exclusions may apply based upon merchant policies.
© 1999-2017