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.
Excel Spreadsheet:
A
B
C
D
E
F
G
1
2
No. Shares
20
30
25
3
Price data
Portfolio
Return
Calc
4
Day 1
2
7
3
=SUMPRODUCT($B$2:$D$2,B4:D4)
=STDEV(F5:F7)
5
Day 2
3
9
2
=SUMPRODUCT($B$2:$D$2,B5:D5)
=(E5-E4)/E4
=AVERAGE(F5:F7)
6
Day 3
3
4
5
=SUMPRODUCT($B$2:$D$2,B6:D6)
=(E6-E5)/E5
0.01%
7
Day 4
4
5
7
=SUMPRODUCT($B$2:$D$2,B7:D7)
=(E7-E6)/E6
=(G5-G6)/G4
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.
