How to you benchmark your portfolio VS S&P500? using excel?

Need some mathematic advice.😆

Try to collect data. Buy & Hold Top companies VS SPY experiment over long term.

1) I am able to download the daily closing price from yahoo finance
2) able to plot my portfolio growth

But how to plot the the SPY superimposed on the same graph?

If i constantly add position to my portfolio. The average price increase, then it make the unrealised gain % decrease, which make it seem your portfolio underperform?

In addition, what is the best index to benchmark sg stock? Beating STI is easy.

6

Discussion (6)

What are your thoughts?

Learn how to style your text

Tan Choong Hwee

06 Jul 2021

Investor/Trader at Home

Just download SPY daily closing prices and put it beside your portfolio closing prices in your spreadsheet. You can plot both prices in Y axis against date in X axis.

If you regularly add position to your portfolio, just make sure you also add the same dollar amount in SPY for a fair comparison.

Over the long term, you want to use the annualized IRR to compare your portfolio and SPY. Excel has XIRR function that helps you do the calculation.

Other than STI, another Singapore stock index is MSCI Singapore Index (SIMSCI), but it has only 19 constituents.

Reply

Save

Frankie Rappaport

06 Jul 2021

MSCI ACWI could be a good benchmark index

the excel project gets ever more complicated: fees, stock splits, dividends, new added funds, XIRR

Reply

Save

View 2 replies

Daz

06 Jul 2021

You'll presumably need to plot your yield & growth against cost as a % which could be comparable wit...

Write your thoughts