Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Accounting questions

Question

1. Why do situations such as these occur?

Answered: 1 week ago