Must be done in Excel We have monthly price data for twelve months on two shares
ID: 2797010 • Letter: M
Question
Must be done in Excel
We have monthly price data for twelve months on two shares: one of Stock A and one of Stock B. The data look as follows:
Month
Stock A Price
Stock A Price
0
25
45
1
24.125
44.875
2
23.375
46.875
3
24.75
45.25
4
26.625
50.875
5
26.5
58.5
6
28
57.25
7
28.875
62.75
8
29.75
65.5
9
31.375
74.375
10
36.25
78.5
11
37.125
78
12
36.875
78.125
These data give the closing price at the end of each month for each stock. The month-0 price is the initial price of the stock. We wish to calculate the relevant return statistics for each stock.
Calculate the monthly return for each stock
Calculate the variance for Stock A and Stock B
Calculate the covariance of the returns
Calculate the correlation coefficient
Now form a portfolio composed of half of Stock A and half of Stock B.
Calculate the monthly expected return of your portfolio
Calculate the mean portfolio return and portfolio variance
Now construct a table varying the proportions of Stock A and Stock B from 0 to 100% in increments of 5%. For each proportion find the expected return and standard deviation. Graph your table.
MUST BE DONE IN EXCEL
Month
Stock A Price
Stock A Price
0
25
45
1
24.125
44.875
2
23.375
46.875
3
24.75
45.25
4
26.625
50.875
5
26.5
58.5
6
28
57.25
7
28.875
62.75
8
29.75
65.5
9
31.375
74.375
10
36.25
78.5
11
37.125
78
12
36.875
78.125
Explanation / Answer
Excel formula for Variance = =VAR.P(H6:H18) where H6 to H18 are the stock prices for A and B respectively
Correlation = =CORREL(H6:H18,I6:I18) where H6 to H18 and I6 to I18 are the stock prices for A and B
Covariance= =COVARIANCE.P(K7:K18,L7:L18) where k7 to k18 and l7 to l18 are the returns calculated
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.