Norwich Business School 2
Investment and Risk Management (NBS-7077B)
IT Lab 2 Handout (Lectures 2, 3 & 4; BKM Chapters 5, 6, 7 & 8)
Part 1: ‘Efficient Frontier 3 Stocks’ spreadsheet
a. We are now attempting to find the efficient frontier for portfolios considering all three stocks. We first need to determine what portfolio returns (target mean returns) we can achieve by investing positive amounts in the three stocks above. For this example, find 6 equally spaced return values which cover the full range of achievable portfolio returns. Write these returns in cells M5 to M10 (hint: use full percentage points).
Answer:
Target Mean Return |
7% |
9% |
12% |
15% |
18% |
21% |
b. Using Solver, compute the stock weights corresponding to the minimum risk portfolio given the constraint that the return portfolio is equal to the first targeted mean return (hint: you have to set the portfolio standard deviation to minimum by changing the percentage invested in the three companies + constraints). Copy the stock weights (cells H5 to J5) in cells G18 to I18.
and
c. Repeat point b above for the other targeted mean returns and copy each time the stock weights below the previous weights populating thus the table in cells G18 to I23.
Answer:
Percentage in Newmont | Percentage in Ford | Percentage in Caterpillar |
0% | 0% | 100% |
13% | 7% | 79% |
19% | 23% | 58% |
24% | 39% | 36% |
30% | 55% | 15% |
100% | 0% | 0% |
d. In cells J18:L23, compute the variance, the standard deviation, the mean return of each portfolio.
Variance | Standard Deviation | Mean Return | Sharpe ratio |
0.07662 | 27.68% | 20.85% | 0.71 |
0.06063 | 24.62% | 17.99% | 0.69 |
0.05073 | 22.52% | 15.12% | 0.62 |
0.04559 | 21.35% | 12.26% | 0.52 |
0.04518 | 21.26% | 9.40% | 0.39 |
0.14010 | 37.43% | 6.54% | 0.15 |
Description:
Here we have a statistical view of all variables which will use in managing risk on investment. Variables we have determined are variance, standard deviation, expected return, and sharp ratio. All the investments have relatively low expected return as compare to 20.85%. as we can see expected return increasing the risk, which is mention as standard deviation, also increasing except few investments.
Part 2: ‘Minimum Variance 3 Stocks’ spreadsheet
e. Using Solver, compute the stock weights corresponding to the minimum variance portfolio (hint: you have to set the portfolio standard deviation to minimum by changing the percentage invested in the three companies + constraints).
DESCRIPTIVE STATISTICS | ||||
Newmont | Ford | Caterpillar | Minimum Variance Portfolio | |
Portfolio weights | 27.42% | 48.81% | 23.77% | 100.00% |
Mean | 6.54% | 7.87% | 20.85% | 10.59% |
Standard deviation | 37.48% | 26.44% | 27.68% | 21.16% |
Variance | 0.1405 | 0.0699 | 0.0766 | 0.0448 |
Sharpe ratio | 0.15 | 0.26 | 0.71 | 0.45 |
Description:
We have a clear results of portfolio weights with their stock weights. The minimum variance of portfolio of portfolio weight is 100%. The other variables remain same, as calculated before including mean, standard deviation, variance, and sharp ratio. It can be computed on the basis of factors which includes units, securities, cost, regions and sectors. It is calculated by as dividing single asset value by total value of the portfolio. There is a reason that is very common but have to mention, of accumulated portfolio weights of three investments to be 100%. As portfolio weight is determine by diving the single stock weight by the total portfolio weight, therefore by combining all parts of single portfolio is become 100%. The high portfolio weight is Ford, of 48.81%. Which means Ford have the highest percentage of investment portfolio.
Part 3: ‘Optimal Risky 3 Stocks’ spreadsheet
f. Using Solver, compute the stock weights corresponding to the optimal risky portfolio (hint: you have to set the portfolio Sharpe ratio to maximum by changing the percentage invested in the three companies + constraints).
DESCRIPTIVE STATISTICS | ||||
Newmont | Ford | Caterpillar | Optimal Risky Portfolio | |
Portfolio weights | 0.00% | 0.00% | 100.00% | 100.00% |
Mean | 6.54% | 7.87% | 20.85% | 20.85% |
Standard deviation | 37.48% | 26.44% | 27.68% | 27.68% |
Variance | 0.1405 | 0.0699 | 0.0766 | 0.0766 |
Sharpe ratio | 0.15 | 0.26 | 0.71 | 0.71 |
Part 4: ‘Higher-Order Statistics’ spreadsheet Up to this point, we have assumed investors only care about expected return and risk (standard deviation). This is only true if returns are assumed to be normally distributed (e.g. probability of achieving a -1% return is the same as the probability of achieving a +1% return, same for -2% vs. +2%, -3% vs. +3%, etc.). We will test whether this is true for the Newmont returns.
h. In cells H2:H3, compute the minimum and maximum return that can be observed for Newmont over the sample period (hint: you can use the following Excel functions: MIN and MAX).
Newmont | |
Minimum Return | -26.43% |
Maximum Return | 33.24% |
Description:
Here we have calculate the minimum return and the higher return that could be generate. This is calculated by using Excel function. Maximum return represents the how much higher the expected return can be. On the other hand the minimum return represents the tendency of how low return could be. The minimum return is -26.43%, which means that it could also have to face loss of 26.43%, while investing in this portfolio.
I. In cells H5:H19, find 15 equally spaced returns which cover the full range of the Newmont returns
Return Range | 33.24% |
28.98% | |
24.72% | |
20.46% | |
16.19% | |
11.93% | |
7.67% | |
3.41% | |
-0.85% | |
-5.12% | |
-9.38% | |
-13.64% | |
-17.90% | |
-22.17% | |
-26.43% |
j. Create a histogram (distribution of returns) for Newmont using the Histogram tool in Excel: Data > Data Analysis > Histogram (hint: select the Newmont returns in the Input Range box; select the return range in the Bin Range box; select cell J2 in the Output Range box). Does the distribution of returns for Newmont match a normal distribution?
Answer:
Bin | Frequency |
-26.43% | 0 |
-22.17% | 1 |
-17.90% | 2 |
-13.64% | 5 |
-9.38% | 6 |
-5.12% | 15 |
-0.85% | 17 |
3.41% | 18 |
7.67% | 11 |
11.93% | 8 |
16.19% | 6 |
20.46% | 0 |
24.72% | 3 |
28.98% | 0 |
33.24% | 3 |
k. In cells H22:K23, estimate the monthly skewness and kurtosis for the three stocks and the portfolio (hint: you can use the following Excel functions: SKEW and KURT)
Answer:
MONTHLY DESCRIPTIVE STATISTICS | ||||
Newmont | Ford | Caterpillar | Portfolio | |
Skewness | 0.54 | 0.51 | 0.29 | 0.31 |
Kurtosis | 1.07 | 1.01 | 0.73 | -0.16 |
l. In cells H33:K34, calculate the annual skewness and kurtosis from corresponding monthly values.
Answer:
ANNUAL DESCRIPTIVE STATISTICS | ||||
Newmont | Ford | Caterpillar | Portfolio | |
Portfolio weights | 33.33% | 33.33% | 33.33% | 100.00% |
Mean | 6.54% | 7.87% | 20.85% | 11.75% |
Standard deviation | 37.48% | 26.44% | 27.68% | 21.59% |
Variance | 0.1405 | 0.0699 | 0.0766 | 0.0466 |
Skewness | 0.1563 | 0.1485 | 0.0845 | 0.0881 |
Kurtosis | 0.09 | 0.08 | 0.06 | -0.01 |
Sharpe ratio | 0.1455 | 0.2569 | 0.7142 | 0.4944 |
Adjusted Sharpe ratio | 0.1461 | 0.2585 | 0.7204 | 0.4980 |
m. Estimating the skewness and kurtosis values allows us to estimate the Adjusted Sharpe ratio which takes into account the whole shape of the distribution of returns. Using Solver, compute the stock weights corresponding to the optimal risky portfolio considering the Adjusted Sharpe ratio (instead of the Sharpe ratio).
Answer:
ANNUAL DESCRIPTIVE STATISTICS | ||||
Newmont | Ford | Caterpillar | Portfolio | |
Portfolio weights | 0.37% | 0.00% | 99.63% | 100.00% |
Mean | 6.54% | 7.87% | 20.85% | 20.79% |
Standard deviation | 37.48% | 26.44% | 27.68% | 27.61% |
Variance | 0.1405 | 0.0699 | 0.0766 | 0.0762 |
Skewness | 0.1563 | 0.1485 | 0.0845 | 0.0848 |
Kurtosis | 0.09 | 0.08 | 0.06 | 0.06 |
Sharpe ratio | 0.1455 | 0.2569 | 0.7142 | 0.7141 |
Adjusted Sharpe ratio | 0.1461 | 0.2585 | 0.7204 | 0.7204 |
Part 5: ‘Efficient Frontier Matrix’ spreadsheet Although estimating the variance of a portfolio of three stocks is still manageable, doing the same for a portfolio of 5 or more stocks quickly becomes impractical. Moreover, the Solver optimization tool might lead to imprecise estimates. We must find a more convenient way to estimate the portfolio variance.
n. Estimate the correlation matrix for the three stocks. Data > Data Analysis > Correlation (hint: select the return series in the Input Range box; select cell F11 in the Output Range box). Complete the upper triangular part of the correlation matrix with the corresponding symmetrical values.
Answer:
Correlation Matrix | |||
Newmont | Ford | Caterpillar | |
Newmont | 1 | ||
Ford | 0.022725304 | 1 | |
Caterpillar | 0.20859828 | 0.577618974 | 1 |
o. Complete the lower triangular part and the diagonal of the variance-covariance matrix in cells G18:I20.
Answer:
Variance – Covariance Matrix | |||
Newmont | Ford | Caterpillar | |
Newmont | 0.011586113 | ||
Ford | 0.000185689 | 0.005762561 | |
Caterpillar | 0.00178463 | 0.003485121 | 0.006317383 |
p. Using Solver, compute the stock weights corresponding to the minimum risk portfolios given the constraint that the return portfolio is equal in turn to the targeted mean return in cells M35:M40 (hint: you have to set the portfolio standard deviation to minimum by changing the percentage invested in the three companies + constraints). Each time, copy the stock weights (cells G24:I24) and the portfolio statistics (cells G31:J31) in the opportunity set table.
Answer: