Week 8 Notes Portfolio Optimisation PDF

Preview

Summary

The weeks summary of what was covered and also all the functions used and additional examples...

Description

Financial Modelling and Data Analysis Week Eight: Portfolio Optimization Following this module, you will be able to:      

Understand what portfolio optimisation means for an investor; Explain the constraints an investor may place on their portfolio; Use the Solver in Excel to optimize a portfolio of stocks; Use matrices to optimize a portfolio of stocks; Calculate a Sharpe ratio; Explain what an efficient portfolio frontier is and how to calculate it given a set of inputs.

Portfolio Management: Key Concepts Investors are considered to be risk averse, meaning they will demand a premium for holding risk (excess returns). Rational investors wish to maximise return and minimise risk. To minimise risk, one should avoid holding just one asset (the equivalent of having ‘all your eggs in one basket’). We can therefore minimise risk through diversification of our investment holdings. A portfolio is a collection of stocks and other assets. By holding a portfolio of assets, rather than just one stock or asset, we can reduce our risk through diversification. The risk left over is referred to as systematic risk and is the ‘beta’ we measure in the CAPM. This is market-wide risk that remains after we have diversified away the individual risks of each asset in our portfolio. When we build a portfolio, we have several choices. How do we choose the assets and stocks to invest in that will maximise our return or minimise our risk? It makes sense that for a given set of assets, there exist combinations that result in better outcomes than others. Also, how much of each asset should we hold to maximise our return or minimise our risk? That is, how do we choose the weighting of each asset in our portfolio? This process is known as portfolio optimisation. We will use mean-variance analysis to optimise our portfolio. Mean-Variance Analysis: Basics Built on the premise of risk and return: the mean refers to the return of the portfolio, the variance measures the risk of the portfolio. Portfolio return is calculated as a weighted average of expected returns for each stock, where we have k assets in the portfolio and the weights (w) must sum to 1. This ensures there is no money left unallocated.

The equation is E rp   w1 E( r1 )  w2 E( r2 )    wk E( rk )

Portfolio variance is more complicated to calculate, as we also need to think about how each asset or stock in the portfolio is related to each of the others – the covariance between each of the assets. That is, how each asset moves in relation to each of the others. For the bivariate (two) asset case, we can calculate the variance of the portfolio easily: Var(𝑅𝑝 ) = 𝑤12 Var(𝑅1 ) + 𝑤22 Var(𝑅2 ) + 2𝑤1 𝑤2 Cov(𝑅1 , 𝑅2 ) However, when we have more than 2 assets (which is commonly the case, in practice this number can vary widely from 15 to 30 to hundreds!) the equation becomes unwieldly. We need a different approach: matrices! Mean-Variance Analysis: Matrix Approach Suppose we have 2 vectors: the first contains the average returns of the assets in the portfolio, over the sample period. The second contains their relative weights within the portfolio. Remember, our weights always sum to a total of 1. Then the portfolio return can be calculated as

𝐏𝐨𝐫𝐭𝐟𝐨𝐥𝐢𝐨 𝐫𝐞𝐭𝐮𝐫𝐧 = 𝐌𝐞𝐚𝐧𝐬𝑇 × 𝐖𝐞𝐢𝐠𝐡𝐭𝐬

Note, as vectors the order (size) of Means and Weights is 𝑛 × 1.

In Excel, we use the TRANSPOSE and MMULT functions. As we are multiplying an 1 × 𝑛 vector with an 𝑛 × 1, the result will be a scalar (our single value return). Recall this is the outer product (the outer 1 in the size).

For portfolio variance, we need to first generate the variance-covariance matrix for our portfolio of assets. First, using our series of returns for each of the n assets over the sample period, we calculate the mean return for each asset, we’ll call these averages or means 𝑟𝑛 . Then, we generate a matrix of excess returns by subtracting 𝑟𝑛 from the corresponding asset’s returns. In matrix notation, this looks like: 𝑟11 − 𝑟1 ⋮

𝐀=[ 𝑟1𝑚 − 𝑟1

𝑟21 − 𝑟2 ⋱

𝑟2𝑚 − 𝑟2

where we have M periods in our data sample.

⋯

⋯

𝑟𝑛1 − 𝑟𝑛 ⋮

𝑟𝑛𝑚 − 𝑟𝑛

]

