RISK FINANCIAL METHODS ASSIGNMENT 2
AUTHOR: Gabriele Calabrò MODULE LEADER: Masar Hadla SESSION 2016/2017 TRIMESTER 2 SPREADSHEET MODELLING SUBMISSION DEADLINE: 17/MAY/2017
Table of Contents INTRODUCTION
2
EXPECTED RETURN
2
VARIANCE AND STANDARD DEVIATION 3
CORRELATION MATRIX 4
PORTFOLIO
5
OPTIMIZATION AND EFFICIENT FRONTIER
LIMITATIONS OF THE MODEL
8
10
CONCLUSION 10
REFERENCES
11
1
INTRODUCTION In this paper will be documented and analyzed the Efficient Frontier concept. In this regard, a detailed description of expected return, standard deviation, variance and correlation will be given. Lastly, some limitation of the spreadsheet model will be discussed.
EXPECTED RETURN Since the majority risks is due to movements in share prices and therefore, expected value of them, it is extremely important to define the expected return of a variable (share price) as:
Daily simple rate of return
t+1=
S t+1 −S t St ¿ r¿
s t+1 −1 st
=
(1)
Daily continuously compounded or log return (2)
Rt +1=ln ( st +1 ) −ln(st ) Where ln (*) is the natural logarithm.
However, daily returns have small autocorrelation, consequently, returns are quite impossible to forecast from their own series. Indeed, the normal distribution has a thinner tail than the unconditional distribution of daily returns than. Moreover, the stock market is sometimes characterized, by plunges but not by recovers of the same size. Therefore, the return distribution is asymmetric.
This is the reason why the spread sheet considers monthly return rather than the daily one. Since Moreover, since the share price starts from January, the fist calculation is on February. However, the expected return concept is generated from the expected value of a variable, defined as average value:
E( x )=∑ Pi Xi
(3)
i ϵN
2
Hence, the expected return is a mean of possible returns multiplicated by their probability to occur. (Peter F. Christoffersen, 2010)
As regard with our data, clearly the expected returns are volatile. Indeed, shares such as Admiral and Rio tinto plunge respectively from 12.69% to -2.55% and from 8.67% to -2.50%.
VARIANCE AND STANDARD DEVIATION Variance indicates the dispersion of the variable X round its average E(X). (FRM, 2014) 2
∑ Pi ( Xi−E ( x ) )
hence
iϵ N
(4)
2
E ( X −E ( x ) )
Where the dispersion is the gap among the average and the actual value. (Bains, 2011) As regard the standard deviation is defined as the square root of the variance: (5)
√ VAR
SD =
Indeed, by developing the integral between – and + infinite of f ( x )
in dx :
+∞
V(X) = E ( X −E ( x ) )
2
=
∫ ( x−E ( x ) )2 f ( x ) dx −∞
=
(6)
It is possible to find (7): (7)
¿ E ( x 2) −E2 ( x ) = And by highlighting E (8):
(8)
2
¿ σ (x ) In our example the SD is calculated first monthly and after annual. To calculate it the following formula has been used: Image 1: Standard deviation monthly
Source: FRM, final excel, 2017
Consequently, it is easy to find the variance since it is just the square of the SD: Image 2: variance per month
Source: FRM, final excel, 2017
3
Moving on the annual variance it is calculated by multiplying the monthly one times 11 or 12 depending on t (months). Therefore, by re-squaring the annual variance it is possible to find the annual SD. Lastly, the overall SD is calculated as standard deviation average (2012:2016) individually for each group, which is high considering 52% of Barclays or 35% of Rio Tinto (riskier investments).
CORRELATION MATRIX The Correlation indicates to what extent two or more variables move together. (P. Gagliardini, 2005) It is possible to have:
Positive correlation: indicate two or more variables that increase or decrease together (same signs) Negative correlation: when given two variables A and B. When A, increase B decrease and
The level of correlation among two variables is calculated by using the linear correlation coefficient r defined as: r=
(9)
Cov (x , y ) σxσ y
The correlation has a range of (-1, +1) It is possible to have a negative correlation from -1 to -0.1, no correlation for 0 and positive correlation from +0.1 up to +1. Naturally, when value like -1 and +1 are touched the variable are perfectly correlated. Consequently, each minimal movement in one of them will cause same or opposite movement in the other variables. As regard with the excel model the correlation has been used to analyze the eventual relation between the different firms and it has been calculated by using the following formula: Image3: Correlation matrix
Source: FRM, Final excel, 2017
Obviously, to analyze the correlation between several variables a correlation matrix is needed. As a normal matrix, the latter has rows and columns and the assets on two sides. Therefore, like in a naval battle, each cell has a precise combo of two variables (companies). 4
The most streaking feature of our matrix is 0.4927 positive correlation between Sainsbury and Morrison, which make sense since both companies are in the same market sector. On the other hand, the strongest negative correlation is between Morrison and Barclays -0.0411. The least correlated companies are Rio Tinto and Glaxo with 0.0035.
PORTFOLIO A grouping of investments all owned by the same person or enterprise.( JA Carrol, 1996) Image 4: classic investments situation
ELEVATED RISK HIGH RETURN
MINIMAL RISK MAX RETURN
MINIMAL RISK LOW RETURN
Source: GFC, how to avoid the next financial crises, 2017
The Image 4 show the classic investment situation in which investors try to reach the blue circle (optimal solution). Consequently, investors try to reach the optimal solution by taking in their portfolio risky investment to get high ret and secure investment to balance the risk they have from the other. In fact, in the excel portfolio there are risky investments with a high SD such as Barclays 52% and less risky such as Sainsbury or Morrison 16%. Therefore, it is possible to think that is convenient to invest in firms with low SD, instead as important as the SD is the Expected return. That is the reason why it is important to study the rapport between Expected return and SD called Sharpe ratio: 5
STANDARD DEVIATION Âż EXPECTED RETURN −RISK FREE RETURN SHARPE RATIO= Âż
(10)
Where the risk - free return is the minimum return that an investor would expect from a risk - free investment. (LP Hansen, 1983) In overall there are two different type of risk: 1. Systematic risk: also, known as non-diversifiable risk, refer to the whole market or segment. (Market Risk1) (A Beja, 1972) 2. Non - systematic risk: also, known as diversifiable risk is carried by the firm you invest in. (liquidity risk2, credit risk3 and operational risk4) The main difference between them is the possibility to eliminate the unsystematic Risk but not the Systematic one. Indeed, the second one can has a micro nature and therefore it can be controlled by diversification and hedging. The diversification process consists of pick up different type of stocks, from different firms, sectors (market diversification) and/or countries (currency diversification). As regard the hedging concept it relies on the diversification. In fact, investors may choose to diversify by taking stocks that are negatively correlated to balance eventually losses or by acquiring stocks with low correlation and therefore not influenceable by other shares prices. In the excel file the portfolio values: Expected return, SD and Sharpe ration has been calculated as follow. Image 5: Portfolio expected return
1 Market risk: the risk to a financial portfolio from movements in market prices such as equity prices, foreign exchange rates, interest rates and commodity prices.
2 Liquidity risk: The risk from conducting transactions in markets with low liquidity as evidenced in low trading volume, and large bid-ask spreads.
3 Credit risk: the risk that a counter-party may become less likely to fulfill its obligations in part or in full on the agreed upon date.
4 Operational risk: the risk of loss due to physical catastrophe, technical failure and human error in the operation of a firm, including fraud, failure of management and process errors.
6
Source: FRM, final excel, 2017 As image 5 show a matrix multiplication between the transpose equal weight and the expected return has been done. (1x9 = 9x1 ďƒ 1x1) Moving on the SD: Image 6: SD portfolio calculation
Source: FRM, final excel, 2017
It is possible to see from the image above that a matrix multiplication has been done here as well. In fact, the transpose wSD5 has been multiplicated by the correlation matrix (9x9 * 9x1 = 9x1) and the resulting matrix has been multiplicated by wSD again to have the one cell result (1x9 * 9x1 = 1x1) The expected return is equal to 1.08% while the SD is equal to 2%. Consequently, the Sharpe ratio is (1.08%/2% = 53%) However, this combination of exp. Ret. – SD is not optimal because it is possible to have a higher level of return for the same level of risk 2%.
5 wSD = is the overall SD value per year times the respective weight for each investment.
7
OPTIMIZATION AND EFFICIENT FRONTIER Portfolio optimization is the mechanism that allow us to maximize the expected return by choosing the best combination (optimal) of weight of various assets to be held in a portfolio. (P. Krokhmal, 2002) In the excel file SOLVER has been used to optimize a portfolio Exp. Ret. (1.08%). Three constraints have been imposed: 1. Sum of all weights equal to 100% (otherwise the result would increase all the weight above 100%, which means more than we can afford) 2. Each of the weights at least 1% (diversification) 3. SD fixed at 2% (otherwise the SD output would rocket, therefore the portfolio would be too risky) The output result in a portfolio with same level of risk, increased Ret. and different weights. Thus, for the same level of risk 2% the Exp. Ret. increased, of 0.14%. Moreover, by comparing the two Sharpe ratio 53% and 60.80%, clearly portfolio B is the optimal choice. Moving on the efficient frontier invented by Harry Markowitz is defined as a range of optimal portfolios that gives the highest expected return for a defined level of risk (standard deviation) and vice versa. (Markowitz, 1991) Therefore, any point (portfolio) above the EF cannot be reached, while any portfolio under the curve is not an optimal combination of Exp. Ret. and Risk. (H. Scheid, 2010) In the excel file SOLVER has been used to derivate the EF. Firstly, by maximizing the exp. Ret. and changing the SD each time and after by minimizing the SD and changing the Exp. Ret. for the lowest level of risk.
8
The biggest Sharpe ratio is equal to 54,49% (0.66%/1.21%), while the smallest is 18.25% (2.37%/18.25%). Consequently, the different options have been classified to be offered to different type of investors with different risk tolerance from moderate up to aggressive. An important feature to notice is that the EF curve is growing at a decreasing pace. Indeed, by adding the error bars in the chart they became smaller and smaller.
Therefore, for higher levels of risk the total return increase but the discard decrease. Image 7: efficient frontier
Source: FRM, Final excel, 2017
Consequently, there will be a point in which is no worth to increase the risk although higher Exp. Ret. To sum up, it is possible to say that although all the point in the efficient frontier are optimal not all of them are efficient.
9
LIMITATIONS OF THE MODEL Although the model analyzed is superior to a lot of other techniques from the integration of client constraints with portfolio assets point of view, it comprehends either technical and scale inefficiencies: 1. Errors: The main issue with MV optimization consists of the propensity to maximize the effect of errors in the input. Consequently, if unconstrained the process might return output inferior to those with equal weighting. (Richard O. Michaud, 1989) In the Efficient Frontier, the error is of the type (x–y), in which x indicates simple randomness, and y is a positive error standing for technical inefficiency. The whole (x–y) is easy to find for each observation, but an issue is how to divide it into its two parts, x and y. (J Jondrow, 1982) 2. Optimal value: it is calculated straight from the data without need ex ante any clarification of weight. Therefore, it suffers from a lack of analysis regarding the determination of relations between input and output. (W. W. Cooper, 1984) 3. Technical inefficiencies: excessive amounts of inputs and failures to achieve best possible output levels. Thus, difficulties in meeting with widespread acceptance by investors, specifically as real instrument for active equity (RD Banker, 1984) 4. Excel: by applying the model trough excel more time is needed than with other Model and programs. Moreover, requiring a lot of input the risk of missing calculation and/or numbers is elevate. Hence, the possibility of wrong output is higher. (Rowley and Cole, 1995) 5. Simplification: simplified version of the model has been created but often they do not reflect the reality. (P Schmidt, 1982) However, MV optimization has leads the classic theoretical model of investment behavior for roughly 30 years. Moreover, the majority of finance world consider it as the process to choose the optimal portfolio weights, assets allocation and understanding the importance of diversification.
10
Finally, the MV optimization gave us the triple bottom line for a lot of progress and for Capital Asset Pricing Model (CAPM) as well and it helped in clarify the difference between systematic and unsystematic risk.
CONCLUSION By the end of this paper the reader should have the basilar knowledge and understanding of the Efficient Frontier and optimization process, including benefits and drawback of it. The word file purpose is to describe, clarify and support the excel document. Moreover, it provides the needed theoretical information to understand and implement it in the real world, including common mistakes and issues during the implementation process.
REFERENCE Beja, A., 1972. On systematic and unsystematic components of financial risk. The Journal of Finance, 27(1), pp.37-45. Banker, R.D., Charnes, A. and Cooper, W.W., 1984. Some models for estimating technical and scale inefficiencies in data envelopment analysis. Management science, 30(9), pp.1078-1092. Christoffersen, P.F., 2012. Elements of financial risk management. Academic Press. Chan, L.K., Karceski, J. and Lakonishok, J., 1999. On portfolio optimization: Forecasting covariances and choosing the risk model. Review of Financial Studies, 12(5), pp.937-974. Carroll, J.A., Potthoff, D. and Huber, T., 1996. Learnings from three years of portfolio use in teacher education. Journal of Teacher Education, 47(4), pp.253-262. Coles, S., and Rowley, J. (1996) “Spreadsheet modelling for management decision making�, Industrial Management & Data Systems, vol. 96, nr. 7, pp. 17-23. Das, S., Markowitz, H., Scheid, J. and Statman, M., 2010. Portfolio optimization with mental accounts. Edirisinghe, N.C.P. and Zhang, X., 2007. Generalized DEA model of fundamental analysis and its application to portfolio optimization. Journal of Banking & Finance, 31(11), pp.3311-3335. Michaud, R.O., 1989. The Markowitz optimization enigma: Is optimized optimal?. ICFA Continuing Education Series, 1989(4), pp.43-54. Markowitz, H.M., 1991. Foundations of portfolio theory. The journal of finance, 46(2), pp.469-477. Hochreiter, R., 2007. An evolutionary computation approach to scenario-based risk-return portfolio optimization for general risk measures. Applications of Evolutionary Computing, pp.199-207. Krokhmal, P., Palmquist, J. and Uryasev, S., 2002. Portfolio optimization with conditional value-at-risk objective and constraints. Journal of risk, 4, pp.43-68. 11
Jondrow, J., Lovell, C.K., Materov, I.S. and Schmidt, P., 1982. On the estimation of technical inefficiency in the stochastic frontier production function model. Journal of econometrics, 19(2-3), pp.233-238.
12