Excel Solver and Matrix Algebra PDF

Preview

Summary

EXCEL HELPER...

Description

EfficientPortfoliosinExcelUsingthe SolverandMatrixAlgebra ThisnoteoutlineshowtousethesolverandmatrixalgebrainExceltocomputeefficientportfolios.The exampleusedinthisnoteisinthespreadsheet3firmExample.xlsx,andisthesameexampleusedinthe lecturenotestitled“PortfolioTheorywithMatrixAlgebra”. Lastupdated:November24,2009

TheSolverAddIn ThesolverisanExcelAdd‐IncreatedbyFrontlineSystems(www.solver.com)thatcanbeusedtosolve generaloptimizationproblemsthat maybesubjecttocertainkindsofconstraints.Inthisnoteweshow howitcanbeusedtofindportfoliosthatminimizerisksubjecttocertainconstraints. Thesolveradd‐inmustbeactivatedbeforeitcanbeusedwithinExcel.InExcel2007,youactivateadd‐ insbyclickingontheofficebuttonandthenclickingontheExcelOptionsboxatthebottomofthe menu.



ThisopenstheExceloptionsdialoguebox.ClickAdd‐Ins,whichdisplaystheavailableAdd‐InsforExcel. MakesuretheSolverAdd‐InisanActiveApplicationAdd‐In.



MatrixAlgebrainExcel Excelhasseveralbuilt‐inarrayformulasthatcanperformbasicmatrixalgebraoperations.Themain functionsarelistedintablebelow ArrayFunction MINVERSE MMULT TRANSPOSE

Description Computeinverseofmatrix Matrixmultiplication Computetransposeofmatrix

 ToevaluateanarrayfunctioninExcel,youmustusethemagickeystokecombination:‐‐ (holddownallthreekeysatoncethenrelease).

ExampleData IntheDatatabofthespreadsheet3firmExample.xlsistheexamplemonthlyreturndataonthreeassets: Microsoft,NordstromandStarbucks.Themonthlymeansandcovariancematrixofthereturnsare computedandthesearereferencedastheinputdataontheportfoliotabasillustratedinthescreen shotbelow.

  Inthespreadsheet,cellscoloredlightbluecontaininputdata(fixeddatanotcreatedbysomeformula) andcellscoloredtancontainoutputdata(datacreatedbyapplyingsomeformula).Also,somecellsare explicitlynamed.ForexampletherangeofcellsB3:B5isnamedmuvec.Ifthesecellsarehighlighted thenmuvecwillappearintheNameBoxintheupperlefthandcornerofthespreadsheet.Similarly,the rangeofcellsE3:G5isnamedsigma.Formatrixalgebracalculations,itisconvenienttousenamed rangesinarrayformulas.

TheGlobalMinimumVariancePortfolio Theglobalminimumvarianceportfoliosolvestheoptimizationproblem

min σ 2p , m = m′Σm s.t. m′1 = 1  m

Thisoptimizationproblemcanbesolvedeasilyusingthesolverwithmatrixalgebrafunctions.The screenshotoftheportfoliotabbelowshowshowtoset‐upthisoptimizationprobleminExcel.

 TherangeofcellsD10:D12iscalledmvecandwillcontaintheweightsintheminimumvarianceportfolio oncethesolverisrunandthesolutiontotheoptimizationproblemisfound.Beforethesolveristobe run,thesecellsshouldcontainaninitialguessoftheminimumvarianceportfolio.Asimpleguessfor thisvectorwhoseweightssumtooneis mmsft = 0.3, mnord = 0.3, msbux = 0.4. Tousethesolver,acellcontainingthefunctiontobemaximizedorminimizedmustbespecified.Here, thiscellisF10whichcontainsthearrayformula {=MMULT(TRANSPOSE(mvec),MMULT(sigma,mvec))} 2

whichevaluatesthematrixalgebraformulaforthevarianceofaportfolio: σ p ,m = m′Σm .Noticethat theformulaissurroundedbycurlybraces{}.Thisindicatesthat‐‐wasusedto evaluatetheformulasothatitistobeinterpretedasanarrayformula.Ifyoudon’tseethecurlybraces thentheformulawillnotbeevaluatecorrectly.Wealsoneedacelltocontainaformulathatwillbe usedtoimposetheconstraintthattheportfolioweightssumtoone: m ′1 = m msft + m nord + m sbux = 1.  ThisformulaisspecifiedincellE10as =SUM(mvec) Thesolveradd‐inislocatedonthedatatabofthetopmenuribbonintherighthandcorner.Torunthe solver,clickthecellcontainingtheformulayouwanttooptimize(cellF10,andnamedsig2px)andthen clickonthesolverbutton.Thiswillopenupthesolverdialogueboxasshownbelow.

 ThefieldnamedSetTargetCellmustcontaineitherthenameorthereferencetothecellcontainingthe formulatooptimize.Youhavethreechoicesforthetypeofoptimization:Max,MinandValueof.Here, wewanttominimizetheportfoliovariancesoMinshouldbeselected.Next,wemustspecifythecells containingthevariableswhicharebeingoptimized.ThesearespecifiedintheByChangingCellsfield. Here,wecantypeinthenamemvecorspecifytherangeofcellsD10:D12.Finally,wemustAddthe constraintthattheweightssumtoone.WedothisbyclickingtheAddbutton,whichopenstheAdd Constraintdialogueboxshowbelow.

 TheCellReferencecontainsthecell(E10)thathastheformulafortheconstraint

m′1 = mmsft + mnord + msbux = 1. Wespecifythevalueoftheconstraint,1,intheConstraintfield.Once everythingisfilledin,clickOKtogobacktothesolverdialogue.Thecompletedialogueshouldlooklike oneshownbelow.

 Torunthesolver,clicktheSolvebutton.Thecomputationisgenerallyveryfast.Ifsuccessful,youshould seethefollowingdialoguebox

 Themessage“Solverfoundasolution.Allconstraintsandoptimalityconditionsaresatisfied”meansthat thefirstandsecondorderconditionsforaminimumaresatisfied.ClicktheKeepSolverSolutionoption buttonandthenclickOK.Yourspreadsheetshouldlookliketheonebelow.

 Theglobalminimumvarianceportfoliohas44%inMicrosoft,36%inNordstromand19%inStarbucks. TheexpectedreturnonthisportfolioisgivenincellC13(calledmupx)andiscomputedusingthe formula μ p , m = m′μ .TheExcelarrayformulais {=MMULT(TRANSPOSE(mvec),muvec)} TheportfoliostandarddeviationincellC14isthesquarerootoftheportfoliovariance,sig2px,incell F10.

MinimumVariancePortfoliosubjecttoTargetExpectedReturn Aminimumvarianceportfoliowithtargetexpectedreturnequalto μ0 solvestheoptimizationproblem

min σ p2 ,y = y′Σ y s.t. y′μ = μ0 and y′1 = 1  y

Thisoptimizationproblemcanalsobeeasilysolvedusingthesolverwithmatrixalgebrafunctions.The screenshotbelowshowshowtoset‐upthisoptimizationprobleminExcelwherethetargetexpected returnistheexpectedreturnonMicrosoft(4.27%).

 TherangeofcellsK10:K12iscalledyvecandwillcontaintheweightsintheefficientportfoliooncethe solverisrunandthesolutiontotheoptimizationproblemisfound.Beforethesolveristoberun,these cellsshouldcontainaninitialguessoftheminimumvarianceportfolio.Asimpleguessforthisvector whoseweightssumtooneis y msft = 0.3, y nord = 0.3, y sbux = 0.4. Thecellcontainingtheformulafor 2

portfoliovariance, σ p , y = y ′Σy ,isincellO10whichcontainsthearrayformula {=MMULT(TRANSPOSE(yvec),MMULT(sigma,yvec))} Wealsoneedtwoadditionalcellstocontainformulasthatwillbeusedtoimposetheconstraintsthat theportfolioexpectedreturnisequaltothetargetreturn, μ p , y = y ′μ = μ 0 ,andthattheportfolio weightssumtoone, y ′1 = ymsft + ynord + ysbux = 1. TheseformulasarespecifiedincellsL10andN10, whichcontaintheExcelformulas=SUM(yvec)and{=MMULT(TRANSPOSE(yvec),muvec)},respectively. Torunthesolver,clickcellO10(calledsig2py)andthenclickonthesolverbutton.Makesurethesolver dialogueboxisfilledouttolookliketheonebelow.



Noticethattherearenowtwoconstraintsspecified.Thefirstoneimposes y′1 = ymsft + ynord + y sbux = 1 , andthesecondoneimposes μp , y = y′μ = μ 0 = μ msft = 0.0475 .Torunthesolver,clicktheSolve button.Youshouldseeadialogueboxthatsaysthatthesolverfoundasolutionandthatalloptimality conditionsaresatisfied.KeepthesolutionandclickOK.Yourspreadsheetshouldlookliketheone below.

 Theefficientportfoliohasweights ymsft = 0.83, ynord = − 0.09, ysbux = 0.26.NoticethatNordstromis soldshortinthisportfoliobecauseithasanegativeweight.Theexpectedreturnonthisportfoliois equaltothetargetexpectedreturn(seecellN10namedmupy)andtheweightssumtoone.Noticethat thestandarddeviationofthisportfolio(seecellP10)issmallerthanthestandarddeviationofMicrosoft (seecellC3).

