+1 (208) 254-6996 [email protected]
  

Excel Project (Efficient portfolios and Efficient frontier)

You are assigned with five stocks  and you are expected to build the investment portfolios using those five stocks. Note that I showed you an example of a three-stock portfolio in the attached excel file. You are expected to apply the same concepts to the five-stock portfolios. 

Don't use plagiarized sources. Get Your Custom Essay on
Excel Project
Just from $13/Page
Order Essay

Please collect monthly stock price for the five companies that you are assigned from FINANCE.YAHOO.COM and employ the data time range from Jan 1, 2015 to Oct 30, 2020. Then using the risk-free rate of 0.33%, you are expected to develop the complete portfolio and develop the efficient frontier of these six securities (T-bill and five stocks). Following are the procedure in details:

1) Collect monthly stock price data from Finance.YAHOO.COM. The time range for the date should be from Jan 1, 2015 to Oct 30, 2020. 

2) Compute monthly stock returns for each stock ( monthly rate of return = (P1 – P0) /P0; P is monthly adjusted close price). Then compute mean and standard deviation of stock returns for each company (See my excel example with the sheet entitled ‘Data’)

3) Develop Table of Covariance of these five stocks (See my excel example with the sheet entitled ‘Data’)

4) Develop Efficient Frontier for all risky portfolios constructed with 5 assigned stocks (See my excel example with the sheet entitled ‘EF’ and ‘Graph’)

5) Find the Minimum-Variance risky portfolio (See my excel example with the sheet entitled ‘MVP’)

6) Find the Efficient portfolios including a risk free asset with risk free rate of 0.33% (See my excel example with the sheet entitled ‘Sharpe Ratio for CML’ )

7) Draw the capital market line (CML) (See my example with the sheet entitled ‘Sharpe Ratio for CML’ and ‘Graph’)

Video Clip for Excel project:

https://drive.google.com/file/d/1y2tPEeOieVBJdBmNjPbs0i-gEyLlCNZq/view

The attachments are the Companies assigned,  An example of a three-stock portfolio (excel template), and Portfolio Optimization Project Slides.

Sheet1

STOCK 1STOCK 2STOCK 3STOCK 4STOCK 5
Company NameTickerCompany NameTickerCompany NameTickerCompany NameTickerCompany NameTicker
McDonald’sMCDSouthwest Airlines CoLUVKrogerKRHormel FoodsHRLHasbroHAS

Data

