Through the questions’ requirements, the choice of “S&P 1500” is decided because of diverse companies which can be seen as the good random samples contributing to accurate data and future analysis. Therefore, 20 companies’ stocks are selected from 8 fields. For example, Apple is from Information Technology Field, P&G is from Health Care Field and so on. By the instrument of Bloomberg, ten years’ historical data of stock prices, returns, market capitalization and other relevant factors have been picked up from November 30, 2002 to October 31, 2012. Here, the first step of choosing stocks and obtaining required data completes.
In order to make an optimal portfolio including risk-free and risky assets and correspond to “S&P 1500” which derives from America, one type of US T-bill(10 years) is a relatively preferable option，which is a general index in financial markets and acts stable among the ten years. Therefore, T-bill is the selected risk-free asset for this portfolio. It should be noticed that according to the financial markets’ regulation, all of the data above all is disposed by annual, which aims to keep the data of portfolio more accurate and be easier to analysis.
Through the well-prepared data, firstly, it should calculate the mean (the average return of each stock by month: R) according to the Excel function of Average, which is one part of formula about risky assets: Expected Return E(R) = W1R1 + W2R2 + .....+ WnRn
Wn =the weight of each asset
Rn = the expected return for asset n
Meantime, the standard deviation is calculated by the Excel function of Stdv. so as to prepare for the further operation. Following that, the mean of the risk-free rate is also calculated through the function of Average, which can be seen as the average return of risk –free asset. Here, they are all the basis data of constructing the optimal portfolio....