70.2K

Verified Solution

Link Copied!

Flag question: Question 1 Question 110 pts At Canvas read the handout Tools of Risk Analysis 5: Regression/CAPM and download the Excel spreadsheet named Data_HW11-capm.xlsx The monthly, closing (adjusted for splits) stock price of three companies are listed, General Mills (GIS), John Deere (DE) and CF Industries (CF), from 2015 to 2018. The S&P 500 index price series is also given for the same period as is the 3-month US Treasury Bill rate (IRX), which we will use for a risk-free rate. Questions 1-4. In Excel, calculate the monthly returns of each company and the S&P 500 index. Your spreadsheet should look something like this Returns Returns Returns Returns Rate GIS DE CF SP500 IRX 9/1/2005 0.0451 -0.0639 -0.1179 0.0081 0.0347 10/1/2005 0.0012 -0.0085 -0.0668 -0.0167 0.0381 11/1/2005 -0.0151 0.1429 0.1179 0.0378 0.0386 12/1/2005 0.0377 -0.0179 -0.0129 0.0003 0.0399 1/1/2006 -0.0144 0.0536 0.1154 0.0265 0.0437 2/1/2006 0.0132 0.0628 0.0359 0.0027 0.0451 3/1/2006 0.0290 0.0364 -0.0358 0.0124 0.0452 4/1/2006 -0.0264 0.1104 0.0182 0.0134 0.0465 5/1/2006 0.0517 -0.0248 -0.0127 -0.0288 0.0472 6/1/2006 -0.0044 -0.0246 -0.1651 0.0014 0.0486 7/1/2006 0.0046 -0.1308 0.1367 0.0062 0.0494 8/1/2006 0.0449 0.0762 -0.0216 0.0238 0.0491 9/1/2006 0.0437 0.0744 0.0763 0.0258 0.0476 10/1/2006 0.0039 0.0145 0.1611 0.0326 0.0494 11/1/2006 -0.0153 0.1277 0.1478 0.0190 0.0489 12/1/2006 0.0295 -0.0097 0.1270 0.0140 0.0489 1/1/2007 -0.0062 0.0548 0.1895 0.0151 0.0498 2/1/2007 -0.0161 0.0799 0.2675 -0.0196 0.0500 3/1/2007 0.0337 0.0032 -0.0028 0.0112 0.0490 4/1/2007 0.0289 0.0070 0.0296 0.0443 0.0473

Answer & Explanation Solved by verified expert

See Answer