DateAPPLEWMTMSFTAppleWMTMSFTAsset AAsset BAsset C
12/31/055.86911640.93650822.005415AppleWMTMSFT
1/31/066.25293341.42686822.3880466.54%1.20%1.74%
2/28/067.18130942.59909422.97915314.85%2.83%2.64%
3/31/067.97858941.32903723.95982911.10%-2.98%4.27%Table of Covariance
4/30/067.84993538.95594820.241114-1.61%-5.74%-15.52%Asset AAsset BAsset CThings in dark grey are input parameters
5/31/067.68644437.25258318.133703-2.08%-4.37%-10.41%Asset A0.61%0.08%0.22%Things in light grey are computed intermediate things
6/30/067.86124239.67047520.3403282.27%6.49%12.17%Asset B0.08%0.23%0.06%Things in yellow are labels
7/31/067.42883838.85676218.49622-5.50%-2.05%-9.07%Asset C0.22%0.06%0.38%Things in red are “Solver” inputs or outputs
8/31/068.6710541.71820419.40302516.72%7.36%4.90%
9/30/069.19757742.22487321.1301946.07%1.21%8.90%Table of expected returns and risk
10/31/069.50836142.16252120.0130793.38%-0.15%-5.29%E(return)Standard Deviation
11/30/069.85703442.03780422.2484953.67%-0.30%11.17%Asset A2.62%7.81%
12/31/0610.36919943.94761722.1050035.20%4.54%-0.64%Asset B1.09%4.81%
1/31/0710.7936640.74188221.1882784.09%-7.29%-4.15%Asset C1.97%6.16%
2/28/0710.65003440.79674120.359304-1.33%0.13%-3.91%
3/31/0710.69954343.39532520.784290.46%6.37%2.09%
4/30/0710.62925743.58476320.054588-0.66%0.44%-3.51%0.61%
5/31/0710.2576642.22054320.985094-3.50%-3.13%4.64%
6/30/0711.93258441.87889922.11505916.33%-0.81%5.38%
7/31/0711.7599342.26026521.469366-1.45%0.91%-2.92%
8/31/0711.65266841.53364620.215986-0.91%-1.72%-5.84%
9/30/0712.36957645.39091921.6292386.15%9.29%6.99%
10/31/0711.67956147.13549420.776413-5.58%3.84%-3.94%
11/30/0712.37629947.82371121.2438265.97%1.46%2.25%
12/31/0713.94946449.41096124.1652612.71%3.32%13.75%
1/31/0816.57629247.57495925.97377218.83%-3.72%7.48%
2/29/0818.32150749.28211626.57309910.53%3.59%2.31%
3/31/0817.84570147.75558926.375406-2.60%-3.10%-0.74%
4/30/0817.65471353.35719724.044287-1.07%11.73%-8.84%
5/31/0817.84631356.90227525.3628251.09%6.64%5.48%
6/30/0818.66407860.74636824.434214.58%6.76%-3.66%
7/31/0820.32890959.25278925.5535268.92%-2.46%4.58%
8/31/0820.47327660.55792224.838120.71%2.20%-2.80%
9/30/0818.27034261.55902523.819889-10.76%1.65%-4.10%
10/31/0817.96221259.09731722.217438-1.69%-4.00%-6.73%
11/30/0816.4068755.98736622.475725-8.66%-5.26%1.16%
12/31/0814.04281657.71725823.098423-14.41%3.09%2.77%
1/31/0913.60842258.40210723.392935-3.09%1.19%1.28%
2/28/0913.72679961.74384724.2738480.87%5.72%3.77%
3/31/0913.7305264.54217528.0833420.03%4.53%15.69%
4/30/0913.9460462.1504929.6105251.57%-3.71%5.44%
5/31/0912.37770862.23087729.510588-11.25%0.13%-0.34%
6/30/0914.12575165.11309827.20373514.12%4.63%-7.82%
7/31/0915.20860460.96938728.5365817.67%-6.36%4.90%
8/31/0914.98004462.16329228.634418-1.50%1.96%0.34%
9/30/0916.42385164.50829330.4670919.64%3.77%6.40%
10/31/0917.47236868.08880632.8074196.38%5.55%7.68%
11/30/0917.73082966.13886332.4320141.48%-2.86%-1.14%
12/31/0915.82127363.13383932.804802-10.77%-4.54%1.15%
1/31/1016.63161363.15075733.2122695.12%0.03%1.24%
2/28/1017.06500864.61328135.8021322.61%2.32%7.80%
3/31/1018.76120867.820935.2867899.94%4.96%-1.44%
4/30/1020.12547565.31943535.7584467.27%-3.69%1.34%
5/31/1020.79770364.26842536.6792073.34%-1.61%2.57%
6/30/1021.39524762.99283237.9634252.87%-1.98%3.50%
7/31/1022.93946564.63655939.9601027.22%2.61%5.26%
8/31/1022.65996965.89829341.03281-1.22%1.95%2.68%
9/30/1024.2905965.72593741.5550167.20%-0.26%1.27%
10/31/1026.74889275.43791242.31620810.12%14.78%1.83%
11/30/1024.93353374.00740141.371773-6.79%-1.90%-2.23%
12/31/1026.46505773.64093835.9832086.14%-0.50%-13.02%
1/31/1129.01759172.73104139.056039.64%-1.24%8.54%
2/28/1128.21818971.27519236.472504-2.75%-2.00%-6.61%
3/31/1128.38146868.04186243.6306690.58%-4.54%19.63%
4/30/1129.54485564.74655242.0339854.10%-4.84%-3.66%
5/31/1128.56378662.22507939.860466-3.32%-3.89%-5.17%
6/30/1127.62327463.14622542.162708-3.29%1.48%5.78%
7/31/1125.67848656.7859839.291664-7.04%-10.07%-6.81%
8/31/1125.23197457.27089340.223278-1.74%0.85%2.37%
9/30/1127.33654650.55808647.8389788.34%-11.72%18.93%
10/31/1127.06203551.97130649.393021-1.00%2.80%3.25%
11/30/1124.18210654.14413850.759815-10.64%4.18%2.77%
12/31/1122.36258959.10441250.402992-7.52%9.16%-0.70%
1/31/1222.21326459.08659746.551182-0.67%-0.03%-7.64%
2/29/1225.17489461.00152250.89388313.33%3.24%9.33%
3/31/1221.65239559.99956145.954697-13.99%-1.64%-9.70%
4/30/1223.06601763.50781648.838976.53%5.85%6.28%
5/31/1222.21646965.99738347.482441-3.68%3.92%-2.78%
6/30/1224.21734265.95219452.5953649.01%-0.07%10.77%
7/31/1224.65655764.56935153.3191531.81%-2.10%1.38%
8/31/1226.41399265.6301853.78227.13%1.64%0.87%
9/30/1226.52847763.71913555.9484180.43%-2.91%4.03%
10/31/1225.82285964.09224756.265884-2.66%0.59%0.57%
11/30/1227.20013462.90012758.4132395.33%-1.86%3.82%
12/31/1228.49884461.16896860.7727134.77%-2.75%4.04%
1/31/1332.17187165.00923260.14289512.89%6.28%-1.04%
2/28/1333.88459466.06323262.2854775.32%1.62%3.56%
3/31/1333.88222569.41110264.744362-0.01%5.07%3.95%
4/30/1336.03097272.5686866.0494546.34%4.55%2.02%
5/31/1334.10971170.3403465.5625-5.33%-3.07%-0.74%
6/30/1335.22522774.34623769.1483313.27%5.70%5.47%
7/31/1338.84177872.56171471.11719510.27%-2.40%2.85%
8/31/1336.64514273.08360371.228363-5.66%0.72%0.16%
9/30/1340.19267381.6602179.5378659.68%11.74%11.67%
10/31/1340.86080690.93829380.4845121.66%11.36%1.19%
11/30/1340.38249692.35993282.205322-1.17%1.56%2.14%
12/31/1339.952976100.75265591.306129-1.06%9.09%11.07%
1/31/1442.50387685.0726790.1144716.38%-15.56%-1.31%
2/28/1440.19973484.08971488.123955-5.42%-1.16%-2.21%
3/31/1439.59593684.1061490.296387-1.50%0.02%2.47%
4/30/1444.77364778.47753195.43301413.08%-6.69%5.69%
5/31/1444.5229881.94750295.623627-0.56%4.42%0.20%
6/30/1445.76888385.372734102.8674092.80%4.18%7.58%
7/31/1454.74996991.716125108.92812319.62%7.43%5.89%
8/31/1454.48580290.372299111.33313-0.48%-1.47%2.21%
9/30/1452.82520796.502312103.973877-3.05%6.78%-6.61%
10/31/1443.1030293.97139107.945564-18.40%-2.62%3.82%
11/30/1438.20580389.64090799.300156-11.36%-4.61%-8.01%
12/31/1440.31300792.723198102.0962455.52%3.44%2.82%
1/31/1541.93822195.780754109.5264054.03%3.30%7.28%
2/28/1546.2046294.368088115.79676810.17%-1.47%5.72%
3/31/1548.812218100.041351128.22675.64%6.01%10.73%
4/30/1542.58511798.679451121.432449-12.76%-1.36%-5.30%
5/31/1548.328697108.054138132.01249713.49%9.50%8.71%
6/30/1552.020744107.946556134.2889257.64%-0.10%1.72%
7/31/1550.970757111.74102135.855804-2.02%3.52%1.17%
8/31/1554.897453116.634895137.4650277.70%4.38%1.18%
9/30/1560.973743117.047661141.75616511.07%0.35%3.12%
10/31/1565.505836117.037827149.676017.43%-0.01%5.59%
11/30/1572.192863116.79213156.45542910.21%-0.21%4.53%
12/31/1576.091995113.02179168.8865515.40%-3.23%7.95%
1/31/1667.204628106.299118160.73143-11.68%-5.95%-4.83%
2/29/1662.664707112.162949156.892731-6.76%5.52%-2.39%
3/31/1672.401154120.522179178.28131115.54%7.45%13.63%
4/30/1678.349983123.010948182.3003858.22%2.06%2.25%
5/31/1690.879066119.289581203.01922615.99%-3.03%11.37%
6/30/16105.886086128.870193204.5156116.51%8.03%0.74%
7/31/16128.585907138.281509224.9861321.44%7.30%10.01%
8/31/16115.610542139.910004210.330002-10.09%1.18%-6.51%
9/30/16108.672516138.75202.470001-6.00%-0.83%-3.74%
10/31/16118.485588145.770004223.7200019.03%5.06%10.50%
11/5/16118.690002145.770004223.7200010.17%0.00%0.00%

