Homework 8

1

HOMEWORK8:DUEJULY272016

Identify two stocks and name them as first and second stock. Download into an excelworksheet the historical price data on the two stocks from January 1, 2015 to July 22,2016 fromwww.finance.yahoo.com. Compute daily returns on the two stocks for eachtrading day. Now, using the CORREL function in MS Excel, compute the correlationbetween the returns on the two stocks. Also compute the expected return and the standarddeviation of returns on each of the two stocks using the AVERAGE and the STDEVfunction in MS Excel. Annualize the expected returns and the standard deviations of thetwo stocks by assuming that the number of trading days in the year is 252.Now suppose Ms. X has 9000 shares in the first stock and Mr. Y has 4500 shares in thesecond stock. Ms. X and Mr. Y decide to get married, and want to allocate theircombined wealth between the two stocks in such a way that the standard deviation of thereturns on their portfolio is minimized.(a):Work out the number of shares in each of the two stocks that the risk minimizingportfolio will contain.(b):Work out E(Wealth1), the expected value of their portfolio att=1 (one year fromtoday)(c):Work out the range within which theirt=1 wealth will lie with a probability of 95%assuming that the future wealth is normally distributed.1(d):Check that your answer for the risk minimizing portfolio weights does in factminimize the risk with the help ofthe MS Excel file 2-SecurityPortfolios.xls on bspace

by entering your solution in an appropriate row in that file, and showing that the portfoliorisk increases if the portfolio weights differ from the optimal by 0.001 in either direction.

Print out the appropriate area of the Excel file and attach it with your solution.Note

that you will lose 60% of the credit for this homework if you do notattach a printout of the appropriate part of Excel spreadsheet thatshows that you have verified that your portfolio weights in factminimize the risk.

1The lower end of the 95% confidence interval is [E(Wealth1)2*SD(Wealth1)] and the upper end is

[E(Wealth1) + 2*SD(Wealth1)].

HAAS SCHOOL OF BUSINESS UNIVERSITY OF CALIFORNIA AT BERKELEY UGBA 103 AVINASH VERMA HOMEWORK 8: DUE JULY 27 2016 Identify two stocks and name them as first and second stock. Download into an excel worksheet the historical price data on the two stocks from January 1, 2015 to July 22, 2016 from www.finance.yahoo.com. Compute daily returns on the two stocks for each trading day. Now, using the CORREL function in MS Excel, compute the correlation between the returns on the two stocks. Also compute the expected return and the standard deviation of returns on each of the two stocks using the AVERAGE and the STDEV function in MS Excel. Annualize the expected returns and the standard deviations of the two stocks by assuming that the number of trading days in the year is 252. Now suppose Ms. X has 9000 shares in the first stock and Mr. Y has 4500 shares in the second stock. Ms. X and Mr. Y decide to get married, and want to allocate their combined wealth between the two stocks in such a way that the standard deviation of the returns on their portfolio is minimized. (a): Work out the number of shares in each of the two stocks that the risk minimizing portfolio will contain. (b): Work out E(Wealth1), the expected value of their portfolio at t=1 (one year from today) (c): Work out the range within which their t=1 wealth will lie with a probability of 95% assuming that the future wealth is normally distributed.1 (d): Check that your answer for the risk minimizing portfolio weights does in fact minimize the risk with the help of the MS Excel file \"2-Security Portfolios.xls\" on bspace by entering your solution in an appropriate row in that file, and showing that the portfolio risk increases if the portfolio weights differ from the optimal by 0.001 in either direction. Print out the appropriate area of the Excel file and attach it with your solution. Note that you will lose 60% of the credit for this homework if you do not attach a printout of the appropriate part of Excel spreadsheet that shows that you have verified that your portfolio weights in fact minimize the risk. 1 The lower end of the 95% confidence interval is [E(Wealth1) - 2*SD(Wealth1)] and the upper end is [E(Wealth1) + 2*SD(Wealth1)]. Homework 8 1