# Excel

Topics: Variance, Standard deviation, Return Pages: 3 (909 words) Published: December 7, 2012
Frist step , collecting the data from Yahoo. Finance, and calculate the quarterly return by the formula (return of fourth mouth- the return of frist mouth)/ retrun og first mouth

Question a
Average quarterly return= average (all returns of one asset) Standard deviations= stdeva (all returns of one asset)

Question b and c
File—options—add-ins—solver add in--go, and click all the options, then using the data analysis, choosing correlation (covariance), then selecting all the returns of all assets. Question d
The average weight= 1/12
Sum of weight =1
Solver portfolio
Return=MMULT(whole covariance matrix, TRANSPOSE(whole correlation matrix)) Variance ==MMULT(average weight,MMULT(whole covariance matrix, TRANSPOSE(average weight))) Risk =SQRT(Variance)
Mvp
First calculate sigma and return
Sigma =SQRT(MMULT(MMULT(weight,whole covariance matrix),TRANSPOSE(weight))) Return=MMULT(weight,TRANSPOSE(annual return))
Then using solver set sigma in target cell, choosing min, by changing cells: total weight The constrains should be sum of weight =1
Lastly, click solve.

Question e
Set a target of sigma ,similarly to one of weight, and though slover, set the return as target cell, choosing max , by changing cells : all weight, the constraints should be :sigma= target of sigma, sum of weight =1,click solve. So after getting a new group figure of sigma, return and weight, change target of sigma slowly, and get 10 different sigma result, the last three should change more, then these group make a new table, to insert a scatter from the charts . and selecting ‘scatter with smooth lines and markets’. ‘Then right click on the chart and choose ‘select data. ‘click on ‘add’ in the new dialog window, then dialog box for maximizing portfolio return for a target sigma.’(p554) After that, change the name of x,y series and add the title, and right click , choosing format gridlines ,click no line.

Question f
Frist, calculate the slope=(return-risk free rate)/sigma...