EXERCISE 8: Number of Firms (NooF)

The Question is. . . How many (randomly selected) firms does one need in his/her portfolio in order to be diversified enough to have the same risk as the market?

The Model: The adjacent chart shows that as more firms are added to a portfolio, the risk of the portfolio tends to decline to the level of the market's risk (systematic risk). The first point on the left on the red line represents a portfolio with one security. The risk of the portfolio is thus the same as the risk of that single security. The next point shows how the addition of a second security to the portfolio reduces the risk of that portfolio. With the addition of each additional security, the portfolio’s risk tends to diminish until it reaches the market risk. Note that the market risk is the same as the risk of a portfolio with every publically traded security included.

The Assumptions:

1) Individual firms are riskier than the market as a whole;

2) As more firms are added to the portfolio, the overall volatility of the portfolio will decline.

3) At some point (i.e. at some number of firms having been added to the portfolio), the volatility of the portfolio will be [virtually] the same as the market.

Creating the Chart:

Create a chart in Excel showing the declining standard deviations of a portfolio (use your port.xls) as additional firms are added to the portfolio. The vertical axis is “std dev”, the horizontal axis is the “number of firms” in the portfolio (ranging from 1 to “no of firms in portfolio”). Also, show the standard deviation of the market as an extended horizontal line.

Recommended methodology:

Start by retrieving a copy of your portfolio spreadsheet with the three tiers from week3.

Copy the first three tiers onto a new "sheet" (but still in your portfolio file). Rename the sheet “noof”.

Calculate standard deviations of the daily returns of the portfolio, every firm, and the market [you may have already done this]. This STDEVP row should probably be immediately under tier3.

Sort the firms (but NOT the portfolio (col. B) or the market), from left to right, highest to lowest, by the stdevp of the firms. Include firm labels, no of shares, daily prices (tier1), market values (tier2) and daily returns (tier3) in your sort. [Note: This step is cheating. Do you see why?]

Go back to top of Tier1.

Insert a new row above "No of shares". In this new row, calculate for all firms a NEW "no. of shares" to equal a normalized no. of shares for $10K based on average share price. Your formula will look something like “ =10000/average(C6:C258)” . [I think these row numbers are correct, but you should double check]

Set (change) all the ACTUALno. of shares to zero ["initializing the data"]

The market value (Tier #2) should show all zeros.

Copy (and “paste special”/” values”) the first firm's normalized “no of shares” to the initialized row. That is, replace the first firms “no of shares” that is used in the tier2 market value calculation, changing it from zero shares to the number of shares required to have $10K of that firm.

Split your screen [drag down that tiny little bar above the scroll bar] Go to the bottom where you have the portfolio's stdevp

Cut 'n paste special/valuesthe stdevp of the port (into the new blank next row down) in the col for the first firm.

Go back to top. cut 'n paste special/values the 2nd firms normalized no of shares to the initialized no of shares row.

Go back to bottom and cut 'n paste special/values the new stdevp of the port to the col for 2nd firm. Continue for all 30 odd firms.

Create the graph: The stdevpsof the portfolios are the data to plot on your graph.
Note that there are a couple of videos in week8 in Moodle that show the process. The data is different than yours, but the videos might help you visualize what you’re trying to do.

Add the stdevp for the market as a red horizontal line.

Put your name in cell A1.

Save the file. Submit via Moodle2.