By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid off. In fact, Warren Buffet recommends investing in an SP500 index fund if you have no knowledge investing in the stock market. Let´s see if your portfolio beats S&P500 over time.

In previous posts a few years ago I explained how to use NAV units to calculate stock portfolio performance. I am going to use Net Asset Value based on units in this post.

Download data

We need monthly data from both the stock portfolio and S&P 500. First download historical weekly prices from yahoo finance. Copy and paste prices to a new sheet.

Calculate stock portfolio performance

The excel workbook attached to Automate net asset value (NAV) calculation on your stock portfolio (vba) can automatically calculate portfolio performance using NAV (net asset value). You can´t base portfolio perfomance on account balance.

Open the excel workbook and insert the last date in each week and then press "Update" button. Make sure dates (col A) are sorted.

Copy values to a new table

  1. Copy "NAV per share" data from the "Transactions" sheet and corresponding dates
  2. Select the new sheet containing the yahoo S&P 500 prices you downloaded
  3. Paste "NAV per share" data and corresponding dates

Indexing data

Index SP500 to 100. This makes it a lot easier to compare performance.

Create an index chart

  1. Select values in column C and D
  2. Go to "Insert" tab
  3. Click "Line chart" button

Download excel *.xlsm file

Stock-portfolio-performance-chart.xls