For the following questions, be prepared to show your work in an Excel worksheet. Provide clarifying comments when necessary. Part I: Empirical Market Returns The Excel workbook titled Market_Returns.xlsx contains historical monthly returns on the aggregate stock market (Mkt Return) and the monthly return on very short term US treasuries (Risk free return) going back to January 1927. Use this data answer the following questions:

1) Compute historical average arithmetic and geometric returns, and std deviations for the stock market and short-term treasuries. What does the distribution of monthly returns for the stock market look like? How does it compare to a normal distribution? You can use the FREQUENCY function in Excel to compute a histogram. You can use NORMDIST and NORMINV functions for the normal distribution.

2) What is the 5% Value at Risk for the stock market using historical returns? What is the Expected shortfall at 5%? What is the Value at Risk at 5% assuming a normal distribution?

Date	year	month	Market Return	Risk free Return
1927-01-31	1927	1	0.0019	0.0025
1927-02-28	1927	2	0.0444	0.0026
1927-03-31	1927	3	0.0043	0.003
1927-04-30	1927	4	0.0071	0.0025
1927-05-31	1927	5	0.0574	0.003
1927-06-30	1927	6	-0.0208	0.0026
1927-07-30	1927	7	0.0756	0.003
1927-08-31	1927	8	0.0225	0.0028
1927-09-30	1927	9	0.0497	0.0021
1927-10-31	1927	10	-0.0406	0.0025
1927-11-30	1927	11	0.0679	0.0021
1927-12-31	1927	12	0.0231	0.0022
1928-01-31	1928	1	-0.0043	0.0025
1928-02-29	1928	2	-0.0137	0.0033
1928-03-31	1928	3	0.091	0.0029
1928-04-30	1928	4	0.0445	0.0022
1928-05-31	1928	5	0.0184	0.0032
1928-06-30	1928	6	-0.0454	0.0031
1928-07-31	1928	7	0.0094	0.0032
1928-08-31	1928	8	0.07	0.0032
1928-09-29	1928	9	0.0315	0.0027
1928-10-31	1928	10	0.0174	0.0041
1928-11-30	1928	11	0.1219	0.0038
1928-12-31	1928	12	0.0042	0.0006
1929-01-31	1929	1	0.05	0.0034
1929-02-28	1929	2	0.0002	0.0036
1929-03-28	1929	3	-0.0055	0.0034
1929-04-30	1929	4	0.0179	0.0036
1929-05-31	1929	5	-0.0595	0.0044
1929-06-29	1929	6	0.1022	0.0052
1929-07-31	1929	7	0.0479	0.0033
1929-08-30	1929	8	0.0858	0.004
1929-09-30	1929	9	-0.0512	0.0035
1929-10-31	1929	10	-0.1966	0.0046
1929-11-27	1929	11	-0.1237	0.0037
1929-12-31	1929	12	0.017	0.0037
1930-01-31	1930	1	0.0575	0.0014
1930-02-28	1930	2	0.028	0.003
1930-03-31	1930	3	0.0745	0.0035
1930-04-30	1930	4	-0.0185	0.0021
1930-05-29	1930	5	-0.014	0.0026
1930-06-30	1930	6	-0.16	0.0027
1930-07-31	1930	7	0.0432	0.002
1930-08-29	1930	8	0.0039	0.0009
1930-09-30	1930	9	-0.1253	0.0022
1930-10-31	1930	10	-0.0869	0.0009
1930-11-29	1930	11	-0.0291	0.0013
1930-12-31	1930	12	-0.0769	0.0014
1931-01-31	1931	1	0.0639	0.0015
1931-02-28	1931	2	0.1092	0.0004
1931-03-31	1931	3	-0.063	0.0013
1931-04-30	1931	4	-0.099	0.0008
1931-05-29	1931	5	-0.1315	0.0009
1931-06-30	1931	6	0.1398	0.0008
1931-07-31	1931	7	-0.0656	0.0006
1931-08-31	1931	8	0.0044	0.0003
1931-09-30	1931	9	-0.291	0.0003
1931-10-31	1931	10	0.0814	0.001
1931-11-30	1931	11	-0.0891	0.0017
1931-12-31	1931	12	-0.1341	0.0012
1932-01-30	1932	1	-0.0135	0.0023
1932-02-29	1932	2	0.0569	0.0023
1932-03-31	1932	3	-0.1105	0.0016
1932-04-30	1932	4	-0.1785	0.0011
1932-05-31	1932	5	-0.2045	0.0006
1932-06-30	1932	6	-0.0068	0.0002
1932-07-30	1932	7	0.3387	0.0003
1932-08-31	1932	8	0.3709	0.0003
1932-09-30	1932	9	-0.0291	0.0003
1932-10-31	1932	10	-0.1315	0.0002
1932-11-30	1932	11	-0.0586	0.0002
1932-12-31	1932	12	0.0441	0.0001
1933-01-31	1933	1	0.0126	0.0001
1933-02-28	1933	2	-0.1527	-0.0003
1933-03-31	1933	3	0.0333	0.0004
1933-04-29	1933	4	0.3895	0.001
1933-05-31	1933	5	0.2147	0.0004
1933-06-30	1933	6	0.1313	0.0002
1933-07-31	1933	7	-0.0961	0.0002
1933-08-31	1933	8	0.1208	0.0003
1933-09-30	1933	9	-0.1063	0.0002
1933-10-31	1933	10	-0.0835	0.0001
1933-11-29	1933	11	0.0999	0.0002
1933-12-30	1933	12	0.0185	0.0002
1934-01-31	1934	1	0.1265	0.0005
1934-02-28	1934	2	-0.0248	0.0002
1934-03-31	1934	3	0.0011	0.0002
1934-04-30	1934	4	-0.0178	0.0001
1934-05-31	1934	5	-0.0724	0.0001
1934-06-30	1934	6	0.0265	0.0001
1934-07-31	1934	7	-0.1095	0.0001
1934-08-31	1934	8	0.0559	0.0001
1934-09-29	1934	9	-0.0022	0.0001
1934-10-31	1934	10	-0.0165	0.0001
1934-11-30	1934	11	0.0834	0.0001
1934-12-31	1934	12	0.0037	0.0001
1935-01-31	1935	1	-0.0344	0.0001
1935-02-28	1935	2	-0.0192	0.0002
1935-03-30	1935	3	-0.0367	0.0001
1935-04-30	1935	4	0.0907	0.0001
1935-05-31	1935	5	0.0348	0.0001
1935-06-29	1935	6	0.0594	0.0001
1935-07-31	1935	7	0.0752	0.0001
1935-08-31	1935	8	0.0266	0.0001
1935-09-30	1935	9	0.0264	0.0001
1935-10-31	1935	10	0.0704	0.0001
1935-11-30	1935	11	0.049	0.0002
1935-12-31	1935	12	0.0457	0.0001