Modern portfolio theory (mpt) | Business & Finance homework help

EXERCISE 4Creating a hyperbola:One of the salient features of Modern Portfolio Theory (MPT) is the phenomenon of putting twostocks together such that the resulting portfolio has a lower standard deviation (lower risk) thaneither of the component stocks. This is the magic of diversification and this exercise illustratesthe phenomenon graphically.1) Overview: Given the pps data of two firms* from your portfolio file of last week you will useExcel to create the hyperbola that would result from plotting the risk/return profile of a set of fiveportfolios, each comprised of the two stocks as the dollar mix goes from 100% of the dollars instock A and 0% in stock B, to 0% in Stock A and 100% in stock B.* Note: For Spring 2014, stock A= GE & stock B=GOOG.2) You will begin by generating data for these five portfolios. The data will be risk and return,where risk is the standard deviation (population) of the daily returns of the portfolio and returns isthe total annual (or 12 month) return.3) The dollar mix of the five portfolios that you plot will be:Portfolio 1: 100% stock A and 0% stock BPortfolio 2: 75% stock A and 25% stock BPortfolio 3: 50% stock A and 50% stock BPortfolio 4: 25% stock A and 75% stock BPortfolio 5: 0% stock A and 100% stock B
4) Start by creating 5 separate portfolios in a new sheet (or “tab”) [Rename the tab “hyperbola”]in your portfolio file with the two firms mentioned above (stockA and stockB). Each of the fivenew portfolios will have the three tiers (pps, mkt value, daily returns) and each portfolio will havethree columns (the total portfolio, stockA , stockB). There will be a total of 15 new columns.See also the screenshot called “hyperbola snapshot” available on the Moodle webpage.5) Populate tier1 of stock A and stock B with the original pps data found in sheet1. Do this fivetimes, that is, once for each of the five portfolios.6) Calculate the “normalized number of shares” for stockA and stockB using =10000/ averagepps . Enter “number of share” data for both stocks in each of the five portfolios such thatportfolio1 has 100% of the normalized number of shares for stockA and 0% of the normalizednumber of shares for stockB. Portfolio2 has a 75% and 25% mix, and so on for the other threeportfolios.7) Using the same logic and formulas as in the main sheet, calculate the Market Values (Tier2)and Daily Returns (Tier3) for all 5 portfolios in the hyperbola tab.8) Calculate the Standard Deviation (of the population using =STDEVP ) of the Daily Returns ofeach of the 5 portfolios. Put these in a row below tier 3.9) Calculate the Total Return (top to bottom of Tier2) for each of the 5 portfolios using (last dayfirst day)/first day. Put these in a row below the STDEVP.10) Build and new little block of data (see again “hyperbola snapshot”) in which you display thestd deviations and total returns of the five portfolios. For each of the cells you can simply
reference the appropriate cell above. For example, use “=B766” [I’m don’t the actual rownumber] to display “.01857” as calculated in a previous cell.11) Chart the stdevp and totalK of the 5 portfolios. Use “scatter with data points connected bysmooth lines”.12) Optional: Modify the two axis so as to exaggerate the curve. Chart Tools>Layout >Axis.That is, by moving the borders closer to the actual curve, the curve will become morepronounced.