portfolio analysis using excel

《portfolio analysis using excel》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、9PortfolioAnalysisUsingExcelJASONLAWSABSTRACTThischapteranalysesthefamiliarMarkovitzmodelusingExcel.ThistopicistaughtonfinancedegreesandMastersprogrammesatUniversitiesallovertheworldincreasinglythroughtheuseofExcel.Wetaketimeouttoexplainhowthespreadsheetissetu

2、pandhowsimpleshortcutscanmakeanalysisofthistypeofproblemquickandstraightforward.Inthefirstsectionofthechapterweusea2variableexampletoshowhowportfolioriskandreturnvarywiththeinputweightsthenhegoesontoshowhowtodeterminetheoptimalweights,inariskminimisationsense,

3、usingbothlinearexpressionsandalsousingmatrixalgebra.InthesecondpartofthechapterweextendthenumberofassetstosevenandillustrateusingmatrixalgebrawithinExcelthatMarkovitzanalysisofann-assetportfolioisasstraightforwardastheanalysisofatwoassetportfolio.Wetakespecia

4、lcareinshowinghowthecorrelationmatrixcanbegeneratedmostefficientlyandhowwithinthesameframeworktheoptimisationobjectivecanbemodifiedwithoutfuss.9.1INTRODUCTIONThemotivationforthischapteristoshowhowtheMarkovitzmodelofportfolioriskcanbemodelledinExcel.Thegoalofthi

5、spaperisnottoprovideanin-depthdiscussionofthismodelbutinsteadtoshowhowitcanbeimplementedwithinExcel.Excellentdiscussionsofthismodelcanbefoundinawiderangeofcorporatefinanceorinvestmenttextbooks,includingCuthbertsonandNitzsche(2001).However,itisworthwhiletoprovi

6、deabriefoverviewofthistheory.Inessencetheunderlyingrationaleisbasedonthetwinfacetsofexpectedriskandreturn,withriskbeingmeasuredbythevarianceorstandarddeviationofexpectedreturns.Theefficientfrontieroftheportfolioisgivenby:1.Thoseassetswhichofferahigherreturnfor

7、thesamerisk,orequivalently2.Thoseassetswhichofferalowerriskforthesamereturn.Theprecisepositionaninvestortakesontheefficientfrontierdependsonhis/herutilityfunctionbasedonthetwomotivesofexpectedreturnandrisk;inotherwordstheutil-ityfunctionispositivelyslopedinthe

8、return/riskdomain.InitiallyIassumethatthisutilityfunctionisverysteep(i.e.theindividualishighlyriskaverse)sohis/herdesiredpositionisacornermaximumwhereriskisminimisedwithrespecttojusttwoas