Question

# Consider a portion of monthly return data (In %) on 20-year Treasury Bonds from 2006–2010. Date...

Consider a portion of monthly return data (In %) on 20-year Treasury Bonds from 2006–2010.

 Date Return Jan-06 5.39 Feb-06 4.83 Mar-06 5.41 Apr-06 4.64 May-06 4.05 Jun-06 3.41 Jul-06 3.92 Aug-06 3.46 Sep-06 5.06 Oct-06 5.44 Nov-06 4.96 Dec-06 4.17 Jan-07 3.48 Feb-07 4.7 Mar-07 4.38 Apr-07 3.82 May-07 4.19 Jun-07 4.35 Jul-07 3.83 Aug-07 5.42 Sep-07 3.29 Oct-07 4 Nov-07 3.42 Dec-07 3.24 Jan-08 5.21 Feb-08 4.84 Mar-08 4.59 Apr-08 3.82 May-08 3.61 Jun-08 4.34 Jul-08 4.94 Aug-08 3.9 Sep-08 4.72 Oct-08 4.58 Nov-08 4.83 Dec-08 4.17 Jan-09 4.68 Feb-09 4.35 Mar-09 4.1 Apr-09 4.98 May-09 5.22 Jun-09 4.79 Jul-09 5 Aug-09 3.58 Sep-09 4.34 Oct-09 3.15 Nov-09 5.48 Dec-09 4.28 Jan-10 4.35 Feb-10 3.24 Mar-10 3.27 Apr-10 4.72 May-10 5 Jun-10 4.82 Jul-10 3.59 Aug-10 4.52 Sep-10 4.44 Oct-10 4.59 Nov-10 4.62 Dec-10 3.74

Estimate a linear trend model with seasonal dummy variables to make forecasts for the first three months of 2011. (Round intermediate calculations to at least 4 decimal places and final answers to 2 decimal places.)

 Year Month yˆt
 2011 Jan 2011 Feb 2011 Mar

Let's breakdown the periods into the corresponding year and months as follows (showing a part below):

 Year Month Return 2006 1 5.39 2006 2 4.83 2006 3 5.41 2006 4 4.64 2006 5 4.05 2006 6 3.41 2006 7 3.92 2006 8 3.46 2006 9 5.06 2006 10 5.44 2006 11 4.96 2006 12 4.17

...............................................

Now, carrying out regression in Excel with Return as the response variable and Year, Month as predictor variables (go to Data tab -> Data Analysis -> Regression, and choose Return as Y-column and Year, Month as X-columns), we get the following output:

 SUMMARY OUTPUT Regression Statistics Multiple R 0.121061158 R Square 0.014655804 Adjusted R Square -0.019917677 Standard Error 0.656504176 Observations 60 ANOVA df SS MS F Significance F Regression 2 0.365402512 0.182701256 0.423903055 0.656533573 Residual 57 24.56687082 0.430997734 Total 59 24.93227333 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Intercept 35.45209091 120.340294 0.294598673 0.769370754 -205.5251913 276.4293731 Year -0.015416667 0.059930358 -0.257243027 0.797917543 -0.135425138 0.104591805 Month -0.021706294 0.024551864 -0.884099618 0.380356471 -0.070870554 0.027457966

Hence, the regression model obtained is: Return = 35.452 - 0.0154 * Year - 0.0217 * Month

Using this regression equation, the forecasts for the first 3 months of 2011 are:

 Year Month Yt 2011 Jan 4.46 2011 Feb 4.44 2011 Mar 4.42