Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

AEM4060/6061 Risk Simulation and Optimization
Assignment 2
Due April 16, 2025

Directions:

(2) All tables and graphs etc. should be cut and paste- do not include a screenshot of the entire screen, as this makes it hard to see the graph. If the question asks for calculated values using Excel functions, pasted images of Excel worksheets are acceptable.

(4) When you are building the spreadsheet models use a low number of iterations. When you are ready to report results set iterations to at least 5000.

(5) ED discussion will be opened up in CANVAS. Also, we will have at least 1 ZOOM tutorial to answer any questions.

(6) No extensions will be permitted unless for emergencies. We will not respond to any queries after 5pm on April 19. For all extension requests, please email both myself (cgt6) and the TA (jt734) so we can keep track of all waivers for late submission.

This assignment has 3 main features. The worksheet has daily data from S&P500, NASDAQ and DOW from 01/01/2018 to 04/02/2025. The first part is to use this data to compute annualized returns and volatility. These are then used in the 2nd part to compute correlated time series in the construction of a portfolio, assuming that each index can be sold as an ETF (exchange traded fund that tracks the index and is tradeable as with any stock). Three portfolios are considered.

The third part follows from the 1st and 2nd by assuming that portfolio 2 can be purchased as a mutual fund. You will then build a retirement model that invests 10% of after tax income in this mutual fund for 40 years, after which you plan to retire. All three parts are connected so the second part will rely on the 1st part and the 3rd part will rely on the 2nd part.

1. The Stock Indices tab holds weekly data for S&P500, NASDAQ and DOW from 01/01/2018 to 04/02/2025. Suppose that you are a portfolio manager and you wish to create an ETF or mutual fund with a value indexed to a portfolio of the three main indices.

The following arithmetic processes should be used

MODEL 1: Build a 40-year annual simulation model for each of the three portfolios using the correlated distributions. Here you will use the correlated arithmetic Brownian motions in 1(c) which is more appropriate for portfolio analysis when correlations are explicitly added to the model . The value of this portfolio for each year is calculated as follows:

Sumproduct Excel function like =SUMPRODUCT($B$12:$D$12,B21:D21) where (in my case) $B$12:$D$12 are the fixed α portfolio weights, and the B21:D21 are the floating values of xt . Or you can simply add =  directly into each cell for each year (again making sure the α are fixed.

MODEL 2: An alternative approach (based on Ito’s Lemma) is to use a geometric Brownian motion using the portfolio means and standard deviations provided in d(i), d(ii) and d(iii). This model is of the form

For each portfolio complete the following Table for

vi. The future value of each portfolio using 

Use arithmetic  . (Hint: for xi to xiv create a returns column for Model 1 and Model 2. Add all to output. For each year use RiskMean() and RiskStdDev(). Take average of each using Excel Average())

xiii. Average annual Monte Carlo % return on each gBm portfolio. (Model 2). Use arithmetic 

Place answers in the Table below

2. You cannot decide upon which of the portfolio mutual funds to invest in and decide to build a Monte Carlo simulation to investigate risk and return tradeoffs. Use the gBm model 2 above for your simulations. 

You decide that you will use the 10% rule which sets aside 10% of your after-tax income each year for investment. Under the 10% rule if the price of the portfolio increases you buy less and if the price falls you buy more. Since the portfolio is sold as a mutual fund it can be purchased in fractions. Your starting income is $90,000 and you expect that the income will grow on average by 3.5%/year with a standard deviation of 1.25% (normally distributed). You start earning income at t=0, but your first investment takes place at t=1. The tax rate is 20% so net income willbe reduced by 20%. You will then invest 10% of the remainder in the portfolio each year. The  value of the portfolio in any year will be the accumulated number of shares times the market value of the portfolio. You plan to retire in 40 years.

3. A question was asked in class about the stability of volatility used in Monte Carlo analysis. I responded by stating that the longer a time series of returns the more volatility events are captured. In financial economics this is often referred to as the ‘stochastic volatility’ problem… when the volatility is in itself volatile. For example, The (CBOT) VIX index value quotes the expected annualized change in the S&P 500 index over the following 30 days, as computed from options-based theory and current options-market data. Example events that contribute to stochastic volatility include trade wars, COVID-19, the Ukraine-Russia war and other economically destabilizing events.

Return to the original Stock Indices sheet where you have already computed the daily change in index (ETF) prices. Using the EXCEL STDEV.S(Range) function compute rolling 30 day standard deviations. The first calculated standard deviation would include days 1-30, and then 2-31, 3-33 and so on. ( For example in my solution the percentage change for S&P500 is in column E (yours may be different). I then have E4:E33 as the 30-day range starting day 1, i.e. =STDEV.S(E4:E33)*SQRT(250). This can be copied down to the last day (4/2/25). Repeat for the other 2 indices. In other words, the k-days rolling average of volatility is calculated by calculating the (sample) standard deviation of the last 30 days of % change in index values, and multiplied by SQRT(250) to annualize. )

a. Plot all three indices on a single line graph.

b. Compute the average annualized 30-day volatility for each index.

c. Compute the standard deviation across all annualized 30-day rolling average volatilities (use STDEV.P because of large sample size).

d. Using your answers to a), b) and c) comment on what you observe about stochastic volatility, including a comparison of the mean volatility computed here and the annualized volatility in question 1(a).