**EXERCISE
4**

**Creating a hyperbola:**

One

of the salient features of Modern Portfolio Theory (MPT) is the phenomenon of

putting two stocks together such that the resulting portfolio has a lower

standard deviation (lower risk) than either of the component stocks. This is the magic of diversification and this

exercise illustrates the phenomenon graphically.

1)** **Overview:** **Given the pps data of two

firms* from your portfolio file of last week you will use Excel to create the

hyperbola that would result from plotting the risk/return profile of a set of

five portfolios, each comprised of the two stocks as the dollar mix goes from

100% of the dollars in stock 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 is the 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 B

Portfolio

2: 75% stock A and 25% stock B

Portfolio

3: 50% stock A and 50% stock B

Portfolio

4: 25% stock A and 75% stock B

Portfolio

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 five new portfolios

will have the three tiers (pps, mkt value, daily returns) and each portfolio

will have three 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 five times, that is,

once for each of the five portfolios.

6) Calculate the “normalized number of shares”

for stockA and stockB using =10000/ average pps . Enter “number of share” data for both stocks

in each of the five portfolios such that portfolio1 has 100% of the normalized

number of shares for stockA and 0% of the normalized number of shares for

stockB. Portfolio2 has a 75% and 25%

mix, and so on for the other three portfolios.

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 of each 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 day-first 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 the std 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 row number] 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 by smooth 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 more pronounced.