This question is based to the stock data that can be obtained from the following link: http://biz.yahoo.com/r/ "Choose Research Tools" and then "Historical Quotes". Obtain monthly stock data for 5 years (60 months + one additional month) starting date: (mm-dd-yy) 12-1-2015 and end date 01-01-2021. (Note: since you will loss one data point when calculating rate of returns i.e. ( ) therefore you require 61 month P price data). The stock tickers for the download are given as follows: Please use three stocks as given below. [NOTE: Each group will have AAPL, AMZN, S&P 500, and T-Bill same but the third common stock. In this assignment, I am using third stock as "CAT" but this will vary across groups. 1) Apple Inc. "AAPL" 2) Amazon Inc. "AMZN" 3) Caterpillar "CAT" 4) T-bills: use the following link to obtain to obtain monthly 5 years T-bill data (mm-dd-yy) (01-01-2016 and end date 12-01-2020) https://research.stlouisfed.org/fred2/series/TB3MS/downloaddata (select the date as required for this assignment, under Treasury bills, choose Three-month bill and monthly data for the stated period.). Note: T-bills are already in returns therefore you do not need to calculate percent returns as required for other stocks. But, you will calculate annual average returns. 5) S&P 500 Composite: use the following link to obtain S&P 500 monthly 5 years data(mm-dd-yy) 12-1-2015 and end date 01-01-2021.required for other stocks. But, you will calculate annual average returns. 5) S&P 500 Composite: use the following link to obtain S&P 500 monthly 5 years data(mm-dd-yy) 12-1-2015 and end date 01-01-2021. http://finance.yahoo.com/q?s=^GSPC&ql=0. S&P 500 is used for market index i.e. market return. How to arrange the data: . Download data and keep in ascending order (i.e. 2015 first and 202 1last value). . Using the "adjusted close" column (delete rest of the columns except date), which is adjusted for stock splits and dividends. . compute monthly returns for companies in your portfolio and the S&P500, calculate monthly rate of returns using Piti -Pt (ignore dividends if present)How to arrange the data: . Download data and keep in ascending order (i.e. 2015 first and 2021 last value). . Using the "adjusted close" column (delete rest of the columns except date), which is adjusted for stock splits and dividends. . compute monthly returns for companies in your portfolio and the S&P500, calculate monthly rate of returns using . (ignore dividends if present). Note: do not worry about dividend data - ignore. Once all data is downloaded and dates are completely matched. Keep only first column for DATE i.e. period. Add "Data analysis" and "Solver" to Excel. Go to file -> Options >add- Ins - Analysis ToolPak -> Go and OK -> check Analysis Toolpak box You have installed Data analysis. Go to DATA on the top menu and you will find Data analysis to right most top corner. Repeat the process to install SOLVER. Q.1: [5 marks each part = 5x9 = 45] A. Calculate annual average returns for each stock. Use monthly rate of returns to calculate annual average returns. You will have 5 annual returns. [take average of 12 months of each year]. For all next calculation, you will be referred to 5 annual returns rather than monthly returns. Calculate arithmetic average (mean) return of annual returns. [it will be one value i.e. average of 5 annual returns]. =AVERAGE(range of data) B. Geometric average (mean) return and compare the results. Use the below formula: geometric average: =SUMPRODUCT(GEOMEAN(D3:D8+1))-1 C. Compare B and C results and comment the difference. D. Calculate Variance and Standard Deviation(SD): =STDEV.S(range) [Use annual returns G. Calculate correlation matrix for all stocks including T-bill, and S&P 500. H. Calculate Covariance matrix for each pair of stocks. [note: Use Data analysis to calculate correlation and covariance] [use annual returns] By looking at the correlation matrix (only common stock correlation), which pair of common stocks would you expect to have the greatest diversification effect. Read lecture notes to understand diversification effect in relation with correlation coefficient. J. Write all of the above results at one place in table format before you proceed to the next questions. Should include: Part A (5 annual returns and its average), D, G, H (correlation and covariance matrix).Q.2: [Caution: You are not using monthly data anymore] [5 marks each 3x5= 15] A. Portfolio of two stocks: Calculate expected return of portfolio for each pair of stock (only common stocks: AAPL+AMZN, AAPL+CAT, AMZN+CAT). Each stock has equal fraction of investment i.e. 0.5 each. Where 0.5 is the weight for each stock, the average will be called weighted average or expected return. e.g. pair expected return: =0.5*average annual return of AAPL+0.5*average annual return of AMZN. B. Calculate each portflio's SD using 0.5 weights for each stock. Portfolio SD formula is given below. [use average annual returns] C. Re-calculate portfolio SD by using portfolio expected returns, calculated in part A by using formula: =STDEV.S(range of portfolio returns). Compare results in part B and C. Are the values same or different? Q.3: [2x5 = 10] Portfolio of three stocks: Make three stock portfolios for common stocks (excluding the S&P 500 and T-Bill). Three stock portfolio AAPL-AMZN-CAT). Use equal weights i.e. 0.333 for each stock and use average monthly returns. A. Calculate expected returns and standard deviations for three stock portfolio. B. Compare SD of two stock (Q.2.B) with three stock portfolio. What difference do you observe between two? Comment. Formula for SD for 2-stock and three stock: SD(protfolio) = SD(AAPL. AMZN) = JAAPLAMEN = wic] + who2 + 2w,w20102/1.2 Three stocks portfolio SD (note: all terms are inside the square root) SD(protfolio) = SD(AAPL. AMZN, CAT) = GAAPLAMEN,CAT = wol + who? + who? + 2w,w20102P1,2 + 2wiW30103P1,3 + 2w2 W20203P2,3 Q.4: [7x5=35] In this question you will be calculating two stock portfolio (AAPL and AMZN) and (AAPL and CAT) weighted annual returns. Weights are: 100%, 90%, 80%, 70%, 60%, 50%, 40%, 30%, 20% , 10% and 0%. A. Calculate weighted (expected) returns for the two stock portfolios: (AAPL andA. Calculate weighted [expected] returns for the two stock portfolios: (MPL and AMEN) and [MPL and CAT]. For this part, use arithmetic average of annual returns that you calculated in part (2.1.3. Then, take the average of weighted retln'ns, this value will he portfolio (ML, AMEN} expected return. Repeat same calculation for the portfolio (ML, CAT]. B. Use the above SD formula to calculate portfolio standard deviation for portolio [AAPL and AMEN] and {HAPL and CAT}, where subscript 1: AAPL and subscript 2: AMEN and p1; = pMPLAMZN where ,3 stands for correlation i.e. correlation betwe- AAPL and AM. [This calculation is based on actual correlation coefci-t calculated in Q. 113.] C. Calculate standard deviation {SD} of portfolio for four values of correlation coefcient. on = Pmtuuzy (+1, 0, l and actual correlation calculated in Q.1.G}. You will he calculating 11 values of SD for each correlation value i.e +1, 0, l and actual correlation calculated in Q.l.G. Where these 11 values are coming from?II We used 11 weights 100%, 90%. 30%, 70%, 60%, 50%, 40%, 30%, 20% , 10% and 0%. [you have 11 values for each year. A matrix of 10x11 will created] D. Repeat part B for portfolio (AAPL and CAT} E. Plot the 'DpporMnitv Set" for portfolio (AAPL and ANIZN} by taking standard deviation {SD} of portfolio on horizontal axis [calculated in part [I] and expected return of portfolio on the vertical axis [calculated in part A} for each degree of correlation i.e. p1; = ppmm {+1I 0I l and actual]. F. Repeat D for the portfolio (AAPL and CAT} using PLE = Pandas? (+1, 0, 1 and actual} G. Interpret the plots in part E and F to discuss diversication benets {considering different correlation values]. (1.5: [5x5 = 30] Q.5: [6x5 = 30] A. Efficient Frontier (EF): Use portfolio SD values (Q.4.B) (i.e. with actual correlation coefficient P1,2 = PAAPLAMEN). NOW you are going to draw a graph that will be efficient frontier (EF) or minimum variance frontier. Use scattered plot with line, select Expected returns of portfolio, vertical axis: Expected portfolio returns (Q.4.A) and horizontal axis Q.4.B (portfolio SD values). This graph gives different combination of risk and return but investor is objectively looking for a combination that minimizes risk with possible highest return. Explain the minimum variance vs returns for variance values between minimum and highest value of SD vs return. The best combination of risk and return for an investor to consider is known when we draw a constraint which will make tangent at a point on EF. Next part will draw the constraint. B. Efficient Portfolio: You are going to calculate Sharpe Ratio (SR) (= (Expected portfolio return - RF rate)/Portfolio SD). Use hypothetical weights (in addition to weights used in Q.4.C) 55% for AAPL and 45% for AMZN to calculate Expected portfolio return and portfolio SD in SR. Whereas, risk free rate RF is used that you calculated in Q.1 A. Now, you are finding a combination of weights such that SR is maximized. For this you need to use SOLVER in excel. C. Go to top menu and select DATA and next select SOLVER. In solver, insert SR cell in "Set Objective", insert first value of hypothetical weight (55%) (used in part B) in "by changing variable cells", solve. The iteration calculation will solve for a specific weight that maximums the portfolio SR ratio. This will give you portfolio return and SD. Collect the following values: RF rate (used in part B), portfolio return and SD, SD of RFR and correlation between RF and portfolio return (equals zero). D. Use portfolio return and portfolio SD which is obtained through Solver that maximized SR. Now use weight from 100% - 0% to -160% and 60% to create portfolio return (AAPL, RFR) and portfolio SD (AAPL, RFR). This will construct the Capital Market Line (CML). In the next part, CML will be make tangent to EF. E. Go to the graph that you have drawn in part A. Add series that you created in part D. This will make tangent to the EF. F. Interpret the tangency point i.e. the efficient portfolio.Q. a: [9x5=45] A. Calculate beta [ ,8] for all stocks against the SELF Elli] and a ,8 for SH 5110 against itself i.e. 5&P SUD [do not need Tbill]. ,3 is the slope of the line or regression You will be calculating four {4] , three for each stock and one for 3&P S'EID. [use regression or slope or formula given below to calculate ]. can also be calculated formula [Cov[stock expected return, market expected retmn}}:"varianee of market index [3&P5}. B. Plot [four] 4 ,3 values i.e. the slopes by taking expected annual rate of return each stock calculated in Q] on the vertical axis {Y} and expected rate of return for market portfolio i.e. SELF SDI] {X} on horizontal axis. C. Interpret the ,3 plots in terms of risk associated with each security. D. Calculate the required rate of return of each stock (ML, AMEN and CAT] by using this equation: ElRM }=R.r_1w +3413. [E(R31P5m}Rr_m II. Use ,8 values calculated part A. Compare required rate of return of each stock [based on ,8] with the expected annual rate of return calculated in (1.1 and decided weather the stock under consideration is overvalued or undervalued or properly valued Repeat for am and CAT stocks. E. Construct the Security Market Line (SML). You need Market return [SELF 5-30} and actual returns of each stock (ML, ALIEN, CAT] and expected returns calculated in part D and values calculated in Part A. For SML Expected returns of {HAPL AMEN, CAT} on the vertical axis and ,3 values on the horizontal axis. This will be a straight line. Now add actual returns of the stock on SML line graph. These points may be above or below or by chance on the SIMIL. Use these actual return points vs SML to make decision weather the each of the stock is overvalued or undervalued. F. Repeat part1 for hypothetical values: ,3 [H., [L51], Lilli], LEI}, 2.00] and E. Construct the Security Market Line (SML). You need Market return (S&P 500) and actual returns of each stock (AAPL, AMZN, CAT) and expected returns calculated in part D and - values calculated in Part A. For SML Expected returns of (AAPL, AMZN, CAT) on the vertical axis and -values on the horizontal axis. This will be a straight line. Now add actual returns of the stock on SML line graph. These points may be above or below or by chance on the SML. Use these actual return points vs SML to make decision weather the each of the stock is over-valued or under-valued. F. Repeat part I for hypothetical -values: [0.00, 0.50, 1.00, 1.50, 2.00] and calculate required rate of return for AAPL (only). E( RAAPL )= RT-Bill + BAAR (E(Reason ) - Ry-Bill ). There will be 5 required rate of return calculations for 5 / values. Plot SML line for AAPL. Compare actual rate of returns with the required rate of return and conclude if the stock is overvalued/undervalued/properly valued. [read how to determine if security is over/under valued in CAPM]. G. Now, consider two scenarios (i) Re-calculate the required rate of return for AAPL only, as calculated in part H, when inflation rate (INF) is 1.5%. [RRRAAPL = (RFR+INF)+ / AAPL((R&P500 +INF)-(RFR+INF)) for each / [ 0.00, 0.50, 1.00, 1.50, 2.00]. (ii) Recalculate the required rate of return when market risk-premium ((RS&P500 +INF)-(RFR+INF)) is 0.5%. H. Plot SML line for AAPL. Compare actual rate of returns with the required rate of return and conclude if the stock is overvalued/undervalued/properly valued. I. Compare part D with part E. Discuss the difference. You Tube Tutorial Videos: For Beta, portfolio SD, Cov and other calculations: https://www.youtube.com/watch?v-wxbmBXzbOLw For Beta, portfolio SD, Cov and other calculations