Lecture Financial modeling - Topic 1: Asset expected return and user-defined function

Financial Modeling Topic #1: Asset Expected Return and User-Defined Function 1 References • Financial Modeling 3rd Edition by Simon Benninga – Ch. 2: Calculating Cost of Capital – 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 return measures are needed to construct portfolios, plan for retirement, value equities and options, and forecast corporate cash flows • In this lecture, students will – Compute historical returns for stocks and market indexes – Estimate expected asset returns using the CapitalAsset Pricing Model (CAPM), Global CAPM (GCAPM), the Fama-French 3 Factor Model (FF3F), and fundamental analysis – Use excel styles and formats 3 1.95%Current RiskFreeRate,10-Year TreasuryYield used for CAPM 5.00%Market RiskPremium IBM JNJ GS 0.20%Current RiskFreeRate,Tbill Rateused for FF3F -Market Pr $ (162.17) $ (108.25) $ (188.41) Exp. Ann.Ret. IBM JNJ GS IOO SPY EFA EEM FCFEst Yr $ 20.00 $ 15.00 $ 23.00 Arith Mean FCFEst Yr $ 21.00 $ 15.75 $ 24.15 Geom Mean FCFEst Yr $ 22.05 $ 16.54 $ 25.36 CAPM Beta FCFEst Yr $ 23.15 $ 17.36 $ 26.63 GCAPM Beta FCFEst Yr $ 138.92 $ 104.19 $ 159.75 CAPM IRR(Ret) 8.58% 11.90% 8.29% GCAPM FF3F IRR Prices Prices Prices Prices Prices Prices Prices FF3F FF3F FF3F FF3F Returns Returns Returns Returns Returns Returns Returns Ret-FFRf Ret-FFRf Ret-FFRf Date IBM JNJ GS IOO SPY EFA EEM Mkt-Rf SMB HML Rf IBM JNJ GS IOO SPY EFA EEM IBM JNJ GS 11/3/2014 162.17 108.25 188.41 78.54 206.06 63.37 40.91 2.55% -2.13% -3.42% 0.00% 10/1/2014 163.28 107.08 189.39 76.81 200.55 63.33 41.55 2.52% 4.17% -1.89% 0.00% 9/2/2014 188.54 105.9 182.99 76.74 195.94 63.5 40.97 -1.97% -3.80% -1.61% 0.00% 8/1/2014 190.99 103.06 178.54 78.31 198.68 66.06 44.42 4.23% 0.49% -0.75% 0.00% 7/1/2014 189.25 98.77 171.79 76.54 191.14 65.95 43.2 -2.04% -4.28% 0.01% 0.00% 6/2/2014 178.98 103.24 166.39 78 193.74 67.71 42.62 2.61% 2.99% -0.66% 0.00% 5/1/2014 182.03 100.13 158.81 77.43 189.82 67.09 41.62 2.06% -1.87% -0.38% 0.00% 4/1/2014 192.86 99.27 158.28 76.85 185.52 66.04 40.42 -0.19% -4.19% 1.57% 0.00% 3/3/2014 188.96 96.27 162.27 75.27 184.24 64.95 40.11 0.43% -1.83% 4.67% 0.00% 2/3/2014 181.77 90.28 164.84 74.6 182.72 65.25 38.61 4.65% 0.32% -0.48% 0.00% 1/2/2014 172.49 86.08 161.99 70.99 174.77 61.48 37.35 -3.32% 0.84% -1.86% 0.00% 12/2/2013 183.12 89.12 174.96 74.67 181.15 64.86 40.88 2.81% -0.43% -0.15% 0.00% 11/1/2013 175.42 92.11 166.75 73.36 176.57 63.48 41.05 3.12% 1.34% -0.33% 0.00% 10/1/2013 174.03 89.49 158.25 71.82 171.49 63.13 41.16 4.18% -1.57% 1.30% 0.00% 9/3/2013 179.82 83.77 155.65 68.86 163.9 61.14 39.51 3.77% 2.85% -1.59% 0.00% 8/1/2013 177 83.5 149.66 65.86 158.87 56.7 36.85 -2.71% 0.29% -2.46% 0.00% 7/1/2013 188.46 89.67 160.85 67.83 163.78 57.83 37.81 5.65% 1.87% 0.74% 0.00% 6/3/2013 184.66 82.34 148.31 64.67 155.74 54.91 37.32 -1.20% 1.23% -0.44% 0.00% 5/1/2013 201 80.73 158.93 66.56 157.84 56.42 39.42 2.80% 1.95% 1.32% 0.00% 4/1/2013 194.79 81.13 142.78 66.14 154.2 58.18 41.42 1.56% -2.40% 0.41% 0.00% 3/1/2013 205.14 77.61 143.84 63.84 151.3 55.4 40.92 4.03% 0.83% -0.06% 0.00% 2/1/2013 193.15 72.45 146.39 62.67 145.76 54.68 41.34 1.29% -0.39% 0.29% 0.00% 1/2/2013 194.48 69.81 144.04 63.32 143.92 55.4 42.31 5.57% 0.47% 1.31% 0.00% 12/3/2012 183.45 66.2 124.27 60.24 136.92 53.4 42.43 1.18% 1.62% 3.25% 0.01% 11/1/2012 182.03 65.85 114.75 58.55 135.7 51.16 39.73 0.78% 0.67% -1.10% 0.01% 10/1/2012 185.49 66.29 118.73 58.24 134.94 49.78 39.12 -1.76% -1.07% 4.14% 0.01% 9/4/2012 197.81 64.5 110.28 58.12 137.44 49.24 39.29 2.73% 0.49% 1.57% 0.01% 8/1/2012 185.8 63.12 102.56 56.54 134.04 47.94 37.34 2.55% 0.71% 0.55% 0.01% 7/2/2012 186.08 64.21 97.45 55.29 130.77 46.45 37.19 0.79% -2.60% 0.02% 0.00% 6/1/2012 185.7 62.67 92.58 54.87 129.24 46.42 37.2 3.89% 0.82% 0.48% 0.00% 5/1/2012 183.16 57.91 92.43 51.9 124.2 43.36 35.42 -6.19% -0.12% 0.11% 0.01% 4/2/2012 195.8 59.81 110.69 57 132.13 48.8 39.66 -0.85% -0.62% -0.17% 0.00% 3/1/2012 197.28 60.6 119.55 58.15 133.02 49.84 40.35 3.11% -0.24% -0.06% 0.00% 2/1/2012 186.01 59.79 110.68 57.48 128.88 49.63 41.65 4.42% -1.64% 0.04% 0.00% 1/3/2012 181.4 60.02 106.83 55.37 123.51 47.34 39.56 5.05% 2.53% -2.16% 0.00% 12/1/2011 173.19 59.72 86.66 52.99 118.04 44.97 35.64 0.74% -0.57% 1.54% 0.00% 11/1/2011 177.07 58.94 91.87 52.71 116.82 45.97 37.23 -0.28% -0.26% -0.16% 0.00% 10/3/2011 173.2 58.1 104.58 53.65 117.3 46.99 37.97 11.35% 3.59% -0.92% 0.00% 9/1/2011 164.04 57.47 90.26 48.45 105.75 42.86 32.66 -7.59% -3.72% -0.99% 0.00% 8/1/2011 161.27 59.37 110.95 52.79 113.64 48.06 39.78 -5.99% -3.30% -1.57% 0.01% 7/1/2011 169.85 57.94 128.46 57.21 120.25 52.67 43.84 -2.36% -1.43% -1.14% 0.00% 6/1/2011 160.23 59.49 126.67 58.75 122.71 53.95 44.29 -1.75% -0.12% -0.23% 0.00% 5/2/2011 157.78 60.18 133.94 59.56 124.81 54.61 44.71 -1.27% -0.64% -2.07% 0.00% 4/1/2011 158.62 58.27 143.36 61.15 126.23 55.85 46.07 2.90% -0.36% -2.17% 0.00% 3/1/2011 151.63 52.53 150.56 58.02 122.68 52.87 44.84 0.45% 2.70% -1.16% 0.01% 2/1/2011 150.53 54.47 155.48 58.96 122.66 54.17 42.19 3.49% 1.56% 1.72% 0.01% 1/3/2011 150.04 52.52 155 57.16 118.54 52.31 42.21 1.99% -2.44% 0.70% 0.01% 12/1/2010 135.93 54.35 159.3 55.28 115.85 51.23 43.89 6.82% 0.78% 3.36% 0.01% 11/1/2010 131.02 54.08 147.91 51.61 108.59 47.3 40.91 0.60% 3.67% -0.59% 0.01% 10/1/2010 132.41 55.53 152.3 53.61 108.59 49.7 42.14 3.88% 1.05% -2.22% 0.01% 9/1/2010 123.69 53.98 136.66 51.4 104.59 47.88 40.9 9.54% 3.86% -2.85% 0.01% 8/2/2010 113.54 49.67 129.43 47.35 95.99 43.54 36.6 -4.77% -3.02% -1.40% 0.01% 7/1/2010 117.81 50.13 142.2 49.55 100.52 45.26 37.83 6.93% 0.27% 0.09% 0.01% 6/1/2010 113.3 50.97 123.77 44.7 94.09 40.55 34.1 -5.56% -2.12% -4.28% 0.01% 5/3/2010 114.93 50.31 136.02 46.5 99.22 41.4 34.58 -7.89% -0.08% -2.35% 0.01% 4/1/2010 117.76 54.99 136.56 51.53 107.79 46.62 38.17 2.00% 4.93% 3.19% 0.01% 3/1/2010 117.08 55.76 160.48 52.7 106.14 47.97 38.23 6.31% 1.58% 2.05% 0.01% 2/1/2010 116.08 53.88 147.05 49.72 100.05 45.09 35.36 3.40% 1.43% 2.73% 0.00% 1/4/2010 111.23 53.35 139.56 49.34 97.03 44.97 34.74 -3.36% 0.21% 0.60% 0.00% 12/1/2009 118.96 54.67 158.44 52.23 100.69 47.37 37.67 2.75% 5.78% 0.73% 0.01% 11/2/2009 114.83 53.33 159.21 52.19 98.8 47.03 36.47 5.56% -2.67% 0.17% 0.00% 10/1/2009 109.12 49.72 159.35 49.46 93.07 45.26 33.82 -2.59% -4.22% -4.37% 0.00% 9/1/2009 108.22 51.27 172.63 50.03 94.89 46.43 35.02 4.08% 2.48% 1.39% 0.01% 8/3/2009 106.8 50.89 154.94 48.17 91.64 44.73 31.78 3.33% -0.52% 7.57% 0.01% 7/1/2009 106.2 50.86 152.58 46.31 88.38 42.8 32.21 7.72% 2.50% 4.79% 0.01% 6/1/2009 94.03 47.44 137.77 42.35 82.24 38.9 29.01 0.43% 2.66% -2.44% 0.01% 5/1/2009 95.71 46.08 135.08 42.68 82.29 39.45 29.68 5.21% -2.66% 0.56% 0.00% 4/1/2009 92.46 43.35 119.76 38.7 77.75 34.85 25.6 10.19% 5.35% 5.42% 0.01% 3/2/2009 86.8 43.55 98.81 35.49 70.72 31.25 22.15 8.95% 0.75% 2.60% 0.02% 2/2/2009 82.45 41.4 84.89 32.75 65.28 28.83 18.96 -10.10% -0.36% -6.91% 0.01% 1/2/2009 81.66 47.37 74.83 36.72 73.14 32.18 20.22 -8.12% -0.98% -9.86% 0.00% 12/1/2008 74.99 49.13 78.22 42.11 79.69 37.3 22.29 1.74% 3.99% -1.20% 0.00% 11/3/2008 72.71 48.1 73.22 40.52 78.91 34.26 20.21 -7.86% -3.64% -4.93% 0.03% 10/1/2008 82.38 49.95 85.74 43.56 84.82 36.59 22.39 -17.23% -2.27% -2.99% 0.08% 9/2/2008 103.63 56.42 118.29 51.4 101.6 46.22 30.08 -9.24% -0.37% 4.41% 0.15% 8/1/2008 107.86 57.36 151.53 55.77 112.16 52.19 35.26 1.53% 3.73% 1.48% 0.13% 7/1/2008 112.96 55.4 170.07 56.63 110.46 54.51 37.64 -0.77% 3.59% 3.69% 0.15% 6/2/2008 104.62 52.06 161.31 57.06 111.46 56.38 39.83 -8.44% 1.09% -1.06% 0.17% 5/1/2008 114.24 54 162.7 62.04 121.62 61.81 43.9 1.86% 3.03% -0.38% 0.18% 4/1/2008 106.1 53.91 176.5 62.44 119.81 61.08 42.55 4.60% -1.49% -0.08% 0.18% 3/3/2008 101.21 52.12 152.26 59.34 114.36 57.93 38.99 -0.93% 0.81% 0.28% 0.17% 2/1/2008 100.09 49.78 156.16 58.94 115.39 57.69 40.51 -3.09% -0.57% -0.03% 0.13% 1/2/2008 93.79 50.4 183.71 60.19 118.45 58.29 39.72 -6.36% -0.75% 3.07% 0.21% 12/3/2007 94.66 53.24 197.63 65.91 126.08 63.25 43.61 -0.87% 0.02% 0.00% 0.27% 11/1/2007 92.1 54.07 208.28 66.5 127.51 65.19 44.23 -4.83% -2.80% -1.09% 0.34% 10/1/2007 101.33 51.7 227.83 68.51 132.65 67.65 47.9 1.80% 0.19% -1.95% 0.32% 9/4/2007 102.79 52.12 198.87 66.52 130.88 64.89 42.81 3.22% -2.46% -2.14% 0.32% 8/1/2007 101.82 49.01 161.5 63.37 126 61.61 38.37 0.92% -0.15% -2.38% 0.42% 7/2/2007 96.21 47.67 172.81 62.81 124.4 62 37.98 -3.73% -2.68% -2.99% 0.40% 6/1/2007 91.52 48.55 198.52 64.25 128.42 63.46 37.71 -1.96% 0.76% -1.07% 0.40% 5/1/2007 92.69 49.85 211.41 64.32 130.33 63.66 36.37 3.24% -0.02% -0.14% 0.41% 4/2/2007 88.53 50.27 200.23 62.52 126.05 62.19 34.62 3.49% -2.10% -0.97% 0.44% 3/1/2007 81.64 47.17 188.95 59.26 120.71 59.95 33.38 0.68% -0.26% 0.39% 0.43% 2/1/2007 80.5 49.26 184.49 58.44 119.32 58.26 31.69 -1.96% 1.37% 0.24% 0.38% 1/3/2007 85.62 51.99 194.01 59.88 121.71 58.33 32.74 1.40% 0.09% -0.02% 0.44% 12/1/2006 83.89 51.38 181.99 59.32 119.91 57.53 32.71 0.87% -0.89% 2.52% 0.40% 11/1/2006 79.38 51.3 177.84 58.21 118.32 55.74 31.01 1.71% 0.78% 0.48% 0.42% 10/2/2006 79.47 52.16 173.26 56.99 116.02 54.08 29.26 3.23% 1.63% 0.48% 0.41% 9/1/2006 70.53 50.26 154.14 55.27 112.47 52.13 27.33 1.84% -1.24% -0.43% 0.41% 8/1/2006 69.69 50.04 135.45 54.67 109.52 52.01 27.57 2.03% 0.83% -1.76% 0.42% 7/3/2006 66.37 48.13 139.18 53.1 107.18 50.72 27.14 -0.78% -4.00% 3.27% 0.40% 6/1/2006 65.86 46.11 136.74 52.02 106.7 50.31 26.52 -0.35% -0.54% 1.59% 0.40% 5/1/2006 68.5 46.34 137.21 52.06 106.42 50.34 26.47 -3.57% -3.06% 2.81% 0.43% 4/3/2006 70.34 44.82 145.7 53.62 109.73 52.34 29.79 0.73% -1.24% 3.07% 0.36% 3/1/2006 70.45 45.29 142.38 51.77 108.36 49.95 27.96 1.46% 3.55% -0.10% 0.37% 2/1/2006 68.54 44.09 128.16 50.58 106.6 48.03 27.37 -0.30% -0.32% -0.82% 0.34% 1/3/2006 69.28 43.75 128.13 51.03 105.99 48.37 28.47 3.04% 5.32% 1.19% 0.35% 12/1/2005 70.04 45.7 115.63 49.48 103.51 45.73 24.93 -0.25% -0.52% 0.49% 0.32% 11/1/2005 75.75 46.95 116.76 48.87 103.7 43.47 23.49 3.61% 0.99% -1.79% 0.31% 10/3/2005 69.6 47.37 114.41 47.77 99.34 42.49 22.21 -2.02% -1.03% -0.69% 0.27% 9/1/2005 68.19 47.87 109.85 49.01 101.74 43.89 23.71 0.49% -0.68% 1.13% 0.29% 8/1/2005 68.53 47.95 100.45 48.09 100.93 42.32 21.82 -1.22% -0.89% 1.45% 0.30% 7/1/2005 70.78 48.13 97.11 47.75 101.89 40.76 21.54 3.92% 2.78% -0.48% 0.24% 6/1/2005 62.92 48.92 91.97 46.4 98.13 39.58 20 0.57% 2.59% 2.82% 0.23% 5/2/2005 64.07 50.5 87.89 46.48 97.99 39.02 19.24 3.65% 2.99% -1.24% 0.24% 4/1/2005 64.6 51.4 96.26 46.05 94.93 39.36 18.65 -2.61% -4.00% -0.46% 0.21% 3/1/2005 77.29 50.3 98.91 46.73 96.74 40 18.88 -1.97% -1.39% 1.75% 0.21% 2/1/2005 78.3 49.13 97.84 48.06 98.54 41.08 20.51 1.89% -0.66% 2.85% 0.16% 1/3/2005 78.86 48.24 96.99 46.44 96.52 39.58 18.7 -2.76% -1.63% 2.67% 0.16% 12/1/2004 83.22 47.29 93.34 47.44 98.74 40.35 18.8 3.43% 0.18% -0.36% 0.16% 11/1/2004 79.55 44.98 93.98 45.82 95.85 38.5 17.94 4.54% 4.16% 1.94% 0.15% 10/1/2004 75.62 43.33 88.26 43.84 91.77 36.29 16.32 1.43% 0.44% -0.83% 0.11% 9/1/2004 72.24 41.81 83.42 42.69 90.6 35.07 15.87 1.60% 2.93% 0.39% 0.11% 8/2/2004 71.35 43.12 80.21 42.68 89.7 34.35 14.83 0.08% -1.59% 1.13% 0.11% 7/1/2004 73.2 40.81 78.9 42.35 89.48 33.95 14.33 -4.06% -3.80% 4.52% 0.10% 6/1/2004 74.11 41.13 84 43.94 92.46 35.46 14.87 1.86% 2.32% 1.67% 0.08% 5/3/2004 74.48 41.14 83.78 43.22 90.78 34.31 14.73 1.17% -0.10% -0.33% 0.06% 4/1/2004 73.98 39.69 86.32 43.03 89.25 33.92 14.56 -1.83% -2.59% -1.67% 0.08% 3/1/2004 77.06 37.26 92.87 43.72 90.97 35.12 16.14 -1.32% 1.84% 0.07% 0.09% 2/2/2004 80.97 39.6 94.22 44.95 92.19 35.09 16.06 1.40% -1.17% 0.45% 0.06% 1/2/2004 83.12 39.07 88.59 43.86 90.96 34.3 15.45 Monthly Price and Return Data Double Click, Select Data Range Copy, Paste into new Excel workbook. Save Excel file as Macro-Enabled Workbook 4 Price Data Description • Source: finance.yahoo.com historical prices • Data Item: Monthly, Adjusted Prices – Adjusted prices include adjustments for stock splits and dividend distributions to provide a measure of total stock return including both capital gains and cash distributions. • Securities: – IBM: IBM Common Stock – GS: Goldman Sachs Common Stock ... - tailieumienphi.vn