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 of 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 for a fictional stock portfolio in this article.
Open the Excel workbook and insert the last date to each week and then press "Update" button. Make sure dates (col A) are sorted.
Copy values to a new table
Copy "NAV per share" data from the "Transactions" sheet and corresponding dates.
Select the new sheet containing the yahoo S&P 500 prices you downloaded.
Paste "NAV per share" data and corresponding dates.
Indexing S&P 500 data
Index SP500 to 100. This makes it a lot easier to compare performance relative to your stock portfolio. Both your stocks and the index must start at the same value, I am going for 100 which is often used.
Formula in cell C2:
Copy formula to cells below as far as needed.
Explaining formula in cell C2
Step 1 - Divide first value with current value
The forward slash is an arithmetic operator that lets you divide a number with another number. The first cell reference B2 is a relative cell reference that changes when you copy the cell and paste to cells below.
The second cell reference is an absolute cell reference that is locked to cell B2 all the time (except if you insert rows or columns). This allows us to divide the corresponding value on the same row with the first value and calculating the outcome in percentage date by date.
and returns 1.
Step 2 - Multiply with 100
The parentheses let you control the order of operations, we want it to do the division before we multiply with 100.
and returns 100.
Create a chart showing portfolio and S&P 500 performance