Project

12 A = ab Wrap Text v A A 141 E Times New... 11 Aa Po IVEVEY iste I UV av AV SEE Merge & Cent Paste B IUvab X X A DAY Styles Dictate 6 X V 1. Data: This project makes use of annual data for two risky securities: the S&P 500 Index and CO LL TI I Gold. Annual values for each of these securities during the period from 1975-2018 are provided DATE S&P 500 Index Gold Prices in a spreadsheet posted on Blackboard. 1975 90.19 139.30 2000 1320.28 271.45 1976 107.46 133.80 2001 1148.08 275.45 You should use an annual risk-free rate of 4% for this project. 1977 95.10 160.45 2002 798.55 321.18 1978 96.11 207.83 2003 1111.92 417.25 2. Return Calculations: Calculate annual returns for each of the two securities from 1976 1979 107.94 455.08 2004 1211.92 435.60 through 2018. Calculate the average annual return, the standard deviation of annual returns, 1980 135.76 594.92 2005 1248.29 513.00 and the correlation between the returns of the two securities during this period and fill in the 1981 122.55 410.09 2006 1418.30 635.70 table provided. (Note: all of these calculations are based on annual security % returns NOT 1982 140.64 444.30 2007 1468.36 836.50 index values). Attach the spreadsheet showing all of the relevant calculations as Exhibit 1. 1983 164.93 389.36 2008 903.25 869.75 1984 167.24 320.14 2009 1115.10 1087.50 1985 211.28 320.81 2010 1257.64 1420.25 S&P 500 Gold 1986 242.17 391.23 2011 1257.60 1531.00 Average Annual Return 1987 247.08 486.31 2012 1426.19 1664.00 988 277.72 418.49 2013 1848.36 1204.50 Standard Deviation of Annual Returns 1989 353.40 409.39 2014 2058.90 1135.80 1990 330.22 378.16 2015 2043.94 1060.30 Return Correlation(S&P.Gold) 1991 417.09 361.06 2016 2238.83 1061.19 1992 435.71 334.80 2017 2673.61 1236.50 1993 466.45 383.35 2018 2506.85 1281.66 Some useful Excel functions: to compute average return, use the function AVERAGE; for standard 1994 459.27 379.29 deviation, use STDEV; for correlation, use CORREL. 1995 615.93 387.44 1996 740.74 369.00 1997 970.43 288.74 1998 1229.23 291.62 Sharpe Ratios: What are the Sharpe ratios for the S&P and Gold? 1999 1469.25 282.37 Equal-weighted Portfolios: Calculate the expected returns and standard deviations of the equal-weighted portfolio that invests 50% and 50% in the two risky securities (S&P and Gold). What is its Sharpe Ratio? The Tangency Portfolio : What are the portfolio weights in the Tangency Portfolio? What are the mean and standard deviation of the Tangency Portfolio? What is its Sharpe tatio? Data + Focus !!! Page 1 of 2 388 words X English (United States) Ready