Spreadsheet Assignment 2: Simulation URGENT

profilegarinh
assignment_2_template.xlsx
Home>Business & Finance homework help>Spreadsheet Assignment 2: Simulation URGENT

Q1d

Year Mkt-RF 30yrs
1927 0.2960 expected­return 10%
1928 0.3545 st­Dev 20%
1929 -0.1925
1930 -0.3117
1931 -0.4527
1932 -0.0973
1933 0.5672
1934 0.0322
1935 0.4476
1936 0.3206
1937 -0.3492
1938 0.2833
1939 0.0286
1940 -0.0712
1941 -0.1038
1942 0.1615
1943 0.2806
1944 0.2093
1945 0.3833
1946 -0.0671
1947 0.0296
1948 0.0107
1949 0.1911
1950 0.2883
1951 0.1921
1952 0.1180
1953 -0.0105
1954 0.4934
1955 0.2375
1956 0.0591
1957 -0.1316
1958 0.4346
1959 0.0976
1960 -0.0146
1961 0.2481
1962 -0.1290
1963 0.1784
1964 0.1254
1965 0.1052
1966 -0.1351
1967 0.2449
1968 0.0879
1969 -0.1754
1970 -0.0649
1971 0.1178
1972 0.1305
1973 -0.2624
1974 -0.3574
1975 0.3245
1976 0.2189
1977 -0.0827
1978 0.0103
1979 0.1308
1980 0.2212
1981 -0.1813
1982 0.1066
1983 0.1375
1984 -0.0606
1985 0.2491
1986 0.1012
1987 -0.0387
1988 0.1155
1989 0.2049
1990 -0.1395
1991 0.2917
1992 0.0623
1993 0.0821
1994 -0.0411
1995 0.3121
1996 0.1597
1997 0.2597
1998 0.1946
1999 0.2056
2000 -0.1760
2001 -0.1520
2002 -0.2276
2003 0.3075
2004 0.1072
2005 0.0309
2006 0.1060
2007 0.0104
2008 -0.3835
2009 0.2827
2010 0.1739
2011 0.0047
2012 0.1629
2013 0.3521

Q2

Inputs
Risk-free rate (rate) 4%
Volatility (sigma) 30%
Time (t) 0.25
Current Stock Price (S) $100
Strike Price (K) $110
@Risk Calculations
Random Normal
Future Stock Price
Future Call Value
Future Put Value
Expected Future Stock Price
Expected Future Call Value
Discounted Call Value
Expected Future Put Value
Discounted Put Value
Probability that future price is above strike
Analytical Black-Scholes Price
d1
d2
N(d1)
N(d2)
Call Price
Put Price