## Description

This is complete write up of your portfolio formation in a Word file, with calculations copied and pasted in it from your Excel file.

1. Select the stocks of three publicly traded companies from different industries. State the criteria for selecting those securities.

2. Retrieve monthly data on adjusted closing prices of your securities from Yahoo Finance for the past 10 years and calculate the monthly rate of return of the stocks for every month.

3. Calculate the mean, variance, and standard deviation of the stocks’ monthly returns.

4. Calculate the correlation coefficient between every possible pair of stocks’ returns.

5. Decide what percentage of your money (weights) you want to invest in each stock and state the criteria you used to select those weights.

6. Now calculate your portfolio’s mean monthly return, variance, and standard deviation.

7. Assuming your portfolio return follows a normal distribution, calculate the chance that your portfolio loses 10% of its value during any month?

8. Assuming you have invested $100,000 in your portfolio, what is value at risk (VaR) of your portfolio at any particular month at 99% confidence level?

9. Now randomly change your portfolio’s weights 100 times (note total weights should always be 100%), for each weight combination calculate the mean and standard deviation of your portfolio, and then draw the efficient frontier.

10. For each item mentioned above explain your rationale and cite peer-reviewed and/or seminal sources.

. Your CLA2
submission (cumulative report) should be a minimum of 4-6 pages in length. The CLA assignments
encompass the learning outcomes for this course and are designed to demonstrate what has been
learning or achieved by the student.

In addition to your CLA2 report, please prepare a professional PowerPoint presentation summarizing your findings for CLA2. The presentation will consist of your major findings, analysis, and recommendations in a concise presentation of 15 slides (minimum). You should use content from your report as material for your PowerPoint presentation.

## Explanation & Answer

Attached.

Running Head: STOCK PORTFOLIO

1

STOCK PORTFOLIO

Name:

Institution:

Date:

STOCK PORTFOLIO

2

Questions:

1. Select the stocks of three publicly traded companies from different industries. State the

criteria for selecting those securities.

a) Netflix

b) IBM

c) APPLE

The three companies were chosen on their performance basis and the completion of the

data throughout the 10 years.

2. Retrieve monthly data on adjusted closing prices of your securities from Yahoo Finance

for the past 10 years and calculate the monthly rate of return of the stocks for every month.

Attached is the excel work-book are the company's datasets over the 10-year period, that is

from April 2010 to April 2020

3. Calculate the mean, variance, and standard deviation of the stocks’ monthly returns

The mean was evaluated as;

𝑚𝑒𝑎𝑛 =

∑ 𝑥𝑖

𝑛

∑ 𝑥𝑖 = 𝑆𝑢𝑚𝑚𝑎𝑡𝑖𝑜𝑛 𝑓𝑜𝑟 𝑡ℎ𝑒 𝑖𝑛𝑑𝑖𝑣𝑖𝑜𝑑𝑢𝑎𝑙 𝑜𝑏𝑠𝑒𝑟𝑣𝑎𝑡𝑖𝑜𝑛𝑠

𝑛 = 𝑇ℎ𝑒 𝑛𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑜𝑏𝑠𝑒𝑟𝑣𝑎𝑡𝑖𝑜𝑛

𝑉𝑎𝑟𝑖𝑎𝑛𝑐𝑒 =

∑(𝑥𝑖 − 𝜇)2

𝑛

𝑆𝐷 = √𝑉𝑎𝑟𝑖𝑎𝑛𝑐𝑒

NETFLIX

MEAN

VARIANCE

SD

131.14

15686.15

125.24

IBM

MEAN

VARIANCE

SD

APPLE

MEAN

VARIANCE

SD

133.24

237.75

15.42

114.15

4323.09

65.75

STOCK PORTFOLIO

3

4. Calculate the correlation coefficient between every possible pair of stocks’ returns.

Correlation between two different stocks (x,y) is given by the following formula;

𝜎𝑥𝑦

𝜌𝑥𝑦 =

𝜎𝑥 𝜎𝑦

CORRELATION COEFFICIENT BETWEEN NETFLIX AND IBM

-0.120053789

CORRELATION COEFFICIENT BETWEEN NETFLIX AND APPLE

0.925168302

CORRELATION COEFFICIENT BETWEEN IBM AND APPLE

-0.013142074

5. Decide what percentage of your money (weights) you want to invest in each stock and

state the criteria you used to select those weights.

The cumulative weights calculated below were established by summing the volumes of all

traded stocks by the end of every month then the totals of all stocks were used to calculate the

individual weights. In general, the number of shares that are available for trading over the 10

years was over 54 billion while for IBM was 11 billion shares, APPLE since it is the best

performing amongst the three shares in the portfolio it has over 118 billion shares. The

weights act as a representation of the volume of shares in the market.

NETFLIX

