Let's give an example of forming an investment portfolio according to G. Markowitz's model using Excel; we will analyze the advantages and disadvantages of this model in the modern economy and ways to solve them.
An investment portfolio is a collection of various financial instruments that satisfy the investor’s goals and, as a rule, consists of creating such combinations of assets that would provide maximum profitability with a minimum level of risk.
Take our proprietary course on choosing stocks on the stock market → training course
Closing rates
In calculations we will use daily closing rates closeData = data.Close closeData
Course schedules
import matplotlib.pyplot as plt for name in closeData.columns: closeData[name].plot() plt.grid() plt.title(name) plt.show()
Investment Management Strategies
There are two main strategies: active and passive
Active management requires that the investor must constantly keep his “finger on the pulse”: study news, analytics, expert opinions and other information and rebalance his portfolio in accordance with it.
Passive tactics involve following the “buy and forget” principle. An investor does not need to constantly monitor the market. He mainly invests for the long term in reliable instruments that do not require constant adjustments. It's easier than sticking to active tactics.
Graphs of relative changes in rates
for name in dCloseData.columns: dCloseData[name].plot() plt.title(name) plt.grid() plt.show()
Construction of G. Markowitz's portfolio in Excel for the Russian stock market
In this article, using Ecxel tools, we create an optimal portfolio according to G. Markowitz for shares of the Russian stock market.
{module 297}
A portfolio is a set of financial assets combined together to achieve the investor’s goals, to maximize profits and minimize losses. In the Markowitz model, only standard portfolios are acceptable, portfolios without short positions (no sales), that is, a portfolio consisting only of purchased shares. Hence the first restriction that is imposed on the portfolio is the positive shares of all securities (xi).
(1)
The second restriction is that the sum of all shares of securities must be 1, this is the rule for normalizing shares. Formula 2 shows this limitation.
(2)
Also, the portfolio's return will look like the sum of the returns of individual stocks with the selected weighting coefficients. Since each investor is trying to maximize the return received, it will be necessary to maximize this objective function. As a result, it will look like formula 3.
(3)
In addition to profitability, an investor must also take into account the risk associated with a particular stock. Risk according to G. Markowitz is expressed as the standard deviation δi of each stock. The value of δр is the level of acceptable risk for the investor. In addition to taking into account the standard deviation of individual stocks, it is necessary to take into account the correlation between stock returns - rij. The correlation in our case for the Markowitz model is zero. As a result, the risk of the entire portfolio is represented by formula 4.
(4)
The economic-mathematical model of the problem of forming an optimal portfolio of shares of maximum efficiency in which the risk of the portfolio does not exceed a given value δр, and taking into account all restrictions on the portfolio, will take the following form (5):
(5)
{module 297}
The inverse portfolio optimization problem comes down to choosing a portfolio structure whose profitability is higher than or equal to a given value mp, and whose risk is minimal. The economic and mathematical model of the problem in this case will take the form (6):
(6)
An example of compiling a Markowitz portfolio for the Russian stock market
For example, let's take the shares of 4 Russian companies Gazprom (GAZP), Dalsvyaz (DLSV), Surgutneftegaz (SNGS) and Rosneft (ROSN). We will build an optimal portfolio based on quotes. To find the optimal portfolio according to Markowitz, we will use Excel tools and the Solver component. First, let's calculate the daily return for each stock for one year from 05/20/2009 to 05/20/2010. The formula for calculating daily profitability (mj) is presented (7): ;
(7)
Where: Рj – share price at the end of the current day; Рj-1 – share price for the previous day. The result should be the following table of daily returns for each stock:
To calculate the profitability for each stock, it is necessary to find the average profitability of the stock for the selected period, in this case 1 year.
(8)
Having calculated, the average daily return for the entire period was for GAZP = -0.02%, DLSV = 0.28%, SNGS = 0.05% and ROSN = 0.08%. Since the average profitability of Gazprom is negative, this share will not be included in the portfolio. In addition to the profitability, it is necessary to calculate the risk of these shares; for this, we calculate the standard deviation of the daily returns of the shares using formula (9).
(9)
For Dalsvyaz (DLSV) δ1 = 1.73%, Surgut Neftegaz (SNGS) δ2 = 1.98% and Rosneft (ROSN) δ3 = 2.05%. Let's create an equation to find the optimal portfolio. We will also set the acceptable maximum risk level to 0.15%.
(10)
We will enter the obtained data into a table to calculate the shares (xi) of each stock in the portfolio. All that remains is to solve the resulting equation and calculate the shares of each share; to do this, we will use the “Solution Search” add-in built into the Excel package.
After launching the “Solution Search” add-on, we will set the objective function, this is the profitability of the entire portfolio. After that, let's check the box to maximize the value of this objective function. The cells to change will correspond to the percentage of shares that need to be found. It is also necessary to impose restrictions on the fact that the sum of all shares is equal to 1, and that each share is not negative and the total risk of the portfolio is less than 0.15%. In the cell with total risk (D7) the following formula is written: =ROOT(0.0301*D2*D2+0.0393*D3*D3+0.0421*D4*D4) The objective function in cell (F3) is equal to: =D2 *B2+D3*B3+D4*B4 In cell (D6) the restriction for the portfolio is written: =SUM(D2:D4) After the work done, the shares in the investment portfolio for each stock will be determined.
The calculation of the optimal portfolio according to G. Markowitz is presented in the figure below. The optimal portfolio will consist of 85.38% shares of Dalsvyaz (DLSV), 3.66% shares of Surgutneftegaz (SNGS) and 10.96% shares of Rosneft (ROSN). The return on the entire portfolio will be 0.24% with a total predetermined portfolio risk of 0.15%.
{module 297}
Author: Zhdanov Ivan www.beintrend.ru
Cloud of portfolios
Let's generate a lot of portfolios and display the results on a risk-return chart. Let's find the parameters of the optimal portfolio based on minimum risk and maximum Sharpe ratio. Let's compare with the data of the average portfolio. risk = np.zeros(N) doh = np.zeros(N) portf = np.zeros((N,cnt)) for n in range(N): r = randPortf() portf[n,:] = r risk [n] = riskPortf(r) doh[n] = dohPortf(r) plt.figure(figsize=(10,8)) plt.scatter(risk*100,doh*100,c='y',marker=' .') plt.xlabel('risk, %') plt.ylabel('return, %') plt.title(“Portfolio Cloud”) min_risk = np.argmin(risk) plt.scatter([(risk[min_risk] )*100],[(doh[min_risk])*100],c='r',marker='*',label='minimal risk') maxSharpKoef = np.argmax(doh/risk) plt.scatter([ risk[maxSharpKoef]*100],[doh[maxSharpKoef]*100],c='g',marker='o',label='maximum Sharpe ratio') r_mean = np.ones(cnt)/cnt risk_mean = riskPortf(r_mean) doh_mean = dohPortf(r_mean) plt.scatter([risk_mean*100],[doh_mean*100],c='b',marker='x',label='average portfolio') plt.legend( ) plt.show()
Let's display the data of the found portfolios.
import pandas as pd print('———- Minimum risk ———-') print() print(“risk = %1.2f%%” % (float(risk[min_risk])*100.)) print(“ profitability = %1.2f%%" % (float(doh[min_risk])*100.)) print() print(pd.DataFrame([portf[min_risk]*100],columns=dCloseData.columns,index=[' shares, %']).T) print() print('———- Maximum Sharpe ratio ———-') print() print(“risk = %1.2f%%” % (float(risk[maxSharpKoef] )*100.)) print(“yield = %1.2f%%” % (float(doh[maxSharpKoef])*100.)) print() print(pd.DataFrame([portf[maxSharpKoef]*100],columns =dCloseData.columns,index=['shares, %']).T) print() print('———- Average portfolio ———-') print() print(“risk = %1.2f%%” % (float(risk_mean)*100.)) print("return = %1.2f%%" % (float(doh_mean)*100.)) print() print(pd.DataFrame([r_mean*100],columns= dCloseData.columns,index=['shares, %']).T) print() ———- Minimum risk ———- risk = 1.80% return = 0.59% shares, % AAPL 53.890706 AMD 12.793389 BAC 4.117541 F 16.547201 GE 10.945462 PLUG 1.705701 ———- Maximum Sharpe ratio ———- risk = 2.17% return = 0.88% share, % AAPL 59.257114 AMD 8.317192 BAC 2.049882 F 8.689935 GE 4.772159 PLUG 16.913719 ———- Average portfolio ———- risk = 2.33 % yield = 0.68% share, % AAPL 16.666667 AMD 16.666667 BAC 16.666667 F 16.666667 GE 16.666667 PLUG 16.666667
Definition of risk measure
So, we took the random (from the point of view of probability theory) value X as the effectiveness of our chosen security.
Then the measure of risk of this quantity will be its DISPERSION
. In the language of formulas, the value of the risk measure (variance) can be written as follows:
Z =
U {( X – m)2}
.
The Z value is always greater than or equal to zero.
The lower the Z value, the lower the degree of risk inherent in the corresponding security (or - there is no big difference - a portfolio of securities).
If Z = 0, we are dealing with a risk-free portfolio.
conclusions
We repeated the classic method of calculating the shares of the investment portfolio.
We got very concrete results. Portfolio optimization using the Markowitz method involves saving parameters in the future (correlations between individual instruments and their level of profitability). But this is not guaranteed. This will be tested in future work.
It is clear that you should not expect a positive result from the above check. But then you can look for ways to modify the Markowitz method to obtain a more guaranteed income in the future. Here is a topic for another study.
Markowitz portfolio theory - what is it?
The book in Russian “Markowitz Portfolio Theory” was published in 2013 and is aimed at a competent approach to investing. The main approach is to find among the variety of instruments those that provide high returns with low risk. The theory itself has been known and has been actively used for almost 60 years, albeit with minor modifications.
An interesting point is that the key postulates of the theory were formulated in the 50s, and during this time they not only did not lose popularity, but became more relevant. Globally speaking: Markowitz's theory of portfolio investment and preliminary market analysis encourages owning assets rather than playing in stock markets. Sometimes portfolio theory is identified not only with Markowitz, but also with Sharp - American economists. W. Sharp proposed his market-based, less labor-intensive index theory of asset valuation later than Markowitz in the 60s, but also received the Nobel Prize in 1990. In addition to being less defensive for the investor, his theory looks at the performance of each security relative to the entire market, and his colleague suggests comparing them to each other.
