Supposewe have 3 risky assets whose net r turn has the ean vector and variance- covariance atrix given below: To odel the portfolio choiceproblem, I begin by highlighting the ean vector and giving it a a e. To do this, le – li k on cell c9 and drag d wn until c ll c11 and th n release. Then go to the na e-box, which isthe white box in the upperrightjustabove the “A” c l mn. Click in the na e-box, hit b ckspace, and then type a na e for cells c9 -c11. Then hit return. I used the na e u”for the vector of ean returns as illustrated below: Then,I oll w a si ilar approach with the varia ce-covaria ce atrix by clicking on cell F9 and then dragging across and down to cell H11. After the varia ce covariance atrix is highlighted, I go to the na e box nd give the variance covariance atrix the na e “vcov” (Note: I don’t use quotes in the na es). Thee cient ronti r c nsists ofpo t olios t atonly invest in the risky assets. Therefore, I introduce avector that eprese ts t e port lio weights in ach ass t. For now, I will assignt e weights arbit arily. Bel w, I willuseexcel to ch ose the wei hts opti ally. For now, I have placed the weights in cellsJ9 through J11 and given them the na e weights. Also, for convenience, I have created a colu n of ones and given it the name ones. Toillu rate why the na es are convenient, n te that for ven portfolio weights, the ean return on the risky asset portfolio is eq al to the trans ose of the weights ect r multiplied y the ean vector. Using excel s mat ix or ula s, the tran pose ofthe weights ve or is gi en y “tra spos (weigh )”, and to ultiply “transp se(weights)”by the ean vector ” u” simply requires using the excel function m ult, which stands for atrix ultiplication. he resulting eanreturn for the port olio is given by mmult(tran pose(weights),ones).In this ex res on, excel ulti lies the transpo e of weights by the vector of ones, producing the ean return on the portfolio. Toprogramthe ean return and st e it in a cell,one uses t e excel for at for atrix fo ulas. For exa ple, to store the ean return for the given weight vector in cell N9, click on cell N9, and then type “= ult(t anspose(weights),mu)” and then hit CTRL SHIFT ENTER. The quantity in t e cell will beequal to the ean return and will c nge when the weights change or when the ele ents of the ean return vector change. Toprogramthe variance of the portfolio ret rn, I use the fact that the ari nce of the port olio return is t e tr nspose ofthe weights v ctor ultiplied by varia ce cova an e atrix ultiplied by the weights ve or. To prog amthis, rst I ultiply the transp seof of weights vector ti es the variance-covar ance atrix. This produces the expression mmult(tran pose(weights),vcov). T en, I have to ultiply this expres onby weights. Therefore, the final answer for portfolio variance is mmult(mmult tra spose weights),v ov),weights). I have typed this expr ssion i to c ll O9 using the sa e approach that I used for typing the ean return. Clicking on the cell will hig li ht the or ula at the top ofthe excel spreadsh et.By changi g the weights or changing t e ele ents of the varia ce cov ance atrix, t e port olio v ance will change. The standard deviation of the portfolio eturn is ju t the squa e oot ofthe variance, and is given in cell P9. To co pute portfolio s d, I na ed portfolio variance portvar. The fo ula for portfolio std (standarddeviation) is just port ar^.5. It is entered in cell P9 with an equal sign before the fo u a. Then you hit return to enter the formula. Weare al st ready to art to use e cel toco ute the efficient frontier for this set of3 assets. To co pute the efficient fr ntier, we need to constrain the portfolio weights so that that th y sumto 1. This is e uivalenttoi posingtheconditionthat mmult(tran pose(one ) eights) s s to 1. ewill i pose this con traint when choosing portfolio weights. To do so, I have assignedtheconstraintto cell N17 by typing the fo ulammult(transpose(ones), ghts) i to t e cell. h n I use the constrained axi zation progra , I will s t the cell equalto1, which will con tr n the weights that Ican choose. I have chosen to na e the constraint “constraint”. Thelast item to note before beginning to use sol er is that it is use ul to ore the r s lts of ax zation proble s in a convenient formfor further analysis. Towards this end, in row 79 I have stored the we ghts vector along with ean po tfolio return and portfolio std.To illustrate how this was done, I highl ghted cells b79-d79 and then in the formula bar typed =transpose(weights) and hit CTRL SH FT ENTER.This puts the transpose of the weights in a 1 by 3 row vector. hen, incells e79, f79, and g79, Isimply repeated the na es port ean,, portvar, and portstd. This produces the sa e values at these cells that were co puted earlier. USING LVER O IND POR FOLIO WEIGHTS Thetextbook provides the answer to solving for the efficient frontier when there are 2 risky a sets. hen there are ore than t o riskyassets, there are ore complicated atrix ormulas or sol ing or the cient rontier. If t ere are co plicated constraints, there ay not be a formula for the efficient f ontier. In this course, I will e phasize using a axi zation programto solve for thee cient ro ti r. More s ci ically, we will use the excel rogr mSOLVER to ind the oints on the e cient r nti r. SOLVERis an excel ad -in pac age.To find solver, click on tools on the enu bar. If you see solver in the nu, then it is inst lled and ready to be used. If you don t find solver, then click on tools, Add-Ins, then c eck olver, and click OK. This should give you the capability to use solver. Toillu r te how to use to nd pointson the e cient rontier, rec ll t at t e lowest p int on the efficient frontier is the point that n zes standard deviation of the portfolio return. To solve for the ean and std of ret rn of the n mumstd portfolio, and to solve for the weights in the portf lio, click on cell h79 (with the s d on top) and then click on tools, solver. The result should giveyou a window that appears as follows: Clickon set target cell, th n, if it not already hi hlighted, click on h79 because we want to n ze portfolio st
. After choosing t e tar et cell t en click on Min because we want to ni ze the value of portfolio sta dard deviation. o do this, we change the weights in the portfolio, hence, write in we ghts where it says by changing cells. Then, where it says subject to the constraints c ick Add. This should bring up a window for each con tr nt. The wi dow will a pear as foll s: Forcell reference, fill in con traint (the na e of he const aint). Click on the arrow and choose equal. Then in the box that ays constraint, fill in the nu ber 1. This constraint entered in this way guarantees t at t e weights s m to 1. Then click OK ifthis is t e last constraint that h s been added. Afteryou have filled in all ofthe ap ropriate infor ation in the solver b x, it should appear as follows. Then,clicking Solve allows solver to choicethe opti al port olio weights to solve this proble . Theweight , ean retu n, and stan ard dev ati n in li e 79 will have c anged beca se I used solver. In particular, the new weights are t ose associated with the n m std portfolio. he sa e is true of the an, variance, and std of the portfolio. Tosave the new results, highlight them ith your cursor, then right click and choose copy. Then, highlight the part of a row with the sa e number of ele ents where you want you save your results, andthen right click and choosethe special paste function. This will st re your results for this p rtfolio. IMPORTANTLY, you need to use special pasteso that your answers are stored, and not changed with e ch successive use of solver, or change inthe p rt olio weig s. Put di erently, sp cial aste co es the nu b rsin the table, but does not ve the cells when it oves the nu bers. Therefore, l ter analysis won t affectthe stored values fromspecial paste. Finally, to use special paste, you need to specify special paste and then choose “values”. The rst it min results is the nimumstd portfolio, it has a ean of 0.07 and std of 0.730297.To co pute another point on the eff cient frontier I use solver to find the portfolio with the s allest variance or a port olio that has a ean return of 0.08; then I will do t e a e for ean return of .09, nd so on.After doing this, for any points, I can graph t e efficient frontier. Tograph the next point on theefficient frontier, the req ire ent that it has a ean return of 0.08 is a constraint. Therefore, I will needto add a seco d constraint when I inv ke solver. Ot erwise, t e pproach is si ilar towhat I did for the fir t p int. There ore I click on cell h79, go to tools, solver, and then change the entriesas needed (solver usuallysaves your previous choices so you on y need to change themslightly when co puting different points on the efficient fro tier. To chan e the ele ents of a constr nt t at sol er has aintained as the default, click on the con t ai t in the s lver window, then click on change, then ake the ch nge that is needed and then click on OK. Once you have entered all of the constraints, click on solve. HELPFULHINTS: 1.R er to give na es to cells that c ntain fo ulas.Then you can refer to the cell by its na e instead of having to re mber t e cell location (suchas h79). Note: excel does not allow you to rena e a cell after you have given it a na e. 2.Use a split screen ew when invoking s lver. To split t e screen, go to window, and then choose split fromthe enu. Doing so will cause the s read sheet to appear in 4 quadrants.By clicking and ho ding on the lines where the screen splits, you can change the split p ints. hen I changed thesplit p ints,I dragged t e vertic l li e to the extre e le t so that y screen is onlysplit e tic lly.Then, I dragged t e horizo tal line upwards so that the bottom screen area was l r er than the top.Given the e splitting points, in the upper screen I used the scroll bar to go to li e 79. This is the line that containsthe results for the weight vector, and in which cells f79, g79, and h79 contain the fo ulas for the ean, variance, and standard e ation ofthe port olio with the weig s given in cells b79 through d79. Therefore, I cansi ply click on these cells within solver when choosing which cells to axi ze or ni ze and when i posing the constraints on the ean return. hen I use solver the oints in li e 79 will cha ge. A ter e ch use ofsolver, I j st c py the poi s inthis line a d then special p ste them into lines 167 to 172. Because I use split screen, I don’t ha e to scr ll ar und the s reen when I u e solver and then copy a d paste fromline 79. Thereare t o final ite . First, is graph ng the efficient frontier. To do so and use excel s graphing routines, I copy the standard deviation and eancolumns that begin with row 167 and put t emnext to each other below: Then,I highlight both colu nsof data. Then I click on the chart wizard (shown as bar chart in toolbar enu). Then I choose xygraph, and then click on the graph subtype within the xy category. Then I click on next. In the next window, I indi ate t at t e data is in colu s. Then I click next. Then, Ichoose titles and gridli es as appropriate and click next. Then, I choose where the graphicswill appear. In ycase I chose themto appear as a new sheet.Then I click finish.The efficientfrontier is graphed on the page labelled Chart1. Thelast taskis nding t e port olio we ghts, and ean and return of the tangency portfolio. uppose the risk free rate is 0.04percent. Then, the to find the tangency portfolio, e si ply find the portfolio that has the ax al sharpe ratio. I have na ed cell b227 rf which stands for riskfree rate. Thesharpe ratio is the po tfolio eanreturn nus the riskfree rate divided by the standard deviation of t e portfolio return. I have program ed the sharpe ratio as ( eanport – rf) portstd in cell D227.Then I used solver to ax ze the shar e ratio su ect to the onstraintsthattheport olio weights sumto 1. The ax l sharpe ratio is Theportfolio weights and and ean varia ce andstd of the ort olio with axi al sharpe ratio (also known as the t ngency portfolio) is given below: Thisconcl des this a dout. You should fa iliarize yourself with solver and with sing excel to ake portfolio co pu ations using the basic appr achthat is used in this handout.