Stage 4: Developing Combined-Asset Portfolios

You are going to develop two multi-asset portfolios from the stocks you chose. Place the information for these steps in the Portfolios worksheet.

Step 1) The first will be a portfolio with equal initial investments in the four stocks. Calculate the periodic return for this portfolio as the average of the 4 individual stock returns for each and every month. Next, construct a cumulative return for your equally weighted portfolio. To compute the cumulative return, set the December 2003 value to 1.0000. Then compute the January 2004 cumulative return as the December 2003 value times 1 plus your periodic return for January. Drag this equation down the page.

Step 2) The second asset will be a market-cap weighted portfolio. You will need to once again visit the finance.yahoo.com site and find the market capital Mkt Cap for each stock. To compute market cap, go to the initial company page to obtain the current price, and go to Key Statistics (on the left) to obtain current shares outstanding. To determine your initial allocation you will need to sum the 4 capitalizations and then divide each of your stocks mkt cap by the sum of the 4 mkt caps. Thus you will have one set of weights based on current price and shares outstanding, and you will use these weights for the entire period. Next, to determine your returns compute a weighted average return for each month based on the weightings you just computed. Display the information on price, shares outstanding, and market cap for each stock on the top of the Portfolios sheet. This will give you 4 market value weightings based on relative market values in December of 2016. Use these market value weightings to compute your portfolio return (and corresponding cumulative return).

Step 3) Graphing: Make a graph at the bottom of the page that displays the cumulative returns for your two portfolios and for the S&P 500. As with all graphs for this assignment, include a title, legend, and appropriate axis labels.

Stage 5: Calculating Returns and Correlations

Place the information described in this stage in the worksheet titled Analysis.

Step 1) Calculate the arithmetic average return and standard deviation for all three stocks, the S&P 500, your two combined asset portfolios, and the T-bill. Use the average function for the arithmetic average and the stdev function for standard deviation. Also compute the geometric average as