EF

ReturnRiskWaWbWcTotal weightEfficient frontier for risky portfolio
Table of expected returns and risk0.4000%7.0479%-53.7010%138.5369%15.1642%100.0000%
E(return)Standard Deviation0.6000%6.2095%-42.2030%124.3108%17.8923%100.0000%
Asset A2.62%7.81%0.8000%5.4522%-30.7223%110.1060%20.6163%100.0000%
Asset B1.09%4.81%1.0000%4.8123%-19.2357%95.8941%23.3416%100.0000%
Asset C1.97%6.16%1.2000%4.3425%-7.7435%81.6752%26.0683%100.0000%
1.4000%4.1021%3.7430%67.4633%28.7937%100.0000%
Table of covariances1.6000%4.1310%15.2295%53.2514%31.5191%100.0000%
Asset AAsset BAsset C1.8000%4.4238%26.7103%39.0466%34.2431%100.0000%
Asset A0.61%0.08%0.22%2.0000%4.9340%38.1968%24.8348%36.9684%100.0000%
Asset B0.08%0.23%0.06%2.2000%5.6024%49.6833%10.6229%39.6938%100.0000%
Asset C0.22%0.06%0.38%2.4000%6.3794%61.1698%-3.5890%42.4191%100.0000%
2.6000%7.2302%72.6563%-17.8009%45.1445%100.0000%
Proporition of wealth invested in2.8000%8.1316%84.1429%-32.0127%47.8699%100.0000%
Asset A290.90%Wa3.0000%9.0686%95.6294%-46.2246%50.5952%100.0000%
Asset B-287.83%Wb3.2000%10.0311%107.1159%-60.4365%53.3206%100.0000%
Asset C96.93%Wc3.4000%11.0126%118.6024%-74.6484%56.0460%100.0000%
Total1.00(this is a CONSTRAINT for “Solver”, this cell B23 must be forced to equal one)3.6000%12.0082%130.0889%-88.8602%58.7713%100.0000%
(it is the constraint that the total proportions of our wealth must equal one)3.8000%13.0149%141.5754%-103.0721%61.4967%100.0000%
This bit is used to find the risk of the portfolio4.0000%14.0301%153.0619%-117.2840%64.2220%100.0000%
Asset AAsset BAsset C4.2000%15.0523%164.5484%-131.4958%66.9474%100.0000%
Proportion290.90%-287.83%96.93%These numbers are a table of, for instance4.4000%16.0800%176.0349%-145.7077%69.6728%100.0000%
Asset A290.90%5.1604%-0.6713%0.6270%proportion of A * proportion of B *4.6000%17.1122%187.5215%-159.9196%72.3981%100.0000%
Asset B-287.83%-0.6713%1.9170%-0.1564%covariance of A and B4.8000%18.1488%199.0137%-174.1386%75.1249%100.0000%
Asset C96.93%0.6270%-0.1564%0.3567%5.0000%19.1875%210.4945%-188.3433%77.8489%100.0000%
5.2000%20.2299%221.9867%-202.5623%80.5756%100.0000%
5.4000%21.2736%233.4675%-216.7671%83.2996%100.0000%
5.6000%22.3197%244.9540%-230.9790%86.0249%100.0000%
Expected return on the portfolio of all 3 assets5.8000%23.3675%256.4405%-245.1908%88.7503%100.0000%
6.40%(if you are minimizing risk for a given return you should tell “Solver” that6.0000%24.4169%267.9270%-259.4027%91.4757%100.0000%
this cell, B35, is fixed–at whatever level of return you want. If you are maximizing6.2000%25.4675%279.4136%-273.6146%94.2010%100.0000%
return for a fixed risk, you should tell “Solver” to maximize this cell6.4000%26.5193%290.9001%-287.8265%96.9264%100.0000%
Risk associated with the portfolio of all 3 assets
26.52%(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.)
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.
This table is to develop the Efficient Frontier that is shown on Graph (See the blue bullet shape curve)

&A

Page &P

Solve List

MVP

This Table is to find the Minimum Variance Portfolio (MVP) and it is captured as the red dot on Graph with return being equal to Cell B31 and risk Cell B36
Table of expected returns and risk
E(return)Standard Deviation
Asset A2.62%7.81%
Asset B1.09%4.81%
Asset C1.97%6.16%
Table of covariances
Asset AAsset BAsset C
Asset A0.61%0.08%0.22%
Asset B0.08%0.23%0.06%
Asset C0.22%0.06%0.38%
Proporition of wealth invested in
Asset A0.08Wa
Asset B0.62Wb
Asset C0.30Wc
Total1.00(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)
This bit is used to find the risk of the portfolio
Asset AAsset BAsset C
Proportion8.28%61.85%29.87%These numbers are a table of, for instance
Asset A8.28%0.0042%0.0041%0.0055%proportion of A * proportion of B *
Asset B61.85%0.0041%0.0885%0.0104%covariance of A and B
Asset C29.87%0.0055%0.0104%0.0339%
Expected return on the portfolio of all 3 assets
1.48%(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
4.08%(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.)
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.

Sharpe Ratio for CML

This table is to develop the Capital Market Line on Graph (See the red straight line)
Table of expected returns and risk on the risky assetsCapital market line
E(return)Riskriskreturnmarket price of risk (Slope of CML)0.3378039691
Asset A2.62%7.81%0.00%0.33%risk free rate0.33%
Asset B1.09%4.81%1.00%0.67%
Asset C1.97%6.16%2.00%1.01%
3.00%1.35%
Riskfree rate0.33%The return on the risk free asset4.00%1.68%
5.00%2.02%
6.00%2.36%
Table of covariances for the risky assets7.00%2.70%
Asset AAsset BAsset CConstruct covariance matrix using formulae8.00%3.04%
Asset A0.61%0.08%0.22%such as covar(A,B)=risk(A)*risk(B)*correl(A,B)9.00%3.37%
Asset B0.08%0.23%0.06%10.00%3.71%
Asset C0.22%0.06%0.38%11.00%4.05%
12.00%4.39%
Proporition of wealth invested in purely risky assets13.00%4.72%
Asset A0.3771WaNormally these values are set by “Solver” but it may be necessary to14.00%5.06%
Asset B0.2543Wbreset them to “sensible” values and then re-run “Solver” if “Solver”15.00%5.40%
Asset C0.3685Wcfails to find a solution (eg, B23=1/3, B24=1/3, B25=1/3 )16.00%5.74%
Total1.0000(this is a CONSTRAINT for “Solver”, this cell B26 must be forced to equal one)17.00%6.08%
(it is the constraint that the sum of the proportions of our wealth must equal one)18.00%6.41%
This bit is used to find the risk of the portfolio of purely risky assets19.00%6.75%
Asset AAsset BAsset C20.00%7.09%
Proportion37.71%25.43%36.85%These numbers are a table of, for instance21.00%7.43%
Asset A37.71%0.0867%0.0077%0.0309%proportion of A * proportion of B *22.00%7.77%
Asset B25.43%0.0077%0.0150%0.0053%covariance of A and B23.00%8.10%
Asset C36.85%0.0309%0.0053%0.0516%24.00%8.44%
25.00%8.78%
26.00%9.12%
27.00%9.45%
Expected return on the portfolio of all 3 risky assets28.00%9.79%
1.99%29.00%10.13%
Risk associated with the portfolio of all 3 risky assets
4.91%
Market Price of Risk ( = Slope of the Capital Market Line)
0.3378039691
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.

Graph

efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 Stocks 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 MVP 0.0408044213249035 0.0147908882617867

risk

return

efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 cap market line 0 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 0.19 0.2 0.21 0.22 0.23 0.24 0.25 0.26 0.27 0.28 0.29 0.00333333333333333 0.00671137302457121 0.0100894127158091 0.013467452407047 0.0168454920982849 0.0202235317895227 0.0236015714807606 0.0269796111719985 0.0303576508632364 0.0337356905544743 0.0371137302457121 0.04049176993695 0.0438698096281879 0.0472478493194258 0.0506258890106637 0.0540039287019016 0.0573819683931394 0.0607600080843773 0.0641380477756152 0.0675160874668531 0.070894127158091 0.0742721668493288 0.0776502065405667 0.0810282462318046 0.0844062859230425 0.0877843256142804 0.0911623653055182 0.0945404049967561 0.097918444687994 0.101296484379232 assets 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 Stocks 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 MVP 0.0408044213249035 0.0147908882617867

risk

return

efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 Stocks 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 MVP 0.0408044213249035 0.0147908882617867

risk

return

efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 cap market line 0 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 0.19 0.2 0.21 0.22 0.23 0.24 0.25 0.26 0.27 0.28 0.29 0.00333333333333333 0.00671137302457121 0.0100894127158091 0.013467452407047 0.0168454920982849 0.0202235317895227 0.0236015714807606 0.0269796111719985 0.0303576508632364 0.0337356905544743 0.0371137302457121 0.04049176993695 0.0438698096281879 0.0472478493194258 0.0506258890106637 0.0540039287019016 0.0573819683931394 0.0607600080843773 0.0641380477756152 0.0675160874668531 0.070894127158091 0.0742721668493288 0.0776502065405667 0.0810282462318046 0.0844062859230425 0.0877843256142804 0.0911623653055182 0.0945404049967561 0.097918444687994 0.101296484379232 assets 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 efficient frontier 0.0704793579016931 0.062094721368613 0.054521703149259 0.0481229449885874 0.0434246455348512 0.0410206293673384 0.0413100805473508 0.0442381056029334 0.0493396832553761 0.0560235417250364 0.0637942680682251 0.0723022722780676 0.0813164623065385 0.0906860189599101 0.100311412884134 0.110125581052238 0.120082247549928 0.130148712165965 0.140301343213612 0.150522706140621 0.160799694488253 0.171122286562571 0.181487889406868 0.191874810650129 0.202298946853395 0.212735523772302 0.223196977851075 0.233675452917815 0.244168757620845 0.254675058902781 0.265192812088546 0.004 0.006 0.008 0.01 0.012 0.014 0.016 0.018 0.02 0.022 0.024 0.026 0.028 0.03 0.032 0.034 0.036 0.038 0.04 0.042 0.044 0.046 0.048 0.05 0.052 0.054 0.056 0.058 0.06 0.062 0.064 Stocks 0.0780902519637998 0.0481039403180182 0.0616222205920844 0.0262018513268502 0.0108851976477998 0.0197152450303428 MVP 0.0408044213249035 0.0147908882617867

risk

return

Portfolio Optimization

Excel Project

Portfolio Mean-Variance Analysis

To construct a n-Asset portfolio

The expected return on a portfolio is the weighted average of the expected returns of the individual assets in the portfolio.

w1 + w2 + w3 …+ wn = 1

Portfolio variance is the weighted sum of all the variances and covariances:

There are n variances, and n2 – n covariances

All feasible/Attainable portfolios

All feasible/Attainable portfolios lie inside a bullet-shaped region, called the minimum-variance boundary or frontier.

Feasible set

Efficient Portfolios and Efficient Frontier

Mean-Variance Criterion suggests that investors should only choose efficient portfolios. An efficient portfolio is one that offers:

the most return for a given amount of risk, or

the least risk for a give amount of return.

The collection of efficient portfolios is called the efficient frontier for risky securities.

Minimum Variance Portfolio (MVP)

How can we find the weights of the securities so that we can minimize the variance of the portfolio?

The Optimal Risky Portfolio With A Risk-Free Asset

With a risk-free asset available, investors choose the efficient portfolios on the capital allocation line with the steepest slope

E(rA)

A

E(r)

Efficient Frontier for risky portfolio

E(rP)

CAL (M)

P

E(r)

s

The Capital Market Line (CML)

M

E(rP&F)

F

Risk Free

P&F

Efficient Frontier for risky portfolio

E(rP)

P

P&F

E(rP&F)

CAL (M) = CML

The optimal CAL is called the Capital Market Line or CML

The CML dominates the EF for risky portfolio

6-

The Capital Market Line (cont.)

The Capital Market Line (CML) is all linear combinations of the risk-free asset and Tangent Portfolio M and it is the new efficient frontier.

Slope =Sharpe Ratio = (E(rp) – rf) / standard deviation of portfolio, that is the return per unit of risk.

CML maximizes the slope or the return per unit of risk or it equivalently maximizes the Sharpe ratio

What does the CML tell us?

The expected rate of return on any efficient portfolio (p) is equal to the risk-free rate plus Slope of Tangent Portfolio times the risk of the portfolio (p).

Return on P = Risk free rate + Sharpe ratio of Tangent Portfolio x Risk of P

=++×××+=

å

1122

ˆˆˆˆˆ

pnnii

i

rwrwrwrwr

p

m

f

m

f

p

r

r

r

r

s

s

´

+

=

ˆ

Order your essay today and save 10% with the discount code ESSAYHELP