Hello,

I need help with the highlighted questions(I, J, and K). Also, can you please explain how you completed the regression part. The assignment is Week 5 assignment. The other documents are from the teacher as an example.

A B C D E F 1 Tool Kit Chapter 6 2 3 Risk and Return 4 5 6-1 Investment Returns and Risk 6 $1,000 7 Amount invested $1,100 8 Amount received in one year $100 9 Dollar return (Profit) 10% 10 Rate of return = Profit/Investment = 11 12 6-2 Measuring Risk for Discrete Distributions 13 14 The relationship between risk and return is a fundamental axiom in finance. Generally speaking, it is 15 totally logical to assume that investors are only willing to assume additional risk if they are This idea is rather difficulty 16 adequately compensated with additional return. of this relationshipfundamental, but theaversion in finance arises from interpreting the exact nature (accepting that risk 17 differs from investor to investor). Risk and return interact to determine security prices, hence it is of 18 paramount importance in finance. 19 20 21 22 A listing of possible outcomes and their probabilities is called a probability distribution, as shown 23 below. 24 25 10/27/2015 26 Scenario 27 28 29 Best Case Most Likely Worst Case Probability of Scenario 0.30 0.40 0.30 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 Rate of Return in Scenario 37% 11% 15% 1.00 Figure 6-1 Discrete Probability Distribution for Three Scenarios Proba bility of S ce nario 0.5 0.4 0.4 0.3 0.3 0.2 0.2 0.1 0.1 0.0 Mo s t L ikely Wo rs t Cas e 15% B es t Cas e 11% 37% Outcomes: Market Ret urns for 3 Scenarios Given the probabilities and the outcomes for possible returns, it is possible to calculate the expected return and standard deviation. G A 58 59 60 61 B D E Figure 6-2 Calculating Expected Returns and Standard Deviations: Discrete Probabilities INPUTS: Expected Return Probability of Scenario (1) 0.30 0.40 0.30 62 63 64 65 C Scenario Best Case Most Likely Worst Case 66 1.00 Product of Probability and Return (3) = (1) (2) 11.1% 4.4% 4.5% Market Rate of Return (2) 37% 11% 15% Exp. ret. = F Standard Deviation Squared Deviation from Deviation Expected Return (5) = (4)2 Prob. Sq. Dev. (4) = (2) D66 (6) = (1) (5) 0.2600 0.0676 0.0203 0.0000 0.0000 0.0000 -0.2600 0.0676 0.0203 Sum = 11.0% Sum = Variance = 67 Std. Dev. = Square root of variance = 68 Note: Calculations are not rounded in intermediate steps. 69 70 71 6-3 Risk in a Continuous Distribution 72 73 It is possible to add more scenarios. 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 Panel A: Probability of Market Return Scenario Scenario 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Panel B: Probability of Stock Return Scenario Rate of Return in Scenario 0.0002 0.0011 0.0054 0.0205 0.0575 0.1201 0.1870 0.2167 0.1870 0.1201 0.0575 0.0205 0.0054 0.0011 0.0002 1.0000 11.0% 20.2% Average = Std. dev. = 0.0198 0.0307 0.0452 0.0625 0.0806 0.0969 0.1082 0.1123 0.1082 0.0969 0.0806 0.0625 0.0452 0.0307 0.0198 1.0000 -66% -55% -44% -33% -22% -11% 0% 11% 22% 33% 44% 55% 66% 77% 88% 11.0% 36.2% Figure 6-3 Discrete Probability Distributions for 15 Scenarios Panel A: Market Return for 15 Scenarios: Standard Devation = 20.2% Proba bility 0.25 0.20 0.15 0.10 0.05 0.00 -66% -55% -44% -33% -22% -11% 0% 11% 22% 33% 44% 55% 66% 77% 88% 77% 88% O utco me s: Market Returns Panel B: Single Company's Stock Return for 15 Scenarios: Standard Devation = 36.2% Proba bility 0.25 0.20 0.15 0.10 0.05 0.00 -66% -55% -44% -33% -22% -11% 0% 11% 22% 33% 44% 55% 66% O utco me s: Sto ck Returns At some point, it becomes impractical to keep adding scenarios. Many analysts use the normal distribution to estimate stock returns. Here is an example of a normal distribution with a similar mean and standard deviation as the discrete distribution shown above. Probabilit y 0.2500 0.2000 0.1500 0.1000 G Normal Distribution 0.0406 20.1% A 0.1000 B C D E F 162 163 0.0500 164 165 166 0.0000 167 -80% -60% -40% -20% 0% 20% 40% 60% 80% 100% 168 169 Ret urn 170 171 172 173 6-4 Using Historical Data to Estimate Risk 174 175 Investors often use historical data to estimate risk. This is quite easy in Excel by using the AVERAGE 176 and STDEV functions. 177 178 G A 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 B C D E Standard Deviation Based On a Sample of Historical Data Inputs: Year 2014 2015 2016 F G Realized return 15.0% 5.0% 20.0% Calculations: =AVERAGE(E183:E185) =STDEV(E183:E185) 10.0% 13.2% Measuring the Standard Deviation of MicroDrive The monthly stock returns for MicroDrive and one of its competitors, SnailDrive, during the past 48 months are shown in the figure below. The actual data are below the figure. Figure 6-5 Historical Monthly Stock Returns for MicroDrive and SnailDrive Monthly Ra te of Re tur n 50% Micro Drive 40% 30% 20% SnailDrive 10% 0% -10% -20% -30% 0 6 12 18 24 Month of Re tur n 30 MicroDrive 14.6% Average Return (annualized) Standard Deviation (annualized) 49.2% Period 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 Full 48 Months Average monthly return: Standard deviation of monthly returns: Average return (annual): Standard deviation (annual): Maximum of monthly returns: Minimum of monthly returns: Past 12 Months Month 37 38 39 40 Market 2.37% 12.68% -1.13% 10.93% -0.02% -3.31% 12.68% -3.96% -4.90% 7.10% 2.94% -6.52% 3.72% 4.74% -8.21% -5.15% 3.92% 1.08% -2.48% 3.92% 3.13% 0.17% 5.17% 2.56% -5.41% -2.09% 1.08% 10.47% -3.74% 2.94% -9.50% 5.17% -0.75% -9.04% -9.50% 4.74% -0.38% 4.32% -1.89% -3.96% 6.58% -1.32% 4.74% -3.10% 7.95% 10.93% -1.70% -3.96% Market 0.9% 5.8% 11.0% 20.0% 12.7% -9.5% Market -0.4% 4.3% -1.9% -4.0% 36 42 48 SnailDrive 8.6% 25.8% Portfolio weights SnailDrive: 75% MicroDrive: 25% MicroDrive SnailDrive Portfolio 1.66% -7.41% -5.15% 23.52% 2.15% 7.49% -4.76% -0.16% -1.31% 38.58% -5.34% 5.64% -3.46% 10.13% 6.73% -5.37% 1.82% 0.02% 22.52% 0.67% 6.13% -8.58% -4.07% -5.19% -13.02% -3.25% -5.69% 0.17% 17.04% 12.82% 24.40% 4.28% 9.31% -18.05% 0.41% -4.20% 6.18% -6.90% -3.63% 12.24% 2.98% 5.30% -18.22% 0.29% -4.34% 7.15% -12.43% -7.54% 9.69% -0.48% 2.06% 12.21% -6.26% -1.64% -6.74% 4.44% 1.64% -16.00% 13.02% 5.76% -11.96% 9.67% 4.26% -19.00% -2.26% -6.45% 13.91% 2.90% 5.65% 17.84% 4.74% 8.01% 14.67% -10.96% -4.55% -16.88% 4.34% -0.97% 3.28% 4.32% 4.06% 28.86% 9.32% 14.21% 2.33% -3.34% -1.92% 12.48% -3.53% 0.47% -7.21% -1.01% -2.56% -9.79% 10.33% 5.30% 0.60% -7.79% -5.69% 0.88% -10.85% -7.92% -8.94% -9.91% -9.67% 2.49% 9.61% 7.83% -11.24% -0.01% -2.82% -9.47% 1.40% -1.31% -20.12% 4.37% -1.75% -0.15% -8.09% -6.10% 3.42% 16.51% 13.24% 4.07% 9.28% 7.98% -13.45% 1.72% -2.07% -13.05% -5.96% -7.73% -1.61% 12.41% 8.91% 29.01% -1.59% 6.06% 6.08% -12.09% -7.55% -2.82% -0.08% -0.76% MicroDrive SnailDrive Portfolio 1.22% 0.72% 0.8% 14.19% 7.45% 6.3% 14.6% 8.6% 10.1% 49.2% 25.8% 21.8% 38.6% 17.0% 14.2% -20.1% -12.4% -9.7% MicroDrive SnailDrive Portfolio -11.2% 0.0% -2.8% -9.5% 1.4% -1.3% -20.1% 4.4% -1.8% -0.2% -8.1% -6.1% A B C D E F G 41 6.6% 3.4% 16.5% 13.2% 292 42 -1.3% 4.1% 9.3% 8.0% 293 43 4.7% -13.5% 1.7% -2.1% 294 44 -3.1% -13.0% -6.0% -7.7% 295 45 7.9% -1.6% 12.4% 8.9% 296 46 10.9% 29.0% -1.6% 6.1% 297 47 -1.7% 6.1% -12.1% -7.5% 298 48 -4.0% -2.8% -0.1% -0.8% 299 Market MicroDrive SnailDrive Portfolio 300 Past 12 Months Average return (annual): 18.2% -29.3% 17.9% 6.1% 301 Standard deviation (annual): 17.8% 44.5% 28.8% 23.9% 302 Total compound return: 18.2% -32.1% 15.1% 3.6% 303 304 305 306 307 6-5 Risk in a Portfolio Context 308 309 Now we are going to analyze the risk of a portfolio instead of the stand-alone risk of individual assets. 310 311 312 Creating a Portfolio 313 314 Look at the data for MicroDrive and SnailDrive shown above. The last column shows a portfolio with 315 the weights shown below. Here are the results for the two companies and for the portfolio. Notice that 316 the portfolio has a higher return than SnailDrive and less risk than either of the two stocks. 317 318 319 320 Portfolio weights 75% 321 SnailDrive: 25% 322 MicroDrive: 323 Market MicroDrive SnailDrive Portfolio 324 Full 48 Months Average monthly return: 0.9% 1.2% 0.7% 0.8% 325 Standard deviation of monthly returns: 5.8% 14.2% 7.4% 6.3% 326 Average return (annual): 11.0% 14.6% 8.6% 10.1% 327 Standard deviation (annual): 20.0% 49.2% 25.8% 21.8% 328 329 330 331 Correlation 332 333 Loosely speaking, correlation measures the tendency of two variables to move together. 334 335 Correlation between MicroDrive and SnailDrive: r= =CORREL(E232:E279,F232:F279) 336 -0.104 337 338 339 6-6 The Relevant Risk of a Stock: The Capital Asset Pricing Model (CAPM) 340 341 The Capital Asset Pricing Model (CAPM) provides a measure of risk. 342 343 Contribution to Market Risk: Beta 344 345 The relevant risk of an individual stock as defined by its beta. Beta measures how much risk a stock 346 contributes to a well-diversified portfolio. 347 Beta for Stock i = bi = r iM(s i/s M) 348 349 350 A portfolio's beta is the weighted average of the stock's individual betas. Consider the following 351 example. 352 353 Contribution of 354 Weight in Stock to Portfolio Beta: Stock Beta: Portfolio: 355 bi wi b i x wi x s M 356 Stock 1 0.6 25.0% 0.150 357 Stock 2 1.2 25.0% 0.300 358 Stock 3 1.2 25.0% 0.300 359 Stock 4 1.4 25.0% 0.350 360 Portfolio beta = 1.100 361 362 363 The standard deviation of a well-diversified portfolio is: 364 Std. Dev. of portfolio = s p = bp (s M) Note: if the bp is negative, then p = |bp| (M). 365 366 367 If the example portfolio had more than 4 stocks and was well-diversified, then its standard deviation 368 would be: 369 Beta of portfolio = bp = 1.1 370 Std. Dev. of market = s M = 20% 371 Std. Dev. of portfolio = s p = 22% 372 373 374 375 Figure 6-7 376 The Contribution of Individual Stocks to Portfolio Risk: The Effect of Beta 377 378 Portfolio standard deviation = 2 2 % b1w1sM = 14% 379 380 381 b4w4sM = 32% 382 383 384 385 b2w2sM = 27% 386 387 388 389 390 b3w3sM = 27% 391 392 393 A 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 B C D E F G Market standard deviation = s M = 20.0% Stock Beta: bi Contribution of Contribution of Weight in Stock to Portfolio Stock to Portfolio Beta: Risk: Portfolio: wi b i x wi bi x wi x s M Category Labels for chart. Stock 1 0.6 25.0% 0.150 3.0% b 1 w1 s M Stock 2 1.2 25.0% 0.300 6.0% b 2 w2 s M Stock 3 1.2 25.0% 0.300 6.0% b 3 w3 s M Stock 4 1.4 25.0% 0.350 7.0% b 4 w4 s M 1.100 22.0% b 5 w5 s M Estimating Beta We can use the data shown previously for MicroDrive and SnailDrive to estimate their betas. Calculating Beta Market 20.0% MicroDrive 49.17% 0.582 SnailDrive 25.80% 0.465 1.430 Standard deviation (annual): Correlation with the market: bi = r iM(s i/s M) 0.600 Beta can also be calculated as the slope of a regression of the stock (on the y-axis) and the market (on the x-axis). This can be done using the SLOPE function or by plotting the returns and specifying that the chart show the TRENDLINE. Calculating Beta as the Slope of a Regression Using Excel Functions (See Excel explanations to right) bi = r iM(s i/s M) Intercept R squared MicroDrive 1.430 -0.001 0.338 SnailDrive 0.600 =SLOPE(F232:F279,$D$232:$D$279) 0.002 =INTERCEPT(F232:F279,$D$232:$D$279) 0.216 =RSQ(F232:F279,$D$232:$D$279) Calculating Confidence Intervals using Excel Functions Input desired probability for confidence interval Lower boundary of confidence interval for beta Upper boundary of confidence interval for beta Lower boundary of confidence interval for intercept Upper boundary of confidence interval for intercept 95% 0.836 2.024 -0.035 0.033 95% 0.261 See explanation to right. 0.939 See explanation to right. -0.018 See explanation to right. 0.021 See explanation to right. Figure 6-8 Stock Returns of MicroDrive and the Market: Estimating Beta y-a xis: Hist orical MicroDrive Ret urns 4 5.0% f(x) = 1.43x - 0.0009583333 R = 0.338285166 0.0% -4 5% 0% 4 5% x-a xis: Hist orical Ma rket Ret urns -4 5.0% A 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 C D E F EXAMPLE: CALCULATING BETA COEFFICIENTS FOR AN ACTUAL COMPANY Now we show how to calculate beta for an actual company, General Electric. Step 1. Retrieve Data We downloaded stock prices and dividends from http://finance.yahoo.com for General Electric, using its ticker symbol GE, and for the S&P 500 Index ( symbol ^SPX), which contains 500 actively traded large stocks. For example, to download the GE data, enter its ticker symbol in the upper left section and click Go. Then select Historical Prices from the upper left side of the new page. After the daily prices come up, click monthly prices, enter a start and stop date, and click "Get Prices." When presenting monthly data, the date shown is for the first date in the month, but the data are actually for the last day of trading in the month, so be alert for this. Note that these prices are "adjusted" to reflect any dividends or stock splits. Scroll to the bottom of the page and click "Download to Spreadsheet." The downloaded data are in csv format. Convert to xls by opening a new Excel worksheet, copying the date and adjusted index price data to it, and saving as an xls file. Then repeat the process to get the S&P index data. At this point you have returns data for GE and the S&P Index, as we show below. Step 2. Calculate Returns Next, calculate the percentage change in adjusted prices (which already reflect dividends) for GE and the S&P to obtain returns, with the spreadsheet set up as shown below. Yahoo actually adjusts the stock prices to reflect any stock splits or dividend payments. For example, suppose the stock price is $100 in July, the company has a 2-for-1 split, and the actual price in August is $60. The reported adjusted price for August would be $60, but the reported price for July would be $50, which reflects the stock split. This gives an accurate stock return of 20%: ($60-$50)/ $50 = 20%, the same as if there had not been a split, in which case the return would have been ($120$100)/$100 = 20%. Or suppose the actual price in September is $50, the company pays a $10 dividend, and the actual price in October is $60. Shareholders have had a return of ($60+$10-$50)/$50 = 40%. Yahoo reports an adjusted price of $60 for October, and an adjusted price of $42.857 for September, which gives a return of ($60-$42.857)/$42.857 = 40%. In other words, the percent change in the adjusted price accurately reflects the actual return. At this point, we are ready to calculate some statistics and to find GE's beta coefficient. This is shown below the data. Not in Textbook: Stock Return Data for GE and the S&P 500 Index 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 B Month February 2015 January 2015 December 2014 November 2014 October 2014 September 2014 August 2014 July 2014 June 2014 May 2014 April 2014 March 2014 February 2014 January 2014 December 2013 November 2013 October 2013 September 2013 August 2013 July 2013 June 2013 May 2013 April 2013 March 2013 February 2013 January 2013 December 2012 November 2012 October 2012 September 2012 August 2012 July 2012 June 2012 May 2012 April 2012 March 2012 February 2012 January 2012 December 2011 November 2011 October 2011 September 2011 August 2011 July 2011 June 2011 May 2011 April 2011 March 2011 February 2011 Market Level (S&P 500 Index) at Month End 2,104.50 1,994.99 2,058.90 2,067.56 2,018.05 1,972.29 2,003.37 1,930.67 1,960.23 1,923.57 1,883.95 1,872.34 1,859.45 1,782.59 1,848.36 1,805.81 1,756.54 1,681.55 1,632.97 1,685.73 1,606.28 1,630.74 1,597.57 1,569.19 1,514.68 1,498.11 1,426.19 1,416.18 1,412.16 1,440.67 1,406.58 1,379.32 1,362.16 1,310.33 1,397.91 1,408.47 1,365.68 1,312.41 1,257.60 1,246.96 1,253.30 1,131.42 1,218.89 1,292.28 1,320.64 1,345.20 1,363.61 1,325.83 1,327.22 Market's Return 5.5% -3.1% -0.4% 2.5% 2.3% -1.6% 3.8% -1.5% 1.9% 2.1% 0.6% 0.7% 4.3% -3.6% 2.4% 2.8% 4.5% 3.0% -3.1% 4.9% -1.5% 2.1% 1.8% 3.6% 1.1% 5.0% 0.7% 0.3% -2.0% 2.4% 2.0% 1.3% 4.0% -6.3% -0.7% 3.1% 4.1% 4.4% 0.9% -0.5% 10.8% -7.2% -5.7% -2.1% -1.8% -1.4% 2.8% -0.1% NA Description of Data Average return (annual): ### Standard deviation (annual): ### Minimum monthly return: ### Maximum monthly return: ### Correlation between GE and the market: Beta: bGE = rGE,M (sGE / sM) Beta (using the SLOPE function): Intercept (using the INTERCEPT function): R2 (using the RSQ function): GE Adjusted Stock Price at Month End $25.99 $23.67 $25.04 $26.00 $25.34 $25.15 $25.29 $24.48 $25.58 $25.86 $25.96 $25.00 $24.59 $24.05 $26.83 $25.31 $24.82 $22.68 $21.80 $22.96 $21.85 $21.80 $20.83 $21.61 $21.70 $20.66 $19.46 $19.41 $19.35 $20.86 $18.88 $18.92 $19.00 $17.21 $17.65 $18.09 $17.17 $16.72 $16.00 $14.08 $14.79 $13.47 $14.30 $15.70 $16.53 $17.08 $17.78 $17.43 $18.19 GE's Return 9.8% -5.5% -3.7% 2.6% 0.8% -0.6% 3.3% -4.3% -1.1% -0.4% 3.8% 1.7% 2.2% -10.4% 6.0% 2.0% 9.4% 4.0% -5.1% 5.1% 0.2% 4.7% -3.6% -0.4% 5.0% 6.2% 0.3% 0.3% -7.2% 10.5% -0.2% -0.4% 10.4% -2.5% -2.4% 5.4% 2.7% 4.5% 13.6% -4.8% 9.8% -5.8% -8.9% -5.0% -3.2% -3.9% 2.0% -4.2% NA 10.7% 18.9% -10.4% 13.6% 0.75 1.23 1.23 0.00 0.57 G A B C D E F G 576 Step 3. Examine the Data and Calculate Beta 577 Using the AVERAGE function and the STDEV function, we found the average historical 578 return and standard deviation for GE and the market. (We converted these from 579 monthly figures to annual figures. Notice that you must multiply the monthly standard 580 deviation by the square root of 12, and not 12, to convert it to an annual basis.) These 581 are shown in the rows above. We also used the CORREL function to find the correlation 582 between GE and the market. We used the SLOPE, INTERCEPT, and RSQ functions to 583 estimate the regression for beta. 584 585 586 6-7 The Relationship between Risk and Return in the Capital Asset Pricing Model 587 588 The SML shows the relationship between the stock's beta and its required return, as predicted by the CAPM. 589 6%