excel term paper project

Excel project  term paper
Hello, find the Maximum
step 1: Find data on three stocks us the last 10 years (provided)
A.Cell1/Cell0 – 1 in the next three columns divide the the the next cell by the previous cell
B. then create a covariance table
C. create a expected return and risk
E(return)= AVERAGE=(CELL:CELL)
STANDARD DEVIATION=STDEV(CELL:CELL)
Step 2:Using the the two table on the previous table
A. PROPORTION OF WEATH INVESTED
(this is a CONSTRAINT for “Solver”, this cell B23 must be forced to equal one)
(it is the constraint that the total proportions of our wealth must equal one)
B. This bit is used to find the risk of the portfolio
These numbers are a table of, for instance proportion of A proportion of B covariance of A and B

C.Expected return on the portfolio of all 3 assets
(if you are minimizing risk for a given return you should tell “Solver” that
this cell, B35, is fixed–at whatever level of return you want. If you are maximizing
return for a fixed risk, you should tell “Solver” to maximize this cell

Risk associated with the portfolio of all 3 assets
(if you are minimizing risk for a given return you should tell “Solver” to minimize
this cell, B40. If you are maximizing return for a fixed risk you should
tell “Solver” that this cell is fixed.)

D.Now use “solver” to either minimize the risk for a given return

maximize return for a given risk (ie, maximize B30 for a given value of B35)

In both cases B18 should be constrained to equal 1, this is the
condition that our “total wealth” is invested in the assets.

( am not sure how to make the table my professor did not explain it

Step 3:
A.Table of expected returns and risk on the risky assets
B.Construct covariance matrix using formulae such as covar(A,B)=risk(A)*risk(B)*correl(A,B)
C. Proportion of wealth invested in purely risky assets
Normally these values are set by “Solver” but it may be necessary to reset them to “sensible” values and then re-run “Solver” if Solver fails to find a solution (eg, B23=1/3, B24=1/3, B25=1/3 )
(this is a CONSTRAINT for “Solver”, this cell B26 must be forced to equal one)
(it is the constraint that the sum of the proportions of our wealth must equal one)
D. This bit is used to find the risk of the portfolio of purely risky assets
These numbers are a table of, for instance proportion of A proportion of B covariance of A and B

E.
Expected return on the portfolio of all 3 risky assets
=B18*B4+B19*B5+B20*B6
2.94%
Risk associated with the portfolio of all 3 risky assets
=SQRT(SUM(C26:E28))
7.64%
Market Price of Risk ( = Slope of the Capital Market Line)
=(B33-B8)/B36
0.341088121
To find the Market Price of Risk (and hence the Capital Market Line)
use “solver” to maximize cell B39 subject to the constraint that
cell B21 is equal to one.
You access “solver” from the “Tools” menu.

Step 4: results
A.Efficient frontier for risky portfolio
B.Capital market line
Step 5:
graph

on the excel project below it contains all the formulas for the project also the format as well

By admin in Essays on July 29, 2016