Download sampp 500 historical prices from 1950 to the

Question

Calibrate S&P 500 Returns to a Mixed Normal in Excel

Use both the random variable approach and the density method. For the random variable approach, also create a theoretical histogram.

• Download S&P 500 historical prices from 1950 to the present (using Bloomberg or Thomson Reuters if possible).
• Calibrate the two parameters σ₁and q via the method of moments.
• As an initial guess, let σ₂= 0.8%. The range of the other two variables will be σ₁∈ [1%,10%], q∈[1%,10%]. Using another choice of σ₂can change these ranges. Experiment with different values.
• Let σ₂= 0.8%, w₁= 1, w₂= 10000, if using the Excel Solver.
• Choose the discretized x-space carefully (start with -25% to 25% in increments of 0.05%).
• Graph both empirical and theoretical distributions as in Figure 3-44. Also, graph a log version of this graph to see how well the distribution fits the tail data.