write a finance equity concepts risk and return Discussion boardI attached the pdf of the assignment requirement, please fellow the step by step to finish the assignment, no plagiarism, when you submit the assignment please also provide a similarity report to prove no plagiarism.
Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -1 of 21- Chee / BU METDescription and Guidance for this assignmentIn the past few weeks, you have learned a lot in Corporate Financial Management and also Investment Analysis,particularly in the areas of equity — its basic valuation [DDM], and risk and return concepts in investments. Nowthat you are caught up in the readings and digested these concepts, it is time for you to revisit them and have ahands-on approach on verifying these concepts with some market data.Once again, we ask the question, does the classroom (“rubber”) meets the market (“road”)? This assignment hasTWO parts:Part [1] Download some historical stocks data, do some Risk-Return calculations and Portfolio Theory analysis.¾ Because of the length of this part of the assignment, it assessment has been increased to 6% [not 4%] ofthe overall course grade.1. Select a three domestic (U.S.) stocks as your portfolio components.2. Download your stocks’ monthly prices since 01/02/2000.3. Compute your stocks’ monthly HPYs, then their risk-return characteristics, and the ʌ among them.4. Download your stocks’ annual prices since 01/02/2000.5. Compute your stocks’ annual HPYs, then their risk-return characteristics, and the ʌamong them.6. Compute and plot all your pairs of 2-stock Minimum Variance Frontiers MVFs.7. Compute and plot your 3-stock portfolio Minimum Variance Frontier MVF.8. Do a scatter plot, trendline, and derive the Beta for your stocks.9. Plot SML, plot each stock’s Beta-Return point, and verify its pricing according to CAPM.10. Do a Histogram Analysis: are your stock’s periodic (monthly) returns normally distributed?Guidance on this parto The guidance document is lengthy but don’t worry. It is not difficult; it is cookbook-like …o … follow the instructions below, Step #0 to Step #10 .o If you do this part “correctly and on time”, “you should get at least 90 points”¾ Grading policy. Any further questions, check in with your facilitator.¾ Grading of this assignment is exclusively done by your facilitator with regard topromptness/timing of submitting this Part [1] of assignment.o To enforce Academic Conduct of “DO YOUR OWN WORK”, you must use your personalized providedstock prices Excel data file emailed to your Internal Messages:¾ You may create as many worksheets as needed to organize, calculate, and tabulate the Stepsin this assignment.¾ See end of this document for sample required tabulations and plots required in thisassignment as constructed from the Steps in this assignments.o This Part [1] does not involve any participation. Just post your personalized completed Excel whenyou complete it. Reminder: part of the scoring on this part is the “promptness” of submitting this partof the assignment.Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -2 of 21- Chee / BU METPart [2] Comment on your own work or your peers’ work with regard to Part [1].After you completed and posted your Excel for Part [1], later in the week, you may follow-up with subsequentposting(s) in this Part [2]. Some guidance on this part, worth another 3% of the overall course grade:a. You may comment on your own work. For example, what did you learn in the Steps? What difficulties didyou have during the process?b. With regard to the shape/position of your 2-asset MVFs [Step #6], your 3-asset Portfolio MVF and EF[Step #7], any comments/observations?c. With regard to ŽĞĨĨŝĐŝĞŶƚŽĨŽƌƌĞůĂƚŝŽŶʌĐĂůĐƵůĂƚŝŽŶƐ [Step #3], any comments/observations? Did the3 stock you picked for your portfolio benefitted from overall risk reduction?d. With regard to CAPM, SML, Beta [Step #8, #9], any comments/observations? How did your selected 3stocks perform relative to CAPM and its SML?e. With regard to using ůŝŶĞĂƌƌĞŐƌĞƐƐŝŽŶ ĂŶĚ ŝƚƐƚƌĞŶĚůŝŶĞƚŽĚĞƌŝǀĞ LJŽƵƌƐƚŽĐŬƐ͛ĞƚĂɴ [Step #8], anycomments/observations. Hints: look at the “dots” in your scatter plots and their respective R2 value.f. With regard to “Mystery Stock X”, what is so “mysterious” about this stock? Hints:i. Why is its standard deviation so small relative to your 3 selected stocks?ii. Any comments on its ŽĞĨĨŝĐŝĞŶƚŽĨŽƌƌĞůĂƚŝŽŶʌ relative to your 3 selected stocks?iii. With regard to its calculated ĞƚĂɴ, did you get a negative value? Aren’t all stock Betas at least0.00 (Risk-Free Rate) and positive (risky stocks)? What does it meant to be negative?g. With regard to “Mystery Stock Y”, what is so “mysterious” about this stock? Hints:iv. Comment on the Histogram Analysis [Step #10] of distribution of monthly returns of this stock.h. PS: the above guidance questions [b] through [e] might be a little too challenging for you right now at thislevel of the course. If you have no idea, leave it out in this part; we will talk about them in our next LiveClassroom session … but AT LEAST you need to do [a] to get the credit for this part.i. Finally, make an attempt on identifying who these “Mystery Stocks X and Y” are? If not, let’s discuss andunveil these two mystery stocks in our next Live Classroom session.j. PS: You will certainly re-visit these issues in AD717 Investment Analysis and Portfolio Management.Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -3 of 21- Chee / BU METStep #0 Assumptions used in this assignment. You may use/assume the following [based on 12/4/2020]. No need to change it if you do it a week or two afterthis date; the numbers should be about the same and won’t impact your calculations:9 Risk-free Rate, also notated as kRF, RFR = 0.88% [U.S. 10-year T-Note].9 Market Risk Premium RPM = kM – kRF = 5.50% [how did we get this “magic” number?].9 Therefore, Required Rate of Return of the Market kM = RFR + MRP = 6.38%.Step #1 Select a three domestic (U.S.) stocks as your portfolio components.PS: BEFORE you start downloading your 3 stocks’ data, see item [i]. Also, a set of pre-screened stock prices Exceldata file is provided. Then read on with the following instruction.You will be building a portfolio of 3 domestic (U.S.) stocks. In order to make this assignment work, your proposedstocks must meet the following requirements:a) It must be U.S. publicly traded only because its long-run historical stock prices are easily available anddownloadable from most (free) financial websites.b) Stock must have been listed since 01/02/2000; we need data from this date till 12/02/2020.c) Its average of the annual rate of returns during 2000~2020 must be between 6% and 17%.(Yeah, 2008~2010 were horrible years … but most of it was recovered during 2011~2017 and it got even betterduring 2018. PS: unfortunately with Covid-19, it will be a few tough years forward). Reasons for theserequirements:i) Stocks are not risk-free investments. And they certainly do not guarantee positive returns. Negativereturns make one of the learning objectives of this project less apparent.ii) Negative returns also will lead to negative stock Beta, a calculation you will be required to performand plot in this project.iii) On the other hand, having an average of the stock’s annual HPY higher than 17% causes scalingproblems when plotting various results (in particular, the Efficient Frontier) throughout this project.PS: which is why we don’t pick Apple — performing too well for the last 20 twenty years.iv) So, for practical purpose and learning objectives in this project, we have this limit. But in practice allinvestors should strive for better returns relative to their acceptable level of risk taken on them. Forexample, during this holding period, had you invested in any of these S&P500 stocks [not limited to aparticular sector], you would have retired a long time ago: Apple [9,500%!!!]; MasterCard [8,000+%and insufficient data]; Gilead Sciences [5,900+%]; UnitedHealth Group [5,000+%]; Nike [4,900+%];Cummins [4,400+%]; Amazon [3,600+%]; Cognizant Technology Solutions [3,600+%]; Union Pacific[3,500%+]; Google [2,500+% and insufficient years]; Starbucks [2,000+%]; etc.d) Each of the stocks in your portfolio must be “well diversified”, typically coming from different sectors(industry) of the economy. For example an all-energy Exxon-Mobile + Chevron + Conoco Philips would be abad idea, while Exxon-Mobile (energy) + Microsoft (technology, computer services) + Johnson&Johnson(healthcare), etc. would probably be a better set of stocks.e) Note that it is OK for more than one student to pick the same ONE or TWO stocks in their portfolio but notEXACTLY the same set of stocks. If you overlap in only 2, that is fine. And I will assume that each student isdoing his/her own (individual) work on the selected stocks.Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -4 of 21- Chee / BU METf) This Project is not graded on who will have the highest portfolio return during the observed period. It isbased on the process of collecting your data; calculating, plotting, compiling, and then reporting these resultsin the specified manner.g) PS: if you do not wish to use U.S. domestic stocks, you are allowed to propose your own stocks from anothersingle country. Then that proposed stocks are “domestic” with respect to that country. For example, allChinese stock, all-EU stocks, all-Latin American stocks, etc. BUT you must still¾ Meet the data requirements as spelled out in above steps [b] thru [e],¾ You need to find out that country’s Risk-free Rate (RFR), i.e. its 10-year T-Note yield,¾ And you need to collect its corresponding stock market index data (the market),¾ And you need to estimate that country’s Market Risk Premium. PS: it may not be 5.50%.h) PS: if you decide on U.S. stocks, you do not have to limit your choice of U.S stocks to the pre-screened listin [i] below.¾ Choose something you are interested in or you plan to follow …¾ … and might have a future interest in investing in that stock, or even employment prospect.¾ However, whichever stock you choose to use, you must still meet those requirements [b] thru [e].i) Suggestions on how to screen for your stockIf you already are an experienced investor, you could use any of the free or commercially available stockscreener apps to help you; most of them comes with your online stock trading account. However, if you donot have this resource, it is not an issue. In fact, one of the goals of this course is to teach you to build andlook for stock information manually; it is part of the learning process.To save you time, the following is a list <see next page> of some pre-screened S&P500 stocks which mightmeet the above requirements [b] through [e] at the time of assignment. They are organized by sectors [andlisted in order of sector weight] as defined by the Global Industry Classification Standards (GICS), a systemdeveloped by Morgan Stanley Capital International (MSCI) and Standard & Poor’s (S&P):Pick a total of 3 stocks to build your portfolio …9 Pick at most one stock from any 3 of the 11 sectors [vertical column]. This satisfies the well-diversifiedrequirement [d],9 PS: if you pick a stock from the Consumer Discretionary sector, you should not pick another stock fromthe Consumer Staples sector, and vice versa. This is because both sectors on a broader sense are stillConsumer-related stocks. In the U.S. economy, the consumer economy represents a significant portion ofthe nation’s economic activity and GDP. Although there are some leads/lags between them in theeconomic cycle, they could be more correlated than with the other sectors of the economy. So, meetingthe well diversified required [d] would not be as apparent in your project.Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -5 of 21- Chee / BU METSELECT FROM THIS LIST OF PRE-SCREENED STOCKS. PS: stocks marked with a * have adividend payout history since 2000 or earlier.Stock candidates listed in order by index weight within sector:Health Comm RealDiscr Staples Energy Financials Care Industrl Materials Tech Utilities Services Estate
HD*MCD*LOW*TGT*EBAYCCL*
PG*KO*PEP*WMT*COSTKMB*WBA*HSY*
XOM*CVX*COP*SLB*HES*HAL*MRO*
BRK-BJPM*WFC*AXP*USB*GS*PNC*SCHW*
JNJ*MRK*PFE*ABT*AMGNLLY*CVS*BAX
HON*MMM*UPS*WM*FDX*LUV*GD*
ECL*APD*NEM*VMC*MLM*IP*IFF*
MSFT*INTC*ORCLTXN*ADP*INTUADI
DUK*D*SO*SRE*XEL*ED*
CMCSA* PLD*
DIS*EAVZ*
EQR*ARE*IRM
<12/4/2020>Sourced from Yahoo! FinanceDO NOT select and use the following stocks in your portfolio. Because:– insufficient data over the required observed period,– negative returns or average of annual HPY > 16%.– no dividend payouts or insufficient dividend history.AMZN #1 PM #5 PSX #3 BAC #3 UNH* #3 BA* #1 LIN #1 AAPL #2 NEE* #1 FB #1 AMT #1NKE* #4 MO* #6 MPC #5 C #5 MDT* #5 UNP* #3 DD #3 V* #3 EXC* #5 GOOG #2 CCI #2SBUX #5 MDLZ* #7 EOG #7 CME #8 TMO #8 UTX* SHW* #5 MA #4 AEP* #6GOOGL #3 SPG* #4BKNG #7 COST #8 KMI #8 CB #9 ABBV #9 LMT* #5 PPG #8 CSCO #6 PEG* #9 CHTR #4 EQIX #5TJX #8 CL* #9 OXY #9 SPGI #12 DHR* #11 GE* #8 DOW #6 ADBE #7 WEC* #11 T* #6 PSA* #6GM #10 EL #11 VLO #10 ICE #13 BMY* #12 CAT* #9 BLL* #9 CRM #8 ES* #12 ATVI #7 WELL #7DG #11 SYY* #13 OKE* #11 MS* #14 GILD #14 NOC* #10 LYB #10 IBM* #9 PPL* #18 NFLX #8 AVB* #9ROST* #12 GIS* #14 WMB #12 BLK #15 CELG #15 RTN* #11 NUE* #15 ORCL #10 TWTR #11 SBAC #10YUM* #13 STZ #15 PXD #13 MMC*#16 BDX* #17 CSX* #12 CE #17 PYPL #12 TTWO #15 DLR #11F* #14 ADM* #18 FANG #17 AIG #17 ANTM #19 DE* #13 FCX #18 ACN #13 CBS #16 VTR*#12VFC* #18 MNST #19 CXO #18 AON #18 ISRG #20 NSC* #14 CF #20 NVDA #14 CTL #17 BXP* #16DLTR #19 KR #21 DVN* #22 BK* #24 CI* #21 CMI* #29 FMC #21AVGO #15 FOXA* #18 WY* #15BBY #30 CLX* #22 APA #25 MET* #26 VRTX #26 EMN* #23QCOM* #16 DISCK #19 O #13MGM #33 KHC #24 COG* #28 BIIB #28 MOS* #26 MU #21 DISH #20 ESS* #14SJM* #27 MCK* #33 airlines SEE #30 CTSH #26 VIAB #21 HCP* #17REGN #34 DAL #24 TRIP #26 CBRE #19UAL #39 HST* #20AAL #54 VNO* #26SectorsConsumerWeek 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -6 of 21- Chee / BU METStep #2 Download your stocks’ monthly prices since 01/02/2000.a) Use the date range 1/2/2000 to 12/02/2020.b) Typical place to download data from is Yahoo! Finance, www.finance.yahoo.com. If you have other moreconvenient/familiar sources to download from, that is also OK; all you really need is historical dividend datafor your chosen stock.c) To save you time, the instructor have already download monthly prices for all the pre-screened stocks in thelist above, Step #1 [i]. This provided stock prices Excel data file is emailed to you in BlackBoard; check yourInternal Messages and use this (your) personalized Excel file to do/submit this assignment.d) PS: if you want to learn how to download stock price data, see previous Weekly Discussion WD4. Similar inprocess, instead of downloading dividend date, you download stock prices data.e) … so, use that Excel data file, copy-paste/use the appropriate columns of stock price data for your 3 stocks.f) In addition, you will also need the columns of data for “mystery stock X?”, S&P500 [your portfolio benchmark]and Dow30 [another popular narrower stock index]. PS: the latter are typically notated as ^SP500 and ^Dow30with a ^ “hat sign” because they are stock indexes.g) Thus, you will have a total of SIX columns of stock prices. For example, the following is a layout of the monthlyhistorical prices (beginning of month):
Period
Date
^SP500
^Dow30
X?
A
B
C
0
01/01/2000
1,394.46
10,940.53
4.20
35.60
17.42
26.77
1
02/01/2000
1,366.42
10,128.31
4.25
36.10
15.56
23.40
2
03/01/2000
1,498.58
10,921.92
4.31
38.24
19.58
15.11
3
04/01/2000
1,452.43
10,733.91
4.29
39.33
18.03
15.91
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
252
01/01/2021
3,699.12
30,218.26
11.58
299.74
90.05
113.81
Monthly Stock Prices Adjusted Close ($)
PS: The date 01/01/2021 is not here yet, a few more weeks to go. But because the year 2020 is such atransformational year, due to COVID-19, stock prices used for this future date is based on 12/04/2020, closeenough to 01/01/2021. Having this last data point will allow us to calculate the annual return for this COVIDYear 2020.h) For convenience in grading, please strictly observe use the following color codes for your indexes and stocks[ticker symbol arranged in alphabetical order] in the plots or data tabulations (or you will lose grading points):
^SP500^Dow30X?first stocksecond stockthird stock
gray
[SP500 benchmark index ^SP500].
black dotted[Dow30 benchmark index ^Dow30].
redyellowbluebrown
[our mystery stock X?].[ticker symbol with the lowest alphabetical order, e.g. A ].[ticker symbol with the next lowest alphabetical order, e.g. B ].[ticker symbol with the third highest alphabetical order, e.g. C ].
Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -7 of 21- Chee / BU METStep #3 Compute your stocks’ monthly HPYs, then their risk-ƌĞƚƵƌŶĐŚĂƌĂĐƚĞƌŝƐƚŝĐƐ͕ĂŶĚƚŚĞʌĂŵŽŶŐƚŚĞŵ.PS: Monthly HPY (holding period yield) is the portfolio technical term for monthly rate of return of an investment,in this case, stocks and indexes.For each of the stocks as well as the benchmarks ^SP500 and ^Dow30 and mystery stock X, do the following:a) Calculate the monthly Holding Period Yields (HPY). Let all historical monthly stock prices be p0, p1, p2, p3, and so on. Generically these stock prices arenotated as pt for the tth monthly observation. Then the respective stock’s HPY during period (month) t, i.e. HPYt , can be calculate as:= ܻܲ௧ܪ௧௧ିଵെ 1where t is a specific period (month) and t-1 is its previous period. The following is an Excel-like numerical example of this process for ^SP500’s HPYs:
Column:
A B C D E
Period t
ObservedDate
Adj Close^SP500
CalculatedHPY
Comments & instructions
123Row
0
01/01/2000
1,394.46
–
Cell C1: this is the p(0) at beginning of holding period.Cell D1: start of holding period, no HPY yet.
1
02/01/2000
1,366.42
-2.01%
Cell D2: HPY1 = (C2/C1) – 1 = -2.01%.This is the HPY during period 1.
2
03/01/2000
1,498.58
9.67%
Cell D3: HPY2 = (C3/C2) – 1 = 9.67%.This is the HPY during period 2.
:
:
:
:
For each period t, we are just calculating the HPYt of thisperiod, i.e. the percentage change from pt-1 to pt.
243
12/01/2020
3,662.45
:
Once you have set up the Excel formula to calculate theperiodic HPY in cell D2, just drag this cell all the waydown to the last period to calculate the rest of theperiodic HPYs.
252
01/01/2021
3,699.12
1.00%
The final period.
b) From each stocks as well as the ^SP500’s, ^Dow30’s, Mystery Stock X monthly HPYs in (a), calculate theaverage from all its monthly HPYs. The relevant Excel formula for doing this is “AVERAGE”. You can use Excel’sHelp pull-down menu, go into the Microsoft Excel Help and then search for “AVERAGE” to learn more abouthow to use this function.c) From each stocks as well as the ^SP500’s, ^Dow30’s, Mystery Stock X monthly HPYs in (a), calculate the samplestandard deviation of its monthly HPYs. The relevant Excel for doing this is “STDEV”.d) From each stocks as well as the ^SP500’s, ^Dow30’s, Mystery Stock X monthly HPYs in (a), calculate thecoefficient of correlation of each pair of monthly HPYs. The relevant Excel for doing this is “CORREL”.e) From each stock as well as the ^SP500’s, ^Dow30’s, Mystery Stock X monthly HPYs in (a), calculate thecovariance of each pair of monthly HPYs. The relevant Excel for doing this is “COVAR”.Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -8 of 21- Chee / BU METf) Then summarize all of the monthly statistics in a 2-by-2 table, such as the following:
Period
Date
^SP500
^Dow30 X?
A
B
C
0
01/01/2000
–
–
–
–
–
–
1
02/01/2000
-2.01%
-7.42%
1.20%
1.40%
-10.69%
-12.81%
2
03/01/2000
9.67%
7.84%
1.39%
5.92%
25.88%
-35.42%
3
04/01/2000
-3.08%
-1.72%
-0.48%
2.85%
-7.91%
5.29%
:
:
:
:
:
:
:
:
:
:
:
:
:
:
252
01/01/2021
1.00%
1.12%
-0.26%
-0.08%
3.79%
-0.64%
Monthly HPYs (%)
Avg Mthly HPYsStdev Mthly HPYsCovar^SP500^Dow30X?ABC
0.48%
0.50%
0.41%
1.17%
0.90%
0.76%
4.36%
4.26%
1.01%
6.41%
6.61%
5.08%
^SP500
^Dow30
X?
A
B
C
Correl
0.96
-0.07
0.49
0.59
0.24
^SP500
0.00177
-0.11
0.47
0.62
0.30
^Dow30
-0.00003
-0.00005
0.03
-0.06
0.06
X?
0.00137
0.00127
0.00002
0.19
0.11
A
0.00170
0.00173
-0.00004
0.00081
0.11
B
0.00053
0.00065
0.00003
0.00037
0.00038
C
Cells highlighted in turquoise are the ŽĞĨĨŝĐŝĞŶƚŽĨŽƌƌĞůĂƚŝŽŶ;ʌͿ statistics between each pair ofstocks/indexes. Cells highlighted in green are the Covariance statistics between each pair of stocks/indexes. PS: the relationship between Coefficient of Correlation and Covariance of two stocks A and B is:ŽǀĂƌ;͕ͿсʍA džʍA džʌAB … … the use of Covariance values will come in handy for calculating portfolio standard deviation in thenext steps.Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -9 of 21- Chee / BU METStep #4 Download your stocks’ annual prices since 01/02/2000.<this sounds like Step #2; now you are doing it annually>a) From Step #2, you have already downloaded the monthly stock prices. So, extract the beginning of the yearstock prices from it, e.g. 1/1/2000, 1/2/2001, 1/2/2002, etc. thru January of the ending date. 1/1/2021 shouldbe your last data.b) Also extract the corresponding annual index data for the ^SP500, ^Dow30, and Mystery Stock X?. These willbe your portfolio benchmarks.c) For example, the following is a layout of the annual historical prices:
Period
Date
^SP500
^Dow30
X?
A
B
C
0
01/01/2000
1,394.46
10,940.53
4.20
34.55
16.68
28.88
1
01/01/2001
1,366.01
10,887.36
4.77
32.66
17.68
20.90
2
01/01/2002
1,130.20
9,920.00
5.12
32.48
18.86
24.29
3
01/01/2003
855.70
8,053.81
5.52
20.38
15.58
26.13
4
01/01/2004
1,131.13
10,488.07
5.78
26.08
22.68
31.46
5
01/01/2005
1,181.27
10,489.94
6.02
33.62
30.26
33.76
6
01/01/2006
1,280.08
10,864.86
6.12
35.83
34.08
38.32
7
01/01/2007
1,438.24
12,621.69
6.38
40.72
43.17
42.85
8
01/01/2008
1,378.55
12,650.36
6.95
49.74
50.72
44.13
9
01/01/2009
825.88
8,000.86
7.13
33.30
44.28
37.63
10
01/01/2010
1,073.87
10,067.33
7.73
43.08
47.01
43.84
11
01/01/2011
1,286.12
11,891.93
8.10
54.61
64.19
46.35
12
01/01/2012
1,312.41
12,632.91
8.79
63.27
71.93
47.79
13
01/01/2013
1,498.11
13,860.58
9.01
85.28
83.03
58.90
14
01/01/2014
1,782.59
15,698.85
9.00
94.63
83.14
61.91
15
01/01/2015
1,994.99
17,164.95
9.60
121.80
79.08
70.26
16
01/01/2016
1,940.24
16,466.30
9.54
134.39
69.72
70.32
17
01/01/2017
2,278.87
19,864.09
9.67
147.48
93.78
77.88
18
01/01/2018
2,823.81
26,149.39
9.87
184.53
109.76
79.16
19
01/01/2019
2,704.10
24,999.67
10.06
205.37
104.27
91.57
20
01/01/2020
3,225.52
28,256.03
11.05
295.21
101.39
121.62
21
01/01/2021
3,699.12
30,218.26
11.58
373.43
93.28
137.47
Annual Stock Prices Adjusted Close ($)
Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -10 of 21- Chee / BU METStep #5 Compute your stocks’ annual HPYs, then their risk-ƌĞƚƵƌŶĐŚĂƌĂĐƚĞƌŝƐƚŝĐƐ͕ĂŶĚƚŚĞʌĂŵŽŶŐƚŚĞŵ.As in Step #3, for each of the stocks as well as the benchmarks ^SP500, ^Dow30 and Mystery Stock X?, calculatethe following:a) Calculate the annual Holding Period Yields (HPY).b) Calculate the average of its annual HPYs.c) Calculate the standard deviation of its annual HPYs.d) Calculate the coefficient of correlation of each pair of stocks and benchmarks.e) Calculate the covariance of each pair of stocks.f) Then summarize all of the annual statistics in a 2-by-2 table, such as the following:
Period
Date
^SP500
^Dow30
X?
A
B
C
0
01/01/2000
–
–
–
–
–
–
1
01/01/2001
-2.04%
-0.49%
13.62%
-5.49%
5.98%
-27.61%
2
01/01/2002
-17.26%
-8.89%
7.41%
-0.54%
6.72%
16.20%
3
01/01/2003
-24.29%
-18.81%
7.70%
-37.24%
-17.38%
7.55%
4
01/01/2004
32.19%
30.22%
4.81%
27.95%
45.56%
20.42%
5
01/01/2005
4.43%
0.02%
4.07%
28.89%
33.39%
7.31%
6
01/01/2006
8.36%
3.57%
1.62%
6.58%
12.62%
13.50%
7
01/01/2007
12.36%
16.17%
4.30%
13.66%
26.70%
11.83%
8
01/01/2008
-4.15%
0.23%
8.98%
22.14%
17.49%
2.97%
9
01/01/2009
-40.09%
-36.75%
2.54%
-33.06%
-12.71%
-14.72%
10
01/01/2010
30.03%
25.83%
8.45%
29.36%
6.18%
16.51%
11
01/01/2011
19.76%
18.12%
4.80%
26.77%
36.54%
5.72%
12
01/01/2012
2.04%
6.23%
8.48%
15.87%
12.05%
3.10%
13
01/01/2013
14.15%
9.72%
2.48%
34.79%
15.43%
23.25%
14
01/01/2014
18.99%
13.26%
-0.05%
10.96%
0.13%
5.11%
15
01/01/2015
11.92%
9.34%
6.59%
28.72%
-4.88%
13.48%
16
01/01/2016
-2.74%
-4.07%
-0.58%
10.33%
-11.84%
0.09%
17
01/01/2017
17.45%
20.63%
1.36%
9.74%
34.51%
10.76%
18
01/01/2018
23.91%
31.64%
2.03%
25.12%
17.05%
1.64%
19
01/01/2019
-4.24%
-4.40%
1.97%
11.30%
-5.01%
15.67%
20
01/01/2020
19.28%
13.03%
9.85%
43.75%
-2.75%
32.81%
21
01/01/2021
14.68%
6.94%
4.76%
26.49%
-8.00%
13.04%
Annual HPYs (%)
Avg Annual HPYsStdev Annual HPYsCovar^SP500^Dow30X?ABC
6.42%
6.26%
5.01%
14.10%
9.89%
8.51%
17.91%
16.16%
3.65%
20.29%
17.83%
12.76%
^SP500
^Dow30
X?
A
B
C
Correl
0.97
-0.09
0.81
0.54
0.47
^SP500
0.02662
-0.05
0.75
0.61
0.41
^Dow30
-0.00054
-0.00027
0.01
-0.06
-0.14
X?A
0.02820
0.02340
0.00005
0.44
0.57
0.01636
0.01677
-0.00037
0.01513
0.16
B
0.01029
0.00809
-0.00061
0.01415
0.00339
C
g) PS: In Steps #3 and #5, why do stocks and indexes risk-return statistics in monthly AND then in annual HPYs?i. Since one year is an extremely long period, there may be a LOT of price and index movement during ayear (remember the COVID-19 year?). With monthly data, we get more data points and tighter inbetween periodic data ƚŽŐĞƚďĞƚƚĞƌĐŽĞĨĨŝĐŝĞŶƚŽĨĐŽƌƌĞůĂƚŝŽŶʌĂƐǁĞůůĂƐĞƚĂɴŵĞĂƐƵƌĞŵĞŶƚƐ͘ii. With annual data, a stock’s or an index’s risk-return statistics are typically reported on an annual basis.Also, we will need its annual (not monthly) statistics to calculate the portfolio’s risk-return values andbuild the Minimum Variance Frontiers MVFs and Efficient Frontier EF in the following steps.Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -11 of 21- Chee / BU METStep #6 Compute and plot all your pairs of 2-stock Minimum Variance Frontiers MVF.For each of your stock pairings (e.g. stock A-B), in 5% portfolio weight intervals:a) Calculate the weighted return of this 2-stock portfolio …E(R)2-asset = wARA + wBRB where RA and RB is the average return of the annual HPYs of the stocks as calculated in Step #5, and wA and wB are the portfolio weights of the two stocks which add up to 100%.b) … calculate its corresponding portfolio standard deviationV 2asset (wAV A ) 2 (wBV B ) 2 2wAwBCov(A, B) ǁŚĞƌĞʍA ĂŶĚʍB is the standard deviation of the annual HPYs of the stocks as calculated in Step #5, and Cov(A,B) is the covariance between these two stocks as calculated in Step #5.Thus, each pair of calculation from above Step (a) and (b) represents a “single dot” of Risk-Return data for a 2-asset portfolio with a specific Asset Allocation weight (what % of investment in A, what % in B) combination.c) From Steps (a) and (b), you thus calculate the Risk-Return of the 2-stock pair (e.g. A-B) in 5% Asset Allocationweight intervals 0%-100%, 5%-95%, 10%-90%, 15%-85%, …, 100%-0%. This collection of “dots” will allow youto derive graphically the Minimum Variance Frontier MVF of this 2-stock pair.d) Then repeat Steps (a), (b), and (c) for the other 2-stock pairs A-C, B-C.e) Finally. Plot all these 2-asset Minimum Various Frontiers MVFs on a single graph. And make sure you labelthe stocks at the end-points of these MVFs.f) For example, the following is a sample of the Risk-Return calculations of the 2-asset portfolios and theirrespective plots of the Minimum Variance Frontiers:
2-asset wghts
A-B Risk-Return
A-C Risk-Return
B-C Risk-Return
w1
w2
;ʍͿ
E(R)
;ʍͿ
E(R)
;ʍͿ
E(R)
0%
100%
17.83%
9.89%
12.76%
8.51%
12.76%
8.51%
5%
95%
17.38%
10.10%
12.70%
8.79%
12.28%
8.58%
10%
90%
16.99%
10.31%
12.70%
9.07%
11.88%
8.65%
15%
85%
16.66%
10.52%
12.76%
9.35%
11.55%
8.72%
20%
80%
16.38%
10.73%
12.88%
9.63%
11.30%
8.78%
25%
75%
16.16%
10.95%
13.05%
9.91%
11.14%
8.85%
30%
70%
16.01%
11.16%
13.27%
10.18%
11.07%
8.92%
35%
65%
15.92%
11.37%
13.55%
10.46%
11.10%
8.99%
40%
60%
15.90%
11.58%
13.87%
10.74%
11.21%
9.06%
45%
55%
15.95%
11.79%
14.23%
11.02%
11.42%
9.13%
50%
50%
16.06%
12.00%
14.64%
11.30%
11.71%
9.20%
55%
45%
16.24%
12.21%
15.08%
11.58%
12.08%
9.27%
60%
40%
16.48%
12.42%
15.56%
11.86%
12.52%
9.34%
65%
35%
16.78%
12.63%
16.07%
12.14%
13.02%
9.41%
70%
30%
17.14%
12.84%
16.61%
12.42%
13.59%
9.48%
75%
25%
17.55%
13.05%
17.17%
12.70%
14.20%
9.55%
80%
20%
18.02%
13.26%
17.76%
12.98%
14.86%
9.62%
85%
15%
18.52%
13.47%
18.36%
13.26%
15.55%
9.69%
90%
10%
19.07%
13.68%
18.99%
13.54%
16.28%
9.75%
95%
5%
19.66%
13.89%
19.63%
13.82%
17.04%
9.82%
100%
0%
20.29%
14.10%
20.29%
14.10%
17.83%
9.89%
Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -12 of 21- Chee / BU METComments on relative location of the stocks and shapes of Minimum Variance Frontiers: The shapes of your 2-asset Minimum Variance Frontiers will vary depending on the relative location ofyour chosen stocks … … in general, stocks are “typically” clustered in the “northeastern corridor” … … this “natural” location is based on the adage, “taking on higher risks requires a higher (expected)return”, i.e. stocks with lower standard deviation typically have lower expected returns while stocks withhigher standard deviation typically have higher expected returns. BUT this adage is not always true — in the real world, i.e. the financial markets:o There are exceptional companies that out-perform the overall market …o … there are also “weaker” companies that under-perform below the market’s expectations.o And then there are good companies with higher returns but lower standard deviation, i.e. lowervariability in HPYs, than not-so-good companies. As seen in the above graph, it seems that all the chosen stocks are “normally priced” in the “northeasterncorridor”o Among the chosen stocks, stock C has the least amount of returns and least amount of risk(standard deviation) …o … while stock A has the most amount of returns and most amount of risk.o Which of the chosen stocks is best? Î how does the Coefficient of Variation of each of the stockcompares?8%9%10%11%12%13%14%15%10% 12% 14% 16% 18% 20%Portfolio (returns)
Portfolio (stdev)C
B
A
2-asset Minimum Variance Frontiers MVFs
Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -13 of 21- Chee / BU METStep #7 Compute and plot your 3-stock portfolio Minimum Variance Frontier MVF.For your chosen stocks (e.g. stock A-B-C), in 5% portfolio weight intervals:a) Calculate the weighted return of your portfolio …E(R)portfolio = wARA + wBRB + wCRC where RA, RB, RC are the average returns of the annual HPYs of the stocks as calculated in Step #5, and wA, wB, wC are the portfolio weights of your stocks that add up to 100%.b) … calculate its corresponding portfolio standard deviationڮ + )ଶߪݓ( + )ଶߪݓ( + )ଶߪݓ(ଷି௦௦௧ = ඥߪ)ܥ ,ܤ(ݒܥݓݓ) + 2ܥ ,ܣ(ݒܥݓݓ) + 2ܤ ,ܣ(ݒܥݓݓඥ… + 2 ǁŚĞƌĞʍA͕ʍB͕ʍC are the standard deviations of the annual HPYs of the respective stocks as calculatedin Step #5, and Cov(x,y) is the covariance between each pair of stocks as calculated in Step #6.Thus, each pair of calculation from above Step (a) and (b) represents a “single dot” of Risk-Return data foryour portfolio with a specific Asset Allocation weight combination.c) From Steps (a) and (b), you thus calculate the Risk-Return of your portfolio (e.g. A-B-C) in 5% Asset Allocationweight intervals 0%-0%-100%, 0%-5%-95%, 0%-10%-90%, 0%-15%-85%, …, 100%-0%-0%. This collection of“dots” will allow you to derive graphically the Minimum Variance Frontier MVF of your portfolio. For example:
A B C
E(R) E(stdev)
0% 0% 100%0% 5% 95%0% 10% 90%0% 15% 85%0% 20% 80%0% 25% 75%0% 30% 70%0% 35% 65%0% 40% 60%: : :: : :85% 15% 0%90% 0% 10%90% 5% 5%90% 10% 0%95% 0% 5%95% 5% 0%100% 0% 0%
12.76% 8.51%12.28% 8.58%11.88% 8.65%11.55% 8.72%11.30% 8.78%11.14% 8.85%11.07% 8.92%11.10% 8.99%11.21% 9.06%: :: :18.52% 13.47%18.99% 13.54%19.00% 13.61%19.07% 13.68%19.63% 13.82%19.66% 13.89%20.29% 14.10%
3-asset Risk-ReturnA-B-C
Asset Allocation weights
PS: the above AA weights are given as a worksheet in your stock prices data Excel file. To save time, you mayuse those rows of AA weights to calculate the corresponding Risk-Return of each of these “portfolio dots”.d) Finally, scatter-plot [not line-plot] your portfolio’s Minimum Variance Frontier. For example:Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -14 of 21- Chee / BU MET8%9%10%11%12%13%14%15%10% 12% 14% 16% 18% 20% 22%Minimum Variance Frontier MVF of 3-stock Portfolio
A
B
C
Portfolio (returns)Portfolio (stdev)The sequence of dots at the entire outer edge [“the envelope”] is the Minimum Variance Frontier MVF ofyour multi-stock portfolio, and those outer-edge portfolio dots on the “top half” of the MVF which are more efficient than theothers represent the Efficient Frontier EF of your portfolio.Step #8 Do a scatter plot, trendline, and derive the Beta for your stocks.For each of your portfolio’s stock, do the following:a) Do a scatter plot of each stock’s monthly HPYs (on the y-axis) against the S&P500’s monthly HPYs (on the xaxis). You should already have the monthly HPY data from previous Step #3.b) Also introduce a trendline* on this same plot. You do this from the pull-down menu Chart, and then “Add Trendline”. Under the tab for Options:9 You may also check on “Display equation on chart”9 You may also check on “Display R-squared value on chart”*PS: depending on the version of your Excel, the above trendline feature might be located in a different menu;so you might need to look around for this feature.c) Derive the following linear regression statistics from the above scatter plot and trendline: The stock’s Beta ɴ, which is the slope of the trendline. You have this data in (b) if you display theequation on the graph. You can also use the Excel formula “SLOPE”. The intercept of the trendline. You have this data in (b) if you display the equation on the graph. Youcan also use the Excel formula “INTERCEPT” to calculate it. The R-square of the trendline. You have this data in (b) if you display the equation on the graph. Youcan also use the Excel formula “RSQ” to calculate it.Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -15 of 21- Chee / BU METFor example, the following is a scatter plot and its companion linear regression trendline:
0.8986
Beta =
0.0047
Intercept =
0.3512
R2 =
Stock B
Date02/01/200003/01/200004/01/200005/01/200006/01/200007/01/200008/01/200009/01/200010/01/200011/01/200012/01/200001/01/200102/01/200103/01/200104/01/200105/01/200106/01/200107/01/2001::12/01/202001/01/2021
^SP500
B
-2.01% -10.69%
9.67%-3.08%-2.19%2.39%-1.63%6.07%-5.35%-0.49%-8.01%0.41%3.46%-9.23%-6.42%7.68%0.51%-2.50%-1.07%::1.13%1.00%
25.88%-7.91%8.59%-6.95%-6.87%7.00%2.46%-3.67%-0.30%4.75%-1.37%2.86%4.07%9.98%-0.53%-4.48%0.98%::4.60%3.79%
y = 0.8986x + 0.0047R2 = 0.3512Stock A (monthly HPYs)
S&P500 (monthly HPYs)
-15%-10%-5%0%5%10%15%-15% -10% -5% 0% 5% 10%d) Finally, prepare a summary table for the stocks as well as the ^SP500, ^Dow30 benchmarks, and Mystery StockX?. Note that the average of the annual HPYs, its standard deviation, and the monthly Coefficient ofCorrelation data were already calculated from some of the previous steps; we are just consolidating ourresults and reporting them in this table:
Step #5
< annual risk-returns>
Avg
6.42%
6.26%
5.01%
14.10%
9.89%
8.51%
Stdev
17.91%
16.16%
3.65%
20.29%
17.83%
12.76%
Step #3
< monthly Betas & Correls>
Beta
1.00
0.93
-0.03
0.18
0.90
0.28
Intercept
–
0.00
0.00
0.01
0.00
0.01
R2
–
0.92
0.01
0.00
0.35
0.06
Correl
^SP500
^Dow30
X?
A
B
C
^SP500
0.96
-0.07
0.49
0.59
0.24
^Dow30
-0.11
0.47
0.62
0.30
X?
0.03
-0.06
0.06
A
0.19
0.11
B
0.11
Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -16 of 21- Chee / BU METStep #9 Plot SML, plot each stock’s Beta-Return point, and verify its pricing according to CAPM.a) With the derived stocks’ Beta and their corresponding average of their annual HPYs, do a scatter plot thestocks’ Beta-Return point.b) Also draw the Security Market Line SML: The SML starts from the RFR at Beta = 0.00 and must pass through the Market “M”. By definition, the Market “M” has a Beta of 1.00 and its kM = RFR + Market Risk Premium. See page 2 of this assignment document for assumed values for RFR and kM.The sequence of steps to “set-up”, compute, and prepare to plot the SML and indicated the returns of yourselected stocks relative to the SML is a little tricky. You need to set up two set “data series” in your plot:(I) Plotting the SMLSML is a plot of Capital Asset Pricing Model CAPM’s required rate of returns (y-axis) against the Beta values (xaxis). We will need to compute the “dots”; if they are close enough, it will depict the line of the SML.a) Determine your maximum Beta for your plot: This value is gotten from among your stocks, the ^SP500, the ^Dow30, and the Market return (e.g. 1.10). Add another 10% to this maximum Beta to make sure the SML will plot up to this value, e.g. 1.20.b) Determine the number of plot points for the SML: Most Beta are no more than 2.0. So, 200 points to plot should suffice. If after you plot the SML and itlooks a little grainy, you can increase the plot points to more. We will number these plots points starting at 1 thru 200. PS: x-axis starts at 0. This point will be plotted by the other plot which includes the RFR with a Beta of0.00.c) Compute the slope of the SML: This is simply: slope = (Market Return kM – RFR) ÷ 1.00 = Market Risk Premium ÷ 1.00 PS: by definition of the SML, the Market Return kM has a Beta of 1.00.d) Compute the SML “dots”: SML is typically an upward-sloping straight line. Thus, its graphical formula is straight-forward: “y = mx + c”o where m is the slope of the SML line as calculated in (c),o and c is the y-axis intercept, which has a value equal to the RFR (i.e. Beta = 0.00). From step (b), going through all the plot points from 0 thru 200:9 We will calculate the x-values of the each Beta value in equal increments from 0.00 thru themaximum Beta value as determined in step (a) using the following:x = i × max Beta in plot ÷ # of points to plot9 We will calculate the corresponding y-values expected return E(R) using the straight-line formulaas follows:y = slope × x + RFR The following is an example of the computed SML “dots”:Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -17 of 21- Chee / BU METinputs# of plot points 200your max Beta 1.10additional Beta 10%RFR 0.96%max Beta in plot 1.21 10% above max Betacomputed slope 5.50% = (kM – RFR) / 1.00
i123
x: Beta
y: E(R)
0.0061
0.99%
0.0121
1.03%
0.0182
1.06%
The SMLBetas & Required Return
: : :: : :195
1.1798
7.45%
1.1858
7.48%
1.1919
7.52%
1.1979
7.55%
1.2040
7.58%
1.2100
7.62%
196 197 198 199 200 (II) Plotting the Return-Beta dots of your selected stocksa) On the same graph, plot the Return-Beta dots of your selected stocks.b) Also plot Return-Beta dots of ^SP500 [proxy of the Market], ^Dow30, and the RFR. For example,
x: Beta
y: E(R)
R(A)
0.18
14.10%
R(B)
0.90
9.89%
R(C)
0.28
8.51%
^SP500
1.00
6.42%
^Dow30
0.93
6.26%
kM
1.00
6.46%
RFR
0.00
0.96%
Stocks and Betas
Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -18 of 21- Chee / BU METFor example, the following is a sample of the Return-Beta dots of the selected stocks, ^SP500 [proxy of theMarket], ^Dow30, RFR, and kM in relation to the SML: This is a tricky plot; make sure you go into the plot’s “Source Data” and define the two “Series” X- and Yvalues correctly. Where necessary, reduce the size of the “dots” for the SML line to make it thinner. Where necessary, increase the size of the Return-Beta “dots” for the selected stocks. Finally, use the draw tool to label the selected stocks, the ^SP500, the Market, and the RFR.c) Note problem with ^SP500 as a proxy for “the market”: We have used the “standard textbook Market Risk Premium RPM of 5.50%”. With the current (12/04/2020) assumed RFR of 0.96%, then according to CAPM, the implied RequiredRate of Return of the Market kM [by definition with a Beta of 1.00] should be 6.46%. BUT the S&P500’s actual annual average rate of return is 6.42% during this observed period (2000 ~2020) … … the question becomes how do we reconcile any differences between these two rates, the actualversus the theoretical CAPM-SML?(i) Is the ^SP500 (only 500 of the biggest stocks; small companies not represented) a valid proxy(approximate representation) for “the entire market”? This is a common practice used andassumed by many practitioners.(ii) Or, is the academically “assumed Market Risk Premium RPM of 5.50%”, a long-run historicalaverage, no longer valid?0%2%4%6%8%10%12%14%16%0.0 0.2 0.4 0.6 0.8 1.0 1.2ReturnsBeta3-Stocks Relative to the SMLA
SML StocksSML Stock
s
6.42%= ^SP500kM =6.46%CBRFR = 0.96 %^Dow30= 6.26%Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -19 of 21- Chee / BU MET(iii) Comments: in the “easy years of the dot-com era” (1990~2005), the RPM is closer to 4.00%. Butduring the worst of the Subprime Crisis from 2008-2012, the market suggests that this eventhad “pushed up” the Market Risk Premium [thereby increasing the required rate of return ofthe “market”] …(iv) … on the other hand, with the more recent “ultra-low level of interest rates during the2013~2017 global slowdown and its associated “Quantitative Easing due to Financial Crisis”from a number of Central Banks, the market pendulum had swung the other way: “pushingdown” the Market Risk Premium.(v) Now, at the beginning of 2020, with the emergence of Covid-19, a health crisis which quicklyimpacted the global economy and the financial markets, it looks like the Market Risk Premiumhas “immediately” been “re-priced” to a lot more than the “old” long-run average. Fromabove results, we do have confirmation that the “appropriate” RPM is closer to the “new” RPMof 5.50% rather than the older 4.00%.d) According to Capital Asset Pricing Model CAPM, identify which points are above (below) the SML. Then thesestocks would be considered undervalued (overvalued). In the above graph:o Stocks A, B, C, and D are “far” above the SML and hence significantly undervalued relative to the SML.o The ^SP500 is minutely overvalued relative to the SML. Because the difference is a miniscule 0.04%),the ^SP500 IS a close proxy for the entire market at the moment.o The ^Dow30 is also slightly undervalued relative to the SML [0.17% difference]. For sure, the ^Dow30which is made up of only 30 stock components, is not the market proxy.Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -20 of 21- Chee / BU METStep #10 Do a Histogram Analysis: are your stock’s periodic (nonthly) returns normally distributed?a) In Week3 Module3 Lecture5, you were introduced to Portfolio Theory and Efficient Portfolios in textbookreading in Chapter 25. You will certainly cover this in depth in your future course AD717 Investment Analysisad Portfolio Management …b) … one of the key assumptions in Markowitz Portfolio Theory is the assumption that the periodic HPYs arenormally distributed:¾ It uses the statistical measure of standard deviations and averages of periodic HPYs as a measure ofrisk-return for each of the assets in a portfolio.¾ Coefficient of Correlations are used to capture the co-movement of each pair of assets in the portfolio.¾ Finally, the overall risk in a portfolio context, the portfolio standard deviation, is that formula we arenow too familiar with.c) But can we or should we make such a (theoretical) assumption (of normal distribution of periodic HPYs) inpractice? To answer this critical question, we will do an empirical analysis of the monthly HPYs of “anotherMystery Stock Y”(i) We can do so by constructing a Histogram of this Mystery Stock Y’s month HPYs.(ii) Find out the minimum and maximum monthly HPY of this “Mystery Stock Y”.(iii) Use a “bin width” of 1.00% to classify (i.e. frequency count) each of the HPYs.(iv) Then create “bins” from this minimum value to the maximum value in increments of 1.00%.(v) PS: to save you time, this Mystery Stock Y’s data and the above Steps (i) through (iv) is already in yourstock prices Excel data file. Use it …(vi) … then in your Excel, select this Mystery Stock Y’s monthly HPYs, then go the Data tab to do a DataAnalysis using Excel’s Histogram feature.(vii) In Excel’s Histogram dialogue box, select “New Worksheet Ply” and select “Chart Output” to generatethe histogram data and its corresponding plot.d) Do a visual inspection of your plotted Histogram …e) … Question: does Mystery Stock Y’s monthly HPYs of this stock look normally distributed to you? Indicate onthe Histogram whether its monthly HPYs is normally or not normally distributed.If the periodic HPYs are not normally distributed, then we should we be using Average and StandardDeviation statistics as Risk-Return characteristics in Portfolio Theory?f) The following is a sample Histogram. PS, it will look different from your Mystery Stock Y:Week 5 Weekly Discussion WD5Equity Concepts: Risk and ReturnAD731: Corporate Finance -21 of 21- Chee / BU MET
MinHPY
MaxHPY
StockY?
-16.94%
12.68%
Instructions#1 Set up bins of returns in 1% increments from MinHPY (rounded dow n tonext full %) to MaxHPY (rounded up to next full %).
BinStart
BinEnd
BinWidth
-17%
13%
1%
#2 Highlight monthly HPYs of the stock.#3 Then go to Excel’s Data tab and do > Data Analysis > Histogram,For Input Range, select your range of HPY cells,For Bin Range, select your range of Bin cells,Bin Frequency Select “New Worksheet Ply”-17% 0 Select “Chart Output”-16% 1-15% 0-14% 0-13% 0-12% 1-11% 1-10% 1-9% 3-8% 6-7% 4-6% 9-5% 4-4% 2-3% 11-2% 13-1% 220% 171% 322% 403% 214% 255% 86% 117% 58% 59% 510% 211% 212% 0More 1TOTAL: 252
5 010152025303540-17%-16%-15%-14%-13%-12%-11%-10%-9%-8%-7%-6%-5%-4%-3%-2%-1%0%1%2%3%4%5%6%7%8%9%10%11%12%MoreDistribution of monthly HPYs (“Mystery Stock Y”)Theory!”It doesn’t looknormally distributed,“cannot useMarkowitz Portfolio[WD4]: P0 тs0[WD5]: Furthermore, distribution of periodic returns are NOT necessarily normally distributed.Once again, the classroom (“rubber”) DOEST NOT meet the market (“road”).Welcome to our Finance Program.Sorry to drag everyone through this long assignment and invalidating our classroom understanding ofPortfolio Theory.In any case, buy low sell high, save early and save often for your retirement, live long and prosper.~ Chee, 12/06/2020Deliverables for WD5 Part [1] of assignment. In your Excel document:x You are free to create as many worksheets as you need to implement the Steps in this assignment.x The following are sample required tabulations and plots required in this assignment as constructed from the Steps.Week5 Weekly Discussion WD5<Sample Deliverables>BU MET AD-731 =2= Chee / 2020 Fall8%9%10%11%12%13%14%15%10% 12% 14% 16% 18% 20%Portfolio (returns)
Portfolio (stdev)C
B
A
2-asset Minimum Variance Frontiers MVFsStep #68%9%10%11%12%13%14%15%10% 12% 14% 16% 18% 20%3-asset Portfolio Minimum Variance Frontier MVF and Efficident Frontier EF
A
B
C
Step #7Week5 Weekly Discussion WD5<Sample Deliverables>BU MET AD-731 =3= Chee / 2020 Fall
Date
Year
^SP500
^Dow30
Mystery X?
A
B
C
01/01/2000
–
–
–
–
–
–
–
01/01/2001
2000
-2.04%
-0.49%
13.62%
-5.49%
5.98%
-27.61%
01/01/2002
2001
-17.26%
-8.89%
7.41%
-0.54%
6.72%
16.20%
01/01/2003
2002
-24.29%
-18.81%
7.70%
-37.24%
-17.38%
7.55%
01/01/2004
2003
32.19%
30.22%
4.81%
27.95%
45.56%
20.42%
01/01/2005
2004
4.43%
0.02%
4.07%
28.89%
33.39%
7.31%
01/01/2006
2005
8.36%
3.57%
1.62%
6.58%
12.62%
13.50%
01/01/2007
2006
12.36%
16.17%
4.30%
13.66%
26.70%
11.83%
01/01/2008
2007
-4.15%
0.23%
8.98%
22.14%
17.49%
2.97%
01/01/2009
2008
-40.09%
-36.75%
2.54%
-33.06%
-12.71%
-14.72%
01/01/2010
2009
30.03%
25.83%
8.45%
29.36%
6.18%
16.51%
01/01/2011
2010
19.76%
18.12%
4.80%
26.77%
36.54%
5.72%
01/01/2012
2011
2.04%
6.23%
8.48%
15.87%
12.05%
3.10%
01/01/2013
2012
14.15%
9.72%
2.48%
34.79%
15.43%
23.25%
01/01/2014
2013
18.99%
13.26%
-0.05%
10.96%
0.13%
5.11%
01/01/2015
2014
11.92%
9.34%
6.59%
28.72%
-4.88%
13.48%
01/01/2016
2015
-2.74%
-4.07%
-0.58%
10.33%
-11.84%
0.09%
01/01/2017
2016
17.45%
20.63%
1.36%
9.74%
34.51%
10.76%
01/01/2018
2017
23.91%
31.64%
2.03%
25.12%
17.05%
1.64%
01/01/2019
2018
-4.24%
-4.40%
1.97%
11.30%
-5.01%
15.67%
01/01/2020
2019
19.28%
13.03%
9.85%
43.75%
-2.75%
32.81%
01/01/2021
2020
14.68%
6.94%
4.76%
26.49%
-8.00%
13.04%
Annual HPYs (%) [Step #5]
Step #5
< annual Risk-Returns>
Avg
6.42%
6.26%
5.01%
14.10%
9.89%
8.51%
Stdev
17.91%
16.16%
3.65%
20.29%
17.83%
12.76%
Step #3
< monthly Betas & Correls>
Beta
1.00
0.93
-0.03
0.18
0.90
0.28
Intercept
–
0.00
-0.09
0.01
0.00
0.01
R2
–
0.92
-0.05
0.00
0.35
0.06
Correl
^SP500
^Dow30
Mystery X?
A
B
C
^SP500
0.96
-0.07
0.49
0.59
0.24
^Dow30
-0.11
0.47
0.62
0.30
Mystery X?
0.03
-0.06
0.06
A
0.19
0.11
B
0.11
C
Week5 Weekly Discussion WD5<Sample Deliverables>BU MET AD-731 =4= Chee / 2020 FallA, B, and C aresignificantly under-valuedrelative to the SML.^SP500 isminutely over-valuedrelative to the SML.(0.04% difference)^Dow30 isslightly under-valuedrelative to the SML.(0.17% difference)PS: See correspondingJensen’s Alpha for theseIndexes and stocks,which can used to measureover-, under-valutation ofstock’s actual returnsversus its CAPM returns.0%2%4%6%8%10%12%14%0.0 0.2 0.4 0.6 0.8 1.0ReturnsBetaStocks Relative to the SML
SML StocksSML Ɣ Sto
cks
BCA^SP500= 6.42%kM= 6.46%RFR = 0.96%^Dow30= 6.26%Step #9Week5 Weekly Discussion WD5<Sample Deliverables>BU MET AD-731 =6= Chee / 2020 FallA B C
y = 0.7231x + 0.0082R² = 0.2419ock A (monthly returns)
St
S&P500 (monthly returns)
-15%-5%5%15%-10% -8% -6% -4% -2% 0% 2% 4% 6% 8% 10%
y = 0.8986x + 0.0046R² = 0.3512Stock B (monthly returns)
S&P500 (monthly returns)
-10%0%10%-10% -8% -6% -4% -2% 0% 2% 4% 6% 8% 10%
y = 02804x + 0.0062R² = 0.0580tock C (monthly returns)
S
S&P500 (monthly returns)
-15%-5%5%15%-10% -8% -6% -4% -2% 0% 2% 4% 6% 8% 10%Step #8Week5 Weekly Discussion WD5<Sample Deliverables>BU MET AD-731 =7= Chee / 2020 FallHistogram Analysis of Monthly HPYs: is a stock’s returns normally distributed?Instructions
MinHPY
MaxHPY
“Mystery
-16.94%
12.68%
Stock Y”
#1 Set up bins of returns in 1% increments from MinHPY (rounded down tonext full %) to MaxHPY (rounded up to next full %).#2 Highlight monthly HPYs of the stock.
BinStart
BinEnd
BinWidth
-17.00%
13.00%
1.00%
#3 Then go to Excel’s Data tab and do > Data Analysis > Histogram,For Input Range, select your range of HPY cells,For Bin Range, select your range of Bin cells,Bin Frequency Select “New Worksheet Ply”,
-17%
0
-16%
1
-15%
0
-14%
0
-13%
0
-12%
1
-11%
1
-10%
1
-9%
3
-8%
6
-7%
4
-6%
9
-5%
4
-4%
2
-3%
11
-2%
13
-1%
22
0%
17
1%
32
2%
40
3%
21
4%
25
5%
8
6%
11
7%
5
8%
5
9%
5
10%
2
11%
2
12%
0
More
1
Select “Chart Output”.TOTAL: 2525 010152025303540-17%-16%-15%-14%-13%-12%-11%-10%-9%-8%-7%-6%-5%-4%-3%-2%-1%0%1%2%3%4%5%6%7%8%9%10%11%12%MoreDistribution of monthly HPYs (stock A) Step #10
It doesn’t look
normally distributed,“cannot useMarkowitz Portfolio
Theory!”
Get Professional Assignment Help Cheaply
Are you busy and do not have time to handle your assignment? Are you scared that your paper will not make the grade? Do you have responsibilities that may hinder you from turning in your assignment on time? Are you tired and can barely handle your assignment? Are your grades inconsistent?
Whichever your reason may is, it is valid! You can get professional academic help from our service at affordable rates. We have a team of professional academic writers who can handle all your assignments.
Our essay writers are graduates with diplomas, bachelor’s, masters, Ph.D., and doctorate degrees in various subjects. The minimum requirement to be an essay writer with our essay writing service is to have a college diploma. When assigning your order, we match the paper subject with the area of specialization of the writer.
Why Choose Our Academic Writing Service?
Plagiarism free papers
Timely delivery
Any deadline
Skilled, Experienced Native English Writers
Subject-relevant academic writer
Adherence to paper instructions
Ability to tackle bulk assignments
Reasonable prices
24/7 Customer Support
Get superb grades consistently
How It Works
1. Place an order
You fill all the paper instructions in the order form. Make sure you include all the helpful materials so that our academic writers can deliver the perfect paper. It will also help to eliminate unnecessary revisions.
2. Pay for the order
Proceed to pay for the paper so that it can be assigned to one of our expert academic writers. The paper subject is matched with the writer’s area of specialization.
3. Track the progress
You communicate with the writer and know about the progress of the paper. The client can ask the writer for drafts of the paper. The client can upload extra material and include additional instructions from the lecturer. Receive a paper.
4. Download the paper
The paper is sent to your email and uploaded to your personal account. You also get a plagiarism report attached to your paper.
Get Professional Assignment Help Cheaply
Are you busy and do not have time to handle your assignment? Are you scared that your paper will not make the grade? Do you have responsibilities that may hinder you from turning in your assignment on time? Are you tired and can barely handle your assignment? Are your grades inconsistent?
Whichever your reason may is, it is valid! You can get professional academic help from our service at affordable rates. We have a team of professional academic writers who can handle all your assignments.
Our essay writers are graduates with diplomas, bachelor’s, masters, Ph.D., and doctorate degrees in various subjects. The minimum requirement to be an essay writer with our essay writing service is to have a college diploma. When assigning your order, we match the paper subject with the area of specialization of the writer.
Why Choose Our Academic Writing Service?
Plagiarism free papers
Timely delivery
Any deadline
Skilled, Experienced Native English Writers
Subject-relevant academic writer
Adherence to paper instructions
Ability to tackle bulk assignments
Reasonable prices
24/7 Customer Support
Get superb grades consistently
How It Works
1. Place an order
You fill all the paper instructions in the order form. Make sure you include all the helpful materials so that our academic writers can deliver the perfect paper. It will also help to eliminate unnecessary revisions.
2. Pay for the order
Proceed to pay for the paper so that it can be assigned to one of our expert academic writers. The paper subject is matched with the writer’s area of specialization.
3. Track the progress
You communicate with the writer and know about the progress of the paper. The client can ask the writer for drafts of the paper. The client can upload extra material and include additional instructions from the lecturer. Receive a paper.
4. Download the paper
The paper is sent to your email and uploaded to your personal account. You also get a plagiarism report attached to your paper.
PLACE THIS ORDER OR A SIMILAR ORDER WITH Essay fount TODAY AND GET AN AMAZING DISCOUNT
The post Finance equity concepts risk and return appeared first on Essay fount.
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but you proved you are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 11***| Rating: ⭐⭐⭐⭐⭐
"This company is the best there is. They saved me so many times, I cannot even keep count. Now I recommend it to all my friends, and none of them have complained about it. The writers here are excellent."
"Order a custom Paper on Similar Assignment at essayfount.com! No Plagiarism! Enjoy 20% Discount!"
