Norwich Business School
Investment and Risk Management (NBS-7077B)
IT Lab 1 Handout (Lectures 2, 3 & 4; BKM Chapters 5, 6, 7 & 8)
Part 1: ‘Descriptive Statistics’ spreadsheet
a. In columns H to K, calculate the monthly return on each asset.
Answer:
MONTHLY RETURN HISTORY | ||||
Date | Newmont Return | Ford Return | Caterpillar Return | S&P 500 Return |
2/1/2010 | 14.98% | 8.30% | -8.85% | 2.85% |
3/1/2010 | 3.35% | 7.07% | 70.86% | 5.88% |
4/1/2010 | 10.33% | 3.58% | 0.33% | 1.48% |
5/1/2010 | -4.03% | -9.91% | -42.00% | -8.20% |
6/1/2010 | 14.72% | -14.07% | 0.57% | -5.39% |
7/1/2010 | -9.30% | 26.69% | 0.00% | 6.88% |
8/1/2010 | 9.70% | -11.59% | 0.00% | -4.74% |
9/1/2010 | 2.43% | 8.41% | 0.00% | 8.76% |
10/1/2010 | -2.87% | 15.44% | -5.71% | 3.69% |
11/1/2010 | -3.34% | 12.81% | -63.64% | -0.23% |
12/1/2010 | 4.42% | 5.33% | 0.00% | 6.53% |
1/1/2011 | -10.14% | -5.00% | -50.00% | 2.26% |
2/1/2011 | 0.36% | -5.64% | 0.00% | 3.20% |
3/1/2011 | -1.25% | -0.93% | 0.00% | -0.10% |
4/1/2011 | 7.70% | 3.76% | 0.00% | 2.85% |
5/1/2011 | -3.48% | -3.56% | 0.00% | -1.35% |
6/1/2011 | -4.60% | -7.57% | 0.00% | -1.83% |
7/1/2011 | 3.44% | -11.46% | -23.33% | -2.15% |
8/1/2011 | 12.61% | -8.93% | 0.00% | -5.68% |
9/1/2011 | 0.53% | -13.04% | 0.00% | -7.18% |
10/1/2011 | 6.66% | 20.79% | 0.00% | 10.77% |
11/1/2011 | 3.07% | -9.25% | -4.35% | -0.51% |
12/1/2011 | -12.88% | 1.51% | -9.09% | 0.85% |
1/1/2012 | 2.99% | 15.43% | 25.00% | 4.36% |
2/1/2012 | -3.33% | 0.07% | 56.00% | 4.06% |
3/1/2012 | -13.73% | 0.81% | 51.28% | 3.13% |
4/1/2012 | -6.47% | -9.62% | -23.73% | -0.75% |
5/1/2012 | -1.03% | -5.98% | -48.89% | -6.27% |
6/1/2012 | 2.86% | -9.19% | 17.39% | 3.96% |
7/1/2012 | -7.69% | -3.65% | -44.44% | 1.26% |
8/1/2012 | 13.96% | 1.08% | 53.33% | 1.98% |
9/1/2012 | 10.54% | 6.14% | 8.70% | 2.42% |
10/1/2012 | -1.89% | 13.18% | -4.00% | -1.98% |
11/1/2012 | -13.72% | 3.10% | -8.33% | 0.28% |
12/1/2012 | -1.38% | 13.10% | -31.82% | 0.71% |
1/1/2013 | -6.78% | 0.00% | 66.67% | 5.04% |
2/1/2013 | -6.22% | -1.91% | -20.00% | 1.11% |
3/1/2013 | 3.97% | 4.28% | 0.00% | 3.60% |
4/1/2013 | -21.81% | 4.26% | 10.00% | 1.81% |
5/1/2013 | 5.80% | 14.37% | -9.09% | 2.08% |
6/1/2013 | -12.63% | -0.61% | 45.00% | -1.50% |
7/1/2013 | 1.21% | 9.11% | 3.45% | 4.95% |
8/1/2013 | 5.90% | -3.52% | 2.67% | -3.13% |
9/1/2013 | -11.55% | 4.20% | 24.55% | 2.97% |
10/1/2013 | -2.22% | 1.42% | 0.10% | 4.46% |
11/1/2013 | -8.91% | 0.39% | -8.33% | 2.80% |
12/1/2013 | -7.25% | -9.66% | -7.95% | 2.36% |
1/1/2014 | -5.42% | -3.05% | 17.90% | -3.56% |
2/1/2014 | 7.69% | 3.70% | 20.63% | 4.31% |
3/1/2014 | 0.77% | 1.36% | 11.28% | 0.69% |
4/1/2014 | 6.59% | 3.53% | -28.32% | 0.62% |
5/1/2014 | -7.81% | 2.59% | -43.31% | 2.10% |
6/1/2014 | 11.14% | 4.87% | 29.56% | 1.91% |
7/1/2014 | -1.98% | -1.28% | -19.41% | -1.51% |
8/1/2014 | 8.75% | 3.02% | 1.10% | 3.77% |
9/1/2014 | -14.91% | -15.05% | -17.82% | -1.55% |
10/1/2014 | -18.53% | -4.73% | -6.64% | 2.32% |
11/1/2014 | -1.92% | 12.63% | -8.77% | 2.45% |
12/1/2014 | 2.72% | -1.46% | 161.56% | -0.42% |
1/1/2015 | 33.24% | -5.10% | 0.20% | -3.10% |
2/1/2015 | 4.69% | 12.21% | 28.05% | 5.49% |
3/1/2015 | -17.55% | -1.22% | -10.53% | -1.74% |
4/1/2015 | 22.15% | -2.11% | -21.97% | 0.85% |
5/1/2015 | 2.83% | -3.08% | 52.88% | 1.05% |
6/1/2015 | -14.24% | -1.05% | 27.77% | -2.10% |
7/1/2015 | -26.43% | -1.20% | 17.93% | 1.97% |
8/1/2015 | -0.58% | -5.52% | -35.90% | -6.26% |
9/1/2015 | -5.86% | -2.16% | -37.01% | -2.64% |
10/1/2015 | 21.29% | 9.14% | 2.98% | 8.30% |
11/1/2015 | -5.40% | -2.26% | -7.29% | 0.05% |
12/1/2015 | -2.28% | -1.67% | 8.74% | -1.75% |
1/1/2016 | 11.09% | -15.26% | -39.72% | -5.07% |
2/1/2016 | 29.41% | 6.07% | 15.05% | -0.41% |
3/1/2016 | 2.90% | 7.91% | -10.93% | 6.60% |
4/1/2016 | 31.69% | 0.44% | 27.51% | 0.27% |
5/1/2016 | -7.32% | 0.58% | 1.46% | 1.53% |
6/1/2016 | 20.70% | -6.82% | -19.54% | 0.09% |
7/1/2016 | 12.55% | 0.72% | 12.32% | 3.56% |
8/1/2016 | -13.09% | 0.62% | 5.88% | -0.12% |
9/1/2016 | 2.75% | -4.21% | 16.22% | -0.12% |
10/1/2016 | -5.67% | -2.73% | -25.71% | -1.94% |
11/1/2016 | -12.42% | 3.14% | -8.70% | 3.42% |
12/1/2016 | 5.02% | 1.42% | -6.67% | 1.82% |
1/1/2017 | 6.65% | 1.90% | 10.20% | 1.79% |
2/1/2017 | -5.62% | 2.60% | -3.70% | 3.72% |
3/1/2017 | -3.74% | -7.10% | -24.04% | -0.04% |
4/1/2017 | 2.73% | -1.46% | -50.63% | 0.91% |
5/1/2017 | 1.01% | -1.74% | -20.51% | 1.16% |
6/1/2017 | -5.15% | 0.63% | 29.03% | 0.48% |
7/1/2017 | 14.93% | 0.27% | -2.50% | 1.93% |
8/1/2017 | 3.15% | -0.43% | -7.69% | 0.05% |
9/1/2017 | -2.16% | 8.52% | -5.56% | 1.93% |
10/1/2017 | -3.41% | 2.51% | 55.88% | 2.22% |
11/1/2017 | -0.19% | 3.30% | -15.09% | 0.37% |
12/1/2017 | 3.96% | -0.24% | 68.89% | 3.43% |
b. In cells N7:Q9, compute the mean, standard deviation and variance of the monthly returns for each asset (hint: you can use the following Excel functions: AVERAGE, STDEV, and VAR).
Answer:
MONTHLY DESCRIPTIVE STATISTICS | ||||
Newmont | Ford | Caterpillar | S&P 500 | |
Mean | 0.54% | 0.66% | 1.76% | 1.02% |
Standard deviation | 10.82% | 7.63% | 31.57% | 3.44% |
Variance | 0.0118 | 0.0057 | 0.0976 | 0.0011 |
Description:
As we know that the function of standard deviation, which is commonly symbolizes as S2, and variance, which is commonly symbolizes as S, are uses for measure of spread. As we know how variance is meant, how data is spread to be out. Beside the fact of quartiles and range, variance syndicate values to produce measure of spread in data set. And the standard deviation calculates the central tendency of data set to be measure out. If we look at the table, aforementioned, Caterpillar have the greatest standard deviation as compare to the Ford, Newmont, and S&P500. This means that Caterpillar have the highest risk as compare to the Ford, Newmont, and S&P500. Then it comes to the other, the second highest risk of these stocks is Newmont, as it have standard deviation of 10.82%. On the other hand, variance of Caterpillar is higher than Ford, Newmont, and S&P500. As we know that variance is calculated to measure the relative risk of different investment scenarios. It usually uses in organizations to measures that project is over budget or not. Variance of Newmont tells us the relative risk as compare to the average mean, and other stocks also. So by considering all of the above table we see caterpillar and Newmont have higher relative risk as compare to the Ford and S&P500.
c. Based on your results, which asset would you expect to offer a higher return in the future? Which asset is riskier? If you had to choose between the two stocks, which one would you choose?
Answer:
Higher return is expected from Caterpillar (1.76%) and S&P 500 Stock index (1.02%). Investor always find a way to increase its return and decrease its risk. It is difficult to understand which asset will get more return in future. As there would be different situations. But by using the finance mechanism of standard deviation and variance. Now it more convenient and trust worthy way to make decisions of investments. After calculations we have seen that caterpillar have expected return of 1.76%which is higher as compare to the Ford, Newmont, and S&P500. Therefore, caterpillar will have the higher expected return in future.
Then it comes to the risk. Because while choosing the investment plan with different or single stock, it is necessary to measure both risk and return. Sometime few stocks may have higher expected return but also have higher risk. Therefore it have to be taken into consideration that, it has the equilibrium in risk and expected return. The most risky asset is Caterpillar (SD: 31.57%) as it has the highest standard deviation. Other assets including Newmont, Ford, and S&P500 have less standard deviation than caterpillar. More standard deviation means Caterpillar have more risk. As it have most expected return but it would be useless, if it also have to face a great risk in future.
I would choose S&P 500 since it has the comparatively lower risk with good return. There is a reason behind choosing the S&P 500, except other assets. We have seen that S&P 500 have mean of 1.02%, which is lower than the mean of Caterpillar. And S&P 500 have the standard deviation of 3.44%, which is also less than the standard deviation of caterpillar. So it has suggested to buy S&P 500, because it has suitable return and risk as compare to the other assets including Ford, Newmont, and caterpillar.
d. Assuming a monthly risk-free rate of 0.09%, compute the Sharpe ratio for each asset in cells N10:Q10. If your objective were to maximise the Sharpe ratio, which asset would you choose? Is this as expected? Why?
Answer:
Newmont | Ford | Caterpillar | S&P 500 | |
Sharpe ratio | 0.04 | 0.07 | 0.05 | 0.27 |
By analysing all of the above data, it has calculated that the highest Sharpe Ratio is of S&P 500 stock index. All sharp ratios are mentioned above. We can easily understand that S&P 500 have the highest sharp ratio, of 0.27, as compare to Newmont, Ford, and caterpillar.As we know that sharp ratio is used to measure performance of investment by comparing to the risk-free assets. Risk free assets are such assets which have certain expected return and zero possibility of loss in future. It was as expected since it contains the portfolio of the top companies. Sharp ratio is define as the difference between return in investment and risk free return, by dividing standard deviation of investment. I will choose S&P 500 stock index for investment as it has the maximum Sharpe ratio.Other investments have relatively low sharp ratio as compare to the S&P 500. Newmont has sharp ratio of 0.04, ford has sharp ratio of 0.07, while sharp ratio of caterpillar is 0.05. Since S&P 500 has the greatest sharp ratio, therefore its performance of investment will be efficient as compare to the risk free assets.
e. Compute the covariance and the correlation between the returns of each pair ofassets (hint: you can use the following Excel functions: COVAR, andCORREL). What is the type of the relationship between the assets? Which twostocks offer the highest diversification benefits?
Answer:
Covariances | Newmont | Ford | Caterpillar |
Ford | 0.000 | ||
Caterpillar | 0.002 | 0.003 | |
S&P 500 | 0.000 | 0.002 | 0.003 |
Correlations | Newmont | Ford | Caterpillar |
Ford | 0.006 | ||
Caterpillar | 0.063 | 0.101 | |
S&P 500 | -0.010 | 0.655 | 0.237 |
The correlation and covariance results are provided above. Both of these are the indicators which show the relationship between variables. They use to determine whether two variables are related or not. And it also shows what relation both they have. There may have positive or negative relation between these variables. Covariance use to measure amount by which two variables together changes. While correlation also shows the degree of relation between random variables, but it translate the covariance into unit-less. This made easy to understand, as it forms between -1.0-to-1.0. The results suggest the highest level of diversification is between Newmont and S&P 500. Because diversification is a process to decrease risk between different financial industries, instrument’s, institutes by allocating all investment. It always aims to maximize the returns by investing in various areas of investments. There are many benefits of using diversification like it reduce impact of market volatility. Moreover it also helps to decrease wastage of time, which happens to spent time to monitor different investment portfolios.The other results suggest that there is lower relationship between stocks except Ford and S&P 500 which is slightly higher.
f. In columns S to W, calculate the annual descriptive statistics using themonthly descriptive statistics. Do we need to make any adjustment to thecorrelation coefficient?
Answer:
ANNUAL DESCRIPTIVE STATISTICS | ||||
Newmont | Ford | Caterpillar | S&P 500 | |
Mean | 6.74% | 8.16% | 23.32% | 12.99% |
Standard deviation | 0.3748 | 0.2644 | 1.0935 | 0.1190 |
Variance | 0.0407 | 0.0196 | 0.3381 | 0.0037 |
Sharpe ratio | 0.15 | 0.27 | 0.20 | 1.00 |
The annual descriptive statistics are provided above. These are calculated based on the monthly descriptive statistics as it was required. We will need to adjustment the correlation as it highly depends whether the correlation is calculated based on daily, weekly or monthly data.
Part 2: ‘Efficient Frontier 2 Stocks’ spreadsheet
g. Change the percentage invested in Newmont (in cell G5) to 60% and 70%. Observe the change of the portfolio standard deviation and Sharpe ratio. Now change the percentage invested in Newmont to 40% and 30%. What do you observe?
Answer:
On one side, If we change the percentage invested in Newmont to 60% and 70%, I have found that the standard deviation of portfolio increases and in its effect the Sharpe ratio declines. On other side, if percentage invested in Newmont to 40% and 30% it shows that the standard deviation of the portfolio decreases and in its effect the Sharpe ratio increases.
h. Allow the percentage invested in Newmont to vary from 0% to 100%. In cells G15:J25, compute the variance, the standard deviation, the mean return and Sharpe ratio of each portfolio. Discuss the advantages of diversification.
Answer:
DESCRIPTIVE STATISTICS | ||||
Newmont | Ford | Portfolio | ||
Portfolio weights | 70.00% | 30.00% | ||
Mean | 6.54% | 7.87% | 6.94% | |
Standard deviation | 37.48% | 26.44% | 27.58% | |
Variance | 0.1405 | 0.0699 | 0.0761 | |
Sharpe ratio | 0.15 | 0.26 | 0.21 | |
Covariance | 0.0022 | |||
Correlation | 2.27% | |||
OPPORTUNITY SET | ||||
Percentage in Newmont | Variance | Standard Deviation | Mean Return | Sharpe ratio |
0% | 0.06991 | 26.44% | 7.87% | 0.26 |
10% | 0.05823 | 24.13% | 7.74% | 0.28 |
20% | 0.05072 | 22.52% | 7.60% | 0.29 |
30% | 0.04737 | 21.76% | 7.47% | 0.29 |
40% | 0.04818 | 21.95% | 7.34% | 0.28 |
50% | 0.05316 | 23.06% | 7.21% | 0.27 |
60% | 0.06230 | 24.96% | 7.07% | 0.24 |
70% | 0.07560 | 27.49% | 6.94% | 0.21 |
80% | 0.09306 | 30.51% | 6.81% | 0.19 |
90% | 0.11469 | 33.87% | 6.67% | 0.16 |
100% | 0.14048 | 37.48% | 6.54% | 0.15 |
The diversification is very important since it is an important investment strategy to do not put your all eggs in one basket. It reduces our risk with minimum decrease in our return. As a risk averse investor it provides us best opportunity to gain maximum at minimum risk
i. Discuss the importance of the efficient frontier.
Answer:
The efficient frontier is very important if we talk about the portfolio process provided by the Harry Markowitz. It consists of two steps; the first step is to use the Markowitz portfolio selection model to find optimal mixtures with the help of estimated expected returns, risk, and correlation between returns and the second step is to choose the final portfolio based on preferences
The efficient frontier is very important if we talk about the portfolio process provided by the Harry Markowitz. Optimal diversification takes into account all available information. Smallest portfolio risk for a given level of expected return. Largest expected return for a given level of portfolio risk It consists of two steps; the first step is to use the Markowitz portfolio selection model to identify optimal combinations with the help of estimated expected returns, risk, and each correlation between returns and the second step is to choose the final portfolio based on your preferences for return relative to risk
Part 3: ‘Minimum Variance 2 Stocks’ spreadsheet
j. 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 Newmont). Discuss the importance of the minimum variance portfolio and its position on the opportunity set.
Answer:
Using Solver, I have compute the stock weight corresponding to the minimum variance portfolio and I have found following results;
Percentage in Newmont | Variance | Standard Deviation |
33% | 0.04767 | 21.83% |
As a risk averse person it is important to follow minimum variance portfolio. It creates a minimum variance frontier which is actually a collection of all minimum risk portfolios. At this point, there exists a minimum variance portfolio that gives highest return with minimum risk. As minimum variance portfolios only have objective to lower the risk, rather to optimize reward ratio. Minimum ratio optimization always leads towards the pronounced concentration at expense of exploitable correlation in low volatility. It has all portfolios which risk averse investor will choose. As we increase level of risk, the return began to decline. Minimum variance solution load the securities which have low covariance and variance. It could be correct that minimum variance solution have relatively less expected returns. However, in a contradiction with modern portfolio theory, which securities having low volatility or high returns. Although minimum variance portfolio is one of the part of modern portfolio theory that assumes investor will make rational decision about his investment. The slope starts to flatten. Investor would have diminishing increase in return as portfolio risk is increased.
Part 4: ‘Optimal Risky 2 Stocks’ spreadsheet
k. 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 Newmont).
Answer:
Percentage in Newmont | Mean Return |
0% | 7.87% |
The results suggest that we need to invest nothing in Newmont (100% investment should be in ford to maximize the return)
l. Find the optimal capital allocation for an investor with a risk aversion parameter equal to 3 (hint: you have to set the portfolio utility to maximum by changing the percentage invested in T-Bills). How should the investor spend $100,000 between the two stocks?
Answer:
CAPITAL ALLOCATION DECISION | |||
T-Bill | Optimal Risky Portfolio | Capital Allocation | |
Weight | 27495.60% | 37.96% | 27533.56% |
Mean | 1.09% | 7.20% | 301.16% |
Standard Deviation | 0.00% | 23.18% | 8.80% |
Utility | 0.0109 | -0.0086 | 3.0000 |