Lecture Financial modeling - Topic 2: Estimating asset risk

Financial Modeling Topic #2: Estimating Asset L. Gattis 1 References • Financial Modeling 3rd Edition by Simon Benninga – Ch. 8: Portfolio Models – Ch. 11: Estimating Beta – Ch. 35: Some Excel Hints – Ch. 36: User Defined Functions with VBA 2 Learning Objectives • Expected asset risk measures are needed to construct optimal portfolios, plan for retirement, value equities and options, and forecast corporate cash flow distributions • In this lecture, students will – Compute asset return variance and standard deviation – Scale standard deviations across time – Compute moving average volatility – Compute volatility using EWMAmodels IBM JNJ GS IOO SPY EFA EEM IBM JNJ GS IOO SPY EFA EEM IBM Mo. Mean σ =Avg(Dev^2) MoMean 0.00% Ann. Mean σ=(Avg(Dev^2))^.5 MoVar 0.00% σ =VARP() MoStd Dev 0.00% σ=STDEVP() Ann StdDev 0.00% Ann.σ =Mo*12 EWMALambda Ann.σ=Mo*12^.5 EWMAAnnVol =EWMAVar ^.5* 12^.5 IBM EWMAVar. Returns Returns Returns Returns Returns Returns Returns Dev^2=(rt-μ)^2 Dev^2 Dev^2 Dev^2 Dev^2 Dev^2 Dev^2 Dev^2 Annualized Returns Dev^2 Sum => Date IBM JNJ GS IOO SPY EFA EEM Date IBM JNJ GS IOO SPY EFA EEM 12M MAV Date IBM IBM Periodi (1-ƛ)ƛ-1 Wi=((1-ƛ)ƛ-1)/Σ W(rt-µ)2 11/3/2014 -0.68% 1.09% -0.52% 2.25% 2.75% 0.06% -1.54% 11/3/2014 11/3/2014 10/1/2014 -13.40% 1.11% 3.50% 0.09% 2.35% -0.27% 1.42% 10/1/2014 10/1/2014 9/2/2014 -1.28% 2.76% 2.49% -2.00% -1.38% -3.88% -7.77% 9/2/2014 9/2/2014 8/1/2014 0.92% 4.34% 3.93% 2.31% 3.94% 0.17% 2.82% 8/1/2014 8/1/2014 7/1/2014 5.74% -4.33% 3.25% -1.87% -1.34% -2.60% 1.36% 7/1/2014 7/1/2014 6/2/2014 -1.68% 3.11% 4.77% 0.74% 2.07% 0.92% 2.40% 6/2/2014 6/2/2014 5/1/2014 -5.62% 0.87% 0.33% 0.75% 2.32% 1.59% 2.97% 5/1/2014 5/1/2014 4/1/2014 2.06% 3.12% -2.46% 2.10% 0.69% 1.68% 0.77% 4/1/2014 4/1/2014 3/3/2014 3.96% 6.63% -1.56% 0.90% 0.83% -0.46% 3.89% 3/3/2014 3/3/2014 2/3/2014 5.38% 4.88% 1.76% 5.09% 4.55% 6.13% 3.37% 2/3/2014 2/3/2014 1/2/2014 -5.80% -3.41% -7.41% -4.93% -3.52% -5.21% -8.64% 1/2/2014 1/2/2014 12/2/2013 4.39% -3.25% 4.92% 1.79% 2.59% 2.17% -0.41% 12/2/2013 12/2/2013 11/1/2013 0.80% 2.93% 5.37% 2.14% 2.96% 0.55% -0.27% 11/1/2013 11/1/2013 10/1/2013 -3.22% 6.83% 1.67% 4.30% 4.63% 3.25% 4.18% 10/1/2013 10/1/2013 9/3/2013 1.59% 0.32% 4.00% 4.56% 3.17% 7.83% 7.22% 9/3/2013 9/3/2013 8/1/2013 -6.08% -6.88% -6.96% -2.90% -3.00% -1.95% -2.54% 8/1/2013 8/1/2013 7/1/2013 2.06% 8.90% 8.46% 4.89% 5.16% 5.32% 1.31% 7/1/2013 7/1/2013 6/3/2013 -8.13% 1.99% -6.68% -2.84% -1.33% -2.68% -5.33% 6/3/2013 6/3/2013 5/1/2013 3.19% -0.49% 11.31% 0.64% 2.36% -3.03% -4.83% 5/1/2013 5/1/2013 4/1/2013 -5.05% 4.54% -0.74% 3.60% 1.92% 5.02% 1.22% 4/1/2013 4/1/2013 3/1/2013 6.21% 7.12% -1.74% 1.87% 3.80% 1.32% -1.02% 3/1/2013 3/1/2013 2/1/2013 -0.68% 3.78% 1.63% -1.03% 1.28% -1.30% -2.29% 2/1/2013 2/1/2013 1/2/2013 6.01% 5.45% 15.91% 5.11% 5.11% 3.75% -0.28% 1/2/2013 1/2/2013 12/3/2012 0.78% 0.53% 8.30% 2.89% 0.90% 4.38% 6.80% 12/3/2012 12/3/2012 11/1/2012 -1.87% -0.66% -3.35% 0.53% 0.56% 2.77% 1.56% 11/1/2012 11/1/2012 10/1/2012 -6.23% 2.78% 7.66% 0.21% -1.82% 1.10% -0.43% 10/1/2012 10/1/2012 9/4/2012 6.46% 2.19% 7.53% 2.79% 2.54% 2.71% 5.22% 9/4/2012 9/4/2012 8/1/2012 -0.15% -1.70% 5.24% 2.26% 2.50% 3.21% 0.40% 8/1/2012 8/1/2012 7/2/2012 0.20% 2.46% 5.26% 0.77% 1.18% 0.06% -0.03% 7/2/2012 7/2/2012 6/1/2012 1.39% 8.22% 0.16% 5.72% 4.06% 7.06% 5.03% 6/1/2012 6/1/2012 5/1/2012 -6.46% -3.18% -16.50% -8.95% -6.00% -11.15% -10.69% 5/1/2012 5/1/2012 4/2/2012 -0.75% -1.30% -7.41% -1.98% -0.67% -2.09% -1.71% 4/2/2012 4/2/2012 3/1/2012 6.06% 1.35% 8.01% 1.17% 3.21% 0.42% -3.12% 3/1/2012 3/1/2012 2/1/2012 2.54% -0.38% 3.60% 3.81% 4.35% 4.84% 5.28% 2/1/2012 2/1/2012 1/3/2012 4.74% 0.50% 23.27% 4.49% 4.63% 5.27% 11.00% 1/3/2012 1/3/2012 12/1/2011 -2.19% 1.32% -5.67% 0.53% 1.04% -2.18% -4.27% 12/1/2011 12/1/2011 11/1/2011 2.23% 1.45% -12.15% -1.75% -0.41% -2.17% -1.95% 11/1/2011 11/1/2011 10/3/2011 5.58% 1.10% 15.87% 10.73% 10.92% 9.64% 16.26% 10/3/2011 10/3/2011 9/1/2011 1.72% -3.20% -18.65% -8.22% -6.94% -10.82% -17.90% 9/1/2011 9/1/2011 8/1/2011 -5.05% 2.47% -13.63% -7.73% -5.50% -8.75% -9.26% 8/1/2011 8/1/2011 7/1/2011 6.00% -2.61% 1.41% -2.62% -2.00% -2.37% -1.02% 7/1/2011 7/1/2011 6/1/2011 1.55% -1.15% -5.43% -1.36% -1.68% -1.21% -0.94% 6/1/2011 6/1/2011 5/2/2011 -0.53% 3.28% -6.57% -2.60% -1.12% -2.22% -2.95% 5/2/2011 5/2/2011 4/1/2011 4.61% 10.93% -4.78% 5.39% 2.89% 5.64% 2.74% 4/1/2011 4/1/2011 3/1/2011 0.73% -3.56% -3.16% -1.59% 0.02% -2.40% 6.28% 3/1/2011 3/1/2011 2/1/2011 0.33% 3.71% 0.31% 3.15% 3.48% 3.56% -0.05% 2/1/2011 2/1/2011 1/3/2011 10.38% -3.37% -2.70% 3.40% 2.32% 2.11% -3.83% 1/3/2011 1/3/2011 12/1/2010 3.75% 0.50% 7.70% 7.11% 6.69% 8.31% 7.28% 12/1/2010 12/1/2010 11/1/2010 -1.05% -2.61% -2.88% -3.73% 0.00% -4.83% -2.92% 11/1/2010 11/1/2010 10/1/2010 7.05% 2.87% 11.44% 4.30% 3.82% 3.80% 3.03% 10/1/2010 10/1/2010 9/1/2010 8.94% 8.68% 5.59% 8.55% 8.96% 9.97% 11.75% 9/1/2010 9/1/2010 8/2/2010 -3.62% -0.92% -8.98% -4.44% -4.51% -3.80% -3.25% 8/2/2010 8/2/2010 7/1/2010 3.98% -1.65% 14.89% 10.85% 6.83% 11.62% 10.94% 7/1/2010 7/1/2010 6/1/2010 -1.42% 1.31% -9.01% -3.87% -5.17% -2.05% -1.39% 6/1/2010 6/1/2010 5/3/2010 -2.40% -8.51% -0.40% -9.76% -7.95% -11.20% -9.41% 5/3/2010 5/3/2010 4/1/2010 0.58% -1.38% -14.91% -2.22% 1.55% -2.81% -0.16% 4/1/2010 4/1/2010 3/1/2010 0.86% 3.49% 9.13% 5.99% 6.09% 6.39% 8.12% 3/1/2010 3/1/2010 2/1/2010 4.36% 0.99% 5.37% 0.77% 3.11% 0.27% 1.78% 2/1/2010 2/1/2010 1/4/2010 -6.50% -2.41% -11.92% -5.53% -3.63% -5.07% -7.78% 1/4/2010 1/4/2010 12/1/2009 3.60% 2.51% -0.48% 0.08% 1.91% 0.72% 3.29% 12/1/2009 12/1/2009 11/2/2009 5.23% 7.26% -0.09% 5.52% 6.16% 3.91% 7.84% 11/2/2009 11/2/2009 10/1/2009 0.83% -3.02% -7.69% -1.14% -1.92% -2.52% -3.43% 10/1/2009 10/1/2009 9/1/2009 1.33% 0.75% 11.42% 3.86% 3.55% 3.80% 10.20% 9/1/2009 9/1/2009 8/3/2009 0.56% 0.06% 1.55% 4.02% 3.69% 4.51% -1.33% 8/3/2009 8/3/2009 7/1/2009 12.94% 7.21% 10.75% 9.35% 7.47% 10.03% 11.03% 7/1/2009 7/1/2009 6/1/2009 -1.76% 2.95% 1.99% -0.77% -0.06% -1.39% -2.26% 6/1/2009 6/1/2009 5/1/2009 3.52% 6.30% 12.79% 10.28% 5.84% 13.20% 15.94% 5/1/2009 5/1/2009 4/1/2009 6.52% -0.46% 21.20% 9.04% 9.94% 11.52% 15.58% 4/1/2009 4/1/2009 3/2/2009 5.28% 5.19% 16.40% 8.37% 8.33% 8.39% 16.82% 3/2/2009 3/2/2009 2/2/2009 0.97% -12.60% 13.44% -10.81% -10.75% -10.41% -6.23% 2/2/2009 2/2/2009 1/2/2009 8.89% -3.58% -4.33% -12.80% -8.22% -13.73% -9.29% 1/2/2009 1/2/2009 12/1/2008 3.14% 2.14% 6.83% 3.92% 0.99% 8.87% 10.29% 12/1/2008 12/1/2008 11/3/2008 -11.74% -3.70% -14.60% -6.98% -6.97% -6.37% -9.74% 11/3/2008 11/3/2008 10/1/2008 -20.51% -11.47% -27.52% -15.25% -16.52% -20.84% -25.57% 10/1/2008 10/1/2008 9/2/2008 -3.92% -1.64% -21.94% -7.84% -9.42% -11.44% -14.69% 9/2/2008 9/2/2008 8/1/2008 -4.51% 3.54% -10.90% -1.52% 1.54% -4.26% -6.32% 8/1/2008 8/1/2008 7/1/2008 7.97% 6.42% 5.43% -0.75% -0.90% -3.32% -5.50% 7/1/2008 7/1/2008 6/2/2008 -8.42% -3.59% -0.85% -8.03% -8.35% -8.78% -9.27% 6/2/2008 6/2/2008 5/1/2008 7.67% 0.17% -7.82% -0.64% 1.51% 1.20% 3.17% 5/1/2008 5/1/2008 4/1/2008 4.83% 3.43% 15.92% 5.22% 4.77% 5.44% 9.13% 4/1/2008 4/1/2008 3/3/2008 1.12% 4.70% -2.50% 0.68% -0.89% 0.42% -3.75% 3/3/2008 3/3/2008 2/1/2008 6.72% -1.23% -15.00% -2.08% -2.58% -1.03% 1.99% 2/1/2008 2/1/2008 1/2/2008 -0.92% -5.33% -7.04% -8.68% -6.05% -7.84% -8.92% 1/2/2008 1/2/2008 12/3/2007 2.78% -1.54% -5.11% -0.89% -1.12% -2.98% -1.40% 12/3/2007 12/3/2007 11/1/2007 -9.11% 4.58% -8.58% -2.93% -3.87% -3.64% -7.66% 11/1/2007 11/1/2007 10/1/2007 -1.42% -0.81% 14.56% 2.99% 1.35% 4.25% 11.89% 10/1/2007 10/1/2007 9/4/2007 0.95% 6.35% 23.14% 4.97% 3.87% 5.32% 11.57% 9/4/2007 9/4/2007 8/1/2007 5.83% 2.81% -6.54% 0.89% 1.29% -0.63% 1.03% 8/1/2007 8/1/2007 7/2/2007 5.12% -1.81% -12.95% -2.24% -3.13% -2.30% 0.72% 7/2/2007 7/2/2007 6/1/2007 -1.26% -2.61% -6.10% -0.11% -1.47% -0.31% 3.68% 6/1/2007 6/1/2007 5/1/2007 4.70% -0.84% 5.58% 2.88% 3.40% 2.36% 5.05% 5/1/2007 5/1/2007 4/2/2007 8.44% 6.57% 5.97% 5.50% 4.42% 3.74% 3.71% 4/2/2007 4/2/2007 3/1/2007 1.42% -4.24% 2.42% 1.40% 1.16% 2.90% 5.33% 3/1/2007 3/1/2007 2/1/2007 -5.98% -5.25% -4.91% -2.40% -1.96% -0.12% -3.21% 2/1/2007 2/1/2007 1/3/2007 2.06% 1.19% 6.60% 0.94% 1.50% 1.39% 0.09% 1/3/2007 1/3/2007 12/1/2006 5.68% 0.16% 2.33% 1.91% 1.34% 3.21% 5.48% 12/1/2006 12/1/2006 11/1/2006 -0.11% -1.65% 2.64% 2.14% 1.98% 3.07% 5.98% 11/1/2006 11/1/2006 10/2/2006 12.68% 3.78% 12.40% 3.11% 3.16% 3.74% 7.06% 10/2/2006 10/2/2006 9/1/2006 1.21% 0.44% 13.80% 1.10% 2.69% 0.23% -0.87% 9/1/2006 9/1/2006 8/1/2006 5.00% 3.97% -2.68% 2.96% 2.18% 2.54% 1.58% 8/1/2006 8/1/2006 7/3/2006 0.77% 4.38% 1.78% 2.08% 0.45% 0.81% 2.34% 7/3/2006 7/3/2006 6/1/2006 -3.85% -0.50% -0.34% -0.08% 0.26% -0.06% 0.19% 6/1/2006 6/1/2006 5/1/2006 -2.62% 3.39% -5.83% -2.91% -3.02% -3.82% -11.14% 5/1/2006 5/1/2006 4/3/2006 -0.16% -1.04% 2.33% 3.57% 1.26% 4.78% 6.55% 4/3/2006 4/3/2006 3/1/2006 2.79% 2.72% 11.10% 2.35% 1.65% 4.00% 2.16% 3/1/2006 3/1/2006 2/1/2006 -1.07% 0.78% 0.02% -0.88% 0.58% -0.70% -3.86% 2/1/2006 2/1/2006 1/3/2006 -1.09% -4.27% 10.81% 3.13% 2.40% 5.77% 14.20% 1/3/2006 1/3/2006 12/1/2005 -7.54% -2.66% -0.97% 1.25% -0.18% 5.20% 6.13% 12/1/2005 12/1/2005 11/1/2005 8.84% -0.89% 2.05% 2.30% 4.39% 2.31% 5.76% 11/1/2005 11/1/2005 10/3/2005 2.07% -1.04% 4.15% -2.53% -2.36% -3.19% -6.33% 10/3/2005 10/3/2005 9/1/2005 -0.50% -0.17% 9.36% 1.91% 0.80% 3.71% 8.66% 9/1/2005 9/1/2005 8/1/2005 -3.18% -0.37% 3.44% 0.71% -0.94% 3.83% 1.30% 8/1/2005 8/1/2005 7/1/2005 12.49% -1.61% 5.59% 2.91% 3.83% 2.98% 7.70% 7/1/2005 7/1/2005 6/1/2005 -1.79% -3.13% 4.64% -0.17% 0.14% 1.44% 3.95% 6/1/2005 6/1/2005 5/2/2005 -0.82% -1.75% -8.70% 0.93% 3.22% -0.86% 3.16% 5/2/2005 5/2/2005 4/1/2005 -16.42% 2.19% -2.68% -1.46% -1.87% -1.60% -1.22% 4/1/2005 4/1/2005 3/1/2005 -1.29% 2.38% 1.09% -2.77% -1.83% -2.63% -7.95% 3/1/2005 3/1/2005 2/1/2005 -0.71% 1.84% 0.88% 3.49% 2.09% 3.79% 9.68% 2/1/2005 2/1/2005 1/3/2005 -5.24% 2.01% 3.91% -2.11% -2.25% -1.91% -0.53% 1/3/2005 1/3/2005 12/1/2004 4.61% 5.14% -0.68% 3.54% 3.02% 4.81% 4.79% 12/1/2004 12/1/2004 11/1/2004 5.20% 3.81% 6.48% 4.52% 4.45% 6.09% 9.93% 11/1/2004 11/1/2004 10/1/2004 4.68% 3.64% 5.80% 2.69% 1.29% 3.48% 2.84% 10/1/2004 10/1/2004 9/1/2004 1.25% -3.04% 4.00% 0.02% 1.00% 2.10% 7.01% 9/1/2004 9/1/2004 8/2/2004 -2.53% 5.66% 1.66% 0.78% 0.25% 1.18% 3.49% 8/2/2004 8/2/2004 7/1/2004 -1.23% -0.78% -6.07% -3.62% -3.22% -4.26% -3.63% 7/1/2004 7/1/2004 6/1/2004 -0.50% -0.02% 0.26% 1.67% 1.85% 3.35% 0.95% 6/1/2004 6/1/2004 5/3/2004 0.68% 3.65% -2.94% 0.44% 1.71% 1.15% 1.17% 5/3/2004 5/3/2004 4/1/2004 -4.00% 6.52% -7.05% -1.58% -1.89% -3.42% -9.79% 4/1/2004 4/1/2004 3/1/2004 -4.83% -5.91% -1.43% -2.74% -1.32% 0.09% 0.50% 3/1/2004 3/1/2004 2/2/2004 -2.59% 1.36% 6.36% 2.49% 1.35% 2.30% 3.95% 2/2/2004 2/2/2004 Black-ScholesModel Today 12/1/2014 Maturity 12/21/2015 Strike(K) $140.00 Stock(S) $141.34 DivYld(D) 2.50% Int. Rate(R) 0.20% Volatility(V) 20.00% Years(T) 1.05 Call Mkt Price $8.48 BSCALL() IBM Annual VolatilityEstimates 11Year Sample 0.00% Trailing12Month (ttm) 0.00% .98EWMA 0.00% BS1-Yr Call Implied 20.00% Return Data – Copy entire range 4 Arithmetic Mean Mo. Mean Ann. Mean Date IBM 0.66% 7.95% Returns IBM JNJ 0.86% 10.37% Returns JNJ GS 0.97% 11.67% Returns GS IOO 0.55% 6.63% Returns IOO SPY 0.72% 8.60% Returns SPY EFA 0.62% 7.38% Returns EFA EEM 1.00% 11.95% Returns EEM Date 11/3/2014 10/1/2014 -0.68% -13.40% 1.09% -0.52% 1.11% 3.50% 2.25% 2.75% 0.09% 2.35% 0.06% -1.54%11/3/2014 -0.27% 1.42%10/1/2014 9/2/2014 8/1/2014 7/1/2014 6/2/2014 5/1/2014 4/1/2014 3/3/2014 2/3/2014 1/2/2014 -1.28% 2.76% 2.49% 0.92% 4.34% 3.93% 5.74% -4.33% 3.25% -1.68% 3.11% 4.77% -5.62% 0.87% 0.33% 2.06% 3.12% -2.46% 3.96% 6.63% -1.56% 5.38% 4.88% 1.76% -5.80% -3.41% -7.41% -2.00% -1.38% 2.31% 3.94% -1.87% -1.34% 0.74% 2.07% 0.75% 2.32% 2.10% 0.69% 0.90% 0.83% 5.09% 4.55% -4.93% -3.52% -3.88% -7.77% 0.17% 2.82% -2.60% 1.36% 0.92% 2.40% 1.59% 2.97% 1.68% 0.77% -0.46% 3.89% 6.13% 3.37% -5.21% -8.64% 5 9/2/2014 8/1/2014 7/1/2014 6/2/2014 5/1/2014 4/1/2014 3/3/2014 2/3/2014 1/2/2014 ... - tailieumienphi.vn