Add a column in your Excel worksheet with the average return across stocks for each month. This is the monthly return to an equally weighted portfolio of these 12 stocks. Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Double check that the average return on this equally weighted portfolio is equal to the average return of all of the individual stocks. Convert these monthly statistics to annual statistics (as described in Step 3) for interpretation.
2022 Corporate Assignment 1 Corporate Finance Academic year 2021-2022 A. Claes. Assignment: Risk and Return Analysis of a Stock Portfolio. Today is February 1st, 2022, and you have just started your new job with a financial planning firm. You have been asked to review a portion of a client’s stock portfolio to determine the risk/return profiles of 12 stocks in the portfolio. Unfortunately, your small firm cannot afford the expensive databases that would provide all this information with a few simple keystrokes, but that’s why they hired you. Specifically, you have been asked: 1) to determine the monthly average returns and standard deviations for the 12 stocks for the past ten years. The 12 stocks (with their ticker) are: Company Symbol 1 Archer Daniels Midland ADM 2 Boeing BA 3 Caterpillar CAT 4 Deere & Co. DE 5 General Mills, Inc. GIS 6 eBay EBAY 7 Hershey HSY 8 International Business Machines Corporation IBM 9 JPMorgan Chase & Co. JPM 10 Microsoft MSFT 11 Your group’s stock (see Table 1 on page 5) 12 A stock you select (that is neither in the list above (stocks 1 to 10), nor in the list on page 5-6). 2) to update the stock portfolio by: - rebalancing the portfolio with the optimum weights that will provide the best risk and return combinations for the new 12-stock portfolio; - determining the improvement in the return and risk that would result from these optimum weights compared to the method of equally weighting the stocks in the portfolio. Part 1 (Using what you learned in chapter 10): 1. Collect price information for each stock from Yahoo! Finance (finance.yahoo.com) as follows: a. Enter the stock symbol. On the page for that stock, click “Historical Data.” b. For the time period, enter the “start date” as March 1, 2012 and the “end date” as February 1, 2022 to cover the ten-year period. Choose the “monthly” frequency; the closing prices re-ported by Yahoo! will then be for the last day of each month. c. After hitting “Apply,” click “Download Data.” d. Open the downloaded data in an Excel spreadsheet (look at the short video should you need any help). Copy the column with the data of the adjusted close and add this column to your portfolio. 2 (Remark: A large part of the data collection is already done for you in the work file Assignment Students.xls – except for the stock that you need to add and the stock of your choice (i.e. stocks nr. 11 and 12)) 2. Convert these prices to monthly returns as the percentage change in the monthly- adjusted prices (Hint: Create a separate worksheet within the Excel file). Note that to compute a return for each month, you need a beginning and ending price, so you will not be able to compute the return for the first month.1 3. Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the monthly statistics to annual statistics for easier interpretation (Hint: multiply the mean monthly return by 12 (as in 12 months), and multiply the monthly standard deviation by square root of 12). 4. Add a column in your Excel worksheet with the average return across stocks for each month. This is the monthly return to an equally weighted portfolio of your 12 stocks. Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Double check that the average return on this equally weighted portfolio is equal to the average return of all of the individual stocks. Convert these monthly statistics to annual statistics (as described in Step 3) for interpretation. 5. Using the annual statistics, create an Excel plot with standard deviation (volatility) on the x- axis and expected return on the y-axis as follows: a. Create three columns on your spreadsheet with the statistics you created in Questions 3 and 4 for each of the individual stocks and the equally weighted portfolio (EWP). The first column will have the ticker, the second will have annual standard deviation, and the third will have the annual mean return. b. Highlight the data in the last two columns (standard deviation and mean), choose Insert> Chart >XY Scatter Plot. Complete the chart wizard to finish the plot. (If you want to indicate the EWP in a different color on the graph, you can add one series, containing only the EWP). 6. What do you notice about the average of the volatilities of the individual stocks, compared to the volatility of the equally weighted portfolio? Part 2: (based on what you learned in chapter 11) Use the Solver function in Excel to perform this analysis (time-consuming alternative is to find the optimum weights by trial-and-error). 1. Begin calculating the expected return of your portfolio, and start from the equally weighted portfolio that you analyzed in part 1. Establish the portfolio using a formula that depends on the portfolio weights (you could e.g. use the function =sumprod(…)). Initially these weights will all equal 1/12. You would like to allow the portfolio weights to vary, so you will need to list the weights for each stock in separate cells and establish another cell that sums the weights of the stocks. The portfolio returns for each month must reference these weights for Excel Solver to be useful. 1 In chapter 10 we showed how to compute returns with stock price and dividend data. The “adjusted close” series from Yahoo! Finance is already adjusted for dividends and splits, so we may compute returns based on the percentage change in monthly-adjusted prices. 3 2. Then you can compute volatility of the portfolio. You will need to do this in several steps. a. Create a correlation matrix, in which you calculate the correlation of the monthly return series of the assets in your portfolio. b. Using the correlation matrix found in (a), create a covariance matrix, multiplying the correlation by the monthly volatility for each individual asset. c. Using the variance-covariance matrix found in (b), create a weighted variance- covariance matrix, multiplying the variance-covariance matrix by the weights (referring to the cells in which you indicated the weights of your equally weighted portfolio in Part 2.1 – that way, if you change the weights of your portfolio, the weighted variance covariance matrix and so later the volatility of your portfolio will automatically be adjusted). d. Calculate the portfolio variance by making the sum of all the cells in your weighted variance-covariance matrix. Calculate the volatility of the portfolio based on this variance. Since this volatility of the portfolio is still a monthly statistic, convert it to annual numbers (as you did in part 1) for easier interpretation. e. Compare the volatility of the portfolio you calculated here with the estimated volatility of the equally weighted portfolio you obtained in part 1. 3. Compute the efficient frontier when short sales are not allowed. Use the Solver tool in Excel (on the Data tab in the analysis section)2. To set the Solver parameters: a. Set the target cell as the cell of interest, making it the cell that computes the (annual) portfolio standard deviation. Minimize the value. b. Establish the “By Changing Cells” by holding the Control key and clicking in each of the 12 cells containing the weights of each stock. c. Add constraints by clicking the Add button next to the “Subject to the Constraints” box. One set of constraints will be the weight of each stock that is greater than or equal to zero. Enter the cells in which you indicated the weights of the portfolio into this constraint. A second constraint is that the weights will sum to one. d. Compute the portfolio with the lowest standard deviation. If the parameters are set correctly, you should get a solution when you click “Solve”. If there is an error, you will need to double-check the parameters, especially the constraints. 4. Next, compute portfolios that have the lowest standard deviation for a target level of the expected return. a. Start by finding the portfolio with an expected return 2% higher than that of the minimum variance portfolio. To do this, add a constraint that the (annual) portfolio return equals this target level. Click “Solve” and record the standard deviation and mean return of the solution (and be sure the mean return equals the target – if not, check your constraint). Keep the results in a column, saving the standard deviation and the expected return. b. Repeat Step (a) raising the target return (rounded to the nearest %) in 2% increments, recording the result for each step. Add the results found in the table you started in step 4.a above. Continue to increase the target return and record the result until Solver can no longer find a solution. c. At what level does Solver fail to find a solution? Why? 2 If the Solver tool is not available, you must load it into Excel as follows: (1) On the File Tab, click Excel Options. (2) Click Add- Ins, and then, in the Manage box, select Excel Add-Ins. (3) Click Go. (4) In the Add-Ins available box, select the Solver Add-in check box, and then click OK. If Solver Add-In is not listed in the Add-Ins available box, click Browse to locate the add-in. If you are prompted that the Solver Add-In is not currently installed on your computer, click Yes to install it. (5) After you load the Solver Add-In, the Solver command is available in the Analysis group on the Data tab. 4 d. Plot the results for the table in which you have the volatility and the expected return for the minimum variance portfolio and the return/volatility combinations found afterwards in