Question

Instructions: The focus of this lab is portfolio theory and the impact of diversification. Use Microsoft Excel to complete this assignment. Submit your Excel file and Word file online in Blackboard for grading. There are up to 5 points possible to Exam 1. Due by Friday, Ianuary.25, 2019 by 11pm. Absolutely, no late work will be accepted. Suppose you have the following information about two securities St Expected Sta Return Deviation 20% 45% 1. Using Microsoft Excel, create a spreadsheet program for calculating the expected return and standard deviation for portfolios consisting of the two stocks. Use the following format. (Hint: You must enter the information for the stocks and the correlations in individual cells in Excel then setup a table similar to the one below. You should have enough rows such that the weight in Stock A increases in increments of.05 until you get to 1.) It will look ing like this in Excel. Be sure to program the formula using the cell references for full credit. Do not code in humbers St | Rho=-0.5 | Rho-0 | Rho= 0.5 Deviations wi ations --| Weight of Stock A 0.0 0.05 0.1 0.15 Return 1.00 2. Plot the standard deviation and expected returns of your portfolios against each other. What do you observe? Comment on the pattern and the potential for obtaining benefits from diversification depending on the correlation of your two assets. Include a discussion about the shape of the graph for each correlation. (i.e. is the line straight or curved) 3. What are the weight combinations of the minimum variance portfolios at the various correlations (i.e. what are the weights invested in Stock A and B for the minimum variance (least risky) portfolio when the correlation is -1, 0.5, 0, 0.5, and 1)? 4. What do you notice about the relationship between the weights in the portfolio and the correlations? That is what happens to your weights in Stock A and B as the correlation changes from -1 to +1? 5. Suppose there exists a large number of assets that are exact "clones" of asset A, i.e. they have the same expected return and the same standard deviation as stock A. What is the standard deviation of a large, well-diversified portfolio of these securities if the correlation between any pair of the assets is -1-0.5, 0, 0.5, and 1? What do you observe regarding the shape of the graph? (You will have to do this in another spreadsheet. Just repeat Part 2 with the new assets.) You will have to create a new graph. What are your new weight combinations of the minimum variance portfolio? What is the new relationship between the weights in Stock A and B as the correlation changes?

Answer 1

		Stock A=Asset 1,Stock B=Asset 2
		Return of asset 1=R1=12%
		Return of asset 2=R2=20%
		Standard deviation of asset 1=S1=15%
		Standard deviation of asset 2=S2=45%
		Correlation of asset 1 and 2=Corr(1,2)	Covariance(1,2)=Corr(1,2)*S1*S2	S1*2=15*45=675
		-1	-675
		-0.5	-337.5
		0	0
		0.5	337.5
		1	675
		w1=Investment in asset 1(A)
		w2=Investment in asset 2(B)
		Portfolio Return
		w1*R1+w2*R2=w1*12+w2*20			……..Equation (1)
		Portfolio Variance=(w1^2)*(S1^2)+(w2^2)*(S2^2)+2*w1*w2*Cov(1,2)=45
		Portfolio Variance=(w1^2)*(15^2)+(w2^2)*(45^2)+2*w1*w2*Cov(1,2)
		Portfolio Variance=(w1^2)*225+(w2^2)*2025+2*w1*w2*Cov(1,2)……………Equation (2)
		Portfolio Standard Deviation=Square root of Variance…..Eq(3)