Question

Problem 4:

You have run a regression of monthly returns on XYZ Corp, a large home appliance

manufacturer, against monthly returns on the S&P 500 index, and come up with the following

output

Rstock= -0.05% + 1.40 Rmarket / R2= 0.25

Assume the one-year treasury bill rate was 3.25% and the 30-year bond rate was 4.5% during the period of your analysis.

(a) What rate will a long term investor in XYZ’s stock require as a return?

(b) What proportion of this firm’s risk is diversifiable?

(c) How well or badly did the firm do relative to expectation during the period of regression?

Show your work.

Answer 1

Problem : 4)Ans. The dataset is needed over here which is not attached. So I try to make a dataset with your given value for 1 year august 2017 to august 2018. Please see the data of monthly returns on XYZ corp , against monthly returns on the S&P 500 index.

c) ans. To find the monthly return just make a difference from previous value and subtract 1 from it.

Suppose S&P 500 monthly return for cell B3 is B3/B4 - 1. You can see here the data are in reverse chronological order. & you will see that last row gives a undefined value,this is for there is no previous value , so you can delete it for the time being.

Now do a regression on xcel.

First do a scatter plot of XYZ & S&P500.

Then put S&P500 as x values and put XYZ as y values. After plotting the scatter plot please remove all the gridlines from diagram. Then click on data point , then right click upon the selected point and add trendline. After opening of add trendline , please tick on regression equation and Rsquare values.

You will get everything. Look at my picture of regression line below :-

a) The regression equation on the output image can explain that if there is 1 unit increase in S&P 500 return , XYZ will decrease by .044 unit. So here if S&P 500 return increases , then return of XYZ decreases.

b)ans . Rsquare = 0.283 , so 28.3% proportion of this firms risk is diversifiable.