The objective of this project is to learn how the Nobel Prize winning, Optimal Portfolio Theory (by Harry Markowitz), works in practice. Three stocks are used for this project. The efficient frontier for the three-stock portfolio is plotted on Figure 1.
The three-stock portfolio possibilities space is derived by assigning different weights for each stock using a random number generator. The random number generator generated random numbers from 0 to 1. To ensure the sum of the three weights equal 1 and all three weights are positive numbers between 0 to 1, the following procedure is followed:
- Generate 3 random numbers.
- Assign the ith random number divided by the sum of the three random numbers as the weight for stock i.
The procedure above ensure each weight will be fairly distributed.
Once the portfolio possibilities space is plotted (in purple) , the optimal portfolio could be found by graphically determining the tangency portfolio consistent with the riskless interest rate. The riskless rate (the U.S. T-Bill rate can be used as a proxy) in this case was assumed to be 4%. The expected return and the standard deviation corresponding to the tangency were 12.5% and 20%, respectively.
To let the computer select the optimal portfolio, the Sharpe Ratio is used. In this case, the portfolio corresponding to the largest Sharpe Ratio is the optimal portfolio. Four thousands (4,000) combinations are generated. The largest Sharpe Ratio is found to be 41.315%. The weights corresponding to this ratio are 17.30%, 42.81% and 39.88% for stocks 1, 2 and 3, respectively. The portfolio's expected return and standard deviation were 12.27% and 20.017%, respectively. Note that, for a 3 stock portfolio, 500 combinations would be enough to provide a very good estimate. To set the number of combinations, place the number on cells "E4" of the sheet "Input Sheet". To use this program, the user needs to create the following sheets: "Input Sheet" & "Output Sheet". The user may not need to set up the format as shown in Figures 2 and 3. For those sheets, however, it is very important that the inputs, stock variances, covariances, expected returns, risk free rate, and the number of iterations, be place in the same cell references as in Figure 2.
Copyright © XL Modeling.
