Assignment -
1. From Data tab in attached please calculate the following statistics for each asset class below - use entire time period
Annual Return (average x 12)
Annual Standard Deviation (SQRT(time))
Create Correlation Table using Data Analysis (in Analyze tab along with Solver) of data using raw data - do not annualize data
2. Create Variance/Covariance Table
Use table format to right and in answer tab:
3. Calculate GMV portfolio:
Weights
Return
Standard Deviation
VaR
4. Calculate Tangency portfolio:
Weights
Return
Standard Deviation
VaR
5. Create Solver Model
Calculate return for Solver to use
Calculate standard deviation for Solver to use
Calculate VaR for Solver to use
6. Run Solver for each data point alone efficient frontier (e.g. every 1% increment return)
Calculate series (Port 1 - Port 10) efficient portfolios using return and standard deviations above using Solver
Add portfolio statistics to table at right with each efficient portfolio's allocations, expected return, standard deviation, VaR
Plot series of Solver risk/returns to create efficient frontier
Plot asset class allocations for each portfolio (GMV, Tangency, others)
7. Calculate an efficient portfolio that will deliver a VaR loss of $50,000 at a 95% confidence level. $1,000,000 investment. (See page 233 in text)
Plot the VaR at $50,000 on efficient frontier along with the others (return on y axis/Standard deviation on x axis)
Attachment:- Assignment File.rar