=((ending cumul ret/beginning cumul ret)^(1/# of returns)) 1

Ending and beginning here refer to the start and end of your sequence of values over the entire period (or December 2003 and December 2016).

Step 2) Build a table of correlations similar to the table below for the correlations among ALL of the INDIVIDUAL stock returns and the S&P 500 returns (the following table is an example for several FIN 3134/5024: MacKinlay Excel Project 2 5

stocks; however, the data is old so dont expect to get these answers). IGNORE YOUR 2 COMBINED-ASSET PORTFOLIO FOR THIS STEP.

The formula for calculating the correlations is the Correl function. To use this function, open the function box and then choose the correlation function. When the dialog box opens, select the data in pairs with the asset appearing in the left column being the ys and the assets appearing across the top being your xs. Alternatively, just type =correl( and follow Excels prompts. You will need to do this for each asset pair; however, you do not need to complete the top half since it is a mirror image of the bottom half. Use the available data for each pair of stocks.

Ian, Grant, Ibrahim Stock Name Price per share Shares Outstanding Mkt Cap Mkt Cap weights Stock 1 IBM Stock 2 AMZN Stock 3 FNLC Stock 4 PXD IBM Date 12/1/2003 1/2/2004 2/2/2004 3/1/2004 4/1/2004 5/3/2004 6/1/2004 7/1/2004 8/2/2004 9/1/2004 10/1/2004 11/1/2004 12/1/2004 1/3/2005 2/1/2005 3/1/2005 4/1/2005 5/2/2005 6/1/2005 7/1/2005 8/1/2005 9/1/2005 10/3/2005 11/1/2005 12/1/2005 1/3/2006 2/1/2006 3/1/2006 4/3/2006 5/1/2006 6/1/2006 7/3/2006 8/1/2006 9/1/2006 10/2/2006 11/1/2006 12/1/2006 1/3/2007 2/1/2007 3/1/2007 4/2/2007 5/1/2007 6/1/2007 7/2/2007 8/1/2007 9/4/2007 10/1/2007 11/1/2007 12/3/2007 1/2/2008 AMZN FNLC PXD S&P 500 Adj. Close Adj. Close Adj. Close Adj. Close Adj. Close $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 71.85 76.93 74.94 71.32 68.47 68.93 68.59 67.75 66.04 66.86 69.99 73.63 77.02 72.99 72.47 71.53 59.79 59.30 58.24 65.50 63.43 63.11 64.42 70.11 64.83 64.12 63.44 65.20 65.10 63.40 60.95 61.42 64.50 65.28 73.55 73.46 77.64 79.24 74.50 75.56 81.93 85.79 84.70 89.05 94.24 95.13 93.78 85.24 87.61 86.81 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 52.62 50.40 43.01 43.28 43.60 48.50 54.40 38.92 38.14 40.86 34.13 39.68 44.29 43.22 35.18 34.27 32.36 35.51 33.09 45.15 42.70 45.30 39.86 48.46 47.15 44.82 37.44 36.53 35.21 34.61 38.68 26.89 30.83 32.12 38.09 40.34 39.46 37.67 39.14 39.79 61.33 69.14 68.41 78.54 79.91 93.15 89.15 90.56 92.64 77.70 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 9.34 9.13 9.04 9.04 9.86 10.43 11.09 9.90 10.58 11.01 10.39 10.08 10.04 10.00 10.14 9.85 9.90 9.89 9.93 10.59 11.35 11.32 10.96 10.87 10.41 10.50 10.47 10.50 10.01 10.01 10.13 10.78 10.64 10.28 10.25 10.25 10.25 10.23 10.26 9.85 10.09 9.96 10.63 10.11 9.64 9.53 9.75 9.49 9.36 9.57 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 30.75 30.74 30.93 31.21 31.60 29.90 33.89 34.83 32.32 33.41 31.40 34.11 34.01 37.20 40.86 41.50 39.49 38.98 40.87 42.09 47.98 53.46 48.72 49.58 49.91 51.69 40.97 43.20 41.80 39.84 45.31 44.27 40.72 38.32 39.90 42.66 38.88 40.16 37.70 42.36 49.33 50.73 47.87 44.71 40.34 44.34 50.29 43.94 48.14 41.30 1,111.92 1,131.13 1,144.94 1,126.21 1,107.30 1,120.68 1,140.84 1,101.72 1,104.24 1,114.58 1,130.20 1,173.82 1,211.92 1,181.27 1,203.60 1,180.59 1,156.85 1,191.50 1,191.33 1,234.18 1,220.33 1,228.81 1,207.01 1,249.48 1,248.29 1,280.08 1,280.66 1,294.87 1,310.61 1,270.09 1,270.20 1,276.66 1,303.82 1,335.85 1,377.94 1,400.63 1,418.30 1,438.24 1,406.82 1,420.86 1,482.37 1,530.62 1,503.35 1,455.27 1,473.99 1,526.75 1,549.38 1,481.14 1,468.36 1,378.55 IBM Periodic Return 0.00% 7.07% -2.59% -4.83% -4.00% 0.68% -0.50% -1.23% -2.53% 1.24% 4.68% 5.21% 4.61% -5.23% -0.71% -1.30% -16.41% -0.82% -1.79% 12.48% -3.17% -0.50% 2.07% 8.83% -7.54% -1.09% -1.06% 2.78% -0.16% -2.61% -3.85% 0.77% 5.02% 1.20% 12.68% -0.12% 5.69% 2.06% -5.98% 1.42% 8.43% 4.70% -1.27% 5.13% 5.83% 0.95% -1.43% -9.10% 2.78% -0.92% 2/1/2008 3/3/2008 4/1/2008 5/1/2008 6/2/2008 7/1/2008 8/1/2008 9/2/2008 10/1/2008 11/3/2008 12/1/2008 1/2/2009 2/2/2009 3/2/2009 4/1/2009 5/1/2009 6/1/2009 7/1/2009 8/3/2009 9/1/2009 10/1/2009 11/2/2009 12/1/2009 1/4/2010 2/1/2010 3/1/2010 4/1/2010 5/3/2010 6/1/2010 7/1/2010 8/2/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 1/3/2011 2/1/2011 3/1/2011 4/1/2011 5/2/2011 6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/3/2011 11/1/2011 12/1/2011 1/3/2012 2/1/2012 3/1/2012 4/2/2012 5/1/2012 6/1/2012 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 92.63 93.67 98.20 105.73 96.82 104.54 99.83 95.91 76.24 67.29 69.40 75.58 76.31 80.33 85.57 88.58 87.03 98.29 98.85 100.16 100.99 106.27 110.10 102.94 107.43 108.35 108.99 106.37 104.86 109.04 105.08 114.48 122.55 121.26 125.80 138.87 139.31 140.34 146.80 146.03 148.30 157.20 149.25 151.82 160.30 163.88 160.29 167.89 172.16 182.59 181.21 169.51 171.87 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 64.47 71.30 78.63 81.62 73.33 76.34 80.81 72.76 57.24 42.70 51.28 58.82 64.79 73.44 80.52 77.99 83.66 85.76 81.19 93.36 118.81 135.91 134.52 125.41 118.40 135.77 137.10 125.46 109.26 117.89 124.83 157.06 165.23 175.40 180.00 169.64 173.29 180.13 195.81 196.69 204.49 222.52 215.23 216.23 213.51 192.29 173.10 194.44 179.69 202.51 231.90 212.91 228.35 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 9.38 9.80 11.05 11.71 8.94 12.18 12.07 13.02 12.07 12.76 13.48 10.79 7.89 10.75 11.96 11.60 13.34 13.55 12.90 12.88 10.92 10.52 10.79 9.97 10.04 11.43 11.39 10.56 9.42 10.19 9.61 10.07 10.25 10.33 11.66 11.04 10.92 11.40 11.24 10.94 11.25 11.30 10.26 9.66 11.21 11.31 11.98 12.60 11.83 11.70 11.59 11.35 13.58 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 44.15 48.56 57.07 70.97 77.39 58.77 62.45 51.83 27.59 19.91 16.04 14.51 14.46 16.36 22.97 27.94 25.33 28.36 28.77 36.10 40.89 41.13 47.91 43.74 46.40 56.06 63.83 63.41 59.18 57.65 57.55 64.77 69.52 79.79 86.47 94.78 101.93 101.55 101.86 91.49 89.25 92.66 77.89 65.57 83.65 94.25 89.21 99.00 109.31 111.29 115.51 96.44 87.97 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 1,330.63 1,322.70 1,385.59 1,400.38 1,280.00 1,267.38 1,282.83 1,166.36 968.75 896.24 903.25 825.88 735.09 797.87 872.81 919.14 919.32 987.48 1,020.62 1,057.08 1,036.19 1,095.63 1,115.10 1,073.87 1,104.49 1,169.43 1,186.69 1,089.41 1,030.71 1,101.60 1,049.33 1,141.20 1,183.26 1,180.55 1,257.64 1,286.12 1,327.22 1,325.83 1,363.61 1,345.20 1,320.64 1,292.28 1,218.89 1,131.42 1,253.30 1,246.96 1,257.60 1,312.41 1,365.68 1,408.47 1,397.91 1,310.33 1,362.16 6.71% 1.12% 4.83% 7.67% -8.42% 7.97% -4.51% -3.92% -20.51% -11.74% 3.14% 8.90% 0.96% 5.28% 6.52% 3.51% -1.75% 12.94% 0.57% 1.32% 0.84% 5.23% 3.60% -6.50% 4.36% 0.86% 0.58% -2.40% -1.42% 3.98% -3.63% 8.94% 7.05% -1.05% 3.75% 10.38% 0.32% 0.74% 4.61% -0.52% 1.55% 6.00% -5.05% 1.72% 5.58% 2.23% -2.19% 4.74% 2.54% 6.06% -0.75% -6.46% 1.39% 7/2/2012 8/1/2012 9/4/2012 10/1/2012 11/1/2012 12/3/2012 1/2/2013 2/1/2013 3/1/2013 4/1/2013 5/1/2013 6/3/2013 7/1/2013 8/1/2013 9/3/2013 10/1/2013 11/1/2013 12/2/2013 1/2/2014 2/3/2014 3/3/2014 4/1/2014 5/1/2014 6/2/2014 7/1/2014 8/1/2014 9/2/2014 10/1/2014 11/3/2014 12/1/2014 1/2/2015 2/2/2015 3/2/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/3/2015 9/1/2015 10/1/2015 11/2/2015 12/1/2015 1/4/2016 2/1/2016 3/1/2016 4/1/2016 5/2/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/3/2016 11/1/2016 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 172.22 171.96 183.08 171.68 168.47 169.79 180.00 178.76 189.86 180.28 186.03 170.91 174.42 163.81 166.43 161.06 162.35 169.48 159.64 168.23 174.88 178.50 168.47 165.65 175.15 176.77 174.50 151.12 150.09 148.49 141.89 150.93 149.59 159.64 159.31 152.75 152.12 140.03 137.27 132.64 133.25 131.53 119.27 126.51 146.22 140.90 149.87 147.96 156.57 156.22 156.19 151.11 160.95 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 233.30 248.27 254.32 232.89 252.05 250.87 265.50 264.27 266.49 253.81 269.20 277.69 301.22 280.98 312.64 364.03 393.62 398.79 358.69 362.10 336.37 304.13 312.55 324.78 312.99 339.04 322.44 305.46 338.64 310.35 354.53 380.16 372.10 421.78 429.23 434.09 536.15 512.89 511.89 625.90 664.80 675.89 587.00 552.52 593.64 659.59 722.79 715.62 758.81 769.16 837.31 789.82 750.57 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 13.58 13.28 14.18 13.47 12.22 13.46 13.68 14.32 14.88 14.28 13.32 14.61 15.12 13.75 14.07 14.88 15.34 14.89 14.69 14.16 14.10 13.94 14.14 15.28 14.53 14.95 14.76 15.87 15.67 16.41 15.04 15.06 15.83 15.32 15.67 17.85 17.66 17.23 17.74 19.86 20.85 19.24 18.16 17.92 18.54 19.08 19.36 20.70 21.42 21.87 23.28 23.21 28.46 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 88.39 97.10 104.16 105.41 106.76 106.35 117.27 125.52 124.01 121.99 138.41 144.47 154.46 174.63 188.47 204.42 177.44 183.75 169.03 200.83 186.86 192.98 209.84 229.46 221.12 208.33 196.71 188.81 143.04 148.66 150.33 152.32 163.34 172.60 147.67 138.54 126.64 122.93 121.55 137.04 144.64 125.29 123.86 120.44 140.68 166.03 160.25 151.14 162.50 178.97 185.61 178.98 191.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 1,379.32 1,406.58 1,440.67 1,412.16 1,416.18 1,426.19 1,498.11 1,514.68 1,569.19 1,597.57 1,630.74 1,606.28 1,685.73 1,632.97 1,681.55 1,756.54 1,805.81 1,848.36 1,782.59 1,859.45 1,872.34 1,883.95 1,923.57 1,960.23 1,930.67 2,003.37 1,972.29 2,018.05 2,067.56 2,058.90 1,994.99 2,104.50 2,067.89 2,085.51 2,107.39 2,063.11 2,103.84 1,972.18 1,920.03 2,079.36 2,080.41 2,043.94 1,940.24 1,932.23 2,059.74 2,065.30 2,096.95 2,098.86 2,173.60 2,170.95 2,168.27 2,126.15 2,198.81 0.20% -0.15% 6.47% -6.23% -1.87% 0.78% 6.01% -0.69% 6.21% -5.04% 3.19% -8.13% 2.06% -6.08% 1.60% -3.22% 0.80% 4.39% -5.81% 5.38% 3.95% 2.07% -5.62% -1.68% 5.74% 0.92% -1.28% -13.40% -0.68% -1.07% -4.44% 6.37% -0.89% 6.72% -0.21% -4.12% -0.41% -7.95% -1.97% -3.37% 0.46% -1.29% -9.32% 6.07% 15.58% -3.64% 6.36% -1.27% 5.82% -0.23% -0.02% -3.25% 6.51% 12/1/2016 $ 164.69 $ 749.87 $ 32.46 $ 180.03 $ 2,238.83 2.32% Cumulative Returns of Selected Stoc 18.0000 16.0000 14.0000 12.0000 10.0000 Cumulative Return 8.0000 6.0000 4.0000 2.0000 0.0000 12/1/2003 9/1/2004 6/1/2005 3/1/2006 12/1/2006 9/1/2007 6/1/2008 3/1/2009 12/1/2009 9 Date AMZN Periodic Return 0.00% -4.22% -14.66% 0.63% 0.74% 11.24% 12.16% -28.46% -2.00% 7.13% -16.47% 16.26% 11.62% -2.42% -18.60% -2.59% -5.57% 9.73% -6.81% 36.45% -5.43% 6.09% -12.01% 21.58% -2.70% -4.94% -16.47% -2.43% -3.61% -1.70% 11.76% -30.48% 14.65% 4.18% 18.59% 5.91% -2.18% -4.54% 3.90% 1.66% 54.13% 12.73% -1.06% 14.81% 1.74% 16.57% -4.29% 1.58% 2.30% -16.13% FNLC Periodic Return 0.00% -2.28% -0.97% 0.00% 9.04% 5.83% 6.31% -10.77% 6.94% 4.05% -5.66% -2.99% -0.34% -0.44% 1.45% -2.86% 0.43% -0.06% 0.35% 6.68% 7.17% -0.21% -3.22% -0.81% -4.20% 0.86% -0.28% 0.29% -4.67% -0.06% 1.26% 6.41% -1.30% -3.42% -0.23% -0.06% 0.00% -0.18% 0.24% -3.92% 2.44% -1.30% 6.72% -4.91% -4.69% -1.11% 2.37% -2.69% -1.41% 2.25% PXD S&P 500 Periodic Return 0.00% -0.03% 0.63% 0.88% 1.27% -5.38% 13.34% 2.77% -7.21% 3.39% -6.03% 8.64% -0.28% 9.37% 9.85% 1.55% -4.82% -1.30% 4.86% 2.97% 14.01% 11.42% -8.87% 1.76% 0.67% 3.57% -20.73% 5.43% -3.23% -4.69% 13.72% -2.28% -8.03% -5.89% 4.12% 6.92% -8.86% 3.30% -6.12% 12.36% 16.45% 2.83% -5.64% -6.59% -9.78% 9.92% 13.43% -12.64% 9.58% -14.21% Periodic Return 0.00% 1.73% 1.22% -1.64% -1.68% 1.21% 1.80% -3.43% 0.23% 0.94% 1.40% 3.86% 3.25% -2.53% 1.89% -1.91% -2.01% 3.00% -0.01% 3.60% -1.12% 0.69% -1.77% 3.52% -0.10% 2.55% 0.05% 1.11% 1.22% -3.09% 0.01% 0.51% 2.13% 2.46% 3.15% 1.65% 1.26% 1.41% -2.18% 1.00% 4.33% 3.25% -1.78% -3.20% 1.29% 3.58% 1.48% -4.40% -0.86% -6.12% IBM AMZN FNLC Cumulative Cumulative Cumulative Return Return Return 1.0000 1.0707 1.0429 0.9925 0.9529 0.9594 0.9546 0.9429 0.9191 0.9305 0.9740 1.0247 1.0719 1.0158 1.0086 0.9955 0.8321 0.8252 0.8105 0.9116 0.8827 0.8783 0.8965 0.9757 0.9022 0.8923 0.8829 0.9074 0.9060 0.8823 0.8483 0.8548 0.8977 0.9084 1.0236 1.0224 1.0806 1.1028 1.0369 1.0516 1.1403 1.1939 1.1788 1.2392 1.3115 1.3240 1.3051 1.1863 1.2193 1.2081 1.0000 0.9578 0.8174 0.8225 0.8286 0.9217 1.0338 0.7396 0.7248 0.7765 0.6486 0.7541 0.8417 0.8214 0.6686 0.6513 0.6150 0.6748 0.6288 0.8580 0.8115 0.8609 0.7575 0.9209 0.8960 0.8518 0.7115 0.6942 0.6691 0.6577 0.7351 0.5110 0.5859 0.6104 0.7239 0.7666 0.7499 0.7159 0.7438 0.7562 1.1655 1.3139 1.3001 1.4926 1.5186 1.7702 1.6942 1.7210 1.7605 1.4766 1.0000 0.9772 0.9677 0.9677 1.0552 1.1167 1.1871 1.0592 1.1327 1.1786 1.1119 1.0786 1.0749 1.0701 1.0856 1.0546 1.0592 1.0585 1.0623 1.1332 1.2144 1.2119 1.1729 1.1634 1.1146 1.1241 1.1210 1.1241 1.0717 1.0711 1.0846 1.1541 1.1391 1.1001 1.0975 1.0969 1.0969 1.0949 1.0976 1.0545 1.0803 1.0663 1.1379 1.0820 1.0313 1.0198 1.0439 1.0158 1.0015 1.0240 -17.03% 10.59% 10.28% 3.80% -10.16% 4.10% 5.86% -9.96% -21.33% -25.40% 20.09% 14.70% 10.15% 13.35% 9.64% -3.14% 7.27% 2.51% -5.33% 14.99% 27.26% 14.39% -1.02% -6.77% -5.59% 14.67% 0.98% -8.49% -12.91% 7.90% 5.89% 25.82% 5.20% 6.16% 2.62% -5.76% 2.15% 3.95% 8.70% 0.45% 3.97% 8.82% -3.28% 0.46% -1.26% -9.94% -9.98% 12.33% -7.59% 12.70% 14.51% -8.19% 7.25% -1.96% 4.48% 12.71% 5.99% -23.66% 36.22% -0.87% 7.87% -7.32% 5.78% 5.60% -19.91% -26.87% 36.14% 11.29% -3.04% 15.07% 1.53% -4.75% -0.21% -15.19% -3.72% 2.59% -7.60% 0.71% 13.86% -0.31% -7.36% -10.80% 8.23% -5.71% 4.77% 1.79% 0.79% 12.87% -5.30% -1.08% 4.38% -1.37% -2.69% 2.84% 0.44% -9.16% -5.90% 16.10% 0.90% 5.85% 5.25% -6.13% -1.13% -0.89% -2.14% 19.72% 6.90% 9.98% 17.53% 24.35% 9.04% -24.05% 6.26% -17.01% -46.77% -27.85% -19.42% -9.52% -0.34% 13.13% 40.38% 21.63% -9.32% 11.96% 1.44% 25.46% 13.28% 0.58% 16.49% -8.70% 6.07% 20.82% 13.87% -0.67% -6.67% -2.57% -0.17% 12.54% 7.34% 14.77% 8.38% 9.61% 7.55% -0.37% 0.30% -10.18% -2.45% 3.82% -15.94% -15.82% 27.57% 12.68% -5.35% 10.97% 10.41% 1.82% 3.79% -16.51% -8.78% -3.48% -0.60% 4.75% 1.07% -8.60% -0.99% 1.22% -9.08% -16.94% -7.48% 0.78% -8.57% -10.99% 8.54% 9.39% 5.31% 0.02% 7.41% 3.36% 3.57% -1.98% 5.74% 1.78% -3.70% 2.85% 5.88% 1.48% -8.20% -5.39% 6.88% -4.74% 8.76% 3.69% -0.23% 6.53% 2.26% 3.20% -0.10% 2.85% -1.35% -1.83% -2.15% -5.68% -7.18% 10.77% -0.51% 0.85% 4.36% 4.06% 3.13% -0.75% -6.27% 3.96% 1.2891 1.3036 1.3666 1.4714 1.3475 1.4549 1.3893 1.3348 1.0610 0.9365 0.9659 1.0518 1.0619 1.1180 1.1909 1.2328 1.2112 1.3679 1.3757 1.3939 1.4055 1.4790 1.5323 1.4327 1.4952 1.5080 1.5168 1.4804 1.4593 1.5175 1.4624 1.5932 1.7055 1.6876 1.7508 1.9326 1.9388 1.9531 2.0430 2.0323 2.0638 2.1877 2.0772 2.1129 2.2309 2.2807 2.2307 2.3365 2.3959 2.5411 2.5220 2.3591 2.3919 1.2252 1.3550 1.4943 1.5511 1.3936 1.4508 1.5357 1.3827 1.0878 0.8115 0.9745 1.1178 1.2313 1.3957 1.5302 1.4821 1.5899 1.6298 1.5429 1.7742 2.2579 2.5829 2.5564 2.3833 2.2501 2.5802 2.6055 2.3843 2.0764 2.2404 2.3723 2.9848 3.1401 3.3333 3.4208 3.2239 3.2932 3.4232 3.7212 3.7379 3.8862 4.2288 4.0903 4.1093 4.0576 3.6543 3.2896 3.6952 3.4149 3.8485 4.4071 4.0462 4.3396 1.0039 1.0489 1.1822 1.2530 0.9566 1.3031 1.2917 1.3934 1.2913 1.3659 1.4425 1.1553 0.8449 1.1502 1.2800 1.2411 1.4282 1.4501 1.3811 1.3782 1.1689 1.1255 1.1547 1.0669 1.0745 1.2234 1.2196 1.1298 1.0077 1.0907 1.0284 1.0775 1.0967 1.1054 1.2476 1.1815 1.1687 1.2199 1.2033 1.1709 1.2041 1.2094 1.0986 1.0337 1.2001 1.2109 1.2818 1.3490 1.2663 1.2520 1.2408 1.2143 1.4537 2.17% 6.42% 2.44% -8.43% 8.23% -0.47% 5.83% -0.46% 0.84% -4.76% 6.06% 3.15% 8.47% -6.72% 11.27% 16.44% 8.13% 1.31% -10.06% 0.95% -7.11% -9.58% 2.77% 3.91% -3.63% 8.32% -4.90% -5.27% 10.86% -8.35% 14.24% 7.23% -2.12% 13.35% 1.77% 1.13% 23.51% -4.34% -0.19% 22.27% 6.22% 1.67% -13.15% -5.87% 7.44% 11.11% 9.58% -0.99% 6.04% 1.36% 8.86% -5.67% -4.97% -0.03% -2.20% 6.82% -5.06% -9.28% 10.17% 1.67% 4.65% 3.92% -4.05% -6.73% 9.66% 3.55% -9.05% 2.27% 5.79% 3.10% -2.95% -1.34% -3.65% -0.43% -1.09% 1.44% 8.04% -4.89% 2.86% -1.24% 7.47% -1.24% 4.76% -8.35% 0.12% 5.12% -3.25% 2.28% 13.95% -1.09% -2.42% 2.96% 11.92% 5.02% -7.75% -5.59% -1.31% 3.45% 2.88% 1.51% 6.90% 3.47% 2.09% 6.49% -0.29% 22.60% 0.48% 9.85% 7.27% 1.20% 1.28% -0.38% 10.27% 7.04% -1.21% -1.63% 13.46% 4.38% 6.92% 13.06% 7.93% 8.46% -13.20% 3.56% -8.01% 18.82% -6.96% 3.28% 8.74% 9.35% -3.63% -5.78% -5.58% -4.02% -24.24% 3.92% 1.13% 1.32% 7.23% 5.67% -14.44% -6.18% -8.59% -2.93% -1.12% 12.74% 5.55% -13.38% -1.14% -2.76% 16.80% 18.02% -3.48% -5.68% 7.51% 10.14% 3.71% -3.57% 6.71% 1.26% 1.98% 2.42% -1.98% 0.28% 0.71% 5.04% 1.11% 3.60% 1.81% 2.08% -1.50% 4.95% -3.13% 2.97% 4.46% 2.80% 2.36% -3.56% 4.31% 0.69% 0.62% 2.10% 1.91% -1.51% 3.77% -1.55% 2.32% 2.45% -0.42% -3.10% 5.49% -1.74% 0.85% 1.05% -2.10% 1.97% -6.26% -2.64% 8.30% 0.05% -1.75% -5.07% -0.41% 6.60% 0.27% 1.53% 0.09% 3.56% -0.12% -0.12% -1.94% 3.42% 2.3968 2.3931 2.5479 2.3892 2.3446 2.3629 2.5050 2.4878 2.6423 2.5090 2.5889 2.3785 2.4274 2.2798 2.3162 2.2415 2.2595 2.3587 2.2218 2.3413 2.4338 2.4842 2.3446 2.3053 2.4376 2.4600 2.4285 2.1031 2.0888 2.0665 1.9747 2.1005 2.0818 2.2217 2.2171 2.1258 2.1170 1.9488 1.9103 1.8459 1.8544 1.8305 1.6598 1.7606 2.0350 1.9610 2.0857 2.0591 2.1790 2.1741 2.1736 2.1030 2.2399 4.4337 4.7182 4.8331 4.4259 4.7900 4.7676 5.0456 5.0222 5.0644 4.8235 5.1159 5.2773 5.7244 5.3398 5.9415 6.9181 7.4804 7.5787 6.8166 6.8814 6.3924 5.7797 5.9398 6.1722 5.9481 6.4432 6.1277 5.8050 6.4356 5.8979 6.7376 7.2246 7.0715 8.0156 8.1572 8.2495 10.1891 9.7471 9.7281 11.8947 12.6340 12.8447 11.1555 10.5002 11.2816 12.5350 13.7360 13.5998 14.4206 14.6173 15.9124 15.0099 14.2640 1.4533 1.4213 1.5182 1.4414 1.3076 1.4405 1.4646 1.5327 1.5928 1.5283 1.4255 1.5632 1.6186 1.4721 1.5056 1.5928 1.6422 1.5937 1.5725 1.5151 1.5086 1.4922 1.5137 1.6355 1.5555 1.6000 1.5801 1.6981 1.6770 1.7567 1.6101 1.6120 1.6946 1.6395 1.6769 1.9108 1.8899 1.8442 1.8989 2.1252 2.2318 2.0588 1.9438 1.9183 1.9844 2.0416 2.0724 2.2154 2.2923 2.3401 2.4919 2.4846 3.0462 -0.09% 14.06% -5.74% 1.82% 2.2920 14.2507 3.4744 ative Returns of Selected Stocks and the S&P 500 Over Time IBM AMZN FNLC PXD S&P 500 1/2007 6/1/2008 3/1/2009 12/1/2009 9/1/2010 6/1/2011 3/1/2012 12/1/2012 9/1/2013 6/1/2014 3/1/2015 12/1/2015 9/1/2016 Date PXD S&P 500 Cumulative Cumulative Return Return 1.0000 0.9997 1.0060 1.0148 1.0277 0.9724 1.1022 1.1326 1.0509 1.0865 1.0210 1.1092 1.1061 1.2097 1.3289 1.3495 1.2844 1.2676 1.3292 1.3687 1.5605 1.7387 1.5845 1.6124 1.6231 1.6811 1.3325 1.4048 1.3594 1.2956 1.4734 1.4397 1.3242 1.2462 1.2975 1.3874 1.2644 1.3061 1.2262 1.3777 1.6043 1.6496 1.5566 1.4541 1.3118 1.4419 1.6356 1.4288 1.5657 1.3432 1.0000 1.0173 1.0297 1.0129 0.9958 1.0079 1.0260 0.9908 0.9931 1.0024 1.0164 1.0557 1.0899 1.0624 1.0825 1.0618 1.0404 1.0716 1.0714 1.1100 1.0975 1.1051 1.0855 1.1237 1.1226 1.1512 1.1518 1.1645 1.1787 1.1422 1.1423 1.1482 1.1726 1.2014 1.2392 1.2596 1.2755 1.2935 1.2652 1.2778 1.3332 1.3766 1.3520 1.3088 1.3256 1.3731 1.3934 1.3321 1.3206 1.2398 1.4359 1.5792 1.8560 2.3080 2.5167 1.9113 2.0309 1.6854 0.8972 0.6474 0.5216 0.4720 0.4704 0.5321 0.7470 0.9085 0.8239 0.9224 0.9357 1.1738 1.3297 1.3375 1.5581 1.4226 1.5089 1.8231 2.0759 2.0620 1.9244 1.8749 1.8717 2.1064 2.2609 2.5948 2.8122 3.0823 3.3149 3.3026 3.3126 2.9753 2.9024 3.0132 2.5330 2.1324 2.7202 3.0651 2.9011 3.2195 3.5547 3.6193 3.7565 3.1363 2.8610 1.1967 1.1896 1.2461 1.2594 1.1512 1.1398 1.1537 1.0490 0.8712 0.8060 0.8123 0.7428 0.6611 0.7176 0.7850 0.8266 0.8268 0.8881 0.9179 0.9507 0.9319 0.9853 1.0029 0.9658 0.9933 1.0517 1.0672 0.9798 0.9270 0.9907 0.9437 1.0263 1.0642 1.0617 1.1311 1.1567 1.1936 1.1924 1.2264 1.2098 1.1877 1.1622 1.0962 1.0175 1.1271 1.1214 1.1310 1.1803 1.2282 1.2667 1.2572 1.1784 1.2251 2.8746 3.1578 3.3874 3.4280 3.4718 3.4585 3.8138 4.0821 4.0328 3.9672 4.5011 4.6982 5.0231 5.6790 6.1292 6.6480 5.7705 5.9757 5.4968 6.5311 6.0767 6.2757 6.8242 7.4622 7.1911 6.7751 6.3972 6.1403 4.6518 4.8343 4.8889 4.9535 5.3118 5.6129 4.8024 4.5055 4.1182 3.9977 3.9529 4.4566 4.7039 4.0744 4.0279 3.9168 4.5749 5.3992 5.2113 4.9152 5.2845 5.8202 6.0361 5.8206 6.2114 1.2405 1.2650 1.2957 1.2700 1.2736 1.2826 1.3473 1.3622 1.4112 1.4368 1.4666 1.4446 1.5161 1.4686 1.5123 1.5797 1.6240 1.6623 1.6032 1.6723 1.6839 1.6943 1.7300 1.7629 1.7363 1.8017 1.7738 1.8149 1.8595 1.8517 1.7942 1.8927 1.8597 1.8756 1.8953 1.8554 1.8921 1.7737 1.7268 1.8701 1.8710 1.8382 1.7449 1.7377 1.8524 1.8574 1.8859 1.8876 1.9548 1.9524 1.9500 1.9121 1.9775 5.8547 /2015 12/1/2015 9/1/2016 2.0135 IBM AMZN FNLC PXD S&P 500 Stock 1 Arithmetic Average Periodic Return Geometric Average Return Standard Deviation of Periodic Returns Arithmetic Averge Excess Return Standard Deviation of Excess Returns Reward to Volatility Ratio using historical data Using the Excel \"Slope\" function Beta Using the Excel regression analysis Alpha Beta R-squared Using the Excel trendline on the scatter-plot Alpha Beta Stock Analysis Stock2 ck Analysis Stock3 Stock4 S&P EW Port MCW Port T-Bill Note: Grayed-out cells do not need to be completed. d-out cells do not ompleted. Place your three rolling window graphs on this worksheet. These should include: 1) Plot of rolling estimates of the average excess return for your two stocks 2) Plot of rolling estimates of the standard deviation for excess returns for your two stocks 3) A plot of rolling estimates of the correlation of each stock's excess return with the S&P 500 excess return Appropriately title and label all graphs. o stocks he S&P 500 excess return Data - Two Risky Assets Data - 1 Risky and 1 Risk-free Return Stock 1 Return Stock 2 StDev Stock 1 StDev Stock 2 Correlation 1 to 2 Return 70/30 Portfolio Return T-bill StDev 70/30 Portfolio Weights Portfolio Stock 1 Stock 2 Return Std Dev 100.00% 0.00% 95.00% 5.00% 90.00% 10.00% 85.00% 15.00% 80.00% 20.00% 75.00% 25.00% 70.00% 30.00% 65.00% 35.00% 60.00% 40.00% 55.00% 45.00% 50.00% 50.00% 45.00% 55.00% 40.00% 60.00% 35.00% 65.00% 30.00% 70.00% 25.00% 75.00% 20.00% 80.00% 15.00% 85.00% 10.00% 90.00% 5.00% 95.00% 0.00% 100.00% Weights Portfolio Risky Risk-free Return Std Dev 100.00% 0.00% 95.00% 5.00% 90.00% 10.00% 85.00% 15.00% 80.00% 20.00% 75.00% 25.00% 70.00% 30.00% 65.00% 35.00% 60.00% 40.00% 55.00% 45.00% 50.00% 50.00% 45.00% 55.00% 40.00% 60.00% 35.00% 65.00% 30.00% 70.00% 25.00% 75.00% 20.00% 80.00% 15.00% 85.00% 10.00% 90.00% 5.00% 95.00% 0.00% 100.00% date Risk-free return Jan-04 0.07% Feb-04 0.06% Mar-04 0.09% Apr-04 0.08% May-04 0.06% Jun-04 0.08% Jul-04 0.10% Aug-04 0.11% Sep-04 0.11% Oct-04 0.11% Nov-04 0.15% Dec-04 0.16% Jan-05 0.16% Feb-05 0.16% Mar-05 0.21% Apr-05 0.21% May-05 0.24% Jun-05 0.23% Jul-05 0.24% Aug-05 0.30% Sep-05 0.29% Oct-05 0.27% Nov-05 0.31% Dec-05 0.32% Jan-06 0.35% Feb-06 0.34% Mar-06 0.37% Apr-06 0.36% May-06 0.43% Jun-06 0.40% Jul-06 0.40% Aug-06 0.42% Sep-06 0.41% Oct-06 0.41% Nov-06 0.42% Dec-06 0.40% Jan-07 0.44% Feb-07 0.38% Mar-07 0.43% Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 Nov-07 Dec-07 Jan-08 Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08 Aug-08 Sep-08 Oct-08 Nov-08 Dec-08 Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 Nov-09 Dec-09 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10 0.44% 0.41% 0.40% 0.40% 0.42% 0.32% 0.32% 0.34% 0.27% 0.21% 0.13% 0.17% 0.18% 0.18% 0.17% 0.15% 0.13% 0.15% 0.08% 0.03% 0.00% 0.00% 0.01% 0.02% 0.01% 0.00% 0.01% 0.01% 0.01% 0.01% 0.00% 0.00% 0.01% 0.00% 0.00% 0.01% 0.01% 0.01% 0.01% 0.01% Aug-10 Sep-10 Oct-10 Nov-10 Dec-10 Jan-11 Feb-11 Mar-11 Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Mar-13 Apr-13 May-13 Jun-13 Jul-13 Aug-13 Sep-13 Oct-13 Nov-13 0.01% 0.01% 0.01% 0.01% 0.01% 0.01% 0.01% 0.01% 0.00% 0.00% 0.00% 0.00% 0.01% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.01% 0.00% 0.00% 0.01% 0.01% 0.01% 0.01% 0.01% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Dec-13 Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15 Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.01% 0.00% 0.02% 0.02% 0.01% 0.01% 0.02% 0.02% 0.02% 0.02% 0.02% 0.01% 0.02% Place results from your beta regressions for each stock on this worksheet Place your scatter plots on this worksheet