Finance 360
FIN360 - 03 and 04
Excel Assignment #2
Assignment value: 25 pts. spreadsheet with calculations, 25 pts. this form (please be neat)
Completed Assignment Due Date: Monday, March 27th, 2017 (midnight) spreadsheet portion, to be turned in via Isidore (Assignments Tab). Tuesday, March 28th, 2017 (beginning of class) this form.
Note: This is an individual assignment
Name:______________________________________________________
Risk and Return of the Risky Portfolio
Find the spreadsheet labeled “Optimal Portfolio Weights – 2017 - for students” in the Resources section of our class’ Isidore page.
Construction of the investment opportunities set: In the tab labeled "Risk and Return Info" you will find the holding period returns you calculated during Excel Assignment #1. You will also find arithmetic average returns, standard deviations, variance, covariance and correlation coefficients.
Use this data to construct the investment opportunities set, which is the set of all possible portfolios that can be built with different weights of TSLA and GM. Calculate the standard deviation and expected returns of 100 portfolios with weights of TSLA ranging between 0% and 100% in increments of 1%.
Graph the investment opportunities set in a mean-variance graph. This is, a chart in which standard deviation is recorded in the horizontal axis and expected return is recorded in the vertical axis.
Copy the figure in the space below. What shape does the investment opportunities set typically take?
Use the spreadsheet to perform sensitivity analysis on the shape of the investment opportunities set relative to the correlation coefficient. Recalculate the standard deviation of the investment opportunities set is correlation coefficients of +1, +0.5, 0. -0.5, and -1. Describe what happens to the shape of the curve and the correlation coefficient declines.
Calculate the weights of the minimum variance portfolio. Use the formulas in the footnote of page 156 in your textbook to calculate these weights. Use these weights to calculate the expected return, standard deviation, and Sharpe ratio of the minimum-variance portfolio. Draw the Capital Allocation line of the minimum-variance portfolio along with the Investment Opportunities set in the mean-variance graph. Copy your figure in the space below and explain what does the minimum-variance portfolio represent? Is this portfolio mean-variance efficient?
Calculate the weights of the optimal portfolio. Use equation 6.10 in page 160 in your textbook to calculate these weights. Use these weights to calculate the expected return, standard deviation and Sharpe ratio of this portfolio. Draw the Capital Allocation line of the optimal portfolio along with the Investment Opportunities set in the mean-variance graph. Copy your figure in the space below and explain what does the minimum-variance portfolio represent? Is this portfolio mean-variance efficient? How do you know?
