Use Delta Airlines and General Electric - estimate the cost of equity using the
ID: 2716253 • Letter: U
Question
Use Delta Airlines and General Electric - estimate the cost of equity using the Capital Asset Pricing Model (CAPM). Here is the formula: RS = RF + Beta (RM-RF).
So, you need to collect or calculate RM and RF to and beta of your stocks to estimate Rs. How to get RM and RF to and beta? Go to Yahoo Finance or Google Finance and find Beta for your stocks. S&P 500 index is a good proxy for return on the market (RM).
To find RM, go to Yahoo!Finance to get the monthly close price of S&P 500 Index for the last 30 years (January 1, 1985 to December 31, 2014) , click on this link (http://finance.yahoo.com/q/hp?s=%5EGSPC+Historical+Prices (Links to an external site.) ), which will take you to historical price of S&P 500 index. Then click on historical price, change the start date and end date, select monthly and then click on get price. If you scroll down you will see download to spreadsheet.
Once you download the data, just leave date and adjusted close price.
Then calculate monthly return using adjusted close price of S&P 500 index and calculate average return using average function in Excel.
You can use the average return as RM. To find RF go to Federal Reserve Website ( http://www.federalreserve.gov/releases/h15/data.htm (Links to an external site.) ) and get Treasury constant maturities 7-year monthly rates for the last 30 years (January 1, 1985 to December 31, 2014).
Download the monthly rates and calculate the average rate using average function in excel. You can use the average rates on Treasury as RF.
Now you have RM, RF and Beta.
Put them in the following formula to get RS for your companies: RS = RF + Beta (RM-RF).
EXPLAIN ANSWER AND SHOW YOUR WORK
Explanation / Answer
Average monthly Return(RF) last 30 years) Average monthly Return(RM) last 30 years) 1985-01 11.27 Date Adj Close Return=12*ln(Adj Close this month/Adj Close previous month) 1985-02 11.44 02-01-1985 179.630005 1985-03 11.82 01-02-1985 181.179993 10.31% 1985-04 11.34 01-03-1985 180.660004 -3.45% 1985-05 10.72 01-04-1985 179.830002 -5.53% 1985-06 10.08 01-05-1985 189.550003 63.17% 1985-07 10.16 03-06-1985 191.850006 14.47% 1985-08 10.2 01-07-1985 190.919998 -5.83% 1985-09 10.24 01-08-1985 188.630005 -14.48% 1985-10 10.11 03-09-1985 182.080002 -42.41% 1985-11 9.62 01-10-1985 189.820007 49.96% 1985-12 9.11 01-11-1985 202.169998 75.64% 1989-03 9.43 01-02-1989 288.859985 -35.25% 1989-04 9.24 01-03-1989 294.869995 24.71% 1989-05 8.88 03-04-1989 309.640015 58.65% 1989-06 8.31 01-05-1989 320.519989 41.44% 1989-07 7.94 01-06-1989 317.980011 -9.55% 1989-08 8.11 03-07-1989 346.079987 101.62% 1989-09 8.23 01-08-1989 351.450012 18.48% 1989-10 8.03 01-09-1989 349.149994 -7.88% 1989-11 7.86 02-10-1989 340.359985 -30.60% 1989-12 7.85 01-11-1989 345.98999 19.69% 1990-01 8.2 01-12-1989 353.399994 25.43% 1990-02 8.48 02-01-1990 329.079987 -85.56% 1990-03 8.65 01-02-1990 331.890015 10.20% 1990-04 8.81 01-03-1990 339.940002 28.76% 1990-05 8.78 02-04-1990 330.799988 -32.71% 1990-06 8.52 01-05-1990 361.230011 105.60% 1990-07 8.46 01-06-1990 358.019989 -10.71% 1990-08 8.64 02-07-1990 356.149994 -6.28% 1990-09 8.79 01-08-1990 322.559998 -118.88% 1990-10 8.59 04-09-1990 306.049988 -63.05% 1990-11 8.28 01-10-1990 304 -8.06% 1990-12 8 01-11-1990 322.220001 69.85% 1991-01 7.97 03-12-1990 330.220001 29.43% 1991-02 7.73 02-01-1991 343.929993 48.81% 1991-03 8 01-02-1991 367.070007 78.14% 1991-04 7.92 01-03-1991 375.220001 26.35% 1991-05 7.94 01-04-1991 375.339996 0.38% 1991-06 8.17 01-05-1991 389.829987 45.45% 1991-07 8.15 03-06-1991 371.160004 -58.89% 1991-08 7.74 01-07-1991 387.809998 52.66% 1991-09 7.48 01-08-1991 395.429993 23.35% 1991-10 7.25 03-09-1991 387.859985 -23.20% 1991-11 7.06 01-10-1991 392.450012 14.12% 1991-12 6.69 01-11-1991 375.220001 -53.88% 1992-01 6.7 02-12-1991 417.089996 126.95% 1992-02 6.96 02-01-1992 408.779999 -24.15% 1992-03 7.26 03-02-1992 412.700012 11.45% 1992-04 7.15 02-03-1992 403.690002 -26.49% 1992-05 7.06 01-04-1992 414.950012 33.01% 1992-06 6.9 01-05-1992 415.350006 1.16% 1992-07 6.36 01-06-1992 408.140015 -21.01% 1992-08 6.12 01-07-1992 424.209991 46.34% 1992-09 5.96 03-08-1992 414.029999 -29.15% 1992-10 6.15 01-09-1992 417.799988 10.88% 1992-11 6.49 01-10-1992 418.679993 2.52% 1992-12 6.46 02-11-1992 431.350006 35.78% 1993-01 6.26 01-12-1992 435.709991 12.07% 1993-02 5.87 04-01-1993 438.779999 8.43% 1993-03 5.66 01-02-1993 443.380005 12.51% 1993-04 5.59 01-03-1993 451.670013 22.23% 1993-05 5.66 01-04-1993 440.190002 -30.89% 1993-06 5.61 03-05-1993 450.190002 26.96% 1993-07 5.48 01-06-1993 450.529999 0.91% 1993-08 5.35 01-07-1993 448.130005 -6.41% 1993-09 5.08 02-08-1993 463.559998 40.62% 1993-10 5.05 01-09-1993 458.929993 -12.05% 1993-11 5.45 01-10-1993 467.829987 23.05% 1993-12 5.48 01-11-1993 461.790009 -15.59% 1994-01 5.43 01-12-1993 466.450012 12.05% 1994-02 5.72 03-01-1994 481.609985 38.38% 1994-03 6.28 01-02-1994 467.140015 -36.61% 1994-04 6.8 01-03-1994 445.769989 -56.19% 1994-05 7.01 04-04-1994 450.910004 13.76% 1994-06 6.91 02-05-1994 456.5 14.79% 1994-07 7.12 01-06-1994 444.269989 -32.59% 1994-08 7.06 01-07-1994 458.26001 37.21% 1994-09 7.28 01-08-1994 475.48999 44.29% 1994-10 7.58 01-09-1994 462.709991 -32.69% 1994-11 7.83 03-10-1994 472.350006 24.74% 1994-12 7.8 01-11-1994 453.690002 -48.37% 1995-01 7.79 01-12-1994 459.269989 14.67% 1995-02 7.44 03-01-1995 470.420013 28.79% 1995-03 7.14 01-02-1995 487.390015 42.53% 1995-04 6.95 01-03-1995 500.709991 32.35% 1995-05 6.5 03-04-1995 514.710022 33.09% 1995-06 6.05 01-05-1995 533.400024 42.80% 1995-07 6.2 01-06-1995 544.75 25.27% 1995-08 6.41 03-07-1995 562.059998 37.54% 1995-09 6.13 01-08-1995 561.880005 -0.38% 1995-10 5.97 01-09-1995 584.409973 47.18% 1995-11 5.83 02-10-1995 581.5 -5.99% 1995-12 5.63 01-11-1995 605.369995 48.27% 1996-01 5.54 01-12-1995 615.929993 20.75% 1996-02 5.64 02-01-1996 636.02002 38.52% 1996-03 6.19 01-02-1996 640.429993 8.29% 1996-04 6.48 01-03-1996 645.5 9.46% 1996-05 6.66 01-04-1996 654.169983 16.01% 1996-06 6.83 01-05-1996 669.119995 27.12% 1996-07 6.76 03-06-1996 670.630005 2.71% 1996-08 6.52 01-07-1996 639.950012 -56.19% 1996-09 6.73 01-08-1996 651.98999 22.37% 1996-10 6.42 03-09-1996 687.330017 63.34% 1996-11 6.1 01-10-1996 705.27002 30.92% 1996-12 6.2 01-11-1996 757.02002 84.97% 1997-01 6.47 02-12-1996 740.73999 -26.09% 1997-02 6.32 02-01-1997 786.159973 71.41% 1997-03 6.65 03-02-1997 790.820007 7.09% 1997-04 6.86 03-03-1997 757.119995 -52.26% 1997-05 6.66 01-04-1997 801.340027 68.12% 1997-06 6.46 01-05-1997 848.280029 68.31% 1997-07 6.2 02-06-1997 885.140015 51.04% 1997-08 6.29 01-07-1997 954.309998 90.29% 1997-09 6.2 01-08-1997 899.469971 -71.02% 1997-10 6.05 02-09-1997 947.280029 62.15% 1997-11 5.9 01-10-1997 914.619995 -42.10% 1997-12 5.83 03-11-1997 955.400024 52.35% 1998-01 5.53 01-12-1997 970.429993 18.73% 1998-02 5.6 02-01-1998 980.280029 12.12% 1998-03 5.71 02-02-1998 1049.339966 81.69% 1998-04 5.7 02-03-1998 1101.75 58.49% 1998-05 5.72 01-04-1998 1111.75 10.84% 1998-06 5.56 01-05-1998 1090.819946 -22.81% 1998-07 5.52 01-06-1998 1133.839966 46.42% 1998-08 5.36 01-07-1998 1120.670044 -14.02% 1998-09 4.76 03-08-1998 957.280029 -189.10% 1998-10 4.46 01-09-1998 1017.01001 72.63% 1998-11 4.78 01-10-1998 1098.670044 92.68% 1998-12 4.65 02-11-1998 1163.630005 68.93% 1999-01 4.8 01-12-1998 1229.22998 65.81% 1999-02 5.1 04-01-1999 1279.640015 48.23% 1999-03 5.36 01-02-1999 1238.329956 -39.38% 1999-04 5.28 01-03-1999 1286.369995 45.67% 1999-05 5.64 01-04-1999 1335.180054 44.69% 1999-06 6.05 03-05-1999 1301.839966 -30.35% 1999-07 5.94 01-06-1999 1372.709961 63.61% 1999-08 6.15 01-07-1999 1328.719971 -39.08% 1999-09 6.12 02-08-1999 1320.410034 -7.53% 1999-10 6.33 01-09-1999 1282.709961 -34.76% 1999-11 6.17 01-10-1999 1362.930054 72.79% 1999-12 6.38 01-11-1999 1388.910034 22.66% 2000-01 6.7 01-12-1999 1469.25 67.48% 2000-02 6.72 03-01-2000 1394.459961 -62.69% 2000-03 6.51 01-02-2000 1366.420044 -24.38% 2000-04 6.27 01-03-2000 1498.579956 110.79% 2000-05 6.69 03-04-2000 1452.430054 -37.54% 2000-06 6.33 01-05-2000 1420.599976 -26.59% 2000-07 6.22 01-06-2000 1454.599976 28.38% 2000-08 6.05 03-07-2000 1430.829956 -19.77% 2000-09 5.98 01-08-2000 1517.680054 70.71% 2000-10 5.84 01-09-2000 1436.51001 -65.96% 2000-11 5.78 02-10-2000 1429.400024 -5.95% 2000-12 5.28 01-11-2000 1314.949951 -100.15% 2001-01 5.13 01-12-2000 1320.280029 4.85% 2001-02 5.1 02-01-2001 1366.01001 40.86% 2001-03 4.88 01-02-2001 1239.939941 -116.20% 2001-04 5.03 01-03-2001 1160.329956 -79.63% 2001-05 5.24 02-04-2001 1249.459961 88.81% 2001-06 5.14 01-05-2001 1255.819946 6.09% 2001-07 5.06 01-06-2001 1224.380005 -30.42% 2001-08 4.84 02-07-2001 1211.22998 -12.96% 2001-09 4.51 01-08-2001 1133.579956 -79.51% 2001-10 4.31 04-09-2001 1040.939941 -102.31% 2001-11 4.42 01-10-2001 1059.780029 21.52% 2001-12 4.86 01-11-2001 1139.449951 86.98% 2002-01 4.79 03-12-2001 1148.079956 9.05% 2002-02 4.71 02-01-2002 1130.199951 -18.84% 2002-03 5.14 01-02-2002 1106.72998 -25.18% 2002-04 5.02 01-03-2002 1147.390015 43.30% 2002-05 4.9 01-04-2002 1076.920044 -76.06% 2002-06 4.6 01-05-2002 1067.140015 -10.95% 2002-07 4.3 03-06-2002 989.820007 -90.26% 2002-08 3.88 01-07-2002 911.619995 -98.76% 2002-09 3.5 01-08-2002 916.070007 5.84% 2002-10 3.54 03-09-2002 815.280029 -139.87% 2002-11 3.64 01-10-2002 885.76001 99.50% 2002-12 3.63 01-11-2002 936.309998 66.60% 2003-01 3.6 02-12-2002 879.820007 -74.68% 2003-02 3.45 02-01-2003 855.700012 -33.36% 2003-03 3.34 03-02-2003 841.150024 -20.58% 2003-04 3.47 03-03-2003 848.179993 9.99% 2003-05 3.07 01-04-2003 916.919983 93.51% 2003-06 2.84 01-05-2003 963.590027 59.57% 2003-07 3.45 02-06-2003 974.5 13.51% 2003-08 3.96 01-07-2003 990.309998 19.31% 2003-09 3.74 01-08-2003 1008.01001 21.26% 2003-10 3.75 02-09-2003 995.969971 -14.42% 2003-11 3.81 01-10-2003 1050.709961 64.21% 2003-12 3.79 03-11-2003 1058.199951 8.52% 2004-01 3.65 01-12-2003 1111.920044 59.42% 2004-02 3.59 02-01-2004 1131.130005 20.55% 2004-03 3.31 02-02-2004 1144.939941 14.56% 2004-04 3.89 01-03-2004 1126.209961 -19.79% 2004-05 4.31 01-04-2004 1107.300049 -20.32% 2004-06 4.35 03-05-2004 1120.680054 14.41% 2004-07 4.11 01-06-2004 1140.839966 21.39% 2004-08 3.9 01-07-2004 1101.719971 -41.87% 2004-09 3.75 02-08-2004 1104.23999 2.74% 2004-10 3.75 01-09-2004 1114.579956 11.18% 2004-11 3.88 01-10-2004 1130.199951 16.70% 2004-12 3.93 01-11-2004 1173.819946 45.44% 2005-01 3.97 01-12-2004 1211.920044 38.33% 2005-02 3.97 03-01-2005 1181.27002 -30.74% 2005-03 4.33 01-02-2005 1203.599976 22.47% 2005-04 4.16 01-03-2005 1180.589966 -23.16% 2005-05 3.94 01-04-2005 1156.849976 -24.38% 2005-06 3.86 02-05-2005 1191.5 35.41% 2005-07 4.06 01-06-2005 1191.329956 -0.17% 2005-08 4.18 01-07-2005 1234.180054 42.40% 2005-09 4.08 01-08-2005 1220.329956 -13.54% 2005-10 4.38 01-09-2005 1228.810059 8.31% 2005-11 4.48 03-10-2005 1207.01001 -21.48% 2005-12 4.41 01-11-2005 1249.47998 41.50% 2006-01 4.37 01-12-2005 1248.290039 -1.14% 2006-02 4.56 03-01-2006 1280.079956 30.18% 2006-03 4.71 01-02-2006 1280.660034 0.54% 2006-04 4.94 01-03-2006 1294.869995 13.24% 2006-05 5.03 03-04-2006 1310.609985 14.50% 2006-06 5.08 01-05-2006 1270.089966 -37.69% 2006-07 5.05 01-06-2006 1270.199951 0.10% 2006-08 4.83 03-07-2006 1276.660034 6.09% 2006-09 4.68 01-08-2006 1303.819946 25.26% 2006-10 4.69 01-09-2006 1335.849976 29.12% 2006-11 4.58 02-10-2006 1377.939941 37.23% 2006-12 4.54 01-11-2006 1400.630005 19.60% 2007-01 4.75 01-12-2006 1418.300049 15.04% 2007-02 4.71 03-01-2007 1438.23999 16.75% 2007-03 4.5 01-02-2007 1406.819946 -26.51% 2007-04 4.62 01-03-2007 1420.859985 11.92% 2007-05 4.69 02-04-2007 1482.369995 50.86% 2007-06 5.05 01-05-2007 1530.619995 38.44% 2007-07 4.93 01-06-2007 1503.349976 -21.57% 2007-08 4.53 02-07-2007 1455.27002 -39.01% 2007-09 4.33 01-08-2007 1473.98999 15.34% 2007-10 4.33 04-09-2007 1526.75 42.20% 2007-11 3.87 01-10-2007 1549.380005 17.66% 2007-12 3.74 01-11-2007 1481.140015 -54.05% 2008-01 3.31 03-12-2007 1468.359985 -10.40% 2008-02 3.21 02-01-2008 1378.550049 -75.74% 2008-03 2.93 01-02-2008 1330.630005 -42.46% 2008-04 3.19 03-03-2008 1322.699951 -7.17% 2008-05 3.46 01-04-2008 1385.589966 55.74% 2008-06 3.73 01-05-2008 1400.380005 12.74% 2008-07 3.6 02-06-2008 1280 -107.86% 2008-08 3.46 01-07-2008 1267.380005 -11.89% 2008-09 3.25 01-08-2008 1282.829956 14.54% 2008-10 3.19 02-09-2008 1166.359985 -114.22% 2008-11 2.82 01-10-2008 968.75 -222.76% 2008-12 1.89 03-11-2008 896.23999 -93.36% 2009-01 1.98 01-12-2008 903.25 9.35% 2009-02 2.3 02-01-2009 825.880005 -107.46% 2009-03 2.42 02-02-2009 735.090027 -139.75% 2009-04 2.47 02-03-2009 797.869995 98.34% 2009-05 2.81 01-04-2009 872.809998 107.73% 2009-06 3.37 01-05-2009 919.140015 62.06% 2009-07 3.14 01-06-2009 919.320007 0.23% 2009-08 3.21 01-07-2009 987.47998 85.83% 2009-09 3.02 03-08-2009 1020.619995 39.61% 2009-10 2.96 01-09-2009 1057.079956 42.12% 2009-11 2.92 01-10-2009 1036.189941 -23.95% 2009-12 3.07 02-11-2009 1095.630005 66.93% 2010-01 3.21 01-12-2009 1115.099976 21.14% 2010-02 3.12 04-01-2010 1073.869995 -45.21% 2010-03 3.16 01-02-2010 1104.48999 33.74% 2010-04 3.28 01-03-2010 1169.430054 68.56% 2010-05 2.86 01-04-2010 1186.689941 17.58% 2010-06 2.66 03-05-2010 1089.410034 -102.64% 2010-07 2.43 01-06-2010 1030.709961 -66.47% 2010-08 2.1 01-07-2010 1101.599976 79.82% 2010-09 2.05 02-08-2010 1049.329956 -58.33% 2010-10 1.85 01-09-2010 1141.199951 100.71% 2010-11 2.02 01-10-2010 1183.26001 43.43% 2010-12 2.66 01-11-2010 1180.550049 -2.75% 2011-01 2.72 01-12-2010 1257.640015 75.91% 2011-02 2.96 03-01-2011 1286.119995 26.87% 2011-03 2.8 01-02-2011 1327.219971 37.75% 2011-04 2.84 01-03-2011 1325.829956 -1.26% 2011-05 2.51 01-04-2011 1363.609985 33.72% 2011-06 2.29 02-05-2011 1345.199951 -16.31% 2011-07 2.28 01-06-2011 1320.640015 -22.11% 2011-08 1.63 01-07-2011 1292.280029 -26.05% 2011-09 1.42 01-08-2011 1218.890015 -70.16% 2011-10 1.62 01-09-2011 1131.420044 -89.36% 2011-11 1.45 03-10-2011 1253.300049 122.77% 2011-12 1.43 01-11-2011 1246.959961 -6.09% 2012-01 1.38 01-12-2011 1257.599976 10.20% 2012-02 1.37 03-01-2012 1312.410034 51.19% 2012-03 1.56 01-02-2012 1365.680054 47.74% 2012-04 1.43 01-03-2012 1408.469971 37.02% 2012-05 1.21 02-04-2012 1397.910034 -9.03% 2012-06 1.08 01-05-2012 1310.329956 -77.64% 2012-07 0.98 01-06-2012 1362.160034 46.55% 2012-08 1.14 02-07-2012 1379.319946 15.02% 2012-09 1.12 01-08-2012 1406.579956 23.48% 2012-10 1.15 04-09-2012 1440.670044 28.74% 2012-11 1.08 01-10-2012 1412.160034 -23.99% 2012-12 1.13 01-11-2012 1416.180054 3.41% 2013-01 1.3 03-12-2012 1426.189941 8.45% 2013-02 1.35 02-01-2013 1498.109985 59.04% 2013-03 1.32 01-02-2013 1514.680054 13.20% 2013-04 1.15 01-03-2013 1569.189941 42.43% 2013-05 1.31 01-04-2013 1597.569946 21.51% 2013-06 1.71 01-05-2013 1630.73999 24.66% 2013-07 1.99 03-06-2013 1606.280029 -18.14% 2013-08 2.15 01-07-2013 1685.72998 57.93% 2013-09 2.22 01-08-2013 1632.969971 -38.16% 2013-10 1.99 03-09-2013 1681.550049 35.18% 2013-11 2.07 01-10-2013 1756.540039 52.36% 2013-12 2.29 01-11-2013 1805.810059 33.20% 2014-01 2.29 02-12-2013 1848.359985 27.95% 2014-02 2.15 02-01-2014 1782.589966 -43.48% 2014-03 2.23 03-02-2014 1859.449951 50.66% 2014-04 2.27 03-03-2014 1872.339966 8.29% 2014-05 2.12 01-04-2014 1883.949951 7.42% 2014-06 2.19 01-05-2014 1923.569946 24.97% 2014-07 2.17 02-06-2014 1960.22998 22.65% 2014-08 2.08 01-07-2014 1930.670044 -18.23% 2014-09 2.22 01-08-2014 2003.369995 44.36% 2014-10 1.98 02-09-2014 1972.290039 -18.76% 2014-11 2.03 01-10-2014 2018.050049 27.52% 2014-12 1.98 03-11-2014 2067.560059 29.08% 2015-01 1.67 01-12-2014 2058.899902 -5.04% 2015-02 1.79 Average monthly Return(in annual terms) 8.15% 2015-03 1.84 2015-04 1.69 2015-05 1.93 GE Delta 2015-06 2.1 Beta 1.2714 1.32174 2015-07 2.04 RF(%) 5.27% 5.27% 2015-08 1.91 RM(%) 8.15% 8.15% 2015-09 1.88 RS = RF + Beta (RM-RF). 8.94% 9.08% 2015-10 1.76 Average monthly Return(in annual terms) 5.27 % (=AVERAGE(above returns)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.