Once we have our matrix of excess returns, A, we generate the variance-covariance matrix (let’s call it D) using 𝐀𝑇 × 𝐀 𝐃= 𝑀−1

Finally, we can calculate the variance of the portfolio!

𝐏𝐨𝐫𝐭𝐟𝐨𝐥𝐢𝐨 𝐕𝐚𝐫𝐢𝐚𝐧𝐜𝐞 = 𝐖𝐞𝐢𝐠𝐡𝐭𝐬𝑇 × 𝐃 × 𝐖𝐞𝐢𝐠𝐡𝐭𝐬

The Excel workbook Portfolio Optimisation Examples.xlsx contains a worksheet titled Variance Covariance Matrix, working through each of the steps we have discussed above. EXERCISE 8.1: Choose 5 companies on the NZX. Using the Yahoo! Finance Website (NZ) get 2 years of daily prices. To gather this data, enter the relevant ticker symbol in the search box at the top of the page, and select your company from the list. Now, along the top of the quote page, click on the Historical Prices link. To get a table of previous prices, select Prices at the top of the table, set the Start Date to two years before today's date (2Y), and click the Apply button. Click the Download to Spreadsheet link at the bottom of the table to download a file with this data. You may have the choice of either saving the file or opening it directly in Excel. It is easier to let it open in Excel. Otherwise, save the .csv (comma separated variables) file, and then open it with Excel. It shouldn't need any further processing other than some formatting. Collate your 5 companies on one worksheet. 1. Calculate the variance-covariance matrix. 2. Assume you have invested equally in each company. Calculate the portfolio return and variance of your equally-weighted portfolio.

When we discuss the mean and variance of a portfolio, we commonly talk about returns (mean) and volatility (risk/variance). Strictly speaking, the volatility of a portfolio is measured by the standard deviation of the returns. Standard deviation is the square root of the variance, which we calculated above.

Mean-Variance Analysis: The Solver Approach When we come to optimise portfolios and various constraints we may choose to impose, the Excel Solver toolpak is very helpful. The Solver has in-built flexibility, allowing us to impose a number of constraints easily and change them if necessary.

You’ll find the Solver under the Data Tab on the Excel ribbon. If it is not there, you’ll need to add it in by going to File > Options and selecting Solver from the list of Add-ins. If you click Solver, a dialogue box will appear (shown below). It asks you to ‘Set Objective’, ‘By Changing Variable Cells’ ‘Subject to the Constraints’. We’ll go through each now.

Objective Function: This is either cell containing the calculated variance of the portfolio (as above) or the cell containing the calculated Sharpe ratio (we’ll get to that in a moment). You’ll need to choose ‘Max’, ‘Min’ or set a specific value (depending on whether you are maximising the Sharpe ratio, minimising the variance or setting one or the other to a specific value). By Changing Variable Cells: You want Solver to adjust the weights of the assets in your portfolio, so you’ll have the range of weights here. Subject to Constraint(s): You need to sum your weights to 1, at least. Plus you may want to impose no short selling or limit it (we’ll get to this too). The Excel workbook Portfolio Optimisation Examples.xlsx contains worksheets titled Minimum Variance and Optimal, working through each of the steps we discuss in the following sections.

To start you off, and get a feel for the Solver and its capabilities, the following videos will demonstrate how to use it to generate a Minimum Variance Portfolio and Optimal Portfolio. Watch these, and then we’ll elaborate on the concepts. Solver: Minimum Variance Portfolio Solver: Optimal Portfolio

Sharpe Ratio In the videos and above, a common portfolio performance metric known as the Sharpe ratio was used to solve for the optimal (best) portfolio. We are going to use the Sharpe ratio to find the combination of assets that gives us the highest return for the optimal level of risk (that is, the optimal allocation of our capital). We need to maximise 𝑆𝑅 =

(𝑅𝑃 − 𝑅𝑓 ) 𝜎𝑃

