Details of this Paper

Creating Efficient Frontier in Excel




Creating Efficient Frontier in Excel;Creating efficient frontiers using excel.;Supposewe have 3 risky assets whose net r e turn has the m ean vector and variance- covariance m atrix given below;Asset;Mean;Variance-;Covariance;Mat r ix;Weights;Ones;Mean;Port f olio;Return;Port f olio;Variance;Port f olio;STD;Port f olio;Constraint;1;0.06;1;0.3;0.3;0.079 3 72;1;0.176 6 66 1 22;2.429 6 1;1.558 7 21;1;2;0.12;0.3;1;0.3;1.603 1 66;1;3;0.03;0.3;0.3;1;-0.68 2 54;1;To m odel the portfolio choiceproblem, I begin by highlighting the m ean vector and giving it a n a m e. To do this, le f t - c li c k on cell c9 and drag d o wn until c e ll c11 and th e n release. Then go to the na m e-box, which isthe white box in the upperrightjustabove the "A" c o l u mn. Click in the na m e-box, hit b a ckspace, and then type a na m e for cells c9;-c11. Then hit return. I used the na m e " m u"for the vector of m ean returns as illustrated below;;Then,I f oll o w a si m ilar approach with the varia n ce-covaria n ce m atrix by clicking on cell F9 and then dragging across and down to cell H11. After the varia n ce covariance m atrix is highlighted, I go to the na m e box a nd give the variance covariance m atrix the na m e "vcov" (Note: I don?t use quotes in the na m es).;Thee ff i cient f ronti e r c o nsists ofpo r t f olios t h atonly invest in the risky assets. Therefore, I introduce avector that r eprese n ts t h e port fo lio weights in e ach ass e t. For now, I will;assignt h e weights arbit r arily. Bel o w, I willuseexcel to ch o ose the wei g hts opti m ally. For now, I have placed the weights in cellsJ9 through J11 and given them the na m e weights. Also, for convenience, I have created a colu m n of ones and given it the name ones.;Toillu s t rate why the na m es are convenient, n o te that for gi ven portfolio weights, the m ean return on the risky asset portfolio is eq u al to the trans p ose of the weights v ect o r multiplied b y the m ean vector. Using excel ' s mat r ix f or m ula ' s, the tran s pose ofthe weights ve ct or is gi v en b y "tra n spos e (weigh t s)", and to m ultiply "transp o se(weights)"by the m ean vector " m u" simply requires using the excel function m m ult, which stands for m atrix m ultiplication. T he resulting m eanreturn for the port f olio is given by mmult(tran s pose(weights),ones).In this ex p res si on, excel m ulti p lies the transpo s e of weights by the vector of ones, producing the m ean return on the portfolio.;Toprogramthe m ean return and st or e it in a cell,one uses t h e excel for m at for m atrix fo r m ulas. For exa m ple, to store the m ean return for the given weight vector in cell N9, click on cell N9, and then type "= m m ult(t r anspose(weights),mu)" and then hit CTRL SHIFT ENTER. The quantity in t h e cell will beequal to the m ean return and will c ha nge when the weights change or when the ele m ents of the m ean return vector change.;Toprogramthe variance of the portfolio ret u rn, I use the fact that the v ari a nce of the port f olio return is t h e tr a nspose ofthe weights v e ctor m ultiplied by varia n ce cova r i an c e m atrix m ultiplied by the weights ve ct or. To prog r amthis, f i rst I m ultiply the transp o seof of weights vector ti m es the variance-covar i ance m atrix. This produces the expression mmult(tran s pose(weights),vcov). T h en, I have to m ultiply this expres s i onby weights. Therefore, the final answer for portfolio variance is mmult(mmult (tra n spose (weights),v c ov),weights). I have typed this expr e ssion i n to c e ll O9 using the sa m e approach that I used for typing the m ean return. Clicking on the cell will hig h li g ht the f or m ula at the top ofthe excel spreadsh e et.By changi n g the weights or changing t h e ele m ents of the varia n ce cov ar i ance m atrix, t h e port f olio v ar i ance will change. The standard deviation of the portfolio r eturn is ju s t the squa r e -r oot ofthe variance, and is given in cell P9. To co m pute portfolio s t d, I na m ed portfolio variance portvar. The fo r m ula for portfolio std (standarddeviation) is just port v ar^.5. It is entered in cell P9 with an equal sign before the fo r m u l a. Then you hit return to enter the formula.;Weare al m o st ready to st art to use e x cel toco m p ute the efficient frontier for this set of3 assets. To co m pute the efficient fr o ntier, we need to constrain the portfolio weights so that that th e y sumto 1. This is e q uivalenttoi m posingtheconditionthat;mmult(tran s pose(one s),w eights) s u m s to 1. W ewill i m pose this con s traint when choosing portfolio weights. To do so, I have assignedtheconstraintto cell N17 by typing the fo r m ulammult(transpose(ones), wei ghts) i n to t h e cell. W h e n I use the constrained m axi m i zation progra m, I will s e t the cell equalto1, which will con s tr a i n the weights that;Ican choose. I have chosen to na m e the constraint "constraint".;Thelast item to note before beginning to use sol v er is that it is use f ul to s t ore the r e s u lts of m ax i m i zation proble m s in a convenient formfor further analysis. Towards this end, in row 79 I have stored the we i ghts vector along with m ean po r tfolio return and portfolio;std.To illustrate how this was done, I highl i ghted cells b79-d79 and then in the formula bar typed =transpose(weights) and hit CTRL SH I FT ENTER.This puts the transpose of the weights in a 1 by 3 row vector. T hen, incells e79, f79, and g79, Isimply repeated the na m es port m ean,, portvar, and portstd. This produces the sa m e values at these cells that were co m puted earlier.;wei g hts mean varia n ce std;0.079 3 72 1.603 1 66 -0.68 2 54 0.176 6 66 2.429 6 1 1.558 7 21;USING SO LVER T O F IND POR T FOLIO WEIGHTS;Thetextbook provides the answer to solving for the efficient frontier when there are 2 risky a s sets. W hen there are m ore than t w o riskyassets, there are m ore complicated m atrix f ormulas f or sol v ing f or the eff i cient f rontier. If t h ere are co m plicated constraints, there m ay not be a formula for the efficient f r ontier. In this course, I will e m phasize using a m axi m i zation programto solve for thee ff i cient f ro n ti e r. More s pe ci f ically, we will use the excel p rogr a mSOLVER to f ind the p oints on the e ff i cient f r o nti e r.;SOLVERis an excel ad d -in pac k age.To find solver, click on tools on the m enu bar. If you see solver in the m e nu, then it is inst a lled and ready to be used. If you don ' t find solver, then click on tools, Add-Ins, then c h eck S olver, and click OK. This should give you the capability to use solver.;Toillu s t r a te how to use to f i nd pointson the e ff i cient f rontier, rec a ll t h at t h e lowest p o int on the efficient frontier is the point that mi n i m i zes standard deviation of the portfolio return. To solve for the m ean and std of ret u rn of the m i n i mumstd portfolio, and to solve for the weights in the portf o lio, click on cell h79 (with the s t d on top) and then click on tools, solver. The result should giveyou a window that appears as follows;;;;Clickon set target cell, th e n, if it not already hi g hlighted, click on h79 because we want to m i n i m i ze portfolio st d. After choosing t h e tar g et cell t h en click on Min because we want to m i ni m i ze the value of portfolio sta n dard deviation. T o do this, we change the weights in the portfolio, hence, write in we i ghts where it says by changing cells. Then, where it says subject to the constraints c l ick Add. This should bring up a window for each con s tr a i nt. The wi n dow will a p pear as foll ow s;;Forcell reference, fill in con s traint (the na m e of t he const r aint). Click on the arrow and choose equal. Then in the box that s ays constraint, fill in the nu m ber 1. This constraint entered in this way guarantees t h at t h e weights s u m to 1. Then click OK ifthis is t h e last constraint that h a s been added.;Afteryou have filled in all ofthe ap p ropriate infor m ation in the solver b o x, it should appear as follows.;Then,clicking Solve allows solver to choicethe opti m al port f olio weights to solve this proble m.


Paper#78308 | Written in 18-Jul-2015

Price : $32