You are asked to do the following portfolio investment decision for an entry test in your job market interviewing for an investment firm.
In your portfolio, you want to diversify and invest stocks in different sectors:
• Four stocks total from following four sectors, one from each sector:
Technology; Financial; Consumer Goods; Utilities;
• Three more stocks from other sectors :
Services; Industrial Goods; Basic Materials; Real Estate; Healthcare
1) Download past 5 year(1/1/2016-1/1/2021) weekly stock prices for the above selected stocks;
2) Compute expected returns and create a variance-covariance matrix
For each of the following portfolio, please find portfolio allocation (weights), portfolio return and risk.
3) Find portfolio A: find the minimum variance portfolio without any imposed restriction. Copy and display your solver constraint window.
4) Find portfolio B: find a portfolio of maximum expected return without any imposed restriction. All weights are greater than 0 and less than 100 percent.
Copy and display your solver constraint window.
5) Find a customized portfolio C with maximum expected return according to the following criteria; note, you need additional cells to check information for criteria b,c & d before including these in the solver:
a) Weight in each stock is greater than 1% and less than 50%
b) The total Weight of Utility and Consumer Goods cannot exceed 40%
c) The different between the weight of Financial and Technology must be at least 3% and at most 15%.
d) use IF function or COUNTIF function to check how many stocks have weights 10% or more;
Make sure the at least two stocks have weights greater than 10%.
For each solver execution, please make a screenshot of your solver popup screen that list all constraints in your solver from part a to part g.
6) Find covariance between portfolio A and B.
7) Creating a list of portfolios based on portfolio A and B by changing the portfolio weights, find the efficient frontier, and display the efficient frontier on the chart.
8) Suppose the annual risk free rate is 1.5%, and then find the efficient portfolio (Tangency portfolio or the market portfolio). (Hint, notice the return frequency when compute Sharpie Ratio: for example, if your return is monthly, then you need to multiply 12 to convert to annual return).
9) If you are required to put not more than 35% at the risk free asset, find the efficient portfolio allocation.