where 𝜎𝑃 is the standard deviation of the portfolio (see discussion of volatility above); 𝑅𝑓 is the risk-free rate; and, 𝑅𝑃 is the return of the portfolio. Using Solver, set the Objective Function to be the cell where you calculate the 𝑆𝑅 and select ‘Max’.

The Excel workbook Portfolio Optimisation Examples.xlsx contains a worksheet titled Optimal, working through each of the steps for the Solver Approach. We’ll practice using the Sharpe ratio in the context of portfolio optimisation throughout the exercises and examples.

Short selling Depending on the particular portfolio we want to construct, and investor preferences, we may need to impose additional constraints on our portfolio. Sometimes, we only have the opportunity to BUY assets (‘go long’) and cannot ‘go short’, or sell an asset to generate funds to invest in another asset. These are referred to as short selling limits or no short selling at all. No short selling constrains all asset weights to be positive, for example $C$11:$C$14 >= 0 (these cells use the Portfolio Optimisation Examples.xlsx worksheet titled Optimal references). The video demonstrations above illustrated how to do this using Solver.

Minimum Variance Portfolio An alternative to optimizing the portfolio based on the Sharpe ratio is to find the minimum variance portfolio. We simply wish to find the portfolio that gives the lowest possible variance: that is, minimises 𝜎𝑃 2 – the variance of the portfolio.

Using Solver, as per the video demonstrations, you set the variance as the Objective Function and select ‘Min’ to minimise.

We can also use matrices to solve for the minimum variance, using the formula: 𝐖𝐞𝐢𝐠𝐡𝐭𝐬 = (𝟏 ∗𝐃−𝟏 )/(𝟏 ∗𝐃−𝟏 ∗ 𝟏𝐓)

where 𝐃 is the variance-covariance matrix (see discussion above); and, 1 is a row vector of ones. The Excel workbook Portfolio Optimisation Examples.xlsx contains a worksheet titled Minimum Variance, working through each of the steps. EXERCISE 8.2: Using your data and solution to Exercise 8.1, complete the following: 1. Use the Solver to calculate the optimal portfolio based on maximising the Sharpe ratio; 2. Use the Matrix approach to solve for the Minimum Variance portfolio.

Optimal Portfolio: Putting it all together  We can use all our skills gained in the previous sections and optimize our portfolio by either maximising our return or minimising our risk. The method we choose depends on the investor’s preferences: perhaps they want to earn a certain level of return (say 10%) or maybe lower the volatility to below a particular threshold. To generate the optimal weightings of a set of assets in a portfolio, we can use Solver or the matrix formula: 𝐖𝐞𝐢𝐠𝐡𝐭𝐬 =

𝐃−1[𝐄(𝐑) − 𝑅𝑓 ] Sum{𝐃−1 [𝐄(𝐑) − 𝑅𝑓 ]}

where 𝑅𝑓 is the risk free rate; 𝐃 is the variance-covariance matrix; and, 𝐄(𝐑) is a vector of mean returns for each asset, over the sample period. The Excel workbook Portfolio Optimisation Examples.xlsx contains a worksheet titled Optimal, working through each of the steps for the Matrix Approach. Of the two approaches, the Solver is more flexible as we can build in various constraints to limit short selling, to limit the level of short selling etc. To interpret our results, we can say that the optimal asset weights are (for example) 23% AIA, 37% CEN, 13.5% FPA and 26.4% EBO.

EXERCISE 8.3: Using your data and solution to Exercise 8.2, use the matrix approach to solve for the optimal portfolio. What are the optimal asset allocations (weights) for your portfolio? Now impose a short selling constraint. Does your solution change?

Efficient frontier The efficient frontier represents all possible optimal portfolios at varying risk free rates. In the diagram below, the efficient frontier is the set of all portfolios that are above the red dotted line.

To calculate the efficient frontier line we need to calculate the optimal portfolio for a number of risk free rates from very low through to high. Then we plot the resulting portfolio returns and variances. So we must solve the optimal portfolio a number of times for varying risk free rates. It is important to note that every constraint (that is, short selling / no short selling / different levels of short selling) have a different efficient frontier. As you can imagine, the process can become tedious. Luckily, you have VBA! It is easier to write a VBA program to generate the optimal portfolios automatically, than taking the time to run the Solver many times manually....