Question

Investment analysts generally believe the interest rate on bonds is related to the prime interest rate for loans. a) Use the following data to construct a scatter graph and then fit a regression line to the data. Report the regression formula and the r-squared value from the chart (right click on the data points, select Add Trend line and select Options to show these metrics). b) Do you think the bond rate can be predicted by the prime interest rate? Justify your answer using the relevant metrics. Prime interest rate Bond rate 0.05 0.28 0.12 0.38 0.09 0.22 0.015 0.14 0.004 0.05 0.11 0.44 0.06 0.28 0.02 0.105

Answer 1

a)

b)

Yes, it can be predicted. We need to apply the linear regression here.

We will be applying the Linear regression model here, it can be done by using the function LINEST(y_value, x_value, TRUE, TRUE) where y_values contain values of Bond rate here and x_values have Prime interest rate values.

Select 5 rows and 2 columns and then write the formula in the first cell and after that, press Shift + Ctrl + Enter.    

The equation comes out to be -
Bond rate = 0.08 + 2.74*Prime interest rate