In: Finance
Supposethattheannualreturnsontwosharesareperfectlynegativelycorrelatedandthat? =0.07, ?
? = 0.20,? = 0.12 , and ? = 0.5. Assuming that there are no arbitrage opportunities, by using ???
the Goal Seek function (excel) calculate the weight (proportion) of the two assets that produce the lowest portfolio variance? (Use the Goal Seek function)
Write down the facts given :
Assume Weight of asset 1 to be .50 and weight of asset 2 to ve .50 and use the formula in the above mentioned screenshot to calculate portfolio variance:
Remember , in cell representing value of weight of 2 we will put in formula "1-cell containing value of weight of 1"
In cell representing value of portfolio variance, we will put in formula in 1st screenshot
Using formula for portfolio variance in 1st Screenshot, we get variance = .0361
The next step is to go to Data in excel, select What if Analysis and select Goal Seek
In the Goal Seek Table , fill in the the following values:
Set Cell : Select cell containing the value of portfolio variance
To value : 0.000001
By Changing Cell : Select cell containing value of weight 1
Press Ok.
Goal seek will apply and we will get the following values:
This shows that weight of asset 1 as .85 and weight of asset 2 as .15 will give the minimum variance of .001
PS: Open images in new tab for better view.