WEIGHTS

IBM

22.08%

TOTAL

APPLE

4.81%

73.11%

100.00%

6. Now calculate your portfolio’s mean monthly return, variance, and standard deviation.

The formula to evaluate the expected portfolio return is given by:

𝛾𝜌 = 𝜔1 𝛾1 + 𝜔2 𝛾2 + 𝜔3 𝛾3

𝜔1 = 𝑊𝑒𝑖𝑔ℎ𝑡 𝑜𝑓 𝑡ℎ𝑒 𝑆𝑡𝑜𝑐𝑘 𝑖𝑛 𝑡ℎ𝑒 𝑝𝑜𝑟𝑡𝑓𝑜𝑙𝑖𝑜

𝛾1 = 𝐼𝑠 𝑡ℎ𝑒 𝑚𝑒𝑎𝑛 𝑣𝑎𝑙𝑢𝑒 𝑡ℎ𝑎𝑡 𝑡ℎ𝑒 𝑠𝑡𝑜𝑐𝑘 ℎ𝑎𝑠

But to evaluate the variance of the portfolio we need to have the following or rather use the

following formula;

σ2p=wσ21+w22σ22+w23σ23+2w1w2σ12+2w1w3σ13+2w2

w3σ23

STOCK PORTFOLIO

4

RETURN, VARIANCE AND STANDARD DEVIATION

MEAN

378.52

VARIANCE

20,246.99

SD

142.2919038

7. Assuming your portfolio return follows a normal distribution, calculate the chance that

your portfolio loses 10% of its value during any month?

Standardization using a normal distribution is;

𝑧=

(𝑥 − 𝜇)

𝜎

Where;

𝜎 = 𝑇ℎ𝑒 𝑠𝑡𝑎𝑛𝑑𝑎𝑟𝑑 𝑑𝑒𝑣𝑖𝑎𝑡𝑖𝑜𝑛

𝜇 = 𝑃𝑜𝑝𝑢𝑙𝑎𝑡𝑖𝑜𝑛 𝑚𝑒𝑎𝑛 𝑜𝑟 𝑡ℎ𝑒 𝑚𝑒𝑎𝑛 𝑢𝑠𝑒𝑑 𝑏𝑒𝑓𝑜𝑟𝑒

𝑥 = 𝑇ℎ𝑒 𝑠𝑎𝑚𝑝𝑙𝑒 𝑚𝑒𝑎𝑛

PROBABILITY OF LOSING 10%

NEW RETURN

340.6690583

VARIANCE

20,246.99

SD

142.2919038

STANDARDIZED

-0.26601737

PROBABILITY

0.395112917

8. Assuming you have invested $100,000 in your portfolio, what is the value at risk (VaR) of

your portfolio at any particular month at a 99% confidence level?

The Value at Risk is usually the cumulative weight multiplied by the current value of the

portfolio.

The cumulative weight is given by;

𝐶𝑀 =

1

𝑊1 + 𝑊2 + 𝑊3

Where:

𝑊𝑖 = 𝑇ℎ𝑒 𝑤𝑒𝑖𝑔ℎ𝑡 𝑔𝑖𝑣𝑒𝑛 𝑡𝑜 𝑡ℎ𝑒 𝑖𝑡ℎ 𝑎𝑠𝑠𝑒𝑡

VALUE AT RISK FOR 100,000

CUMULATIVE WEIGHTS

VaR

0.004844641

484.4641393

STOCK PORTFOLIO

5

9. Now randomly change your portfolio’s weights 100 times (note total weights should

always be 100%), for each weight combination calculate the mean and standard deviation of

your portfolio, and then draw the efficient frontier.

Using random numbers for both the expected return and the standard deviation, so that we

can derive an effective frontier for the portfolio that we have created between the three assets

or rather stocks. The visualization of the randomized portfolio weights is as below. Please

note that the visual keeps on changing because of the randomness of the numbers.

EFFICIENT FRONTIER

160

140

120

100

80

60

40

20

1

4

7

10

13

16

19

22

25

28

31

34

37

40

43

46

49

52

55

58

61

64

67

70

73

76

79

82

85

88

91

94

97

100

0

EXPECTED RETURN

STANDARD DEVIATION

STOCK PORTFOLIO

6

References

Cooper, R. G., Edgett, S. J., & Kleinschmidt, E. J. (2001). Portfolio management for new

products.

Cooper, R. G., Edgett, S. J., & Kleinschmidt, E. J. (1999). New product portfolio

management: practices and performance. Journal of Product Innovation Management: AN

INTERNATIONAL PUBLICATION OF THE PRODUCT DEVELOPMENT &

MANAGEMENT ASSOCIATION, 16(4), 333-351.

Jeffery, M., & Leliveld, I. (2004). Best practices in IT p...