Question
Do this on excel please The table below lists the expected return and the standard deviation of returns on five securities: Security Expected Return (E
Do this on excel please
The table below lists the expected return and the standard deviation of returns on five securities:
Security | Expected Return (Ei) | SD(Return) (i) |
1 | 8.1% | 22.5% |
2 | 13.5% | 37.8% |
3 | 22.5% | 54% |
4 | 31.5% | 63% |
5 | 36% | 81% |
The correlation matrix, [ij], or [CORR(Ri, Rj)], for various values of i and j as follows:
1 | 2 | 3 | 4 | 5 | |
1 | 1 | 0.225 | 0.36 | 0.09 | 0.36 |
2 | 0.225 | 1 | 0.405 | 0.45 | 0.135 |
3 | 0.36 | 0.405 | 1 | 0.27 | 0.675 |
4 | 0.09 | 0.45 | 0.27 | 1 | 0.63 |
5 | 0.36 | 0.135 | 0.675 | 0.63 | 1 |
Thus, the matrix above says, for instance, that 13, the correlation between returns on Security1 and those on Security3, is 0.36.
Compute the variance-covariance matrix, [ij], COV(Ri, Rj), for various values of i and j and use Solver add-in in MS Excel to choose xi and xj so as to minimize p = SQRT(xixjij) subject to constraints (a) that xi = 1 and, (b) that Ep = xiEi = a given value, to start with, say, 0.09%. Note in a separate table values of Ep and, the minimized portfolio standard deviation, p. Now, compute p by changing the value of Ep from 0.09% to 81% in steps of 5%. Plot these points in Excel using an x-y scatter graph to generate the minimum variance frontier. Print the table and the minimum variance frontier, and submit the hard copy by Aug 7.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started