Investment

1. Introduction

As Certified Financial Planners in a major investment bank, we are aimed to provide our clients the optimal portfolio for their investment. One of our client seeks advice for his investment into nine stocks and bank deposit of $10 million cash. In this report, we will show our suggested portfolio to our client which can achieve an expected return of 0.60% per month as well as minimize the risk exposure at the same time. Our calculation is based on “Markowitz Portfolio Selection Model” using excel as a tool. Later, we will give our advice, notice and warnings on implementing our suggested investing proposal. Lastly, we will explain the modification of the procedure incorporate with latest news.

1. Recommendation of the optimal portfolio

2.1 Estimation of parameters

According to historical data, we can use the 61 data of return index for each asset to calculate 60 data of rate of return per month. And then we figure out the expectation, variance and standard deviation of rate of return.

Then with parameters above, we can get the covariance and correlation efficiency of any pairs of assets.

Covariance table

[pic 1]

Correlation efficiency table

[pic 2]

1. Minimum variance frontier without short selling

Then we can put these processed information into excel and use “solver” to calculate the minimum portfolio variance given fixed portfolio expectation and restricted condition (each weighting is equal to or greater than 0; the sum of weighting is equal to 1; and the portfolio expectation is equal to the given number).We also get weightings of the nine individual stocks in this situation. After changing expectation, we can use “solver” again to get the new pair of data.

The followings are the table of weights of individual stocks for those points for constructing minimum variance frontier and the graph of minimum variance frontier.

[pic 3]

[pic 4]

[pic 5]

1. Determine the optimal risk portfolio – Tangency portfolio (T)

We can use “solver” to calculate the maximize portfolio expectation given the market risk-free rate (0.05% per month) and restricted condition (each weighting is equal to or greater than 0 and the sum of weighting is equal to 1).We also get weightings of the nine individual stocks in this situation and Sharpe ratio.

[pic 6]

1. Calculate the weighting of risk-free asset and risky assets in the overall investment.

From above, we know that E(Rp)=0.65% and E(Rf)=0.05%. Let x be the weighting of risk-free asset. We can get the function: 0.05%x+0.65%(1-x)=0.60%. After solving it, we can get x=1/12, which means that the weighting of risk-free asset is 1/12 and the weighting of risky portfolio is 11/12. Now we can get the weighting of these ten assets.

[pic 7]

Under this combination, the total return is 0.60%, the total variance is still 0.001186433 and the total standard deviation is still 0.034444642.

1. Evaluation on our suggested portfolio.

3.1 Performance Comparison with the whole market

[pic 8]

The graph above shows the change of realized monthly return from 2011.2 to 2016. 1. The blue curve is the realized return of the market(represented by the return of Heng Seng Index). The red curve is the realized return of our suggested portfolio. As it shown in the graph, our suggested portfolio can outperform the market. For example, in 2013.1, the realized return of the market is -2.97% which is lower that of our suggested portfolio which is 0.12%.