ComputingtheEfficientFrontierofRiskyAssets Theefficientfrontierofriskyassetscanbeconstructedfromanytwoefficientportfolios.Anatural questiontoaskiswhichtwoefficientportfoliosshouldbeused?Ifindthatthefollowingtwoefficient portfoliosleadstotheeasycreationoftheefficientfrontier: 1. Efficientportfolio1:globalminimumvarianceportfolio 2. Efficientportfolio2:efficientportfoliowithtargetexpectedreturnequaltothehighestaverage returnamongtheassetsunderconsideration. Forthecurrentexample,theassetwiththehighestaveragereturnisMicrosoft(averagereturnis4.27%) andwealreadycomputedtheefficientportfoliowithtargetexpectedreturnequaltotheaveragereturn onMicrosoft. Givenanytwoefficientportfolioswithweightvectorsmandytheconvexcombination

z = α ⋅ m + (1 − α ) ⋅ y 

foranyconstant α isalsoanefficientportfolio.Theexpectedreturnandvarianceofthisportfolioare

μ p, z = α ⋅ μ p, m + (1 − α ) ⋅ μ p, y σ p2, z = α 2σ 2p, m + (1 − α ) 2σ 2p, y + 2α (1 − α )σ my

,

wherethecovariancebetweenthereturnsonportfoliosmandyiscomputedusing σ my = m′Σy .To createtheefficientfrontier,createagridof α valuesstartingat1anddecreaseinincrementsof0.1. Useasmanyvaluesinthegridasnecessarytomakeaniceplot. Ascreenshotofthepartofthespreadsheettocreatetheseportfoliosisshownbelow.

 Considerthefirstconvexcombinationwith α = 1 .Thisportfolioistheglobalminimumvariance portfolio.ThecellP20containstheformula=N20*mupx+O20*mupyfortheexpectedportfolioreturn, andthecellQ20containstheformula=N20^2*sig2px+O20^2*sig2py+2*N20*O20*sigmaxyforthe portfoliovariance.ThecovariancetermsigmaxyiscomputedinthecellR9(notshown)whichcontains thearrayformula{=MMULT(MMULT(TRANSPOSE(mvec),sigma),yvec)}.ThecellsS20:U20givethe weightsintheconvexcombinationcomputedusingthearrayformula {=TRANSPOSE(N20*D10:D12+O20*K10:K12)}.

Theefficientfrontiercanbeplottedbymakingascatterplotwiththeexpectedreturnvalues(cells P20:P50)onthey‐axisandthestandarddeviationvalues(cellsR20:R50)onthehorizontalaxis.

ComputingtheTangencyPortfolio ThetangencyportfolioistheportfolioofriskyassetsthathasthehighestSharpe’sslope.Thisportfolio canbefoundbysolvingtheoptimizationproblem

max t

t′μ − rf 1/ 2

( t′Σ t )

s.t. t ′1 = 1 

Thisoptimizationproblemcanalsobeeasilysolvedusingthesolverwithmatrixalgebrafunctions.The screenshotbelowshowshowtoset‐upthisoptimizationprobleminExcel.

 TherangeofcellsD33:D35iscalledtvecandwillcontaintheweightsinthetangencyportfoliooncethe solverisrunandthesolutiontotheoptimizationproblemisfound.Beforethesolveristoberun,these cellsshouldcontainaninitialguessoftheminimumvarianceportfolio.Asimpleguessforthisvector whoseweightssumtooneis t msft = 0.3, t nord = 0.3, t sbux = 0.4. ThecomputationofSharpe’sslopeis brokendownintotwopieces.ThefirstpieceisthenumeratorofSharpe’sslope, μ p ,t − rf = t ′μ − rf , andiscomputedincellF33usingthearrayformula{=MMULT(TRANSPOSE(tvec),muvec)‐rf}.Thesecond 2

pieceisthesquareofthedenominatorofSharpe’sslope, σ p ,t = t′Σt ,andiscomputedincellG33using thearrayformula{=MMULT(TRANSPOSE(tvec),MMULT(sigma,tvec))}.Finally,Sharpe’sslopeis evaluatedincellH33usingtheformula=F33/SQRT(G33).Thisisthecellthatispassedtothesolver. Torunthesolver,clickcellH33andthenclickonthesolverbutton.Makesurethesolverdialogueboxis filledouttolookliketheonebelow.

 MakesurethattheMaxbuttonisselectedbecausewewanttomaximizetheSharpe’sslope.Torunthe solver,clicktheSolvebutton.Youshouldseeadialogueboxthatsaysthatthesolverfoundasolution andthatalloptimalityconditionsaresatisfied.KeepthesolutionandclickOK.Yourspreadsheetshould lookliketheonebelow.

 Thetangencyportfoliohasweights tmsft = 1.03, tnord = −0.32, t sbux = 0.30. NoticethatNordstromissold shortinthisportfoliobecauseithasanegativeweight.Theexpectedreturnonthisportfolio, μ p ,t = t′μ , isgivenincellC36(calledmut)andiscomputedusingthearrayformula {=MMULT(TRANSPOSE(tvec),muvec)}.

