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.
- Automate net asset value (NAV) calculation on your stock portfolio (vba)
- Calculate your stock portfolio performance with Net Asset Value based on units
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
- 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
Index SP500 to 100. This makes it a lot easier to compare performance.
Create an index chart
- Select values in column C and D
- Go to "Insert" tab
- Click "Line chart" button