Part II: A (relatively) straightforward way to figure out your stock portfolio’s performance

Personal finance, Value investing

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

Note: This is the second of a two part series on how to calculate your returns versus the market. You may want to read Part I, which contains additional background information.

The Nitty Gritty Steps

What you’ll need:

  1. Internet access to Yahoo! Finance
  2. MS Excel (or use the provided simple template)
  3. Purchase prices, purchase dates, # of shares for each investment held in your portfolio
  4. Sale prices, sale dates, # of shares for each investment (if applicable)
  5. Dividend history of each investment during the period you held it (optional, see below)

The good thing about this method is that after setting it up the first time, updating it becomes quite easy. Personally, I use Excel to do this, but it’s possible to calculate your returns by hand. For future updates, you might also be able to save some steps by setting up your portfolio via My Yahoo so that you can look at current prices on all your investments at a glance, but this is completely optional.

I should also clarify that the method I use compares how I would have done had I invested the same amount of money in SPY instead of what I invested in. An alternative method might be to compare how I would have done had I invested the same number of shares in SPY instead of what I invested in. In other words, if you had $10K to invest, you have been able to buy 400 shares in MSFT, but only 100 shares of SPY since SPY’s stock price is higher.

Step 1: Collect your investment data. This is probably the most important step in the process. For each stock in the portfolio that you’ll be evaluating, you’ll need to find the purchase date, purchase price, and number of shares that you purchased. If you have stocks included in the portfolio that you’ve sold to be included in the evaluation, you’ll need all the purchase info as well as the shares info for later.

Step 2: Enter the info in excel or the attached template. Enter stock symbols, # of shares purchased, purchase date, and purchase price in the attached spreadsheet as outlined in the examples in blue. If you purchased a particular stock more than once, you’ll need to enter each purchase on a separate line.

Step 3: Collect historical price info for SPY. Go to Yahoo! Financials, enter SPY in the quote box, and click on “historical prices” on the left hand sidebar (or just click on the previous link to go directly there). For each purchase date you’ve listed, use the “Close” column and enter this info in the “SPY closing price on purchase date” column for each corresponding entry as shown in the example. You could use averages or opening price as well, but to keep it simple, I just use the closing price. (More on using Adjusted Close later.)

Step 4: Collect current price info for your investments and for SPY. Assuming you’re evaluating your portfolio’s performance “today”, you’ll need to look up the current price for each of your investments. Enter the date of evaluation (probably today’s date) at the top of the worksheet. (This is purely for bookkeeping purposes so that should you look back in the future, you know what date you last used the program.) Enter in SPY’s current price in the column below. Again, examples have been provided in blue. If you should want to evaluate your portfolio’s performance as of a date in the past, you can also do this by looking up the historical prices for each of your investments, just as you did in Step 3 for SPY.

Step 5: (optional for shares sold): Calculate gain or loss from sale, and equivalent gain or loss for SPY. If you have sold shares in your portfolio that you want included in the performance evaluation, you need to enter them in the next section of the spreadsheet, under “Stocks in portfolio that have been sold”. For each sale, you’ll need to enter in purchase info, and if the sale was for purchases made on different dates, you’ll have to enter those in separately on each line. This is a bit of a pain, but it’s the cleanest way to calculate your returns. You probably do something similar when you file your taxes to the IRS. You will also have to look up the purchase price and closing price for SPY on the purchase date and sale date, respectively, and enter them in the columns provided.

Step 6: Compare! You’re almost done! As you can see, the spreadsheet automatically calculates your returns and SPY’s returns. Scroll down to the orange box at the bottom to compare your performance versus the market. In the example provided, the portfolio underperformed the market by almost 5% (3.98% versus SPY’s 8.63%). It may be sobering and unpleasant to see you’re not beating the market, but it can definitely be useful knowledge to have since you always have a alternative choices such as changing your investment strategy, or choosing to invest in index ETFs or funds instead.

Step 7: Save your work. As I mentioned earlier, the first time you do this is the most time consuming. Once you save your work, all the purchase price info for both your investments and SPY should remain unchanged, and if you decide to evaluate your performance later, you simply need to update the current prices of your holdings, the current price of SPY, and enter in some sales of stock if you have had any.

I should mention that for those who want to be more accurate, you can use the “Adjusted Close” price for SPY instead and also adjust the purchase prices on your holdings and sales for any dividend payouts. This is a little more complicated as it involves collecting dividend payout history for each of your holdings and decreasing the purchase price by the corresponding amount. However, note that if you don’t include dividends, you’re not really getting the complete or real picture on how your investments have performed, since total returns for investments should include dividends. For example, if you owned Citigroup (C), not adjusting for dividend payouts would make it look like you’ve underperformed the market significantly, even if you had bought the stock 5 years ago.

An alternate, perhaps easier way to approximate this (which I have not tried) might be to look up the Adjusted Close for each holding in your portfolio on the date you purchased the stock and comparing it with SPY’s Adjusted Close. This should give close to the same result as the more rigorous method, assuming your purchase price wasn’t that far off from the closing price that day.

I tried my best to ensure that the excel template was free of errors but cannot be 100% sure, so please use it with this in mind. You’re free to use, distribute, and modify it as you need, and if there are any improvements or errors, don’t hesitate to let me know. Thanks for reading!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

6 Feedbacks on "Part II: A (relatively) straightforward way to figure out your stock portfolio’s performance"

Sastry

The data sheets are very good and easy to follow. But it is better to explain some terms such as SPY for laymen to understand.
Thank you



Ricemutt

@Sastry: Thanks for your comment. If you read Part I of this post, I explain that SPY is an ETF (exchange traded fund) that passively mimics the S&P 500 index. It trades like a stock (e.g. you can actually buy it) and information on it is readily available from Yahoo! Finance, etc. so I use it as a substitute for the S&P 500 itself. Hope that helps.



Spencer

I have made the switch over to managing all ov my own investments and was looking for something to compare my portfolio to the S&P 500 and other Indexes which I believe is extremely important. Thank you for the spreadsheet! I wish my brokerage Fidelity would allow more comprehensive analysis like this and more “what-if” scenarios. My guess is that a very large number of individuals have no idea how their portfolios stack up against the indexes.



Software Find Payroll

Payroll Software Review-PayWindow 2006 Payroll System…

ZPay Systems has been creating payroll software for over 20 years starting with ZPAY, ZPAY 3 and now PayWindow 2006. This payroll software is easy to use especially if you don’t have any experience in accounting….



Nick

Question regarding dividends: My dividends are on auto DRIP (dividend reinvestment plan). Would I simply input each dividend as a new share purchase transaction?

Thanks



Nick

On second thought… would the correct method be to include the dividend info but set the cost basis for each dividend share repurchase to $0.00?



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.