University of The Cumberlands Netflix IBM APPLE Stock Portfolio Excel Sheet & PPT

User Generated

xonynwvhf

Economics

University of the Cumberlands

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.

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

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...


Anonymous
Really helped me to better understand my coursework. Super recommended.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags