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
Get step-by-step solutions from verified subject matter experts
