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