ComputingEfficientPortfoliosofTBillsandRiskyAssets Fromthemutualfundseparationtheorem,theefficientportfoliosofT‐Billsandriskyassetsare combinationsofT‐Billsandthetangencyportfolio.Theexpectedreturnandstandarddeviationvalues oftheseportfoliosarecomputedusing

μep = rf + xtan ( μtan − r f ) σ ep = x tanσ tan



Ascreenshotofthespreadsheetwheretheseportfoliosarecomputedisgivenbelow.

 Theportfoliowith xtan = 0 isshowninthecellsJ34:L34.TheexpectedreturniscomputedincellK34and isgivenbytheformula=rf+J34*(mut‐rf).ThestandarddeviationiscomputedincellL34andisgivenby theformula=J34*sigt.Thenamedrangesigtisthestandarddeviationofthetangencyportfolioandis givenincellC37.   



EfficientPortfolioswithNoShortSalesConstraints Inmanysituationsshortsalesofassetsarenotallowed.Recall,ashortsaleofanassetoccurswhenyou borrowtheassetandthensellit.Theproceedsoftheshortsaleareusuallyusedtofinancethepurchase ofotherassets.Becausetheassetwasborrowediteventuallyhastobereturned.Youdothisby repurchasingtheassetatsometimeinthefutureandthenreturningtheassettowhomeveryou borroweditfrom.Youmakeaprofitonashortsaleifthepriceoftheassetdropsduringtheperiodof timeyouhaveborrowedtheassetbecauseyourepurchasetheassetforapricelessthanforwhatyou originallysoldit.Inthecontextofportfoliotheory,whenyoushortsellanassetthecorresponding portfolioweightisnegative.Hence,whenshortsalesareprohibitedalloftheportfolioweightsmustbe constrainedtobepositive.Thistypeofnon‐negativityconstraintiseasytoimposeinthesolver.

MinimumVariancePortfoliosubjecttoTargetExpectedReturnwithNoShort Sales Aminimumvarianceportfoliowithtargetexpectedreturnequalto μ0 andnoshortsalessolvesthe optimizationproblem

min σ 2p , y = y′Σy s.t. y′μ = μ0 , y′1 = 1 and yi ≥ 0  y

Thisoptimizationproblemcanalsobeeasilysolvedusingthesolverwithmatrixalgebrafunctions.The screenshotbelowshowshowtoset‐upthisoptimizationprobleminExcelwherethetargetexpected returnistheexpectedreturnonMicrosoft(4.27%).Previously,wesolvedthisproblemwherewe allowedforshortsales.Inthatcase,theefficientportfoliowas y msft = 0.83, y nord = −0.09, y sbux = 0.26.  NoticethatNordstromwassoldshortinthatportfolio.Nowwewanttoimposethenoshortsales restrictions.WesetuptheExcelspreadsheetexactlyhowwedidbefore.Theonlydifferenceoccursin howweusethesolver.Weaddanadditionalconstraintthatforcesalloftheportfolioweightstobe positive.Thescreenshotbelowshowstheinitialset‐up.

 TherangeofcellsAC5:AC7iscalledwvecandwillcontaintheweightsintheefficientportfoliooncethe solverisrunandthesolutiontotheoptimizationproblemisfound.Beforethesolveristoberun,these

cellsshouldcontainaninitialguessoftheminimumvarianceportfolio.Thecellcontaining theformula 2 forportfoliovariance, σ p ,w = w′ Σy ,isincellAG5whichcontainsthearrayformula

{=MMULT(TRANSPOSE(wvec),MMULT(sigma,wvec))} ThecellsAD5andAF5containtheExcelformulas=SUM(wvec)and{=MMULT(TRANSPOSE(wvec), muvec)}thatwillbeusedtoimposetherestrictionsthattheportfolioweightssumtooneandthatthe portfolioexpectedreturnisequaltothetargetreturn.Torunthesolver,clickcellAG5andthenclickon thesolverbutton.Makesurethesolverdialogueboxisfilledouttolookliketheonebelow.

 Toaddthenoshortsalesconstraints,clicktheAddbuttontoopentheAddConstraintdialogue.Theno shortsalesconstraintsinequalityconstraintsontheelementsofwvec:

 ClickOK.Thefinalsolverdialogueshouldlookliketheonebelow.

 Youshouldseeadialogueboxthatsaysthatthesolverfoundasolutionandthatalloptimality conditionsaresatisfied.KeepthesolutionandclickOK.Yourspreadsheetshouldlookliketheone below.

 Theefficientportfoliohas100%inMicrosoftand0%intheotherassets....