I need 3 securities 3 individual stocks, and 3 benchmarks (indxes). Download 10 years of monthly total return data for each stock and benchmark. Estimate static CAPM and APT using the Fama-MacBeth methodology. Interpret the variance between the stocks and benchmarks. I attached the Excel template for static CAPM and APT using the Fama-MacBenth .

ASSET PRICING Static CAPM Using Fama-MacBeth Method Inputs Market Portfolio Benchmark Asset Type Market Portfolio Benchmark US S&P 500 CRSP (SPY) VWMR Asset Type US FF Country Stock Port ETF Stock Barrick Gold (ABX) Monthly Returns Dec 2012 Nov 2012 Oct 2012 Sep 2012 Aug 2012 Jul 2012 Jun 2012 May 2012 Apr 2012 Mar 2012 Feb 2012 Jan 2012 Dec 2011 Nov 2011 Oct 2011 Sep 2011 Aug 2011 Jul 2011 Jun 2011 May 2011 Apr 2011 Mar 2011 Feb 2011 Jan 2011 Dec 2010 Nov 2010 Oct 2010 Sep 2010 Aug 2010 Jul 2010 Jun 2010 May 2010 Apr 2010 Mar 2010 Feb 2010 Jan 2010 Dec 2009 Nov 2009 Oct 2009 Sep 2009 Aug 2009 1.39% -14.25% -3.02% 8.41% 17.76% -12.48% -3.81% -2.89% -7.01% -8.90% -2.80% 8.86% -14.43% 7.13% 6.11% -8.08% 6.94% 5.03% -5.17% -6.14% -1.73% -1.72% 11.43% -10.66% 2.96% 7.65% 3.89% -1.01% 14.06% -9.49% 7.91% -2.92% 13.59% 1.81% 8.16% -11.58% -7.75% 19.37% -5.20% 9.22% -0.57% Stock IBM (IBM) 0.78% -1.86% -6.23% 6.47% -0.14% 0.20% 1.39% -6.44% -0.75% 6.06% 2.53% 4.74% -2.19% 2.23% 5.58% 1.72% -5.05% 6.00% 1.55% -0.53% 4.61% 0.74% 0.33% 10.38% 3.75% -1.04% 7.05% 8.94% -3.60% 3.98% -1.42% -2.40% 0.58% 0.86% 4.35% -6.50% 3.60% 5.22% 0.84% 1.32% 0.57% DJ World (DWG) 1 3 Stock Korea Electric (KEP) 13.85% -4.81% 3.78% 15.97% -3.08% -1.16% 18.94% -1.05% -2.36% -12.74% -9.57% 12.30% -1.08% 0.63% 29.76% -19.12% -13.00% -8.97% -3.42% 13.55% -1.14% 0.33% -4.16% -5.77% 13.43% -9.84% 2.17% 5.04% -12.63% 9.39% -4.45% -10.37% -7.39% 0.68% -0.74% 11.76% 5.98% -1.15% -8.92% 23.40% -7.63% Stock Siemens (SI) 5.77% 2.57% 0.76% 6.23% 11.32% 0.74% 1.78% -11.06% -7.90% 1.13% 5.75% 2.69% -5.72% -3.39% 16.91% -12.99% -18.93% -7.45% 2.75% -8.28% 6.26% 2.16% 4.70% 6.32% 13.16% -3.95% 8.45% 16.43% -7.04% 8.78% 0.00% -8.31% -2.33% 15.89% -3.20% -0.38% -7.02% 9.55% -3.16% 7.05% 9.26% Jul 2009 Jun 2009 May 2009 Apr 2009 Mar 2009 Feb 2009 Jan 2009 Dec 2008 Nov 2008 Oct 2008 Sep 2008 Aug 2008 Jul 2008 Jun 2008 May 2008 Apr 2008 Mar 2008 Feb 2008 Jan 2008 Dec 2007 Nov 2007 Oct 2007 Sep 2007 Aug 2007 Jul 2007 Jun 2007 May 2007 Apr 2007 Mar 2007 Feb 2007 Jan 2007 Dec 2006 Nov 2006 Oct 2006 Sep 2006 Aug 2006 Jul 2006 Jun 2006 May 2006 Apr 2006 Mar 2006 Feb 2006 Jan 2006 Dec 2005 Nov 2005 Oct 2005 Sep 2005 Aug 2005 Jul 2005 Jun 2005 May 2005 Apr 2005 Mar 2005 4.02% -11.90% 31.55% -10.24% 7.35% -19.45% 1.96% 24.81% 30.43% -38.11% 5.79% -17.99% -6.92% 12.93% 4.84% -11.12% -16.36% 0.99% 22.33% 3.80% -7.86% 9.56% 23.86% -1.16% 13.18% -0.21% 4.16% -1.54% -4.42% 0.84% -3.52% -2.35% 1.77% 0.91% -8.24% 8.70% 4.05% -3.30% 0.79% 11.89% -0.48% -13.00% 12.88% 4.74% 5.82% -13.08% 10.79% 7.02% -2.12% 8.87% 3.49% -6.84% -3.78% 12.94% -1.75% 3.51% 6.52% 5.28% 0.96% 8.90% 3.14% -11.69% -20.51% -3.92% -4.49% 7.97% -8.42% 7.65% 4.83% 1.12% 6.68% -0.92% 2.78% -9.08% -1.43% 0.95% 5.82% 5.13% -1.27% 4.69% 8.43% 1.42% -5.96% 2.06% 5.69% -0.12% 12.68% 1.20% 4.99% 0.77% -3.85% -2.60% -0.16% 2.78% -1.06% -1.09% -7.54% 8.82% 2.07% -0.50% -3.16% 12.48% -1.79% -0.82% -16.42% -1.30% 16.26% 0.17% 7.49% 16.72% 19.92% -23.24% -14.38% 24.84% -6.06% -20.10% -17.67% -5.64% 9.77% -10.09% -1.64% 9.24% -13.11% -14.01% -3.45% 0.70% -5.26% -3.89% 2.75% -5.06% 8.36% 0.27% 5.61% 3.40% -4.94% -8.00% 0.70% 8.18% 8.65% 1.18% 0.67% 1.46% 0.95% -6.56% -11.01% 5.56% -2.75% -2.24% 16.57% 19.28% 3.12% -7.79% 10.34% -8.13% 11.49% 5.24% 0.61% 10.12% -4.00% 14.87% -5.79% 9.73% 17.48% 12.54% -9.70% -23.21% 26.88% -0.75% -35.94% -13.70% -10.36% 10.22% -3.22% -3.93% 8.73% -14.66% -1.84% -15.86% 3.68% 11.29% -0.64% 9.54% -1.05% -11.48% 8.38% 9.12% 12.85% 1.48% -4.60% 14.25% 3.25% 6.28% 3.11% 2.41% 5.35% -7.01% 0.86% -9.04% 1.58% 1.29% 0.41% 8.94% 13.26% 1.55% -3.76% 1.09% -0.61% 5.95% -0.78% -0.41% -7.01% 1.31% Feb 2005 Jan 2005 Dec 2004 Nov 2004 Oct 2004 Sep 2004 Aug 2004 Jul 2004 Jun 2004 May 2004 Apr 2004 Mar 2004 Feb 2004 Jan 2004 Dec 2003 Nov 2003 Oct 2003 Sep 2003 Aug 2003 Jul 2003 Jun 2003 May 2003 Apr 2003 Mar 2003 Feb 2003 Jan 2003 Monthly Excess Returns Dec 2012 Nov 2012 Oct 2012 Sep 2012 Aug 2012 Jul 2012 Jun 2012 May 2012 Apr 2012 Mar 2012 Feb 2012 Jan 2012 Dec 2011 Nov 2011 Oct 2011 Sep 2011 Aug 2011 Jul 2011 Jun 2011 May 2011 Apr 2011 Mar 2011 Feb 2011 Jan 2011 13.91% -9.74% -1.50% 9.73% 6.99% 5.20% 4.60% -3.19% -4.45% 8.06% -19.13% 16.28% 3.81% -13.25% 1.66% 15.31% 3.40% -6.83% 18.05% -4.36% 2.40% 17.66% -3.92% -3.47% -2.01% 6.75% -0.71% -5.23% 4.61% 5.20% 4.68% 1.24% -2.53% -1.23% -0.50% 0.68% -4.00% -4.83% -2.59% 7.07% 2.36% 1.36% 1.30% 7.71% 1.13% -1.52% -6.29% 3.89% 8.25% 0.62% -0.13% 0.90% 1.38% 4.31% 1.93% 17.42% 8.82% 5.08% 8.31% 1.20% 2.35% -4.69% -9.01% -0.10% 2.89% -3.37% -5.74% 4.88% 3.14% 13.98% 1.76% 1.80% -4.19% -1.27% 18.05% -3.27% -0.72% -2.24% -1.73% -4.29% 6.08% 6.80% 1.41% 6.73% -1.69% -3.14% 2.43% -0.81% -3.61% -4.82% -4.40% 3.53% 9.42% 8.54% 13.30% -4.61% 11.00% 14.86% 2.67% -4.65% 21.29% 3.55% -3.10% -0.13% Dec 2010 Nov 2010 Oct 2010 Sep 2010 Aug 2010 Jul 2010 Jun 2010 May 2010 Apr 2010 Mar 2010 Feb 2010 Jan 2010 Dec 2009 Nov 2009 Oct 2009 Sep 2009 Aug 2009 Jul 2009 Jun 2009 May 2009 Apr 2009 Mar 2009 Feb 2009 Jan 2009 Dec 2008 Nov 2008 Oct 2008 Sep 2008 Aug 2008 Jul 2008 Jun 2008 May 2008 Apr 2008 Mar 2008 Feb 2008 Jan 2008 Dec 2007 Nov 2007 Oct 2007 Sep 2007 Aug 2007 Jul 2007 Jun 2007 May 2007 Apr 2007 Mar 2007 Feb 2007 Jan 2007 Dec 2006 Nov 2006 Oct 2006 Sep 2006 Aug 2006 Jul 2006 Jun 2006 May 2006 Apr 2006 Mar 2006 Feb 2006 Jan 2006 Dec 2005 Nov 2005 Oct 2005 Sep 2005 Aug 2005 Jul 2005 Jun 2005 May 2005 Apr 2005 Mar 2005 Feb 2005 Jan 2005 Dec 2004 Nov 2004 Oct 2004 Sep 2004 Aug 2004 Jul 2004 Jun 2004 May 2004 Apr 2004 Mar 2004 Feb 2004 Jan 2004 Dec 2003 Nov 2003 Oct 2003 Sep 2003 Aug 2003 Jul 2003 Jun 2003 May 2003 Apr 2003 Mar 2003 Feb 2003 Jan 2003 CAPM Beta from the First Pass, Time-Series Regression 5 Yr Estimation Per: Beg Mon - End Mon Barrick Gold (ABX) Dec 2007 - Nov 2012 Nov 2007 - Oct 2012 Oct 2007 - Sep 2012 Sep 2007 - Aug 2012 IBM (IBM) Korea Electric (KEP) Siemens (SI) Aug 2007 - Jul 2012 Jul 2007 - Jun 2012 Jun 2007 - May 2012 May 2007 - Apr 2012 Apr 2007 - Mar 2012 Mar 2007 - Feb 2012 Feb 2007 - Jan 2012 Jan 2007 - Dec 2011 Dec 2006 - Nov 2011 Nov 2006 - Oct 2011 Oct 2006 - Sep 2011 Sep 2006 - Aug 2011 Aug 2006 - Jul 2011 Jul 2006 - Jun 2011 Jun 2006 - May 2011 May 2006 - Apr 2011 Apr 2006 - Mar 2011 Mar 2006 - Feb 2011 Feb 2006 - Jan 2011 Jan 2006 - Dec 2010 Dec 2005 - Nov 2010 Nov 2005 - Oct 2010 Oct 2005 - Sep 2010 Sep 2005 - Aug 2010 Aug 2005 - Jul 2010 Jul 2005 - Jun 2010 Jun 2005 - May 2010 May 2005 - Apr 2010 Apr 2005 - Mar 2010 Mar 2005 - Feb 2010 Feb 2005 - Jan 2010 Jan 2005 - Dec 2009 Dec 2004 - Nov 2009 Nov 2004 - Oct 2009 Oct 2004 - Sep 2009 Sep 2004 - Aug 2009 Aug 2004 - Jul 2009 Jul 2004 - Jun 2009 Jun 2004 - May 2009 May 2004 - Apr 2009 Apr 2004 - Mar 2009 Mar 2004 - Feb 2009 Feb 2004 - Jan 2009 Jan 2004 - Dec 2008 Dec 2003 - Nov 2008 Nov 2003 - Oct 2008 Oct 2003 - Sep 2008 Sep 2003 - Aug 2008 Aug 2003 - Jul 2008 Jul 2003 - Jun 2008 Jun 2003 - May 2008 May 2003 - Apr 2008 Apr 2003 - Mar 2008 Mar 2003 - Feb 2008 Feb 2003 - Jan 2008 Jan 2003 - Dec 2007 Average Beta Risk Premium and Intercept from the Second Pass, Cross-Sectional Regression in the Following Month Following Month Dec 2012 Nov 2012 Oct 2012 Sep 2012 Aug 2012 Jul 2012 Jun 2012 May 2012 Apr 2012 Mar 2012 Feb 2012 Jan 2012 Dec 2011 Nov 2011 Oct 2011 Sep 2011 Aug 2011 Jul 2011 Jun 2011 May 2011 Apr 2011 Mar 2011 Feb 2011 Jan 2011 Dec 2010 Nov 2010 Oct 2010 Sep 2010 Aug 2010 Jul 2010 Jun 2010 May 2010 Apr 2010 Mar 2010 Feb 2010 Jan 2010 Dec 2009 Nov 2009 Oct 2009 Sep 2009 Aug 2009 Jul 2009 Risk Prem Intercept Jun 2009 May 2009 Apr 2009 Mar 2009 Feb 2009 Jan 2009 Dec 2008 Nov 2008 Oct 2008 Sep 2008 Aug 2008 Jul 2008 Jun 2008 May 2008 Apr 2008 Mar 2008 Feb 2008 Jan 2008 Static CAPM Est. vs. Theoret. Value 100.00% 90.00% 80.00% 70.00% 60.00% 50.00% 40.00% 30.00% 20.00% 10.00% 0.00% Risk Prem Empirical Average vs. Theoretical Value Intercept Empirical Average Risk Prem