please do all parts of Q.3 only in 40 minutes please urgently... I'll give you up thumb definitely

10 11 Q1 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 0 34 betal 35 1.0 36 ii 2.0 37 38 Q2 a Coeff. 39 alpha 40 41 beta R-squared % idio. 42 b 43 44 Q3 a beta(1) 0.70 45 beta(2) 1.50 46 firm betal 47 b beta(1) 0.80 48 beta(2) 49 firm beta 1.00 50 beta Type correct beta in cell. 51 52 Type the number corresponding to the correct answer in the yellow box! 53 Q4 If the CAPM holds, which of the following statements is true? 54 1 The betas of all assets are non-negative 55 2 The alphas of all assets are zero 56 3 The expected returns of all assets are greater than the risk-free rate 57 4 The R-squareds of security characteristic line (SCL) regressions for all assets are 100% 58 5 There are many different portfolios of just risky assets (no position in the risk-free asset) that are efficient Answer 59 60 61 62 63 64 65 66 67 68 69 70 71 72 a b d e rf rM SD[rM] i 11 iii 11 i ii iii i ii iii PO P1 D1 i 2% 6% 20% beta 1.4 0.6 -0.2 beta r 4% 5% 7% sd[r] 6% 15% 21% 50 53 E[r] E[r] 10% 5% -1% w(rf) w(rf) alpha Std. Err. w(rM) w(rM) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 Excess return MMM 10.38% -1.54% 0.33% 2.75% 1.87% 5.52% 1.37% 3.97% -2.34% 0.50% -8.13% -4.14% -10.55% 10.07% 3.28% 0.85% 6.09% 1.71% 1.84% 0.17% -4.90% 6.15% 1.82% 2.14% -0.20% -5.23% 4.50% 2.08% 8.29% 4.07% 4.01% 1.03% 5.91% -0.83% 7.39% -2.74% 5.13% 5.39% 6.61% 5.05% -5.60% 5.79% 0.69% 2.53% 3.11% 0.48% -1.64% 2.81% 0.05% 4.27% 4.67% 2.64% -1.23% 4.56% -2.19% -3.26% 2.36% -3.00% -1.92% -5.42% US Mkt 9.05% 3.87% -0.12% 6.65% 2.41% 3.19% 0.07% 2.99% -1.19% -1.65% -1.95% -5.50% -6.98% 11.25% -0.38% 0.95% 4.59% 4.25% 3.30% -0.55% -6.05% 4.17% 1.49% 2.16% 2.52% -1.74% 0.40% 0.76% 5.29% 1.22% 3.78% 2.04% 2.25% -1.36% 5.13% -2.99% 3.17% 4.67% 3.01% 2.47% -3.39% 4.48% 0.82% 0.75% 2.26% 2.06% -1.35% 3.92% -1.38% 2.47% 2.67% -0.24% -2.88% 5.60% -1.54% 0.95% 1.18% -1.92% 2.20% -6.09% Download the Excel spreadsheet "Problem Set #3" from the Assignments tab in the NYU LMS (Brightspace). Please put your name and NetID in the blue boxes at the top of the worksheet (my name is currently there). The spreadsheet contains the input data for each problem (as described below) and shaded yellow cells for you to enter your answers. Once completed, upload the file via the Assignments tab in the NYU LMS (Brightspace). Because your solutions will be computer-graded it is very important to follow the instructions: Please do NOT reformat the file or move the yellow cells. Do NOT add rows or columns to the worksheet. However, you can add additional calculations in non-highlighted cells if you wish. Everything in non-highlighted cells will be ignored for grading purposes. You can either enter the formula to calculate your answer in the yellow cells, or enter these formulas elsewhere and just put references to these answers in the yellow cells. Please do NOT round your answers or intermediate calculations. Excel will display rounded answers based on the formatting of the cells, but it will retain the unrounded numbers. Please do NOT add a new worksheet to the file. Do all of your calculations in the "Results" worksheet and submit a file with only a single worksheet. 1. Assume the risk-free rate is 2% (r= 2%), the expected (total) return on the market portfolio is 6% (TM = 6%) and the standard deviation of the return on the market portfolio is 20% (OM = 20%). (All numbers are annual.) Assume the CAPM holds. a. What are the expected (total) returns on securities with the following betas: (1 point each) (i) B = 1.4 (ii) B = 0.6 (iii) B = -0.2 b. What are the betas of securities with the following expected (total) returns: (1 point each) (i) 10% (ii) 5% (iii) -1% c. What are the portfolio weights (in the risk-free asset and the market portfolio) for efficient portfolios (portfolios on the efficient frontier/CML) with the following expected (total) returns: (1 point each) (i) 4% (ii) 5% (iii) 7% 1 d. What are the portfolio weights (in the risk-free asset and the market portfolio) for efficient portfolios (portfolios on the efficient frontier/CML) with the following standard deviations: (1 point each) (i) 6% (ii) 15% (iii) 21% e. For a moment (but just a moment) assume that the CAPM may not hold. A non-dividend paying stock has a current price of $50/share and an expected price in 1 year of $53/share (based on your personal analysis of the company's prospects). (1 point each) (i) If the stock has a beta of 1 (B= 1.0), what is its alpha (a)? (ii) What is the alpha (a) if the beta is 2 ( = 2.0)? 2. In the spreadsheet, in columns O and P, you will find 60 months of data on excess returns of 3M and the US stock market. Run a SCL regression for 3M using these data. When using the regression package in Excel make sure to direct the output to somewhere in the Results worksheet. Do not create a separate worksheet for the regression results. To do this click on the "Output Range:" button and input a suitable cell in the corresponding box. (See the regression video for more instructions.) a. What are the monthly alpha and beta for 3M, the associated standard errors and the R- squared of the regression? (1 point each) b. What percentage of the variance of 3M's return is idiosyncratic? (2 points) 3. Consider a firm with two equally sized divisions (in terms of their value) that engage in completely different lines of business with different risks. a. If these 2 divisions have betas of 0.7 and 1.5, what is the beta of the firm? (2 points) b. If one division has a beta of 0.8, and the beta of the firm is 1.0, what is the beta of the second division? (2 points) c. For the firm in part (b), what beta should be used to compute the cost of capital for the low risk division, i.e., should it be the firm beta (1.0) or the divisional beta (0.8)? (2 points)