a report about your stock portfolio project
3000 word
Summary of Assignment
You are required to write a report in which you discuss the relationship between the stock market and the economy and economic and financial policies. You will also report on the details of an investment project that you undertake over eight weeks. In this project, you will analyze data of several companies or stock indexes to construct a stock portfolio. You will also manage, trade, and rebalance this portfolio live over eight weeks. Finally, you will provide a full performance evaluation of your stock portfolio.
Project Overview and Goals
You are acting as a portfolio manager for London National Corporation (LNC) clients, your clients have entrusted you with $5,000,000 to invest in the U.S. stock market at your discretion for eight weeks. Your clients are expecting you to invest their money prudently to help them achieve their investment objectives. While your clients desire a high rate of return, their risk tolerance is considered low. Additionally, your clients will be assessing your success in managing their portfolio as measured by the performance evaluation criteria shown below. You will be able to construct and manage your portfolio live on the MarketWatch Virtual Stock Exchange. At the end of the trading period, if your clients are satisfied with your performance managing their portfolio, they will extend their relationship with you, otherwise, they will consider other asset managers.
Investment Policy Statement (IPS)
Following a meeting with your clients you have agreed an investment policy statement with them in which you assessed their risk tolerance to be low, and a return objective equal to the holding period return on S&P 500 + 1%. No withdrawals or additions are allowed during the investment period.
Portfolio Management Rules and Constraints
You are limited to the U.S. stock market
• Each position you take in an asset must be a minimum of 5% of your assets and a maximum of 20% of your assets
• Cash must not exceed 20% of your portfolio at any time
• Transaction cost is flat $100 per transaction
• Trading volume limitation is 1%
• Debt interest rate for leverage is 5%
• Short selling and margin selling are allowed
• Limit orders and stop orders are allowed
Performance Evaluation
Specifically, your clients are concerned about the following criteria:
• Performance measurement: Absolute return as measured by the total dollar increase in the portfolio over the trading period.
• Performance attribution: To assess your skills, clients are interested in a performance attribution of your portfolio returns
• Performance appraisal: Risk-adjusted return as measured by the return on the portfolio taking into account the level of risk assumed by you, using different performance appraisal measures (e.g. Sharpe Ratio).
Sources of Data
Sources that you may find to be particularly valuable for your research include: • MarketWatch: http://www.marketwatch.com/ • Wall Street Journal: www.wsj.com • Yahoo Finance: finance.yahoo.com • Data Stream • Bloomberg • WARDS
a report about your stock portfolio project
1. Discuss the theories, methods, techniques, and models you used in security selection for your portfolio. In this section you need at least to: • Discuss the approach you followed in security selection (e.g. fundamental analysis vs technical analysis) • Discuss the details of any analysis you undertake to select securities for your portfolio (economy or industry analysis, valuation methods, financial ratios, technical indicators, trends, etc) • Demonstrate the robustness of your analysis by providing a summary of data used, illustrations, and the outcome of your analysis. • Support your discussion with some literature
2. Discuss the optimization methods and techniques that you followed to construct your portfolio. In this section you need at least to: • Discuss the appropriateness of the approach you followed in portfolio construction/optimization (e.g. Markowitz mean-variance model, Factor models, or other models) • Discuss the details of any analysis you undertake to construct your portfolio (e.g. Markowitz mean-variance model, Factor models, or other models) • Demonstrate the robustness of your analysis by providing a summary of data used, illustrations, and the outcome of your analysis. • Support your discussion with some literature.
3. Discuss the approaches, methods, and techniques that you followed to manage, execute, trade, and rebalance your portfolio. In this section you need at least to: • Evaluate the appropriateness of the approach you followed in portfolio management (e.g. active, semi-active, passive management) • Discuss the appropriateness of the methods and techniques you follow to manage, execute, trade, and rebalance your portfolio. • Discuss the details of any analysis you undertake to manage, execute, trade, and rebalance your portfolio. • Demonstrate the robustness of your analysis by providing a summary of data used, illustrations, and the outcome of your analysis. • Support your discussion with some literature.
4. Evaluate the performance of your portfolio. In this section you need at least to: • Discuss the process of stock portfolio performance evaluation (e.g. importance, steps, and best practice) • Discuss the appropriateness of the methods and techniques you follow to evaluate the performance of your stock portfolio. • Discuss the details of any analysis you undertake to evaluate the performance or your portfolio including performance measurement, attribution, and appraisal. • Demonstrate the robustness of your analysis by providing a summary of data used, illustrations, and the outcome of your analysis. • Support your discussion with some literature.
Word count: 3000
Symbol,Shares,% Holdings,Type,Price,Price Change,Price Change %,Value,Value Gain/Loss,Value Gain/Loss %,Players Holding
AMZN,”2,000″,59%,BUY,”$3,162.16 “,23.78,0.76%,”$6,403,300.00 “,”-46,700.00″,-0.72%,57
BABA,”5,699″,14%,BUY,$226.90 ,-0.71,-0.31%,”$1,481,740.00 “,”-109,730″,-6.89%,39
AAPL,”8,164″,10%,BUY,$130.92 ,4.32,3.41%,”$1,034,011.42 “,”85,825.50″,9.05%,64
TWTR,”10,154”,5%,BUY,$52.33 ,-0.93,-1.75%,”$567,303.98 “,”93,213.72″,19.66%,7
PLTR,”10,071”,2%,BUY,$25.00 ,1.46,6.20%,”$261,543.87 “,”-42,650.69″,-14.02%,13
GOOGL,126,2%,BUY,”$1,774.34 “,51.46,2.99%,”$217,503.72 “,”-5,057.64″,-2.27%,10
ZM,505,2%,BUY,$342.50 ,-1.91,-0.55%,”$205,035.05 “,”-41,394.85″,-16.80%,17
GOLD,”8,452″,2%,BUY,$24.77 ,0.27,1.10%,”$196,678.04 “,”-8,881.36″,-4.32%,7
MSFT,501,1%,BUY,$218.29 ,6.04,2.85%,”$109,513.59 “,”3,413.56”,3.22%,24
NVDA,156,1%,BUY,$533.76 ,29.18,5.78%,”$82,817.28 “,”-5,569.20″,-6.30%,5
NTDOY,998,1%,BUY,$79.01 ,0.32,0.41%,”$80,678.32 “,”8,119.73″,11.19%,3
EBAY,”1,319”,1%,BUY,$53.06 ,-0.16,-0.30%,”$69,986.14 “,”4,431.84”,6.76%,4
FSLY,500,0%,BUY,$86.91 ,5.29,6.48%,”$50,890.00 “,”13,150.00”,34.84%,1
INTC,390,0%,BUY,$52.19 ,1.09,2.13%,”$18,509.40 “,647.4,3.62%,10
Rank,Date,Cash,Cash Interest,Margin Cost,Net Worth,% Return
39,12/21/2020,”$0.00″,”N/A”,”N/A”,”$5,705,732.31″,”14.11%”
42,12/18/2020,”$0.00″,”N/A”,”N/A”,”$5,697,718.74″,”13.95%”
32,12/17/2020,”$0.00″,”N/A”,”N/A”,”$5,788,661.76″,”15.77%”
31,12/16/2020,”$0.00″,”N/A”,”N/A”,”$5,749,728.76″,”14.99%”
46,12/15/2020,”$0.00″,”N/A”,”N/A”,”$5,474,810.62″,”9.50%”
38,12/14/2020,”$0.00″,”N/A”,”N/A”,”$5,524,960.69″,”10.50%”
48,12/11/2020,”$0.00″,”N/A”,”N/A”,”$5,438,781.94″,”8.78%”
51,12/10/2020,”$0.00″,”N/A”,”N/A”,”$5,446,507.23″,”8.93%”
44,12/9/2020,”$0.00″,”N/A”,”N/A”,”$5,556,482.18″,”11.13%”
43,12/8/2020,”$0.00″,”N/A”,”N/A”,”$5,616,585.12″,”12.33%”
45,12/7/2020,”$0.00″,”N/A”,”N/A”,”$5,538,180.04″,”10.76%”
38,12/4/2020,”$0.00″,”N/A”,”N/A”,”$5,531,902.31″,”10.64%”
35,12/3/2020,”$0.00″,”N/A”,”N/A”,”$5,592,928.95″,”11.86%”
31,12/2/2020,”$0.00″,”N/A”,”N/A”,”$5,568,896.06″,”11.38%”
34,12/1/2020,”$0.00″,”N/A”,”N/A”,”$5,555,595.18″,”11.11%”
34,11/30/2020,”$0.00″,”N/A”,”N/A”,”$5,528,578.51″,”10.57%”
32,11/27/2020,”$0.00″,”N/A”,”N/A”,”$5,640,812.19″,”12.82%”
34,11/25/2020,”$0.00″,”N/A”,”N/A”,”$5,527,293.91″,”10.55%”
52,11/24/2020,”$0.00″,”N/A”,”N/A”,”$5,227,651.45″,”4.55%”
59,11/20/2020,”$0.00″,”N/A”,”N/A”,”$5,068,757.42″,”1.38%”
51,11/19/2020,”$0.00″,”N/A”,”N/A”,”$5,121,353.97″,”2.43%”
47,11/18/2020,”$0.00″,”N/A”,”N/A”,”$5,109,921.96″,”2.20%”
83,11/17/2020,”$0.00″,”N/A”,”N/A”,”$4,911,818.00″,”-1.76%”
101,11/16/2020,”$0.00″,”N/A”,”N/A”,”$4,719,122.64″,”-5.62%”
98,11/13/2020,”$0.00″,”N/A”,”N/A”,”$4,739,355.48″,”-5.21%”
100,11/12/2020,”$0.00″,”N/A”,”N/A”,”$4,720,737.86″,”-5.59%”
93,11/11/2020,”$0.00″,”N/A”,”N/A”,”$4,793,138.08″,”-4.14%”
101,11/10/2020,”$0.00″,”N/A”,”N/A”,”$4,499,054.33″,”-10.02%”
92,11/9/2020,”$0.00″,”N/A”,”N/A”,”$4,847,430.07″,”-3.05%”
28,11/6/2020,”$0.00″,”N/A”,”N/A”,”$5,300,841.97″,”6.02%”
26,11/5/2020,”$0.00″,”N/A”,”N/A”,”$5,325,046.49″,”6.50%”
84,11/4/2020,”$0.00″,”N/A”,”N/A”,”$4,612,317.50″,”-7.75%”
77,11/2/2020,”$0.00″,”N/A”,”N/A”,”$4,313,621.25″,”-13.73%”
70,10/30/2020,”$0.00″,”N/A”,”N/A”,”$4,367,927.20″,”-12.64%”
56,10/29/2020,”$0.00″,”N/A”,”N/A”,”$4,895,277.90″,”-2.09%”
44,10/28/2020,”$0.00″,”N/A”,”N/A”,”$4,832,759.81″,”-3.34%”
6,10/27/2020,”$0.00″,”N/A”,”N/A”,”$5,136,694.60″,”2.73%”
N/A,10/26/2020,”$5,000,000.00″,”N/A”,”N/A”,”$5,000,000.00″,”0.00%”
Symbol,Order Date,Transaction Date,Type,Cancel Reason,Amount,Price
PLTR,12/2/20 8:23a,12/2/20 9:31a,Buy,”Insufficient Buying Power”,”21,487″,”$22.25″
PLTR,11/27/20 11:19a,11/27/20 11:19a,Buy,””,”10,071″,”$30.21″
TWTR,11/27/20 11:18a,11/27/20 11:18a,Buy,””,”10,154″,”$46.69″
MSI,11/27/20 11:16a,11/27/20 11:16a,Sell,””,”990″,”$173.45″
BABA,11/27/20 11:15a,11/27/20 11:15a,Buy,””,”4,994″,”$276.43″
TSLA,11/26/20 9:46p,11/27/20 9:30a,Sell,””,”4,309″,”$581.54″
AAPL,11/26/20 9:47p,11/27/20 9:30a,Buy,””,”5,164″,”$116.53″
UBER,11/19/20 8:39p,11/20/20 9:35a,Sell,””,”1,099″,”$49.92″
GOLD,11/19/20 8:38p,11/20/20 9:31a,Buy,””,”8,452″,”$24.32″
QCOM,11/19/20 8:38p,11/20/20 9:31a,Sell,””,”500″,”$148.28″
AMD,11/18/20 9:16p,11/19/20 9:35a,Sell,””,”5,022″,”$82.39″
NTDOY,11/5/20 2:19p,11/5/20 2:34p,Buy,””,”998″,”$72.70″
EBAY,11/5/20 2:27p,11/5/20 2:27p,Buy,””,”1,319″,”$49.70″
UBER,11/5/20 2:27p,11/5/20 2:27p,Buy,””,”1,099″,”$41.78″
BABA,11/5/20 2:17p,11/5/20 2:17p,Buy,””,”405″,”$289.31″
FB,11/5/20 2:17p,11/5/20 2:17p,Sell,””,”1,000″,”$296.70″
GOOGL,11/5/20 2:15p,11/5/20 2:15p,Buy,””,”126″,”$1,766.36″
MSI,11/5/20 2:11p,11/5/20 2:11p,Buy,””,”990″,”$161.87″
ZM,11/5/20 2:05p,11/5/20 2:05p,Buy,””,”505″,”$487.98″
TSLA,11/5/20 2:04p,11/5/20 2:04p,Sell,””,”1,491″,”$432.85″
AMD,11/5/20 2:02p,11/5/20 2:02p,Buy,””,”5,021″,”$82.27″
NVDA,11/4/20 11:26p,11/5/20 9:32a,Buy,””,”156″,”$566.58″
MSFT,11/4/20 11:27p,11/5/20 9:31a,Sell,””,”499″,”$221.89″
AMD,11/4/20 11:26p,11/5/20 9:30a,Buy,””,”1″,”$83.01″
SNE,11/2/20 3:30p,11/2/20 3:30p,Sell,””,”1,500″,”$85.54″
AMD,10/27/20 6:16p,10/28/20 9:32a,Buy,”Insufficient Buying Power”,”505″,”$78.60″
XLNX,10/27/20 6:19p,10/28/20 9:30a,Buy,”Insufficient Buying Power”,”177″,”$123.00″
INTC,10/27/20 10:50a,10/27/20 10:50a,Buy,””,”390″,”$45.80″
QCOM,10/27/20 10:49a,10/27/20 10:49a,Buy,””,”500″,”$126.67″
SNE,10/27/20 10:47a,10/27/20 10:47a,Buy,””,”500″,”$77.32″
BABA,10/27/20 10:44a,10/27/20 10:44a,Buy,””,”300″,”$312.70″
TSLA,10/27/20 10:42a,10/27/20 10:42a,Sell,””,”200″,”$425.50″
AMZN,10/26/20 10:20p,10/27/20 9:31a,Buy,””,”2,000″,”$3,225.00″
AAPL,10/26/20 10:22p,10/27/20 9:31a,Buy,””,”3,000″,”$115.48″
FSLY,10/26/20 10:24p,10/27/20 9:31a,Buy,””,”500″,”$75.48″
FB,10/26/20 10:29p,10/27/20 9:30a,Buy,””,”1,000″,”$279.00″
TSLA,10/26/20 10:19p,10/27/20 9:30a,Buy,””,”3,000″,”$423.76″
TSLA,10/26/20 10:19p,10/27/20 9:30a,Buy,””,”3,000″,”$423.76″
SNE,10/26/20 10:21p,10/27/20 9:30a,Buy,””,”1,000″,”$77.36″
MSFT,10/26/20 10:17p,10/27/20 9:30a,Buy,””,”1,000″,”$211.78″
Frequently Asked Questions:
1. Will the project on MarketWatch be assessed?
No. the project itself is not assessed, but the report you write on this project will be assessed.
2. Is the project on MarketWatch mandatory?
Yes. You can write a report about a portfolio if you don’t have one.
3. Can I use another trading platform, not MarketWatch?
MarketWatch is the only platform supported.
4. Does the performance of my portfolio affect my assessment result?
No. Your assessment result will only depend on the analysis you make to support the investment decisions you make while running your portfolio, and the critical assessment of the outcome of these decisions.
5. In my assignment, do I need to discuss the impact of COVID-19 on stock markets?
No, at least not directly. Part 1 of your assignment should discuss the impact on stock markets due to the restrictions imposed by governments to fight COVID-19. You will also need to critically discuss the role of monetary and fiscal policies decisions in supporting domestic and global stock markets during the COVID-19 pandemic.
6. Which markets should I focus on when discussing the impact of COVID-19-related policies?
Any market is ok (e.g. US, UK, Europe, China, or global markets) as long as you have detailed data about this market and economic and financial policies taken by authorities in this market.
7. How many stocks should I include in my portfolio?
Between 5 and 20 stocks.
8. Can I use technical analysis as the only method for security selection in order to build my portfolio?
Fundamental analysis should play a major role in selecting stocks to build your portfolio. Technical analysis can then be used to inform trading decisions and rebalancing the portfolio.
9. Which model should I use to optimise my portfolio?
Use the model that you understand well and feel confident to implement. Also, make sure you can obtain the inputs needed to implement the model.
10. Which optimization model is easier to implement?
The Markowitz mean-variance model is the easiest one to implement, while the Black-Litterman (the manager’s views) model is the most challenging one. The Treynor-Black model (the Single Index Model) comes in between.
11. Can I use historical stock returns to estimate the expected returns and covariance matrix need to implement the Markowitz model?
Yes.
12. Which rate should I use as the risk-free rate to estimate excess returns?
The yield on the 10-year Treasury Bill of the US Government.
13. Can excess returns be negative?
Yes.
14. What should I do if the optimisation model I used suggested zero or negative allocations to some stocks?
The minimum weight you can allocate to a given stock in your portfolio should be 5%. To meet this constraint, make sure to add a constraint when solving the optimization model.
15. So, if negative weight is not allowed to construct the portfolio, why is short selling allowed on MarketWatch?
Short selling is allowed just in case someone wants to make some money during trading and rebalancing. But should not be used in constructing the portfolio.
16. Are there anu spreadsheets or solvers that I can use to optimise my portfolio without the need to build them myself?
Yes.
You can use this spreadsheet if you decided to use the Markowitz model: The Markowitz Model.xlsx [for more details see BKM (11th Ed) page 213]
You can use this spreadsheet if you decided to use the Single Index Model: The Treynor-Black Model.xlsx [ for more details see BKM (11th Ed) page 268]
Or you can use this website for any one of the three models: Portfolio Visualizer
17. Which portfolio management approach (active, semi-active, or passive) should I follow?
Any approach is ok, but you might need to deviate slightly from the benchmark (S&P 500) to meet the return target of 1% above the holding period return of S&P 500.
18. Which rebalancing strategy should I use?
The percentage of portfolio rebalancing will be more suitable given the short time horizon of only 8 weeks. For simplicity you could use 10% to identify the corridor. Other factors are still ok with proper justification.
19. Which method should I use to measure the performance of my portfolio?
Given the time horizon, you can treat the investment period as a single period and use the holding period return. You can also use the money-weighted rate of return method with appropriate assumptions and calculations.
20. What level should I use for performance attribution of my portfolio performance (e.g., asset class, sector, industry, stock)?
This will depend on what stocks/ETFs/Funds you included in your portfolio. The sector level would provide a reasonable comparison with the sectors of the S&P500, but other ways can be used with proper justification.
21. Which appraisal measures should I use for the performance appraisal of my portfolio?
At least use the Sharpe Ratio and the Information Ratio. Compare them to other well know index funds or the average market ratios.
22. How to estimate my portfolio’s holding period return?
Usse the net worth of your portfolio on MarketWatch.
Portfolio holding period return = [ (Net worth on 18-12-2020 / $5,000,000) -1]
See this excel template here: Performance Evaluation-1.xlsx
23. How to estimate my portfolio’s standard deviation (total risk measure)?
Use daily returns of your portfolio to estimate the standard deviation.
See this excel template here: Performance Evaluation-1.xlsx
23. How to estimate my portfolio’s active return (Alpha)?
Portfolio active return = Daily return of the portfolio – Daily return of its benchmark (S&P 500).
See this excel template here: Performance Evaluation-1.xlsx
25. How to estimate my portfolio’s active risk?
Use the standard deviation of active return.
See this excel template here: Performance Evaluation-1.xlsx
26. How to estimate my portfolio’s Sharpe Ratio?
Sharpe Ratio = (Portfolio Average Daily Return – Average Daily Risk-Free Rate) / Portfolio Daily Standard Deviation.
See this excel template here: Performance Evaluation-1.xlsx
27. How to estimate my portfolio’s Information Ratio?
Information Ratio = Portfolio Average Daily Active Return – Portfolio Daily Active Risk.
See this excel template here: Performance Evaluation-1.xlsx
28. Which sectors can I use in my portfolio’s performance attribution?
Use the 11 sectors of the S&P 500 according to the GICS classification.
29. Where can I get the data on the benchmark sector returns and weights?
Data on the different indexes that track the 11 sectors of the S&P 500 can be obtained from:
S&P Global: here
and TradingView here.
30. Which fund or ETF should I compare my portfolio performance to?
Use any one of the following funds/ETFs:
Fidelity 500 Index Fund. More information here: Factsheet FXAIX.O 2020-12-17-18-14-41.pdf
Vanguard 500 Index Fund. More information here: Factsheet VFINX.O 2020-12-17-18-28-01.pdf
SPDR S&P 500 ETF Trust. More information here: Factsheet SPY 2020-12-17-18-30-45.pdf
Markowitz Model
Efficient Frontier for World Equity Benchmark Securities (WEBS)
Mean Standard LEGEND:
WEBS Return Deviation Country Enter data
EWD 15.5393 26.4666 Sweden Value calculated
EWH 6.3852 41.1475 Hong Kong See comment
EWI 26.5999 26.0514 Italy
EWJ 1.4133 26.0709 Japan
EWL 18.0745 21.6918 Switzerland
EWP 16.6347 25.0779 Spain
EWW 16.2243 38.7686 Mexico
S&P 500 17.2306 17.1944 U.S.
Correlation Matrix
EWD EWH EWI EWJ EWL EWP EWW S&P 500
EWD 1.0000 0.4496 0.6062 0.4539 0.5898 0.6977 0.5427 0.5665
EWH 0.4496 1.0000 0.3399 0.6295 0.5039 0.5753 0.7752 0.6672
EWI 0.6062 0.3399 1.0000 0.1503 0.6867 0.7125 0.4711 0.4002
EWJ 0.4500 0.6295 0.1503 1.0000 0.4951 0.4287 0.4852 0.6066
EWL 0.5898 0.5039 0.6867 0.4951 1.0000 0.7708 0.4633 0.6294
EWP 0.6977 0.5753 0.7125 0.4287 0.7708 1.0000 0.5406 0.6378
EWW 0.5427 0.7752 0.4711 0.4852 0.4633 0.5406 1.0000 0.7243
S&P 500 0.5665 0.6672 0.4002 0.6066 0.6294 0.6378 0.7243 1.0000
Covariance Matrix
EWD EWH EWI EWJ EWL EWP EWW S&P 500
EWD 700.4814 489.5784 417.9441 313.1641 338.6342 463.1149 556.8682 257.8227
EWH 489.5784 1693.1161 364.3733 675.2964 449.7674 593.6221 1236.5777 472.0434
EWI 417.9441 364.3733 678.6757 102.0882 388.0346 465.4811 475.7823 179.2474
EWJ 310.5041 675.2964 102.0882 679.6933 280.0137 280.3160 490.4024 271.9451
EWL 338.6342 449.7674 388.0346 280.0137 470.5337 419.3265 389.6314 234.7461
EWP 463.1149 593.6221 465.4811 280.3160 419.3265 628.9036 525.5507 275.0026
EWW 556.8682 1236.5777 475.7823 490.4024 389.6314 525.5507 1503.0060 482.8325
S&P 500 257.8227 472.0434 179.2474 271.9451 234.7461 275.0026 482.8325 295.6475
Bordered Covariance Matrix for Optimal Portfolio
EWD EWH EWI EWJ EWL EWP EWW S&P 500
Weights 0.1202 0.0500 0.2000 0.0500 0.2000 0.1298 0.0500 0.2000
0.1202 10.1156 2.9416 10.0449 1.8817 8.1388 7.2254 3.3460 6.1965
0.0500 2.9416 4.2328 3.6437 1.6882 4.4977 3.8535 3.0914 4.7204
0.2000 10.0449 3.6437 27.1470 1.0209 15.5214 12.0867 4.7578 7.1699
0.0500 1.8657 1.6882 1.0209 1.6992 2.8001 1.8197 1.2260 2.7195
0.2000 8.1388 4.4977 15.5214 2.8001 18.8213 10.8883 3.8963 9.3898
0.1298 7.2254 3.8535 12.0867 1.8197 10.8883 10.6008 3.4116 7.1408
0.0500 3.3460 3.0914 4.7578 1.2260 3.8963 3.4116 3.7575 4.8283
0.2000 6.1965 4.7204 7.1699 2.7195 9.3898 7.1408 4.8283 11.8259
1.0000 49.8745 28.6695 81.3924 14.8553 73.9537 57.0268 28.3150 53.9911
Portfolio Variance 388.0783
Portfolio Standard Deviation 19.6997
Portfolio Mean 17.6092
Sharpe Ratio 0.8939
Bordered Covariance Matrix: Equally Weighted Portfolio
EWD EWH EWI EWJ EWL EWP EWW S&P 500
Weights 0.125 0.125 0.125 0.125 0.125 0.125 0.125 0.125
0.125 10.9450 7.6497 6.5304 4.8932 5.2912 7.2362 8.7011 4.0285
0.125 7.6497 26.4549 5.6933 10.5515 7.0276 9.2753 19.3215 7.3757
0.125 6.5304 5.6933 10.6043 1.5951 6.0630 7.2731 7.4341 2.8007
0.125 4.8516 10.5515 1.5951 10.6202 4.3752 4.3799 7.6625 4.2491
0.125 5.2912 7.0276 6.0630 4.3752 7.3521 6.5520 6.0880 3.6679
0.125 7.2362 9.2753 7.2731 4.3799 6.5520 9.8266 8.2117 4.2969
0.125 8.7011 19.3215 7.4341 7.6625 6.0880 8.2117 23.4845 7.5443
0.125 4.0285 7.3757 2.8007 4.2491 3.6679 4.2969 7.5443 4.6195
1.000 55.2336 93.3496 47.9942 48.3269 46.4170 57.0518 88.4477 38.5826
Portfolio Variance 475.4033
Portfolio Standard Deviation 21.8037
Portfolio Mean 14.7627
Sharpe Ratio 0.6771
Step 1: collect data on the return of a number of stocks (this spreadsheet uses data for 8 stocks). This could be 5 years of monthly data or 60 observations in total.
Use these return to calculate Mean Return and Standard deviation.
Insert the calculated mean returns in cells B5:B12, and the calculated standard devaition in cells C5:C12.
Step 2: Use the data on returns to estimate the correlation between each pair of stocks and insert the results in this matrix.
In excel you can use the “CORREL” function.
For 8 stocks, this will require 32 calculations.
Step 3: The covariance matrix will be estimated automatically. You don’t need to do anything.
Step 4: Use “Solver” to calculate the optimal weights.
Make sure “Solver” is enabled in excel (or enable it from add-ons. If you don’t know how to do this, google it)
Go to the “Data” tab and click “Solver”. The Solver window will look as shown to the right >>>>
All settings are made for you, just click on “Solve” and then “OK” in the new window that will appear after the solution is found.
Use the weights that will appear in cells A40:A47 to build you portfolio on MarketWatch.
Step 5: This will generate the cretun, standard deviation and Sharpe Ratio of an optially-weighted portfolio.
Calculations are based on 8 stocks.
You may use these results for comparison with your optimal portfolio.
Table 8.1
Monthly Return 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
Date 2011-01 2011-02 2011-03 2011-04 2011-05 2011-06 2011-07 2011-08 2011-09 2011-10 2011-11 2011-12 2012-01 2012-02 2012-03 2012-04 2012-05 2012-06 2012-07 2012-08 2012-09 2012-10 2012-11 2012-12 2013-01 2013-02 2013-03 2013-04 2013-05 2013-06 2013-07 2013-08 2013-09 2013-10 2013-11 2013-12 2014-01 2014-02 2014-03 2014-04 2014-05 2014-06 2014-07 2014-08 2014-09 2014-10 2014-11 2014-12 2015-01 2015-02 2015-03 2015-04 2015-05 2015-06 2015-07 2015-08 2015-09 2015-10 2015-11 2015-12 AVERAGE
Market Index 0.017757 0.037065 0.002766 0.028388 -0.015135 -0.018595 -0.022869 -0.057672 -0.084873 0.113882 -0.006326 0.003702 0.053877 0.040586 0.023394 -0.007501 -0.066231 0.037656 0.009566 0.025365 0.025839 -0.015201 0.004893 0.012131 0.053593 0.007531 0.034474 0.014445 0.018866 -0.015339 0.052481 -0.026114 0.037277 0.038751 0.024447 0.025926 -0.030157 0.045673 0.003969 0.00146 0.019918 0.027741 -0.020724 0.039871 -0.025229 0.020976 0.02075 -0.003916 -0.027352 0.055748 -0.010639 0.008522 0.010223 -0.019258 0.011795 -0.060293 -0.033735 0.07386 0.001742 -0.023961 0.0082631
WMT 0.038281 -0.074045 0.007769 0.055992 0.010804 -0.037868 -0.008492 0.015831 -0.024253 0.092771 0.038334 0.020798 0.026574 -0.037758 0.042012 -0.038118 0.123345 0.058753 0.066856 -0.020146 0.015829 0.015431 -0.041289 -0.047505 0.024709 0.011066 0.06306 0.038121 -0.031209 -0.004977 0.046115 -0.058008 0.013228 0.036623 0.055005 -0.023037 -0.051159 -0.000232 0.029085 0.042715 -0.031162 -0.022344 -0.020048 0.032318 0.012748 -0.002815 0.147365 -0.01378 -0.01068 -0.012556 -0.014378 -0.051264 -0.042253 -0.044971 0.014503 -0.094215 0.001699 -0.117312 0.027252 0.048436 0.0049592333
TGT -0.089542 -0.038124 -0.048935 -0.018496 0.013649 -0.053097 0.097234 0.009122 -0.0509 0.116336 -0.032063 -0.028083 -0.008205 0.12103 0.027271 -0.006363 0.00396 0.004335 0.041575 0.061754 -0.010374 0.003312 -0.005378 -0.063128 0.020457 0.047371 0.086398 0.030325 -0.010121 -0.009509 0.034508 -0.105803 0.010383 0.01156 -0.007137 -0.010524 -0.104989 0.111259 -0.032959 0.020293 -0.074146 0.020766 0.0281 0.016481 0.043349 -0.01392 0.205035 0.025511 -0.030499 0.050608 0.068002 -0.039678 0.012712 0.029123 0.002395 -0.044039 0.012225 -0.018915 -0.054082 -0.000183 0.00628745
VZ 0.007753 0.035396 0.043279 -0.007371 -0.022699 0.007923 -0.039414 0.024595 0.017558 0.018378 0.020181 0.063345 -0.049053 0.011349 0.002549 0.068617 0.030504 0.066743 0.026303 -0.049637 0.060548 -0.010207 -0.012949 -0.019666 0.01926 0.066157 0.055508 0.106824 -0.100923 0.038066 -0.007053 -0.042841 -0.01508 0.092419 -0.01812 -0.009874 -0.012206 -0.009663 -0.00071 -0.006717 0.06882 -0.020816 0.041083 -0.0122 0.003312 0.016003 0.006366 -0.075611 -0.011316 0.08162 -0.016782 0.04833 -0.019926 -0.057241 0.015362 -0.01697 -0.054336 0.090339 -0.031203 0.015242 0.0086524667
T -0.050073 0.03015 0.077977 0.030409 0.013939 -0.004953 -0.05516 -0.026858 0.001405 0.042677 -0.011359 0.043478 -0.013097 0.039522 0.020322 0.067183 0.037586 0.043106 0.075015 -0.034655 0.02823 -0.071922 -0.014599 -0.012706 0.044887 0.031393 0.020921 0.032752 -0.066137 0.011418 0.00884 -0.041228 -0.000496 0.082578 -0.027848 -0.00162 -0.039449 -0.042217 0.09784 0.030881 -0.006743 -0.003301 0.019314 -0.018002 0.007909 0.001503 0.015099 -0.050894 -0.006154 0.049618 -0.055466 0.075144 -0.002987 0.028373 -0.009027 -0.044629 -0.018675 0.042871 0.004075 0.020278 0.0074406333
F -0.05143 -0.057526 -0.009902 0.037259 -0.035753 -0.075937 -0.114976 -0.089471 -0.130396 0.207759 -0.092566 0.015094 0.158722 -0.003821 0.007074 -0.092484 -0.06453 -0.092356 -0.037196 0.015334 0.054974 0.135817 0.024686 0.130604 0.007222 -0.027055 0.042023 0.042086 0.150785 -0.013693 0.097408 -0.041277 0.041801 0.019054 -0.002253 -0.096804 -0.022559 0.028243 0.013145 0.043069 0.017657 0.048462 -0.00571 0.022614 -0.150588 -0.039078 0.115995 -0.014922 -0.04149 0.110609 -0.01244 -0.011972 -0.039973 -0.010547 -0.002299 -0.065034 -0.021629 0.102332 -0.033111 -0.018448 0.0011767
GM -0.011438 -0.082218 -0.07516 0.03386 -0.008925 -0.045783 -0.088674 -0.132064 -0.160216 0.280871 -0.176502 -0.04791 0.184802 0.082664 -0.01482 -0.104014 -0.035483 -0.112212 -0.001207 0.082307 0.064874 0.119779 0.013602 0.113588 -0.026168 -0.034264 0.023878 0.108055 0.098698 -0.017414 0.076654 -0.050302 0.055258 0.026145 0.047673 0.055054 -0.117401 0.002826 -0.041384 0.001543 0.0026 0.058215 -0.06852 0.028677 -0.073663 -0.017107 0.06425 0.052946 -0.065797 0.143577 0.012933 -0.065267 0.025856 -0.063386 -0.054906 -0.065993 0.031929 0.162791 0.036252 -0.052253 0.0030284333
Monthly Return Date Market Index WMT TGT VZ T Ford GM
0 2011-01 0.017757 0.038281 -0.089542 0.007753 -0.050073 -0.05143 -0.011438
1 2011-02 0.037065 -0.074045 -0.038124 0.035396 0.03015 -0.057526 -0.082218 Table 8.1 Excel output: Regression statistics for the SCL of Ford
2 2011-03 0.002766 0.007769 -0.048935 0.043279 0.077977 -0.009902 -0.07516
3 2011-04 0.028388 0.055992 -0.018496 -0.007371 0.030409 0.037259 0.03386 Regression Statistics
4 2011-05 -0.015135 0.010804 0.013649 -0.022699 0.013939 -0.035753 -0.008925 Multiple R 0.6280
5 2011-06 -0.018595 -0.037868 -0.053097 0.007923 -0.004953 -0.075937 -0.045783 R Square 0.3943
6 2011-07 -0.022869 -0.008492 0.097234 -0.039414 -0.05516 -0.114976 -0.088674 Adjusted R Square 0.3839
7 2011-08 -0.057672 0.015831 0.009122 0.024595 -0.026858 -0.089471 -0.132064 Standard Error 0.0577
8 2011-09 -0.084873 -0.024253 -0.0509 0.017558 0.001405 -0.130396 -0.160216 Observations 60
9 2011-10 0.113882 0.092771 0.116336 0.018378 0.042677 0.207759 0.280871
10 2011-11 -0.006326 0.038334 -0.032063 0.020181 -0.011359 -0.092566 -0.176502
11 2011-12 0.003702 0.020798 -0.028083 0.063345 0.043478 0.015094 -0.04791 Coefficients Standard Error t Stat P-value
12 2012-01 0.053877 0.026574 -0.008205 -0.049053 -0.013097 0.158722 0.184802 Intercept -0.0098 0.0077 -1.2767 0.2068
13 2012-02 0.040586 -0.037758 0.12103 0.011349 0.039522 -0.003821 0.082664 Market index 1.3258 0.2157 6.1451 0.0000
14 2012-03 0.023394 0.042012 0.027271 0.002549 0.020322 0.007074 -0.01482
15 2012-04 -0.007501 -0.038118 -0.006363 0.068617 0.067183 -0.092484 -0.104014
16 2012-05 -0.066231 0.123345 0.00396 0.030504 0.037586 -0.06453 -0.035483
17 2012-06 0.037656 0.058753 0.004335 0.066743 0.043106 -0.092356 -0.112212
18 2012-07 0.009566 0.066856 0.041575 0.026303 0.075015 -0.037196 -0.001207
19 2012-08 0.025365 -0.020146 0.061754 -0.049637 -0.034655 0.015334 0.082307
20 2012-09 0.025839 0.015829 -0.010374 0.060548 0.02823 0.054974 0.064874
21 2012-10 -0.015201 0.015431 0.003312 -0.010207 -0.071922 0.135817 0.119779
22 2012-11 0.004893 -0.041289 -0.005378 -0.012949 -0.014599 0.024686 0.013602
23 2012-12 0.012131 -0.047505 -0.063128 -0.019666 -0.012706 0.130604 0.113588
24 2013-01 0.053593 0.024709 0.020457 0.01926 0.044887 0.007222 -0.026168
25 2013-02 0.007531 0.011066 0.047371 0.066157 0.031393 -0.027055 -0.034264
26 2013-03 0.034474 0.06306 0.086398 0.055508 0.020921 0.042023 0.023878
27 2013-04 0.014445 0.038121 0.030325 0.106824 0.032752 0.042086 0.108055
28 2013-05 0.018866 -0.031209 -0.010121 -0.100923 -0.066137 0.150785 0.098698
29 2013-06 -0.015339 -0.004977 -0.009509 0.038066 0.011418 -0.013693 -0.017414 IGNORE THIS (FOLLOWING) PART OF THE SPREADSHEET
30 2013-07 0.052481 0.046115 0.034508 -0.007053 0.00884 0.097408 0.076654
31 2013-08 -0.026114 -0.058008 -0.105803 -0.042841 -0.041228 -0.041277 -0.050302 ANOVA
32 2013-09 0.037277 0.013228 0.010383 -0.01508 -0.000496 0.041801 0.055258 df SS MS F Significance F
33 2013-10 0.038751 0.036623 0.01156 0.092419 0.082578 0.019054 0.026145 Regression 1 0.126 0.126 37.762 0.0000000785
34 2013-11 0.024447 0.055005 -0.007137 -0.01812 -0.027848 -0.002253 0.047673 Residual 58 0.193 0.003
35 2013-12 0.025926 -0.023037 -0.010524 -0.009874 -0.00162 -0.096804 0.055054 Total 59 0.319
36 2014-01 -0.030157 -0.051159 -0.104989 -0.012206 -0.039449 -0.022559 -0.117401
37 2014-02 0.045673 -0.000232 0.111259 -0.009663 -0.042217 0.028243 0.002826
38 2014-03 0.003969 0.029085 -0.032959 -0.00071 0.09784 0.013145 -0.041384
39 2014-04 0.00146 0.042715 0.020293 -0.006717 0.030881 0.043069 0.001543
40 2014-05 0.019918 -0.031162 -0.074146 0.06882 -0.006743 0.017657 0.0026
41 2014-06 0.027741 -0.022344 0.020766 -0.020816 -0.003301 0.048462 0.058215
42 2014-07 -0.020724 -0.020048 0.0281 0.041083 0.019314 -0.00571 -0.06852
43 2014-08 0.039871 0.032318 0.016481 -0.0122 -0.018002 0.022614 0.028677
44 2014-09 -0.025229 0.012748 0.043349 0.003312 0.007909 -0.150588 -0.073663
45 2014-10 0.020976 -0.002815 -0.01392 0.016003 0.001503 -0.039078 -0.017107
46 2014-11 0.02075 0.147365 0.205035 0.006366 0.015099 0.115995 0.06425
47 2014-12 -0.003916 -0.01378 0.025511 -0.075611 -0.050894 -0.014922 0.052946
48 2015-01 -0.027352 -0.01068 -0.030499 -0.011316 -0.006154 -0.04149 -0.065797
49 2015-02 0.055748 -0.012556 0.050608 0.08162 0.049618 0.110609 0.143577
50 2015-03 -0.010639 -0.014378 0.068002 -0.016782 -0.055466 -0.01244 0.012933
51 2015-04 0.008522 -0.051264 -0.039678 0.04833 0.075144 -0.011972 -0.065267
52 2015-05 0.010223 -0.042253 0.012712 -0.019926 -0.002987 -0.039973 0.025856
53 2015-06 -0.019258 -0.044971 0.029123 -0.057241 0.028373 -0.010547 -0.063386
54 2015-07 0.011795 0.014503 0.002395 0.015362 -0.009027 -0.002299 -0.054906
55 2015-08 -0.060293 -0.094215 -0.044039 -0.01697 -0.044629 -0.065034 -0.065993
56 2015-09 -0.033735 0.001699 0.012225 -0.054336 -0.018675 -0.021629 0.031929
57 2015-10 0.07386 -0.117312 -0.018915 0.090339 0.042871 0.102332 0.162791
58 2015-11 0.001742 0.027252 -0.054082 -0.031203 0.004075 -0.033111 0.036252
59 2015-12 -0.023961 0.048436 -0.000183 0.015242 0.020278 -0.018448 -0.052253
Monthly Return Date Market Index WMT TGT VZ T Ford GM
AVG 0.0082631 0.0049592333 0.00628745 0.0086524667 0.0074406333 0.0011767 0.0030284333
SD 0.034815872 0.0479832345 0.0555842757 0.0427573738 0.0391963737 0.0735037882 0.0858232101
min -0.084873 -0.117312 -0.105803 -0.100923 -0.071922 -0.150588 -0.176502
max 0.113882 0.147365 0.205035 0.106824 0.09784 0.207759 0.280871
max – min 0.198755 0.264677 0.310838 0.207747 0.169762 0.358347 0.457373
Spreadsheet 8.1
Panel A: Risk Parameters of the Investable Universe (annualized)
SD of Excess Return BETA SD of Systematic Component SD of Residual Correlation with the Market Index
Market Index 0.1206 1.0000 0.1206 0.0000 1.0000
WMT (Walmart) 0.1662 0.2095 0.0253 0.1657 0.1520
TGT (Target) 0.1925 0.5265 0.0635 0.1833 0.3298
VZ (Verizon) 0.1481 0.2375 0.0286 0.1466 0.1934
T (AT&T) 0.1358 0.2981 0.0360 0.1321 0.2648
F (Ford) 0.2546 1.3258 0.1599 0.1999 0.6280
GM (General Motors) 0.2973 1.6613 0.2004 0.2215 0.6739
Panel B: Correlation of Residuals
WMT TGT VZ T F GM
WMT (Walmart) 1
TGT (Target) 0.405 1
VZ (Verizon) 0.089 -0.071 1
T (AT&T) 0.193 -0.007 0.624 1
F (Ford) 0.095 0.077 -0.230 -0.200 1
GM (General Motors) 0.036 0.175 -0.320 -0.309 0.699 1
Panel C: The Index Model Covariance Matrix
Market Index WMT TGT VZ T F GM
Beta 1.0000 0.2095 0.5265 0.2375 0.2981 1.3258 1.6613
Market Index 1.0000 0.0145 0.0030 0.0077 0.0035 0.0043 0.0193 0.0242
WMT (Walmart) 0.2095 0.0030 0.0276 0.0016 0.0007 0.0009 0.0040 0.0051
TGT (Target) 0.5265 0.0077 0.0016 0.0371 0.0018 0.0023 0.0102 0.0127
VZ (Verizon) 0.2375 0.0035 0.0007 0.0018 0.0219 0.0010 0.0046 0.0057
T (AT&T) 0.2981 0.0043 0.0009 0.0023 0.0010 0.0184 0.0057 0.0072
F (Ford) 1.3258 0.0193 0.0040 0.0102 0.0046 0.0057 0.0648 0.0320
GM (General Motors) 1.6613 0.0242 0.0051 0.0127 0.0057 0.0072 0.0320 0.0884
Panel D: Macro Forecast and Forecasts of Alpha Values
market risk premium = 0.06
Market Index WMT TGT VZ T F GM
Alpha 0.0000 0.0150 -0.0100 -0.0050 0.0075 0.0120 0.0025
Beta 1.0000 0.2095 0.5265 0.2375 0.2981 1.3258 1.6613
Risk Premium 0.0600 0.0276 0.0216 0.0092 0.0254 0.0915 0.1022
Panel E: Computation of the Optimal Risky Portfolio
Market Index Active Pf A WMT TGT VZ T F GM Overall Pf
s2(e) 0.0275 0.0336 0.0215 0.0174 0.0399 0.0491
wi(0) = ai/s2(e) 0.7975 0.5463 -0.2975 -0.2328 0.4301 0.3004 0.0509
wi 1.0000 0.6850 -0.3730 -0.2918 0.5393 0.3767 0.0639
[wi(0)]2 0.4693 0.1392 0.0852 0.2908 0.1419 0.0041
aA 0.0242
s2(eA) 0.0303
E(RM)/s2(M) 4.1249
wA(0) 0.1933
bA 0.6441
w*(Optimal Risky Port) 0.8191 0.1809 0.1239 -0.0675 -0.0528 0.0975 0.0681 0.0116
Beta 1.0000 0.6441 0.2095 0.5265 0.2375 0.2981 1.3258 1.6613 0.9356
Risk Premium 0.0600 0.0628 0.0276 0.0216 0.0092 0.0254 0.0915 0.1022 0.0605
SD 0.1206 0.1907 0.1172
Sharpe Ratio 0.4975 0.3295 0.5165
Measurement & Appraisal
Performance Measurement Performance Appraisal
Portfolio Holding Period Return 63.32% Portfolio Average Daily return 1.44%
Benchmark Holding Period Return -100.00% Portfolio Daily Standard Deviation 4.75%
Portfolio Return Objective -99.00% Average Daily Risk Free Rate 0.0024%
Has the portfolio achieved the return objective? YES Benchmark Average Daily Return -2.50%
Average Daily Active Return (Alpha) 3.94%
Active Risk (St. Dev. of Daily Active Return) 16.86%
Sharpe Ratio 0.30
Required inputs Information Ratio 0.23
Trading Days Date Net Worth
Microsoft Office User: Microsoft Office User:
Import data of your own portfolio from MarketWatch Daily Return Cumulative Retun Risk-Free Rate (10Y US Treasury Yield)
Microsoft Office User: Microsoft Office User:
Use the 10-Year US Treasury Yield between 26/10/2020 and 18/12/2020. Make sure to include data for the days in column C only. Data Can be obtained from US Treasury website:
https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yieldYear&year=2020
Benchmark Value
Microsoft Office User: Microsoft Office User:
Use the S&P 500 index daily close between 26/10/2020 and 18/12/2020. Make sure to include data for the days in column C only. Data Can be obtained from Yahoo Finance:
https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC Return on Benchmark Cumulative Retun on Benchmark Active Return
37 12/18/2020 8166207.01 0.00% 63.32% 0.00% 0.00 -100.00% -100.00% 100.00%
36 12/17/2020 8166207.01 0.00% 63.32% 0.94% 3,722.48 0.58% 7.42% -0.58%
35 12/16/2021 8166207.01 0.00% 63.32% 0.92% 3,701.17 0.18% 6.80% -0.18%
34 12/15/2021 8166207.01 0.00% 63.32% 0.92% 3,694.62 1.29% 6.61% -1.29%
33 12/14/2022 8166207.01 0.00% 63.32% 0.90% 3,647.49 -0.44% 5.25% 0.44%
32 12/11/2020 8166207.01 -0.13% 63.32% 0.90% 3,663.46 -0.13% 5.72% -0.00%
31 12/10/2020 8176946.09 -2.34% 63.54% 0.92% 3,668.10 -0.13% 5.85% -2.21%
30 12/9/2020 8373153.81 -2.64% 67.46% 0.95% 3,672.82 -0.79% 5.99% -1.84%
29 12/8/2020 8599827.22 0.79% 72.00% 0.92% 3,702.25 0.28% 6.84% 0.51%
28 12/7/2020 8532285.21 10.83% 70.65% 0.94% 3,691.96 -0.19% 6.54% 11.03%
27 12/4/2020 7698302.79 -0.67% 53.97% 0.97% 3,699.12 0.88% 6.74% -1.55%
26 12/3/2020 7750291.75 7.61% 55.01% 0.92% 3,666.72 -0.06% 5.81% 7.67%
25 12/2/2020 7202526.55 -6.21% 44.05% 0.95% 3,669.01 0.18% 5.88% -6.39%
24 12/1/2020 7679138.08 -1.70% 53.58% 0.92% 3,662.45 1.13% 5.69% -2.82%
23 11/30/2020 7811640.12 -2.02% 56.23% 0.84% 3,621.63 -0.46% 4.51% -1.56%
22 11/27/2020 7972621.80 2.26% 59.45% 0.84% 3,638.35 0.24% 4.99% 2.02%
21 11/25/2020 7796772.06 11.48% 55.94% 0.88% 3,629.65 -0.16% 4.74% 11.64%
20 11/24/2020 6994012.53 11.99% 39.88% 0.88% 3,635.41 2.19% 4.91% 9.80%
19 11/20/2020 6245180.41 -1.41% 24.90% 0.83% 3,557.54 -0.68% 2.66% -0.73%
18 11/19/2020 6334468.01 2.68% 26.69% 0.86% 3,581.87 0.39% 3.36% 2.28%
17 11/18/2020 6169175.69 7.83% 23.38% 0.88% 3,567.79 -1.16% 2.95% 8.99%
16 11/17/2020 5721201.25 8.66% 14.42% 0.87% 3,609.53 -0.48% 4.16% 9.14%
15 11/16/2020 5264994.59 0.17% 5.30% 0.91% 3,626.91 1.16% 4.66% -1.00%
14 11/13/2020 5256183.49 1.54% 5.12% 0.89% 3,585.15 1.36% 3.46% 0.18%
13 11/12/2020 5176521.51 -3.49% 3.53% 0.88% 3,537.01 -1.00% 2.07% -2.49%
12 11/11/2020 5363712.65 5.61% 7.27% 0.98% 3,572.66 0.77% 3.10% 4.84%
11 11/10/2020 5078792.97 -5.14% 1.58% 0.98% 3,545.53 -0.14% 2.31% -5.00%
10 11/9/2020 5354084.36 -0.98% 7.08% 0.96% 3,550.50 1.17% 2.46% -2.15%
9 11/6/2020 5407322.90 2.99% 8.15% 0.83% 3,509.44 -0.03% 1.27% 3.02%
8 11/5/2020 5250427.15 7.22% 5.01% 0.79% 3,510.45 1.95% 1.30% 5.27%
7 11/4/2020 4896841.54 3.89% -2.06% 0.78% 3,443.44 4.02% -0.63% -0.13%
6 11/2/2020 4713456.57 2.52% -5.73% 0.87% 3,310.24 1.23% -4.48% 1.29%
5 10/30/2020 4597735.76 -7.90% -8.05% 0.88% 3,269.96 -1.21% -5.64% -6.68%
4 10/29/2020 4991914.22 1.08% -0.16% 0.85% 3,310.11 1.19% -4.48% -0.11%
3 10/28/2020 4938522.51 -2.75% -1.23% 0.79% 3,271.03 -3.53% -5.61% 0.78%
2 10/27/2020 5077997.41 0.77% 1.56% 0.79% 3,390.68 -0.30% -2.16% 1.08%
1 10/26/2020 5039026.30 0.78% 0.78% 0.81% 3,400.97 -1.86% -1.86% 2.64%
0 10/25/2020 5000000.00 3,465.39 0.00%
&”Helvetica Neue,Regular”&12&K000000&P
Attribution
Performance Attribution
(1) (2) (3) (4) (5) = (1) – (2) (6) = (5) * (4) (7) = (3) – (4) (8) = (7) * (1) (9) = (6) + (8)
Economic Sectors Portfolio Weight (%)
Microsoft Office User: Microsoft Office User:
Ues your own portfolio data. Sum the allocations to all stocks that belong to this sector. Insert zero in case the sector was not represented in your portfolio.
Sector Benchmark Weight (%)
Microsoft Office User: Microsoft Office User:
Based on the S&P 500 year-end sector representation on 31/12/2019
More inofrmation here: The S&P 500 Index Factsheet by S&P Global:
https://www.spglobal.com/spdji/en/indices/equity/sp-500/#overview
Portfolio Return (%)
Microsoft Office User: Microsoft Office User:
Ues your own portfolio data. Use the weighted average return of the stocks in your portfolio that belong to this sector. Insert zero in case the sector was not represented in your portfolio.
Sector Benchmark Return (%)
Microsoft Office User: Microsoft Office User:
Use TradingView to get information on the 11 indexes that track the 11 sectors.
Estimate the price return (change) on the 11 indexes between 26/10/2020 and 18/12/2020.
https://uk.tradingview.com/markets/indices/quotes-snp/ Active/Excess Weight Sector Allocation Excess Return Within- Sector Selection Total Value- Added
Information Technology 5.97 23.20 -0.79 -0.67 -17.23 0.12 -0.12 -0.01 0.11
Health Care 7.82 14.20 -3.60 -3.95 -6.38 0.25 0.35 0.03 0.28
Financials 2.90 12.95 0.46 -0.21 -10.05 0.02 0.67 0.02 0.04
Consumer Discretionary 31.78 9.75 1.92 1.97 22.03 0.43 -0.05 -0.02 0.42
Communication Services 7.15 10.39 0.37 0.14 -3.24 -0.00 0.23 0.02 0.01
Industrials 12.47 9.05 2.92 2.05 3.42 0.07 0.87 0.11 0.18
Consumer Staples 12.14 7.20 2.00 -0.30 4.94 -0.01 2.30 0.28 0.26
Energy 5.00 4.35 1.00 2.00 0.65 0.01 -1.00 -0.05 -0.04
Utilities 5.00 3.32 1.00 2.00 1.68 0.03 -1.00 -0.05 -0.02
Real Estate 8.64 2.93 0.46 -0.37 5.71 -0.02 0.83 0.07 0.05
Materials 1.13 2.65 0.14 0.00 -1.52 0.00 0.14 0.00 0.00
Total 100.00 100.00 1.07 -0.23 0.00 0.90 0.40 1.30
Required Inputs
−
−
−
−
−
−
−
−