Let’s say you have a client who wants to construct a stock portfolio, and she chose the following stocks:
- Apple (AAPL)
- Boeing Airlines (BA)
- Netflix (NFLX)
- Tesla (TSLA)
Your client has stated that the objective of maximizing the Sharpe ratio of her portfolio. Her maximum risk tolerance is based on a standard deviation of 30% per annum.
You collected the monthly data of the stocks mentioned above and computed the monthly returns (from 1 Jan 2015 to 1 Dec 2019) on a continuously compounded basis. The data file can be found: here. For the purpose of this exercise, you assume the risk-free rate is 4% per annum.
Use Excel to compute the optimal weights for each stock in order to achieve the client’s objective.
- Compute the covariance of each stock.
- Compute the average monthly return of each stock.
- Based on an initial weight, we will compute the portfolio’s monthly return and standard deviation.
- Then, we will annualize the portfolio return and standard deviation.
- We then use Solver to find the optimal weights based on the client’s objective.