How to test Statistical significance of portfolio in a spreadsheet.
(3/5 from 1 vote)
Assume that i track 10 different portfolios with 10 stocks in single portfolio, for 15 trading days. So how can I determine which portfolio returns are best with low risk profile and high returns in an excel spreadsheet.
In respect to statistical proven results and formulas.
To evaluate the risk/return profiles of multiple portfolios within the same spreadsheet, you could create a worksheet for each portfolio and then an analysis sheet linking the results of each portfolio sheet for analysis.
For simplicity, suppose there were 3 stocks in each portfolio with 4 trading days. Each portfolio sheet could be constructed as follows.
Columns A to D list the price data and number of shares held. Column E calculates the total portfolio value for each day by using the SUMPRODUCT formula. The formula in cell E4 can be copied or dragged down from the bottom right corner of the cell. Column F calculated the daily returns of the total portfolio value.
The performance calculation are made in column G. Cell G4 calculates the standard deviation of returns (risk). Cell G5 calculates the average daily return. The 1 day risk-free rate can be entered into cell G6. Finally the Sharpe Ratio is calculated in cell G7 which summarizes the return/risk profile. The portfolio with the highest Sharpe Ratio will have the best profile.
None of this analysis requires tests for statistical significance. If, however, you wanted to calculate the probability of achieving a certain return, risk or Sharpe Ratio for any portfolio then tests for statistical significance at a prescribed confidence interval would be recommended.