Problem
Develop an Excel worksheet model to perform a risk analysis.
To get started on the assessment task, follow the instructions below.
A. Identify the 10 best performing stocks on the Australian Stock Exchange (ASX) and get the past five years weekly stock price data using @stockhistory. Once you download the price data, copy, and paste only the values and format into another sheet (in the same workbook).
B. Determine the weekly compound returns for each stock.
C. For each stock, calculate the average return (both geometric and simple average), standard deviation, coefficient of variation, cumulative wealth index, and parametric value at risk.
D. Comment on the estimated statistics. Do you think the above statistics are sufficient for a rational investor?
E. Now, construct the variance and covariance matrix and the correlation matrix. Based on the estimated coefficients of two matrices, rank the investments in order of their appropriateness for an investment portfolio. Comment on your ranking.
F. Assume you are planning to equally invest your money in all 10 stocks. Estimate the portfolio standard deviation and return on your portfolio.
G. Use the Excel Solver and find the portfolio return and standard deviation of the minimum variance portfolio and the optimum portfolio (assuming annual risk-free rate of return is 4%).
H. If you want to forgo 2% of the portfolio return and invest some of your money in risk-free assets, what would be the composition of your investment portfolio? Estimate the return and standard deviation of the portfolio.