Financial Modelling 2018-19 Project

Portfolio Optimization

Group members:


In our project, we aim to perform a portfolio optimization analysis based on a portfolio of thirty UK stocks and solve seven tasks. In the provided dataset, we have 10 years (Dec. 2007 – Dec. 2017) of monthly price and market capitalization data for each of the stocks as well as prices for the FTSE100 market index and proxies for the risk-free rates over various sub-periods of the sample.

In this report, we offer the clear detailed working process and explanation for efficient frontier generation, portfolio optimization, and out-of-sample risk-adjusted performance analysis. We mainly incorporate Excel Solver and several Excel functions to achieve our results. These tools are extremely effective and next we will present our solution on the seven tasks

Task 1

Our Workings and findings

(1)As required, firstly we calculate return, then we use Covariance.S function to generate sample variance-covariance matrix for the 30 stocks as shown in table 1.1 (a snapshot of 30*30 matrix).

Table 1.1

(2)Then we set up initial weight for each stock as 1/30, and we calculate portfolio statistics using both portfolio return series and Variance-covariance matrix as shown in table 1.2.

Table 1.2

(3)Use Solver to search for the minimum variance portfolio. The calculated weights and portfolio statistics are shown in table 1.3. As we can see, the minimum variance is 0.00075.

Set up the target standard deviation and use Solver to maximize and minimize return for portfolios, then plot return against variance and get the efficient frontier graph as shown in graph 1.1.

Graph 1.1

(5) Calculate 30 stocks return and variance and then plot them in the same efficient frontier graph as shown in graph 1.2

Graph 1.2

As we can see, the mean-variance frontier generates a much better risk-adjusted return and this shows that firm specific risks of these 30 stocks tend to offset each other and expected returns are not sacrificed. Based on what we have learnt, the unsystematic risks fade away when we add more and more risky asset into our portfolio: the benefits of diversification.

Task 2

Our workings and findings

(1) Repeat steps in task 1 to calculate portfolio statistics using both portfolio return series and Variance-covariance matrix.

(2) Use Solver to search for the maximum Sharpe ratio portfolio and the weights for each stock are shown in table 2.2 But we do not believe that the composition of the market portfolio that we have found is a desirable or practical one as an investment. The reasons are we need to short 15 stocks, and the short-selling will be very costly since we need to put the money in margin account and we may have margin call. Further, we need to monitor and manage 30 stocks, and this will be very time-consuming and hard.

Set up the different weight for risk-free asset and calculate weight for risky asset, and portfolio return and variance. Then we plot investor portfolio return against variance and standard deviation, and add them on the efficient frontier graph as shown in graph 2.1 and 2.2. As we can see, based on two fund separation theorem, the introduction of risk-free asset changes the optimal portfolio holdings for investors from red line to blue line (capital market line).

Task 3

Our workings and findings

(1) We repeat steps from task 1 to task 2, but restricting short selling. The composition of market portfolio is shown in table 3.1. As we can see this portfolio only has long position so we do not need to short any stocks. Further, we only need to invest in 8 stocks, so our management and transaction costs will be lower than previous portfolio, which has 30 positions to invest and manage. In conclusion, the portfolio restricting short selling is the more desirable or practical portfolio as an investment.

(2) We also plot the capital market line and efficient frontier as shown in graph 3.1 and 3.2.

Task 4

Our workings and findings

(1) We Divide the sample in first half (Jan. 2008-Dec. 2012, “in-sample period”) and second half Jan. 2013-Dec. 2017, “out-of-sample period”).

(2) Then we construct equally-weighted portfolios and market capitalization-weighted portfolios of the 30 stocks for both sub-sample periods.

We compare the risk-adjusted performance of these four portfolios using Sharpe ratio as shown in table 4.1

As we can see, equal weighted portfolio outperforms market capitalization weighted portfolios in both in-sample and out-of-sample period since its Sharpe ratios are higher than later. Further, the Sharpe ratio difference even increases in out-of-sample period and this shows that equal-weighted portfolio is more stable.

Task 5

Our workings and findings

(1) We repeat steps in task 3 but this time we re-estimate the market portfolio and the minimum variance portfolio using the first half of the data only (Jan.

2008-Dec.2012). The weights of both portfolios are shown below in table 5.1 and 5.2.

(2) We then Use the weights of the market portfolio and the minimum variance portfolio from the first half of the data and apply those to the second half of the data (Jan. 2013-Dec. 2017)

As we can see, the market portfolio outperforms all other strategy in both the in-sample and out-sample period, but it actually performs worse than pervious period (with global financial crisis), so this is a surprise to us and may show that the estimated weight for market portfolio only suits for the similar market conditions. If the market conditions change, the market portfolio can not perform as good as it was. The Sharpe ratio difference among four strategies also shrink in out-of-sample period. As Victor DeMiguel says in his paper, Optimal Versus Naive Diversification: How Inefficient is the 1/N Portfolio Strategy?, the gain from optimal diversification is more than offset by estimation error with time. We are also amazed by the performance of naive equal-weighted portfolio as we will discuss next.

Further, As ROGER C LARKE et al. presented in their paper, Risk Parity, Maximum Diversification, and Minimum Variance: An Analytic Perspective, from 1986 to 2012, equal-weighted portfolio outperforms market capitalization-Weighted Portfolio but underperforms Minimum Variance Portfolio in risk-adjusted basis. But in our results, we can see that equal-weighted portfolio performs better than minimum variance portfolio in resurgence period but worse than minimum variance portfolio in recession. We think this makes sense since the minimum variance portfolio limits the big downside risk but also restrains the upside returns. This is why our results are not what we have expected


After the long-term working on financial modeling, and analysis on different portfolio strategy (such as minimum Variance, maximum Sharpe ratio, equally-weighted and market capitalization-weighted), we have learnt how to merge theory with practice and conduct further research in empirical literature to solve our questions. We sincerely appreciate this project and will continuously reflect on our work throughout our future life.


1. Clarke, Roger G and de Silva, Harindra and Thorley, Steven, Risk Parity, Maximum Diversification, and Minimum Variance: An Analytic Perspective (June 1, 2012). Journal of Portfolio Management, Vol. 39, No. 3, pp. 39-53 (Spring 2013). Available at SSRN: or

DeMiguel, Victor and Garlappi, Lorenzo and Uppal, Raman, Optimal Versus Naive Diversification: How Inefficient is the 1/N Portfolio Strategy? (May 2009). The Review of Financial Studies, Vol. 22, Issue 5, pp. 1915-1953, 2009. Available at SSRN: or